Wednesday, 8 August 2012

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



  1. Eclipse 3.7 Indigo IDE
  2. Hibernate 4.1.1
  3. JavaSE 1.6
  4. 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


image

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
image

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>






4 comments:

  1. Dear Madam and Mr,

    We are looking for partner in building a local mining company or
    serious cash buyers of AU Gold and rough and uncut Diamond, my father
    and others are local sellers and miners of Gold and Diamond here in
    Guinea, they want to get money to help our families as you know here
    in Africa we have many people in our families, they are counting on
    some people who went to school to help them but these people just want
    to steal their products even the government promised to help our
    community but since my father gave them produts they've been just
    telling story every day, every time. This is the reason why our
    community decided to contact the good people.

    Due to the way they are mining their products many people are trying
    to teach them because they don't know the world market even some
    mandates and buyers rep have come and carried their products on
    credits and they have never come back to pay their money. Right now i
    want to get them serious people who can help them to sell their
    products following the market. At the moment they have GOLD DUST /
    BARS and ROUGH/UNCUT DIAMOND in large quantities for sale at cheap
    price.

    They need people like you, people over there to help them develop
    their goal, so that they can help our community, because people sicks
    and dies because no money. They have the products but they are very
    poor here because of the way the government and some people are
    treating them, they want serious buyers to be their eyes and mouths,
    they are ready to give you goods if you are serious and ready to open
    a local mining company with them to work together and help them and
    you in mutual benefit or help them buy their products so that they
    can at least buy mining equipments such as, bulldozers, tractors,
    excavators, wheel loaders, backhoe loaders, motor graders, dump
    trucks, crushing equipment, and etc as we need to enhance our mining
    productivity. We will give you good price far below the word market
    price.

    Hope to hear from you soon.

    Thank you,
    Mr Mamady Konaté
    Community representative

    Siguiri Gold and Diamond local
    mining community company,
    BP:3005, Siguiri,
    Republue de Guinea
    E-mail: k.mamady82@yahoo.fr
    Tel: +224657629251

    ReplyDelete
    Replies
    1. Wow! What a great story about your gold bars for a cheap price! I would love to help your community. Please contact me ASAP.

      Delete
  2. Hai senthill you pasted Enumeration class instead of Person,will you rectify it. and Will you please share some similar blogs on banking and E-commerce domains

    ReplyDelete
  3. We are a third party technical support service. Avast Customer Support is here to help you out with the whole procedure to Download Avast Antivirus online, We not only fix your Avast Support related issues but will guide with how to get started with your new Avast product once it gets installed successfully. Call on our Toll Free no. 1 855 966 3855
    Gmail Customer service is a third party technical support service for Gmail users when they face any technical issue or error in their Gmail account. Our Gmail Customer Support team solves issues like forgot Gmail account password, Gmail configuration or Sync issues, recover deleted emails and many more. Toll Free number (800) 986-9271
    How you install or reinstall Office 365 or Office 2016 depends on whether your Office product is part of an Office for home or Office for business plan. If you're not sure what you have, see what office com setup products are included in each plan and then follow the steps for your product. The steps below also apply if you're installing a single, stand-alone Office application such as Access 2016 or Visio 2016. Need Help with office setup Enter Product Key? Call 1-800-000-0000 Toll Free
    Norton Tech Support is a third party service provider and not in any way associated with Norton or any of its partner companies. We offer support for Norton products and sell subscription based additional warranty on computer and other peripheral devices. Call our Toll Free number 1 855 966 3855
    Other Services
    Norton Toll Free , Office-Setup , office.com/setup.

    ReplyDelete