Hibernate N+1 Select Problem

Hibernate N + 1 Select Problem

The N + 1 Select problem is a performance issue in Hibernate. In this problem, a Java application makes N + 1 database calls (N = number of objects fetched). For example, if N= 2, the application makes 3 (N+1= 3) database calls.

Example

Let’s understand this problem with the help of an example. In this example, we are taking two POJO classes, Employee.java and Department.java. There exists a one-to-many association mapping between the two POJO classes. So that, one Department (Parent) can have many Employees (Child).

The Department and Employee classes are written below:

Department.java

import java.util.ArrayList;
import java.util.List;
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;
@Entity
@Table(name="deptmnt")
public class Department {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="d_id")
private int did;
@Column(name="d_name")
private String dname;
@OneToMany(mappedBy="dep", cascade=CascadeType.PERSIST)
private List emp = new ArrayList();
public int getDid() {
return did;
}
public void setDid(int did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public List getEmp() {
return emp;
}
public void setEmp(List emp) {
this.emp = emp;
}
} 

In the Department class, department id (did), department name (dname), and a List of employees are present.

Employee.java

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;
@Entity
@Table(name="employee")
public class Employee {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name="e_id")
private int eid;
@Column(name="e_name")
private String ename;
@ManyToOne
@JoinColumn(name="dept_id")
private Department dep;
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Department getDep() {
return dep;
}
public void setDep(Department dep) {
this.dep = dep;
}
} 

In the Employee class, employee id (eid), employee name (ename), and an object of department class are present.

Following code is of the configuration file, which contains information about the database and the mapping classes.

hibernate.cfg.xml

org.hibernate.dialect.MySQL5Dialect
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/test2
root
root
create
true 
true



 

Now, we are going to create the main class, which stores the object of the POJO classes. Following code is of the App.java.

App.java

import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class App 
{
public static void main( String[] args )
{
Configuration cfg= new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory fact= cfg.buildSessionFactory();
Session sess= fact.openSession();
sess.beginTransaction();
Employee e1 = new Employee();
e1.setEname("jyotika");
Employee e2= new Employee();
e2.setEname("shubham");
Employee e3= new Employee();
e3.setEname("nihal");
Employee e4= new Employee();
e4.setEname("neha");
Department d1= new Department();
d1.setDname("IT");
d1.getEmp().add(e1);
d1.getEmp().add(e2);
e1.setDep(d1);
e2.setDep(d1);
Department d2= new Department();
d2.setDname("Accounts");
d2.getEmp().add(e3);
d2.getEmp().add(e4);
e3.setDep(d2);
e4.setDep(d2);
sess.persist(d1);
sess.persist(d2);
List depart= sess.createQuery("From Department", Department.class).getResultList();
//List depart= sess.createQuery("From Department d JOIN fetch d.emp", Department.class).getResultList();
for (Department dep: depart){
System.out.println("Department details:::::");
System.out.println(dep.getDid()+"\t"+dep.getDname());
List<Employee> emp = dep.getEmp();
System.out.println("Employees details::::::");
for (Employee employee : emp) {
System.out.println(employee.getEid() + "\t" + employee.getEname());
}
}
sess.getTransaction().commit();
sess.close();
System.out.println("done");
}
} 

OUTPUT

The above output encountered the N+1 select problem, as separate select queries are executed for different objects. The first SELECT query selects the values of the Department (parent), and the rest two is for the Employee class (child).

The N+1 problem occurs due to the following code:

List depart= sess.createQuery("From Department", Department.class).getResultList();

To resolve the N+1 Select problem we can use the following approaches:

1. HQL JOIN fetch

In place of the above code, we can use JOIN fetch to resolve the N+1 problem.

 List depart=sess.createQuery("From Department d JOIN fetch d.emp",Department.class)

.getResultList();

• Criteria Query

We can also use Criteria query to resolve the N+1 select Problem. Following code shows the use of criteria query:

 CriteriaBuilder builder = sess.getCriteriaBuilder();
CriteriaQuery<Department> query = builder.createQuery(Department.class);
Root root = query.from(Department.class);
root.fetch("employees", JoinType.INNER); 

After resolving the N+1 problem, only one select query is executed for all the objects.

OUTPUT

Database Tables

Employee

Department