Wednesday, August 31, 2011

N+1 and fetch strategies in hibernate

What is N+1 problem in Hibernate?
Lets take a use case of Library.One Library can have any number of books.Imagine a screen where we are listing library information and book details.

By default hibernate fetches the associated objects lazily.That means associated collection is not fetched.Instead hibernate creates a proxy for the same.There will be proxy for every object of the collection.

So when hibernate fetches the library information.Hibernate will fire following queries.These are:-
i) One query to fetch the parent object which is Library in this case and initializing the associated collection with proxy for each item in the collection which is books in this case.
ii)N queries for hydrating/fetching the data for the proxy of associated item in the collection.
So for fetching the data we are firing N+1 queries.

Are there any solutions to this problem.Can we avoid it?
We can avoid it by tweaking batch size and fetch mode which is explained below.

Batch Size 
When we define batch size for the many valued collection.Hibernate fetches child records for the size mentioned by batch size property.

i.e If we have a data set of 5 libraries.Each having N number of books. and we provide batch size as 3.Then when do listing of all libraries.It will eagerly fetch book data for 3 libraries .so instead of 5+1 queries.It will fire 1+1(Fetch book data for 3 libraries)+1(fetch book data for remaining 2 libraries)

Sub Select strategy
When we use sub select strategy for fetch mode.Then hibernate fetches child records for all entities of the parent node.It actually fires query with IN clause while selecting child records.


i.e Taking the above example.Lets suppose we are selecting data on the basis of some condition which returns 4 libraries.If we have incorporated sub select strategy.Then hibernate will fetch book data for all the 4 libraries.

In order to understand better lets take a use case

UseCase:- Every project has set of QA people,Dev people, and works on a particular technology stack.So a project has one to many relationship with QA ,Dev team and technology stack.

SQL Script:-
create table ORM_SW_Project(
	ProjectId bigint Identity(1,1) primary key,
	ProjectName varchar(50) not null,
	ProjectManager varchar(100) not null
)

create table ORM_SW_QA(
    QAId bigint Identity(1,1) primary key,
    QAName varchar(100) not null,
    QAAge int not null,
    ProjectId bigint not null
)

create table ORM_SW_Dev(
    DevId bigint identity(1,1) primary key,
    DevName varchar(100) not null,
    DevAge int not null,
    ProjectId bigint not null
)

create table ORM_SW_Tech(
    TechId bigint identity(1,1) primary key,
    TechName varchar(100) not null,
    TechVersion varchar(50) not null,
    ProjectId bigint not null
)

alter table ORM_SW_QA 
add  FOREIGN KEY (ProjectId) REFERENCES ORM_SW_Project(ProjectId);

alter table ORM_SW_Dev 
add  FOREIGN KEY (ProjectId) REFERENCES ORM_SW_Project(ProjectId);

alter table ORM_SW_Tech 
add  FOREIGN KEY (ProjectId) REFERENCES ORM_SW_Project(ProjectId);

insert into ORM_SW_Project(ProjectName,ProjectManager)values('New Website','Mr.X');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project1','Mr.Y1');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project2','Mr.Y2');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project3','Mr.Y3');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project4','Mr.Y4');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project5','Mr.Y5');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project6','Mr.Y6');
insert into ORM_SW_Project(ProjectName,ProjectManager)values('Integration Project7','Mr.Y7');

insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Sidney',34,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Rama',40,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Shiva',28,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Hary',35,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Avril',29,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Nancy',30,1);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James',34,2);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Tommy',29,2);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James',25,2);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Michael',29,2);

insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Sidney3',34,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Rama3',40,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Shiva3',28,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Hary3',35,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Avril3',29,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Nancy3',30,3);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James4',34,4);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Tommy4',29,4);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James4',25,4);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Michael4',29,4);

insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Sidney5',34,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Rama5',40,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Shiva5',28,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Hary5',35,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Avril5',29,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Nancy5',30,5);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James6',34,6);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Tommy6',29,6);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('James6',25,6);
insert into ORM_SW_QA(QAName,QAAge,ProjectId)values('Michael6',29,6);


insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kunaal',33,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pavan',30,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Nitish',35,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Arvind',30,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pankaj',28,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mamu',29,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Preet',32,1);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mohan',38,2);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Deepak',37,2);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kanak',29,2);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Rashmi',34,2);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Jeril',30,2);

insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kunaal3',33,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pavan3',30,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Nitish3',35,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Arvind3',30,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pankaj3',28,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mamu3',29,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Preet3',32,3);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mohan4',38,4);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Deepak4',37,4);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kanak4',29,4);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Rashmi4',34,4);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Jeril4',30,4);

insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kunaal5',33,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pavan5',30,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Nitish5',35,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Arvind5',30,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Pankaj5',28,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mamu5',29,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Preet5',32,5);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Mohan6',38,6);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Deepak6',37,6);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Kanak6',29,6);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Rashmi6',34,6);
insert into ORM_SW_Dev(DevName,DevAge,ProjectId)values('Jeril6',30,6);

insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Java','1.6',1);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Spring','3.0',1);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Hibernate','3.5',1);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Restful Webservices','1.2',1);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Oracle','9.0',1);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('.Net','2.0',2);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Oracle','9.0',2);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Restful Webservices','1.2',2);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Oracle','1.2',8);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Restful Webservices','1.2',8);
insert into ORM_SW_Tech(TechName,TechVersion,ProjectId) values('Java','1.2',8);

Java Model Classes

SWProject.java having one to many relationship with QA,Dev, and Technology
package com.kunaal.model.fetchMode;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
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;
import org.hibernate.annotations.FetchProfile;
import org.hibernate.annotations.FetchProfile.FetchOverride;
import org.hibernate.annotations.FetchProfiles;

import com.kunaal.model.BaseModel;

/**
 * @author Kunaal A Trehan
 * 
 */
@Entity
@Table(name="ORM_SW_Project")
public class SWProject implements BaseModel<Long>{

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	@Column(name="ProjectId")
	private Long projId;
	
	@Column(name="ProjectName")
	private String projName;
	
	@Column(name="ProjectManager")
	private String projMgr;
	
	@OneToMany(cascade=CascadeType.ALL,mappedBy="project")
//	@BatchSize(size=5)
	@Fetch(FetchMode.SUBSELECT)
	private Set<SWQA> qaSet=new HashSet<SWQA>();
	
	@OneToMany(cascade=CascadeType.ALL,mappedBy="project")
//	@BatchSize(size=5)
	private Set<SWDev> devSet=new HashSet<SWDev>();
	
	@OneToMany(cascade=CascadeType.ALL,mappedBy="project")
//	@BatchSize(size=5)
	private Set<SWTech> techSet=new HashSet<SWTech>();
	
	public Long getPrimaryKey() {
		return getProjId();
	}

	/**
	 * @return the projId
	 */
	public Long getProjId() {
		return projId;
	}

	/**
	 * @param projId the projId to set
	 */
	public void setProjId(Long projId) {
		this.projId = projId;
	}

	/**
	 * @return the projName
	 */
	public String getProjName() {
		return projName;
	}

	/**
	 * @param projName the projName to set
	 */
	public void setProjName(String projName) {
		this.projName = projName;
	}

	/**
	 * @return the projMgr
	 */
	public String getProjMgr() {
		return projMgr;
	}

	/**
	 * @param projMgr the projMgr to set
	 */
	public void setProjMgr(String projMgr) {
		this.projMgr = projMgr;
	}

	/**
	 * @return the qaSet
	 */
	public Set<SWQA> getQaSet() {
		return qaSet;
	}

	/**
	 * @param qaSet the qaSet to set
	 */
	public void setQaSet(Set<SWQA> qaSet) {
		this.qaSet = qaSet;
	}

	/**
	 * @return the devSet
	 */
	public Set<SWDev> getDevSet() {
		return devSet;
	}

	/**
	 * @param devSet the devSet to set
	 */
	public void setDevSet(Set<SWDev> devSet) {
		this.devSet = devSet;
	}

	/**
	 * @return the techSet
	 */
	public Set<SWTech> getTechSet() {
		return techSet;
	}

	/**
	 * @param techSet the techSet to set
	 */
	public void setTechSet(Set<SWTech> techSet) {
		this.techSet = techSet;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((projId == null) ? 0 : projId.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		SWProject other = (SWProject) obj;
		if (projId == null) {
			if (other.projId != null)
				return false;
		} else if (!projId.equals(other.projId))
			return false;
		return true;
	}

}


SWDev.java showing Dev details
package com.kunaal.model.fetchMode;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import com.kunaal.model.BaseModel;

/**
 * @author Kunaal A Trehan
 *
 */
@Entity
@Table(name="ORM_SW_Dev")
public class SWDev implements BaseModel<Long>{

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	@Column(name="DevId")
	private Long devId;
	
	@Column(name="DevName")
	private String devName;
	
	@Column(name="DevAge")
	private Integer age;
	
	@ManyToOne
	@JoinColumn(name="ProjectId")
	private SWProject project;
	
	public Long getPrimaryKey() {
		return getDevId();
	}

	/**
	 * @return the devId
	 */
	public Long getDevId() {
		return devId;
	}

	/**
	 * @param devId the devId to set
	 */
	public void setDevId(Long devId) {
		this.devId = devId;
	}

	/**
	 * @return the devName
	 */
	public String getDevName() {
		return devName;
	}

	/**
	 * @param devName the devName to set
	 */
	public void setDevName(String devName) {
		this.devName = devName;
	}

	/**
	 * @return the age
	 */
	public Integer getAge() {
		return age;
	}

	/**
	 * @param age the age to set
	 */
	public void setAge(Integer age) {
		this.age = age;
	}

	/**
	 * @return the project
	 */
	public SWProject getProject() {
		return project;
	}

	/**
	 * @param project the project to set
	 */
	public void setProject(SWProject project) {
		this.project = project;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((devId == null) ? 0 : devId.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		SWDev other = (SWDev) obj;
		if (devId == null) {
			if (other.devId != null)
				return false;
		} else if (!devId.equals(other.devId))
			return false;
		return true;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "SWDev [devId=" + devId + ", devName=" + devName + "]";
	}

}

SWQA.java showing QA details
package com.kunaal.model.fetchMode;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.BatchSize;

import com.kunaal.model.BaseModel;

/**
 * @author Kunaal A Trehan
 *
 */
@Entity
@Table(name="ORM_SW_QA")
//@BatchSize(size=2)
public class SWQA implements BaseModel<Long>{

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	@Column(name="QAId")
	private Long id;
	
	@Column(name="QAName")
	private String name;
	
	@Column(name="QAAge")
	private Integer age;
	
	@ManyToOne
	@JoinColumn(name="projectId")
	private SWProject project;
	
	public Long getPrimaryKey() {
		return getId();
	}

	/**
	 * @return the id
	 */
	public Long getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Long id) {
		this.id = id;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the age
	 */
	public Integer getAge() {
		return age;
	}

	/**
	 * @param age the age to set
	 */
	public void setAge(Integer age) {
		this.age = age;
	}

	/**
	 * @return the project
	 */
	public SWProject getProject() {
		return project;
	}

	/**
	 * @param project the project to set
	 */
	public void setProject(SWProject project) {
		this.project = project;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		SWQA other = (SWQA) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		return true;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "SWQA [id=" + id + ", name=" + name + "]";
	}

}

SWTech.java showing technology details
package com.kunaal.model.fetchMode;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import com.kunaal.model.BaseModel;

/**
 * @author Kunaal A Trehan
 *
 */
@Entity
@Table(name="ORM_SW_Tech")
public class SWTech implements BaseModel<Long>{

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	@Column(name="TechId")
	private Long id;
	
	@Column(name="TechName")
	private String name;
	
	@Column(name="TechVersion")
	private String version;
	
	@ManyToOne
	@JoinColumn(name="ProjectId")
	private SWProject project;
	
	public Long getPrimaryKey() {
		return getId();
	}

	/**
	 * @return the id
	 */
	public Long getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Long id) {
		this.id = id;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the version
	 */
	public String getVersion() {
		return version;
	}

	/**
	 * @param version the version to set
	 */
	public void setVersion(String version) {
		this.version = version;
	}

	/**
	 * @return the project
	 */
	public SWProject getProject() {
		return project;
	}

	/**
	 * @param project the project to set
	 */
	public void setProject(SWProject project) {
		this.project = project;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		SWTech other = (SWTech) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		return true;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "SWTech [id=" + id + ", name=" + name + "]";
	}

}


So we have 8 projects in the database and in SWProject.java we have placed fetch mode as SUBSELECT and there is a commented entry for BatchSize(size=5).There is a test case in which we list all the projects and print the QA details.So when we have fetch mode as SUBSELECT,there will be 2 queries fired,one for project details and creating the proxy for all the associated collections and another for  hydrating the proxy for QA collection.

However when we use comment fetch mode as SUBSELECT and use BatchSize(size=5).There will be 3 queries fired,one for project details  and creating the proxy for associated collections and another 2 queries for hydrating the proxy for QA collection.Since batch size is 5 and no of projects is 8 so 2 queries will be fired.


Test Case(ProjectServiceTest.java)
package com.kunaal.service;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.FetchMode;
import org.hibernate.annotations.FetchProfile;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import com.kunaal.model.fetchMode.SWProject;

/**
 * @author Kunaal A Trehan
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:spring-main.xml"})
@TransactionConfiguration(defaultRollback=false)
public class ProjectServiceTest  implements ApplicationContextAware {

	private ApplicationContext appCtx;
	
	@Autowired	
	private IProjectService projectService;
	
	public void setApplicationContext(ApplicationContext applicationContext)
			throws BeansException {
		this.appCtx=applicationContext;
	}

        @Test
	@Transactional
	public void listAll(){
		List<SWProject> list = projectService.listAll();
		
		for(SWProject project:list){
			System.out.println("Project is:"+project);
			System.out.println("QA is:"+project.getQaSet());
			//System.out.println("Dev is:"+project.getDevSet());
			//System.out.println("Tech is:" +project.getTechSet());
		}
	}
}

Test execution log when @BatchSize(size=5) is enabled.If we look into highlighted italic section ,hibernate has fired 3 queries as explained earlier

Aug 31, 2011 5:19:08 PM org.springframework.test.context.TestContextManager retrieveTestExecutionListeners
INFO: @TestExecutionListeners is not present for class [class com.kunaal.service.ProjectServiceTest]: using defaults.
Aug 31, 2011 5:19:08 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-main.xml]
Aug 31, 2011 5:19:08 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-hibernate-config.xml]
Aug 31, 2011 5:19:08 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-dao.xml]
Aug 31, 2011 5:19:08 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-service.xml]
Aug 31, 2011 5:19:08 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.GenericApplicationContext@1befab0: startup date [Wed Aug 31 17:19:08 GMT+05:30 2011]; root of context hierarchy
Aug 31, 2011 5:19:08 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1506dc4: defining beans [sessionFactory,interceptor,transactionManager,baseDAO,addrDAO,deptDAO,emplDAO,personDAO,flatEmpDAO,apptDAO,nriBankAcctDAO,enumPersonDAO,qualEnumDAO,publisherDAO,parentDAO,paymentDAO,singlePymtDAO,empVersionDAO,stockDAO,stateDAO,singerDAO,hqlDAO,criteriaDAO,mfDAO,mappedDAO,plainDeptDAO,plainUniDeptDAO,projectDAO,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,baseService,addrService,emplService,deptService,personService,flatEmplService,apptService,nriBankAcctService,enumPersonService,qualEnumService,publisherService,parentService,paymentService,singlePymtService,empVersionService,stockService,stateService,singerService,hqlService,criteriaService,mfService,mappedService,plainDeptService,plainUniDeptService,projectService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor]; root of factory hierarchy
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Aug 31, 2011 5:19:09 PM org.springframework.orm.hibernate3.LocalSessionFactoryBean buildSessionFactory
INFO: Building new Hibernate SessionFactory
Aug 31, 2011 5:19:11 PM org.springframework.test.context.transaction.TransactionalTestExecutionListener startNewTransaction
INFO: Began transaction (1): transaction manager [org.springframework.orm.hibernate3.HibernateTransactionManager@12a416a]; rollback [false]
Hibernate: select this_.ProjectId as ProjectId39_0_, this_.ProjectManager as ProjectM2_39_0_, this_.ProjectName as ProjectN3_39_0_ from ORM_SW_Project this_
Project is:com.kunaal.model.fetchMode.SWProject@20
Hibernate: select qaset0_.projectId as projectId39_1_, qaset0_.QAId as QAId1_, qaset0_.QAId as QAId40_0_, qaset0_.QAAge as QAAge40_0_, qaset0_.QAName as QAName40_0_, qaset0_.projectId as projectId40_0_ from ORM_SW_QA qaset0_ where qaset0_.projectId in (?, ?, ?, ?, ?)
QA is:[SWQA [id=3, name=Shiva], SWQA [id=1, name=Sidney], SWQA [id=5, name=Avril], SWQA [id=2, name=Rama], SWQA [id=4, name=Hary], SWQA [id=6, name=Nancy]]
Project is:com.kunaal.model.fetchMode.SWProject@21
QA is:[SWQA [id=10, name=Michael], SWQA [id=9, name=James], SWQA [id=7, name=James], SWQA [id=8, name=Tommy]]
Project is:com.kunaal.model.fetchMode.SWProject@22
QA is:[SWQA [id=14, name=Hary3], SWQA [id=16, name=Nancy3], SWQA [id=12, name=Rama3], SWQA [id=15, name=Avril3], SWQA [id=11, name=Sidney3], SWQA [id=13, name=Shiva3]]
Project is:com.kunaal.model.fetchMode.SWProject@23
QA is:[SWQA [id=20, name=Michael4], SWQA [id=19, name=James4], SWQA [id=17, name=James4], SWQA [id=18, name=Tommy4]]
Project is:com.kunaal.model.fetchMode.SWProject@24
Hibernate: select qaset0_.projectId as projectId39_1_, qaset0_.QAId as QAId1_, qaset0_.QAId as QAId40_0_, qaset0_.QAAge as QAAge40_0_, qaset0_.QAName as QAName40_0_, qaset0_.projectId as projectId40_0_ from ORM_SW_QA qaset0_ where qaset0_.projectId in (?, ?, ?)
QA is:[SWQA [id=22, name=Rama5], SWQA [id=24, name=Hary5], SWQA [id=26, name=Nancy5], SWQA [id=21, name=Sidney5], SWQA [id=25, name=Avril5], SWQA [id=23, name=Shiva5]]
Project is:com.kunaal.model.fetchMode.SWProject@25
QA is:[SWQA [id=27, name=James6], SWQA [id=29, name=James6], SWQA [id=30, name=Michael6], SWQA [id=28, name=Tommy6]]
Project is:com.kunaal.model.fetchMode.SWProject@26
QA is:[]
Project is:com.kunaal.model.fetchMode.SWProject@27
QA is:[]
Aug 31, 2011 5:19:11 PM org.springframework.test.context.transaction.TransactionalTestExecutionListener endTransaction
INFO: Committed transaction after test execution for test context [[TestContext@1df3d59 testClass = ProjectServiceTest, locations = array<String>['classpath:spring-main.xml'], testInstance = com.kunaal.service.ProjectServiceTest@134c5ff, testMethod = listAll@ProjectServiceTest, testException = [null]]]
Aug 31, 2011 5:19:11 PM org.springframework.context.support.AbstractApplicationContext doClose
INFO: Closing org.springframework.context.support.GenericApplicationContext@1befab0: startup date [Wed Aug 31 17:19:08 GMT+05:30 2011]; root of context hierarchy
Aug 31, 2011 5:19:11 PM org.springframework.beans.factory.support.DefaultSingletonBeanRegistry destroySingletons
INFO: Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1506dc4: defining beans [sessionFactory,interceptor,transactionManager,baseDAO,addrDAO,deptDAO,emplDAO,personDAO,flatEmpDAO,apptDAO,nriBankAcctDAO,enumPersonDAO,qualEnumDAO,publisherDAO,parentDAO,paymentDAO,singlePymtDAO,empVersionDAO,stockDAO,stateDAO,singerDAO,hqlDAO,criteriaDAO,mfDAO,mappedDAO,plainDeptDAO,plainUniDeptDAO,projectDAO,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,baseService,addrService,emplService,deptService,personService,flatEmplService,apptService,nriBankAcctService,enumPersonService,qualEnumService,publisherService,parentService,paymentService,singlePymtService,empVersionService,stockService,stateService,singerService,hqlService,criteriaService,mfService,mappedService,plainDeptService,plainUniDeptService,projectService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor]; root of factory hierarchy
Aug 31, 2011 5:19:11 PM org.springframework.orm.hibernate3.AbstractSessionFactoryBean destroy
INFO: Closing Hibernate SessionFactory

Test execution log when @Fetch(FetchMode.SUBSELECT) is enabled.If we look into highlighted italic section ,hibernate has fired 2 queries as explained earlier


Aug 31, 2011 5:38:05 PM org.springframework.test.context.TestContextManager retrieveTestExecutionListeners
INFO: @TestExecutionListeners is not present for class [class com.kunaal.service.ProjectServiceTest]: using defaults.
Aug 31, 2011 5:38:05 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-main.xml]
Aug 31, 2011 5:38:05 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-hibernate-config.xml]
Aug 31, 2011 5:38:05 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-dao.xml]
Aug 31, 2011 5:38:05 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-service.xml]
Aug 31, 2011 5:38:05 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.GenericApplicationContext@1befab0: startup date [Wed Aug 31 17:38:05 GMT+05:30 2011]; root of context hierarchy
Aug 31, 2011 5:38:06 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1506dc4: defining beans [sessionFactory,interceptor,transactionManager,baseDAO,addrDAO,deptDAO,emplDAO,personDAO,flatEmpDAO,apptDAO,nriBankAcctDAO,enumPersonDAO,qualEnumDAO,publisherDAO,parentDAO,paymentDAO,singlePymtDAO,empVersionDAO,stockDAO,stateDAO,singerDAO,hqlDAO,criteriaDAO,mfDAO,mappedDAO,plainDeptDAO,plainUniDeptDAO,projectDAO,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,baseService,addrService,emplService,deptService,personService,flatEmplService,apptService,nriBankAcctService,enumPersonService,qualEnumService,publisherService,parentService,paymentService,singlePymtService,empVersionService,stockService,stateService,singerService,hqlService,criteriaService,mfService,mappedService,plainDeptService,plainUniDeptService,projectService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor]; root of factory hierarchy
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Aug 31, 2011 5:38:06 PM org.springframework.orm.hibernate3.LocalSessionFactoryBean buildSessionFactory
INFO: Building new Hibernate SessionFactory
Aug 31, 2011 5:38:08 PM org.springframework.test.context.transaction.TransactionalTestExecutionListener startNewTransaction
INFO: Began transaction (1): transaction manager [org.springframework.orm.hibernate3.HibernateTransactionManager@12cb585]; rollback [false]
Hibernate: select this_.ProjectId as ProjectId39_0_, this_.ProjectManager as ProjectM2_39_0_, this_.ProjectName as ProjectN3_39_0_ from ORM_SW_Project this_
Project is:com.kunaal.model.fetchMode.SWProject@20
Hibernate: select qaset0_.projectId as projectId39_1_, qaset0_.QAId as QAId1_, qaset0_.QAId as QAId40_0_, qaset0_.QAAge as QAAge40_0_, qaset0_.QAName as QAName40_0_, qaset0_.projectId as projectId40_0_ from ORM_SW_QA qaset0_ where qaset0_.projectId in (select this_.ProjectId from ORM_SW_Project this_)
QA is:[SWQA [id=3, name=Shiva], SWQA [id=1, name=Sidney], SWQA [id=5, name=Avril], SWQA [id=2, name=Rama], SWQA [id=4, name=Hary], SWQA [id=6, name=Nancy]]
Project is:com.kunaal.model.fetchMode.SWProject@21
QA is:[SWQA [id=10, name=Michael], SWQA [id=9, name=James], SWQA [id=7, name=James], SWQA [id=8, name=Tommy]]
Project is:com.kunaal.model.fetchMode.SWProject@22
QA is:[SWQA [id=14, name=Hary3], SWQA [id=16, name=Nancy3], SWQA [id=12, name=Rama3], SWQA [id=15, name=Avril3], SWQA [id=11, name=Sidney3], SWQA [id=13, name=Shiva3]]
Project is:com.kunaal.model.fetchMode.SWProject@23
QA is:[SWQA [id=20, name=Michael4], SWQA [id=19, name=James4], SWQA [id=17, name=James4], SWQA [id=18, name=Tommy4]]
Project is:com.kunaal.model.fetchMode.SWProject@24
QA is:[SWQA [id=22, name=Rama5], SWQA [id=24, name=Hary5], SWQA [id=26, name=Nancy5], SWQA [id=21, name=Sidney5], SWQA [id=25, name=Avril5], SWQA [id=23, name=Shiva5]]
Project is:com.kunaal.model.fetchMode.SWProject@25
QA is:[SWQA [id=27, name=James6], SWQA [id=29, name=James6], SWQA [id=30, name=Michael6], SWQA [id=28, name=Tommy6]]
Project is:com.kunaal.model.fetchMode.SWProject@26
QA is:[]
Project is:com.kunaal.model.fetchMode.SWProject@27
QA is:[]
Aug 31, 2011 5:38:08 PM org.springframework.test.context.transaction.TransactionalTestExecutionListener endTransaction
INFO: Committed transaction after test execution for test context [[TestContext@df824a testClass = ProjectServiceTest, locations = array<String>['classpath:spring-main.xml'], testInstance = com.kunaal.service.ProjectServiceTest@1e2c9bf, testMethod = listAll@ProjectServiceTest, testException = [null]]]
Aug 31, 2011 5:38:08 PM org.springframework.context.support.AbstractApplicationContext doClose
INFO: Closing org.springframework.context.support.GenericApplicationContext@1befab0: startup date [Wed Aug 31 17:38:05 GMT+05:30 2011]; root of context hierarchy
Aug 31, 2011 5:38:08 PM org.springframework.beans.factory.support.DefaultSingletonBeanRegistry destroySingletons
INFO: Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1506dc4: defining beans [sessionFactory,interceptor,transactionManager,baseDAO,addrDAO,deptDAO,emplDAO,personDAO,flatEmpDAO,apptDAO,nriBankAcctDAO,enumPersonDAO,qualEnumDAO,publisherDAO,parentDAO,paymentDAO,singlePymtDAO,empVersionDAO,stockDAO,stateDAO,singerDAO,hqlDAO,criteriaDAO,mfDAO,mappedDAO,plainDeptDAO,plainUniDeptDAO,projectDAO,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,baseService,addrService,emplService,deptService,personService,flatEmplService,apptService,nriBankAcctService,enumPersonService,qualEnumService,publisherService,parentService,paymentService,singlePymtService,empVersionService,stockService,stateService,singerService,hqlService,criteriaService,mfService,mappedService,plainDeptService,plainUniDeptService,projectService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor]; root of factory hierarchy


Posted by at

2 comments:

  1. Javin @ Enum examples in javaSeptember 23, 2011 at 11:42 PM

    Hi Kunaal,

    Nice article very informative and clean as well. I see you got a very good blog, keep it up.

    Thanks
    Javin

    ReplyDelete
  2. AnonymousFebruary 20, 2012 at 12:47 AM

    explained in a cleaned way..Nice stuff..Kunaal

    ReplyDelete

Subscribe to: Post Comments (Atom)