Investigating Hibernate fetch strategies – A tutorial

Short link: http://wp.me/p5Jvc-8m

It has been nearly 3 or more years since my last post on Object Relational Mapping (ORM) tool Hibernate. There is an inevitable performance overhead associated with ORM tools like Hibernate. The overhead gets further magnified by application of imprudent querying or fetch strategy. Hibernate is a sophisticated ORM tool and a developer needs to understand the programming intricacies/flexibilities provided by Hibernate before applying it in application development. This blog post intends to cover the various data fetching strategies supported by Hibernate.

Before getting into the nitty gritties of hibernate fetch, a quick summary of the setup. I am using the following softwares:

  • Hibernate 3.6.6
  • Oracle Express Edition 10.2.0.1.0
  • Eclipse 3.4.1
  • Windows 7 Operating System

Now let’s move on to hibernate configuration. First the hibernate configuration xml i.e. hibernate.cfg.xml.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
        <property name="connection.username">system</property>
        <property name="connection.password">manager</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">10</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <!--<property name="format_sql">true</property>-->
		<!--<property name="use_sql_comments">true</property>-->
        

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

    </session-factory>

</hibernate-configuration>

I have defined the Oracle database details such as its driver class, connection URL, and credentials. Please customize these values to your environment specifics. Next we look at project dependencies. They are as follows:

  • hibernate3.jar
  • antlr-2.7.6.jar
  • commons-collections-3.1.jar
  • dom4j-1.6.1.jar
  • hibernate-jpa-2.0-api-1.0.1.Final.jar
  • javassist-3.12.0.GA.jar
  • jta-1.1.jar
  • log4j-1.2.16.jar
  • ojdbc14.jar
  • slf4j-api-1.6.1.jar
  • slf4j-lo4j12-1.6.1.jar

Since we are using lo4j for logging, here’s the log4j properties file. Note specific changes have been made in the logging levels to gain insights into the SQL statements executed by hibernate. Refer lines 22 to 28. This is an essential tool for SQL debugging.

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### direct messages to file hibernate.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=d:\hibernate.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'debug' to 'debug' ##

log4j.rootLogger=info, file
#log4j.logger.net.sf.hibernate=debug

### enable the following line if you want to track down connection ###
### leakages when using DriverManagerConnectionProvider ###
#log4j.logger.net.sf.hibernate.connection.DriverManagerConnectionProvider=trace

### log JDBC bind parameters ###
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.hql.ast.AST=info
log4j.logger.org.hibernate.tool.hbm2ddl=warn
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.cache=info
log4j.logger.org.hibernate.jdbc=debug

### log prepared statement cache activity ###
log4j.logger.org.hibernate.ps=debug

Now let’s move on to the meatier stuff of hibernate. To understand Hibernate’s fetch strategy let’s consider a scenario consisting of two entities Person and Address. They share a one-to-many relationship; i.e. one person has more than one addresses. Both the entities need to be made persistent.

I am using annotations to make the classes persistent. Here are the classes with their annotations.

package com.tutorial.hibernate.fetch;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Generated;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.BatchSize;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

@Entity
@Table(name="PERSON",
		schema="SYSTEM")
public class Person implements Serializable {

	private static final long serialVersionUID = 4333499410830154895L;

	private int id = 0;
	private String firstName = null;
	private String lastName = null;

	private List<Address> addresses = new ArrayList<Address>();
	
	@Id
	@Generated(value="assigned")
	@Column(name="ID")
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	@Column(name="FIRST_NAME")
	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	@Column(name="LAST_NAME")
	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	@BatchSize(size=2)
	public List<Address> getAddresses() {
		return addresses;
	}

	public void setAddresses(List<Address> addresses) {
		this.addresses = addresses;
	}
	
	public void addAddress(Address address) {
		address.setPerson(this);
		this.addresses.add(address);
		
	}
	
	@Override
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("**** Person **** ");
		sb.append("Id: ");
		sb.append(this.id);
		sb.append("\n");
		sb.append("First name: ");
		sb.append(this.firstName);
		sb.append("\n");
		sb.append("Last name: ");
		sb.append(this.lastName);
		sb.append("\n");
		sb.append("**** Person **** ");
		sb.append("\n");
		
		return sb.toString();
	}
	
}
package com.tutorial.hibernate.fetch;

import java.io.Serializable;

import javax.annotation.Generated;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name="ADDRESS",
		schema="SYSTEM")

public class Address implements Serializable {

	@Id
	@Generated(value="assigned")
	private int id = 0;
	
	@Column(name="FIRST_LINE")	
	private String firstLine  = null;
	@Column(name="SECOND_LINE")
	private String secondLine = null;
	@Column(name="CITY")
	private String city       = null;
	@Column(name="ZIP_CODE")
	private String zipCode    = null;
	@ManyToOne
	@JoinColumn(name="PERSON_ID")	
	private Person person     = null;
	
	@Column(name="PERSON_ID", insertable=false, updatable=false)
	private Integer personId = null;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}

	public String getFirstLine() {
		return firstLine;
	}
	
	public void setFirstLine(String firstLine) {
		this.firstLine = firstLine;
	}
	
	public String getSecondLine() {
		return secondLine;
	}
	
	public void setSecondLine(String secondLine) {
		this.secondLine = secondLine;
	}
	
	public String getCity() {
		return city;
	}
	
	public void setCity(String city) {
		this.city = city;
	}
	
	public String getZipCode() {
		return zipCode;
	}
	
	public void setZipCode(String zipCode) {
		this.zipCode = zipCode;
	}

	public Person getPerson() {
		return person;
	}
	
	public void setPerson(Person person) {
		this.person = person;
	}
	
	public Integer getPersonId() {
		return personId;
	}
	public void setPersonId(Integer personId) {
		this.personId = personId;
	}
	@Override
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("***** Address ******");
		sb.append("\n");
		sb.append("Id: ");
		sb.append(this.id);
		sb.append("\n");
		sb.append("First Line: ");
		sb.append(this.firstLine);
		sb.append("\n");
		sb.append("Second Line: ");
		sb.append(this.secondLine);
		sb.append("\n");
		sb.append("City: ");
		sb.append(this.city);
		sb.append("\n");
		sb.append("Zip Code: ");
		sb.append(this.zipCode);
		sb.append("\n");
		sb.append("Person Id: ");
		sb.append(this.personId);
		sb.append("\n");
		sb.append("***** Address ******");
		sb.append("\n");
		
		return sb.toString();

	}
	
}

In the Person class refer the annotation defined for getAddresses method.

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	//@Fetch(FetchMode.SELECT)
	//@Fetch(FetchMode.SUBSELECT)
	//@BatchSize(size=2)

This annotation defines one-to-many relationship between Person and Address. Note the commented out lines for the Fetch and BatchSize annotation. This will be changed later as we progress.

Similarly a many-to-one relationship is defined in Address class for person attribute.

	@ManyToOne
	@JoinColumn(name="PERSON_ID")	
	private Person person     = null;

Purely from a relationship definition standpoint, there is no need to define the personId attribute in Address. This has been specifically added to facilitate querying using HQL. More on that later.

Run the following SQL scripts to create tables PERSON and ADDRESS in database.

CREATE TABLE person(id INTEGER, first_name VARCHAR2(20), last_name VARCHAR2(20), 
	PRIMARY KEY(id));
	
CREATE TABLE ADDRESS(id INTEGER, first_line VARCHAR2(20), second_line VARCHAR2(20), 
	city VARCHAR2(20), zip_code VARCHAR2(10), person_id INTEGER, 
	PRIMARY KEY(id), CONSTRAINT ADDRESS_FK1 FOREIGN KEY (person_id) REFERENCES person(id));

To query the database, we need to have data. The DataLoader class creates some records in PERSON and ADDRESS. On successful run of the code we will be creating two persons with four addresses each and one person with two addresses. Here’s the source code of DataLoader class.

package com.tutorial.hibernate.fetch;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class DataLoader {

	public static void main(String[] args) {
		
		SessionFactory factory = HibernateUtil.getFactory();
		
		Session session = factory.openSession();
		Transaction tx = session.beginTransaction();
		Person p = new Person();
		p.setId(1);
		p.setFirstName("Bill");
		p.setLastName("Banks");
		
		Address a1 = new Address();
		a1.setFirstLine("Line 11");
		a1.setSecondLine("Line 12");
		a1.setZipCode("12345");
		a1.setId(1);
		a1.setPerson(p);
		p.addAddress(a1);
		
		Address a2 = new Address();
		a2.setFirstLine("Line 21");
		a2.setSecondLine("Line 22");
		a2.setZipCode("54321");
		a2.setId(2);
		a2.setPerson(p);
		p.addAddress(a2);

		Address a3 = new Address();
		a3.setFirstLine("Line 31");
		a3.setSecondLine("Line 32");
		a3.setZipCode("11221");
		a3.setId(3);
		a3.setPerson(p);
		p.addAddress(a3);

		Address a4 = new Address();
		a4.setFirstLine("Line 41");
		a4.setSecondLine("Line 42");
		a4.setZipCode("22331");
		a4.setId(4);
		a4.setPerson(p);
		p.addAddress(a4);

		Person p1 = new Person();
		p1.setId(2);
		p1.setFirstName("Steve");
		p1.setLastName("Jobs");
		
		Address a11 = new Address();
		a11.setFirstLine("Line 111");
		a11.setSecondLine("Line 112");
		a11.setZipCode("12345");
		a11.setId(5);
		a11.setPerson(p1);
		p1.addAddress(a11);
		
		Address a22 = new Address();
		a22.setFirstLine("Line 221");
		a22.setSecondLine("Line 222");
		a22.setZipCode("54321");
		a22.setId(6);
		a22.setPerson(p1);
		p1.addAddress(a22);

		Address a33 = new Address();
		a33.setFirstLine("Line 331");
		a33.setSecondLine("Line 332");
		a33.setZipCode("11221");
		a33.setId(7);
		a33.setPerson(p1);
		p1.addAddress(a33);

		Address a44 = new Address();
		a44.setFirstLine("Line 441");
		a44.setSecondLine("Line 442");
		a44.setZipCode("22331");
		a44.setId(8);
		a44.setPerson(p1);
		p1.addAddress(a44);

		Person p2 = new Person();
		p2.setId(3);
		p2.setFirstName("Clark");
		p2.setLastName("Jacob");
		
		Address a111 = new Address();
		a111.setFirstLine("Line 111");
		a111.setSecondLine("Line 112");
		a111.setZipCode("12345");
		a111.setId(9);
		a111.setPerson(p2);
		p2.addAddress(a111);
		
		Address a222 = new Address();
		a222.setFirstLine("Line 221");
		a222.setSecondLine("Line 222");
		a222.setZipCode("54321");
		a222.setId(10);
		a222.setPerson(p2);
		p2.addAddress(a222);
	
		session.save(p);
		session.save(p1);
		session.save(p2);
		
		tx.commit();
	}

}

On running the DataLoader class, the following SQL output is generated on the console:

Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: select address_.id, address_.CITY as CITY1_, address_.FIRST_LINE as FIRST3_1_, address_.PERSON_ID as PERSON4_1_, address_.SECOND_LINE as SECOND5_1_, address_.ZIP_CODE as ZIP6_1_ from SYSTEM.ADDRESS address_ where address_.id=?
Hibernate: insert into SYSTEM.PERSON (FIRST_NAME, LAST_NAME, ID) values (?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.PERSON (FIRST_NAME, LAST_NAME, ID) values (?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.PERSON (FIRST_NAME, LAST_NAME, ID) values (?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into SYSTEM.ADDRESS (CITY, FIRST_LINE, PERSON_ID, SECOND_LINE, ZIP_CODE, id) values (?, ?, ?, ?, ?, ?)

Hibernate provides 4 strategies for retrieving data:

  • SELECT
  • JOIN
  • SUBSELECT
  • BATCH

Fetch Strategy : SELECT

At the moment the persistent classes are configured at default settings. Here’s the DataSelectFetch class to retrieve Person and Address records.

package com.tutorial.hibernate.fetch;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

public class DataSelectFetch {

	public static void main(String[] args) {
		SessionFactory fact = HibernateUtil.getFactory();
		Session session = fact.openSession();
		
		Query q = session.createQuery(" FROM Person p ");
		
		System.out.println("Retrieving Data");
		List l = q.list();
		System.out.println("Data retrieved: " + l.size());
		for (Object object : l) {
			Person p = (Person) object;
			System.out.println(p);
			System.out.println("Address retrieval initiated.");
			List<Address> addresses = p.getAddresses();
			System.out.println("Address retrieval complete.");
			int i=1;
			for (Address address : addresses) {
				System.out.println("#### individual address: " + i);
				System.out.println(address);
				System.out.println("#### Address complete");
				i++;
			}
		}

	}
}

The console output generated is as below:

Retrieving Data
Hibernate: select person0_.ID as ID0_, person0_.FIRST_NAME as FIRST2_0_, person0_.LAST_NAME as LAST3_0_ from SYSTEM.PERSON person0_
Data retrieved: 3
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
#### individual address: 1
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

#### Address complete
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, 
addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
#### individual address: 1
***** Address ******
Id: 5
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 6
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 7
First Line: Line 331
Second Line: Line 332
City: null
Zip Code: 11221
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 8
First Line: Line 441
Second Line: Line 442
City: null
Zip Code: 22331
Person Id: 2
***** Address ******

#### Address complete
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, 
addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
#### individual address: 1
***** Address ******
Id: 9
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 3
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 10
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 3
***** Address ******

#### Address complete

There are four SQLs fired. This first one is fired for retrieving all the records in the PERSON table. The remaining three are fired for retrieving addresses records for each Person. This is basically the N+1 problem. The first query retrieves N records from database, in this case N Person records. For each Person a new query retrieves Addresses. Therefore for N persons, N queries retrieve information from ADDRESS table.

Change the annotation definition in the getAddresses method to the following:

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	@Fetch(FetchMode.SELECT)
	//@Fetch(FetchMode.SUBSELECT)
	//@BatchSize(size=2)

Basically uncomment the following line.

	@Fetch(FetchMode.SELECT)

Running DataSelectFetch generates the same console output. This is the SELECT fetch mode. Let’s now move on to the next fetch strategy join.

Fetch Strategy : JOIN

Refer to the class DataOuterJoinFetch.

package com.tutorial.hibernate.fetch;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

public class DataOuterJoinFetch {

	public static void main(String[] args) {
		SessionFactory fact = HibernateUtil.getFactory();
		Session session = fact.openSession();
		
		Query q = session.createQuery(" SELECT p, a FROM Person as p left outer join p.addresses a where p.id = a.personId");
		
		System.out.println("Retrieving Data");
		List l = q.list();
		System.out.println("Result size: " + l.size());
		System.out.println("Data retrieved");
		for (Object object : l) {
			if (object instanceof Object[]) {
				Object[] objArr = (Object[]) object;
				System.out.println("Person");
				System.out.println(objArr[0]);
				System.out.println("Address");
				System.out.println(objArr[1]);
			} else {
				throw new RuntimeException("Unable to process.");
			}
		}

	}
}

Refer to line 15 of the source code. We have established an outer join between Person and Address. Note the usage of the personId attribute here. That is the reason to maintain the attribute. Also note the special attributes added in its annotation definition.

	@Column(name="PERSON_ID", insertable=false, updatable=false)
	private Integer personId = null;

The annotation definition ensures that many-to-one relationship maintains the PERSON_ID column value in the ADDRESS table. This attribute is purely for display purposes only.

The run output of DataOuterJoinFetch is as below:

Retrieving Data
Hibernate: select person0_.ID as ID0_0_, addresses1_.id as id1_1_, person0_.FIRST_NAME as FIRST2_0_0_, person0_.LAST_NAME as LAST3_0_0_, addresses1_.CITY as CITY1_1_, addresses1_.FIRST_LINE as FIRST3_1_1_, addresses1_.PERSON_ID as PERSON4_1_1_, addresses1_.SECOND_LINE as SECOND5_1_1_, addresses1_.ZIP_CODE as ZIP6_1_1_ from SYSTEM.PERSON person0_ left outer join SYSTEM.ADDRESS addresses1_ on person0_.ID=addresses1_.PERSON_ID where person0_.ID=addresses1_.PERSON_ID
Result size: 10
Data retrieved
Person
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

Person
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

Person
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

Person
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

Person
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address
***** Address ******
Id: 5
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 2
***** Address ******

Person
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address
***** Address ******
Id: 6
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 2
***** Address ******

Person
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address
***** Address ******
Id: 7
First Line: Line 331
Second Line: Line 332
City: null
Zip Code: 11221
Person Id: 2
***** Address ******

Person
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address
***** Address ******
Id: 8
First Line: Line 441
Second Line: Line 442
City: null
Zip Code: 22331
Person Id: 2
***** Address ******

Person
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address
***** Address ******
Id: 9
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 3
***** Address ******

Person
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address
***** Address ******
Id: 10
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 3
***** Address ******

All the data is retrieved in a single SQL. The generated query is an outer join between the PERSON and ADDRESS tables. This is efficient except for the fact that the Person object is repeated for the number of addresses. Alternatively change the Fetch annotation on the getAddresses method to the following:

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	@Fetch(FetchMode.JOIN)
	//@Fetch(FetchMode.SUBSELECT)
	//@BatchSize(size=2)
	public List<Address> getAddresses() {
		return addresses;
	}

Run the DataSelectFetch class. Here’s the ouput:

Retrieving Data
Hibernate: select person0_.ID as ID0_, person0_.FIRST_NAME as FIRST2_0_, person0_.LAST_NAME as LAST3_0_ from SYSTEM.PERSON person0_
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, 
addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
Data retrieved: 3
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

#### Address complete
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 5
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 6
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 7
First Line: Line 331
Second Line: Line 332
City: null
Zip Code: 11221
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 8
First Line: Line 441
Second Line: Line 442
City: null
Zip Code: 22331
Person Id: 2
***** Address ******

#### Address complete
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 9
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 3
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 10
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 3
***** Address ******

#### Address complete

Four SQLs are fired. First to retrieve all Persons and the next three for retrieving associated addresses for that person. This seems similar to the SELECT fetch strategy except that fact that all database retrieval take place upfront in JOIN fetch unlike in SELECT where it happens on a need basis. This can become an important performance consideration.

Update 18th Nov 2011: This does not really look like a join query. I changed the querying class to the following class:

package com.tutorial.hibernate.fetch;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;

public class DataFetchJoin {

	public static void main(String[] args) {
		SessionFactory fact = HibernateUtil.getFactory();
		Session session = fact.openSession();
		
		Person p = (Person)session.get(Person.class, 1);
		
		System.out.println("Retrieving Data");
		System.out.println(p);
		System.out.println("Address retrieval initiated.");
		List<Address> addresses = p.getAddresses();
		System.out.println("Address retrieval complete.");
		int i=1;
		for (Address address : addresses) {
			System.out.println("#### individual address: " + i);
			System.out.println(address);
			System.out.println("#### Address complete");
			i++;
		}
	}
}

On running the test class, the console output is as follows:

Hibernate: select person0_.ID as ID0_1_, person0_.FIRST_NAME as FIRST2_0_1_, person0_.LAST_NAME as LAST3_0_1_, addresses1_.PERSON_ID as PERSON4_0_3_, addresses1_.id as id3_, addresses1_.id as id1_0_, addresses1_.CITY as CITY1_0_, addresses1_.FIRST_LINE as FIRST3_1_0_, addresses1_.PERSON_ID as PERSON4_1_0_, addresses1_.SECOND_LINE as SECOND5_1_0_, addresses1_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.PERSON person0_ left outer join SYSTEM.ADDRESS addresses1_ on person0_.ID=addresses1_.PERSON_ID where person0_.ID=?
Retrieving Data
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

#### Address complete

This is more on expected lines for JOIN mode. The difference was that here I retrieved one Person only vis-a-vis the earlier queries retrieved N persons. Hibernate querying is complex and too flexible for my comfort.
End Update:18th Nov 2011

Fetch Strategy : SUBSELECT

Let’s move on to the next fetch strategy SUBSELECT. Change the fetch mode in getAddresses annotation to the following:

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	@Fetch(FetchMode.SUBSELECT)
	//@BatchSize(size=2)
	public List<Address> getAddresses() {
		return addresses;
	}

Run the DataSelectFetch class. The output is as follows:

Retrieving Data
Hibernate: select person0_.ID as ID0_, person0_.FIRST_NAME as FIRST2_0_, person0_.LAST_NAME as LAST3_0_ from SYSTEM.PERSON person0_
Data retrieved: 3
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID in (select person0_.ID from SYSTEM.PERSON person0_)
#### individual address: 1
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

#### Address complete
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 5
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 6
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 7
First Line: Line 331
Second Line: Line 332
City: null
Zip Code: 11221
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 8
First Line: Line 441
Second Line: Line 442
City: null
Zip Code: 22331
Person Id: 2
***** Address ******

#### Address complete
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 9
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 3
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 10
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 3
***** Address ******

#### Address complete

Two SQLs are fired. One to retrieve all Persons and the second uses a SUBSELECT query in the WHERE clause to retrieve all addresses that has matching person ids.

Let’s change line number 15 in DataSelectFetch from

		Query q = session.createQuery(" FROM Person p ");
		Query q = session.createQuery(" FROM Person p WHERE p.id = ? OR p.id = ?");
		q.setInteger(0, 1);
		q.setInteger(1, 2);

The two database queries are changed to the following:

Hibernate: select person0_.ID as ID0_, person0_.FIRST_NAME as FIRST2_0_, person0_.LAST_NAME as LAST3_0_ from SYSTEM.PERSON person0_ where person0_.ID=? or person0_.ID=?
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID in (select person0_.ID from SYSTEM.PERSON person0_ where person0_.ID=? or person0_.ID=?)

Fetch Strategy : BATCH

Now let’s look at the last fetch strategy i.e. batch.

Change the getAddresses annotation to the following:

	@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
	@Column(name="id")
	@BatchSize(size=2)
	public List<Address> getAddresses() {
		return addresses;
	}

Run the DataSelectFetch class. This will be the older version of the class without the changes as suggested for Subselect option. The console output is:

Retrieving Data
Hibernate: select person0_.ID as ID0_, person0_.FIRST_NAME as FIRST2_0_, person0_.LAST_NAME as LAST3_0_ from SYSTEM.PERSON person0_
Data retrieved: 3
**** Person **** Id: 1
First name: Bill
Last name: Banks
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID in (?, ?)
#### individual address: 1
***** Address ******
Id: 1
First Line: Line 11
Second Line: Line 12
City: null
Zip Code: 12345
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 2
First Line: Line 21
Second Line: Line 22
City: null
Zip Code: 54321
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 3
First Line: Line 31
Second Line: Line 32
City: null
Zip Code: 11221
Person Id: 1
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 4
First Line: Line 41
Second Line: Line 42
City: null
Zip Code: 22331
Person Id: 1
***** Address ******

#### Address complete
**** Person **** Id: 2
First name: Steve
Last name: Jobs
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
Hibernate: select addresses0_.PERSON_ID as PERSON4_0_1_, addresses0_.id as id1_, addresses0_.id as id1_0_, addresses0_.CITY as CITY1_0_, addresses0_.FIRST_LINE as FIRST3_1_0_, addresses0_.PERSON_ID as PERSON4_1_0_, addresses0_.SECOND_LINE as SECOND5_1_0_, addresses0_.ZIP_CODE as ZIP6_1_0_ from SYSTEM.ADDRESS addresses0_ where addresses0_.PERSON_ID=?
#### individual address: 1
***** Address ******
Id: 5
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 6
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 3
***** Address ******
Id: 7
First Line: Line 331
Second Line: Line 332
City: null
Zip Code: 11221
Person Id: 2
***** Address ******

#### Address complete
#### individual address: 4
***** Address ******
Id: 8
First Line: Line 441
Second Line: Line 442
City: null
Zip Code: 22331
Person Id: 2
***** Address ******

#### Address complete
**** Person **** Id: 3
First name: Clark
Last name: Jacob
**** Person **** 

Address retrieval initiated.
Address retrieval complete.
#### individual address: 1
***** Address ******
Id: 9
First Line: Line 111
Second Line: Line 112
City: null
Zip Code: 12345
Person Id: 3
***** Address ******

#### Address complete
#### individual address: 2
***** Address ******
Id: 10
First Line: Line 221
Second Line: Line 222
City: null
Zip Code: 54321
Person Id: 3
***** Address ******

#### Address complete

The batch size maps to the number of persons whose addresses are retrieved. In the present case the first batch is triggered for Person with id 1 and 3, the second batch is for Person with id is 2. I am not sure why hibernate deviates from the order in which the ids are retrieved. Need to figure that out.

To sum up we have four different methods/strategies hibernate provides to retrieve data. It is up to the developer to judiciously choose one of the four strategies which ensures optimum application and database performance.

That’s all at the moment.

Advertisements

9 thoughts on “Investigating Hibernate fetch strategies – A tutorial

  1. I dont see any difference in output when fetch mode is changed to ‘join’ from ‘select’. I found that all the queries are not done at same time. Can you please confirm why it is happening like this?

    1. Nath,

      The only difference is when the addresses are retrieved. Although there is another use case where the familiar join appears. Refer my update on 18th Nov 2011.

  2. Good Example.But need to change this concept : Only one person at one address.Take Father -Child Example.
    Your article is really excellent.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s