Healthcare IT EMR PMS: Hibernate n+1 problem

First let us try to understand what n+1 Problem in hibernate
Look at this stack overflow thread for examples. One of the example is here
Supplier with a one-to-many relationship with Product. One Supplier has (supplies) many Products.

***** Table: Supplier *****
+-----+-------------------+
| ID  |       NAME        |
+-----+-------------------+
|  1  |  Supplier Name 1  |
|  2  |  Supplier Name 2  |
|  3  |  Supplier Name 3  |
|  4  |  Supplier Name 4  |
+-----+-------------------+

***** Table: Product *****
+-----+-----------+--------------------+-------+------------+
| ID  |   NAME    |     DESCRIPTION    | PRICE | SUPPLIERID |
+-----+-----------+--------------------+-------+------------+
|1    | Product 1 | Name for Product 1 |  2.0  |     1      |
|2    | Product 2 | Name for Product 2 | 22.0  |     1      |
|3    | Product 3 | Name for Product 3 | 30.0  |     2      |
|4    | Product 4 | Name for Product 4 |  7.0  |     3      |
+-----+-----------+--------------------+-------+------------+

Factors:

Lazy mode for Supplier set to “true” (default)
Fetch mode used for querying on Product is Select
Fetch mode (default): Supplier information is accessed
Caching does not play a role for the first time the
Supplier is accessed

Fetch mode is Select Fetch (default)

// It takes Select fetch mode as a default
Query query = session.createQuery( "from Product p");
List list = query.list();
// Supplier is being accessed
displayProductsListWithSupplierName(results);

// It takes Select fetch mode as a default
Query query = session.createQuery( "from Product p");
List list = query.list();
// Supplier is being accessed
displayProductsListWithSupplierName(results);

select ... various field names ... from PRODUCT
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?

Result:

1 select statement for Product
N select statements for Supplier

This is N+1 select problem!
Now let us get to our example.

Environment

Eclipse 3.7 Indigo IDE
Hibernate 4.1.1
JavaSE 1.6
MySQL 5.1

Step 1:

Let us set the environment. Follow this post to set up Hibernate with java in eclipse IDE.

Step 2:

Mysql

CREATE TABLE `enumeration` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `enumCode` VARCHAR(255) DEFAULT NULL,
  `enumType` VARCHAR(255) DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

insert  into `enumeration`(`ID`,`enumCode`,`enumType`,`description`) values (1,'Mr','TITLE','Mr'),(2,'Miss','TITLE','Miss'),(3,'Master','TITLE','Master');

CREATE TABLE `person` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR(100) DEFAULT NULL,
  `LastName` VARCHAR(100) DEFAULT NULL,
  `Email` VARCHAR(100) DEFAULT NULL,
  `PERSONAL_TITLE` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY  (`ID`),
  KEY `FK_person` (`PERSONAL_TITLE`),
  CONSTRAINT `FK_person` FOREIGN KEY (`PERSONAL_TITLE`) REFERENCES `enumeration` (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

insert  into `person`(`ID`,`FirstName`,`LastName`,`Email`,`PERSONAL_TITLE`) values (1,'John','Smith','JohnSmith@Yahoo.com',1),(2,'James','William','William@yahoo.com',1),(3,'David','Richard','Richard@Yahoo.com',1),(4,'Daniel','Paul','Daniel@Yahoo.com',1),(5,'Gary','Kevin','Kevin@Yahoo.com',1),(6,'Jose','Larry','Larry@Yahoo.com',1),(7,'Scott','Andrew','Scott@Yahoo.com',1);

CREATE TABLE `userlogin` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `LoginID` VARCHAR(100) DEFAULT NULL,
  `Password` VARCHAR(100) DEFAULT NULL,
  `PersonID` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY  (`ID`),
  KEY `FK_userlogin` (`PersonID`),
  CONSTRAINT `FK_userlogin` FOREIGN KEY (`PersonID`) REFERENCES `person` (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

insert  into `userlogin`(`ID`,`LoginID`,`Password`,`PersonID`) values (1,'user1','Pass1',1),(2,'user2','Pass2',2),(3,'user3','pass3',3),(4,'user4','pass4',4),(5,'user5','pass5',5),(6,'user6','pass6',6),(7,'user7','pass7',7);

Java Bean

Enumernation.java

package domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.ManyToOne;
import javax.persistence.JoinColumn;

@Entity
@Table(name = "enumeration")
public class Enumeration {

    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Integer id;

    @Column(name = "ENUMCODE")
    private String enumCode;
    
    @Column(name = "ENUMTYPE")
    private String enumType;
    
    @Column(name = "DESCRIPTION")
    private String description;
    
    public Integer getId() {
        return id;
    }

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

    public String getEnumCode() {
        return enumCode;
    }

    public void setEnumCode(String enumCode) {
        this.enumCode = enumCode;
    }

    public String getEnumType() {
        return enumType;
    }

    public void setEnumType(String enumType) {
        this.enumType = enumType;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

}

Person.java

package domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.ManyToOne;
import javax.persistence.JoinColumn;

@Entity
@Table(name = "enumeration")
public class Enumeration {

    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Integer id;

    @Column(name = "ENUMCODE")
    private String enumCode;
    
    @Column(name = "ENUMTYPE")
    private String enumType;
    
    @Column(name = "DESCRIPTION")
    private String description;
    
    public Integer getId() {
        return id;
    }

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

    public String getEnumCode() {
        return enumCode;
    }

    public void setEnumCode(String enumCode) {
        this.enumCode = enumCode;
    }

    public String getEnumType() {
        return enumType;
    }

    public void setEnumType(String enumType) {
        this.enumType = enumType;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

}

userlogin.java

package domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table(name = "userlogin")
public class userlogin {

    @Id
    @GeneratedValue
    private int ID;

    @Column(name = "LoginID")
    private String LoginID;

    @Column(name = "password")
    private String password;

    @OneToOne(optional = false)
    @JoinColumn(name = "PersonID")
    private Person person;


    public int getID() {
        return ID;
    }

    public void setID(int iD) {
        ID = iD;
    }

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public String getLoginID() {
        return LoginID;
    }

    public void setLoginID(String loginID) {
        LoginID = loginID;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

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">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/sampledb</property>
        <property name="connection.username">root</property>
        <property name="connection.password">123</property>

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

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</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>

     

        <!-- Mapping Classes -->
        <mapping class="domain.userlogin" />
        <mapping class="domain.Person" />
        <mapping class="domain.Enumeration" />
        

    </session-factory>
</hibernate-configuration>

Test.java

package test;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import domain.*;
import HibernateUtilities.*;

public class Test {
    public static void main(String[] args) {

        Session session = HibernateUtil.beginTransaction();
        List<userlogin> people = session.createQuery("FROM userlogin").list();
        for(userlogin p : people){
            System.out.println(p.getLoginID() + " ! " + p.getPerson().getFirstName() + " ! " + p.getPerson().getPersonalTitle().getDescription());  
        }
        HibernateUtil.CommitTransaction();

    }

}

Now let us run the test.java and observe the sql output as follows.

Hibernate: select userlogin0_.ID as ID0_, userlogin0_.LoginID as LoginID0_, userlogin0_.password as password0_, userlogin0_.PersonID as PersonID0_ from userlogin userlogin0_
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
Hibernate: select person0_.ID as ID1_1_, person0_.FirstName as FirstName1_1_, person0_.LastName as LastName1_1_, person0_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio1_.ID as ID2_0_, enumeratio1_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio1_.ENUMCODE as ENUMCODE2_0_, enumeratio1_.ENUMTYPE as ENUMTYPE2_0_ from person person0_ left outer join enumeration enumeratio1_ on person0_.PERSONAL_TITLE=enumeratio1_.ID where person0_.ID=?
user1 ! John ! Mr
user2 ! James ! Mr
user3 ! David ! Mr
user4 ! Daniel ! Mr
user5 ! Gary ! Mr
user6 ! Jose ! Mr
user7 ! Scott ! Mr

Hibernate issues 8 query into mysql to show the output. This is typical n+1 Problem. Now let us solve using fetch join in the query as follows

Now let us change our test.java as follows

package test;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import domain.*;
import HibernateUtilities.*;

public class Test {
    public static void main(String[] args) {

        Session session = HibernateUtil.beginTransaction();
        List<userlogin> people = session.createQuery("FROM userlogin u join fetch u.person").list();
        for(userlogin p : people){
            System.out.println(p.getLoginID() + " ! " + p.getPerson().getFirstName() + " ! " + p.getPerson().getPersonalTitle().getDescription());  
        }
        HibernateUtil.CommitTransaction();

    }

}

Now let us run the test.java and observe the sql output as follows.

Hibernate: select userlogin0_.ID as ID0_0_, person1_.ID as ID1_1_, userlogin0_.LoginID as LoginID0_0_, userlogin0_.password as password0_0_, userlogin0_.PersonID as PersonID0_0_, person1_.FirstName as FirstName1_1_, person1_.LastName as LastName1_1_, person1_.PERSONAL_TITLE as PERSONAL4_1_1_ from userlogin userlogin0_ inner join person person1_ on userlogin0_.PersonID=person1_.ID
Hibernate: select enumeratio0_.ID as ID2_0_, enumeratio0_.DESCRIPTION as DESCRIPT2_2_0_, enumeratio0_.ENUMCODE as ENUMCODE2_0_, enumeratio0_.ENUMTYPE as ENUMTYPE2_0_ from enumeration enumeratio0_ where enumeratio0_.ID=?
user1 ! John ! Mr
user2 ! James ! Mr
user3 ! David ! Mr
user4 ! Daniel ! Mr
user5 ! Gary ! Mr
user6 ! Jose ! Mr
user7 ! Scott ! Mr

Now it is perfect. Hibernate use inner join between userlogin and person, because each userlogin must have the reference to person .

But if you see the output, there is one more select which is coming from our person class where we mapped to another master table called enumeration.

Now let us change our test.java as follows

package test;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import domain.*;
import HibernateUtilities.*;

public class Test {
    public static void main(String[] args) {

        Session session = HibernateUtil.beginTransaction();
        List<userlogin> people = session.createQuery("FROM userlogin u join fetch u.person p join fetch p.personalTitle ").list();
        for(userlogin p : people){
            System.out.println(p.getLoginID() + " ! " + p.getPerson().getFirstName() + " ! " + p.getPerson().getPersonalTitle().getDescription());  
        }
        HibernateUtil.CommitTransaction();

    }

}

Now let us run the test.java and observe the sql output as follows.

Hibernate: select userlogin0_.ID as ID0_0_, person1_.ID as ID1_1_, enumeratio2_.ID as ID2_2_, userlogin0_.LoginID as LoginID0_0_, userlogin0_.password as password0_0_, userlogin0_.PersonID as PersonID0_0_, person1_.FirstName as FirstName1_1_, person1_.LastName as LastName1_1_, person1_.PERSONAL_TITLE as PERSONAL4_1_1_, enumeratio2_.DESCRIPTION as DESCRIPT2_2_2_, enumeratio2_.ENUMCODE as ENUMCODE2_2_, enumeratio2_.ENUMTYPE as ENUMTYPE2_2_ from userlogin userlogin0_ inner join person person1_ on userlogin0_.PersonID=person1_.ID inner join enumeration enumeratio2_ on person1_.PERSONAL_TITLE=enumeratio2_.ID
user1 ! John ! Mr
user2 ! James ! Mr
user3 ! David ! Mr
user4 ! Daniel ! Mr
user5 ! Gary ! Mr
user6 ! Jose ! Mr
user7 ! Scott ! Mr

Now it is only one select statement

Finally, what happen if personal_title is optional in person table, we will left outer join as folllows

package test;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import domain.*;
import HibernateUtilities.*;

public class Test {
    public static void main(String[] args) {

        Session session = HibernateUtil.beginTransaction();
        List<userlogin> people = session.createQuery("FROM userlogin u join fetch u.person p left outer join fetch   p.personalTitle ").list();
        for(userlogin p : people){
            System.out.println(p.getLoginID() + " ! " + p.getPerson().getFirstName());  
        }
        HibernateUtil.CommitTransaction();

    }

}

follow me

<a href="https://plus.google.com/112392680157232301619" rel="author" target="_blank">Follow Me On Google+</a>

X12 EDI Examples

Hibernate n+1 problem

Environment

Step 1:

Let us set the environment. Follow this post to set up Hibernate with java in eclipse IDE.

Step 2:

Mysql

Enumernation.java

Person.java

userlogin.java

hibernate.cfg.xml

Test.java