n+1 query not working

Question

In the below code I expect the n+1 query problem to occur, but it's not happening. User.java:

import java.util.*;

public class User {
    private long userId;
    private String firstName;
    private Set phones;

    public User() {
        System.out.println("0-arg constructor :User");
    }

    public String getFirstName() {
        return firstName;
    }

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

    public long getUserId() {
        return userId;
    }

    public void setUserId(long userId) {
        this.userId = userId;
    }

    public Set getPhones() {
        return phones;
    }

    public void setPhones(Set phones) {
        this.phones = phones;
    }
}

PhoneNumber.java:

public class PhoneNumber {
    private String numberType;
    private long phone;
    private long id;
    User parent;

    public PhoneNumber() {
        System.out.println("0-arg constructor :PhoneNumber");
    }

    // write getXxx(),setXxx() methods (4 sets)

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

    public long getId() {
        return id;
    }

    public String getNumberType() {
        return numberType;
    }

    public void setNumberType(String numberType) {
        this.numberType = numberType;
    }

    public long getPhone() {
        return phone;
    }

    public void setPhone(long phone) {
        this.phone = phone;
    }

    public void setParent(User parent) {
        this.parent = parent;
    }

    public User getParent() {
        return parent;
    }

}

User.hbm.xml:

 <hibernate-mapping>
  <class name="User" 

      table="USER_TABLE" >

      <id name="userId" 

      column="USER_ID"/>
      <property name="firstName"  

       column="FIRST_NAME"/>

     <set name="phones"  

     table="PHONE_NUMBERS" cascade="all"  

       lazy="true">
    <key column="UNID"/>
    <one-to-many 

    class="PhoneNumber"/>
   </set>

  </class>
   </hibernate-mapping>

phoneNumber.hbm:

   <hibernate-mapping>

    <class name="PhoneNumber" table="PHONE_NUMBERS" >

   <id name="phone"  column="PHONE"/>
   <property name="numberType" column="NUMBER_TYPE"/>
   <property name="id" column="UNID" insert="false" update="false"/>

   <many-to-one name="parent" class="User"  column="UNID2" cascade="all"/>
    </class>
    </hibernate-mapping>

hibernate.cfg:

    <session-factory>
    <property 

    name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</

     property>
    <property 

   name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</prope

    rty>
    <property 

     name="hibernate.connection.username">system</property>
    <property 

       name="hibernate.connection.password">oracle123</property>

    <property 

    name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
    <property name="hibernate.hbm2ddl.auto">update</property>
    <property name="show_sql">true</property>

    <mapping resource="user.hbm.xml"/>          

      <mapping resource="phoneNumber.hbm.xml"/>

</session-factory>

HQLClient.java:

import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.*;

public class HQLJoinsClient {

    public static void main(String[] args) {
        try {
            Configuration conf = new Configuration().configure();
            SessionFactory factory = conf.buildSessionFactory();
            Session ses = factory.openSession();

            String hql = "from User ";
            Query q = ses.createQuery(hql);
            List l = q.list();
            System.out.println("++++++++++++++++++" + l.size()
                    + "+++++++++++++");

            for (int i = 0; i < l.size(); ++i) {
                User u1 = (User) l.get(i);
                System.out
                        .println("\n\n\nParent----------------------------------------------------------------->");

                System.out.print("user id: " + u1.getUserId());
                System.out.println("FirstName " + u1.getFirstName());

                Set s = u1.getPhones();
                if (s != null) {
                    Iterator it = s.iterator();
                    while (it.hasNext()) {
                        PhoneNumber p1 = (PhoneNumber) it.next();
                        System.out.println("\nchild---->");
                        System.out.print("Number Type=" + p1.getNumberType());
                        System.out.print("Phone Number=" + p1.getPhone());
                        System.out.println("User id=" + p1.getId());

                    }// inner while
                }// if
            }

            ses.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The output is as:

INFO: schema update complete                                                    Hibernate: select user0_.USER_ID as USER1_0_, user0_.FIRST_NAME as FIRST2_0_ from USER_TABLE user0_                                                             


0-arg constructor :User 0-arg constructor :User                                                         0-arg constructor :User                                                         ++++++++++++++++++3+++++++++++++++++++++++++                                                                                                                                                                                                                                                                                    Parent----------------------------------------------------------------->        user id: 102FirstName ravi                                                      Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=?                                                                              0-arg constructor :PhoneNumber                                                  0-arg constructor :PhoneNumber                                                  0-arg constructor :PhoneNumber                                                                                                                                  child---->                                                                      Number Type=resPhone Number=81818181User id=102                                                                                                                 child---->                                                                      Number Type=officePhone Number=71717171User id=102                                                                                                              child---->                                                                      Number Type=homePhone Number=91919191User id=102                                                                                                                                                                                                                                                                                Parent----------------------------------------------------------------->        user id: 103FirstName jayendra                                                  Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=?                                                                              0-arg constructor :PhoneNumber                                                  0-arg constructor :PhoneNumber                                                                                                                                  child---->                                                                      Number Type=resPhone Number=3748329382User id=103                                                                                                               child---->                                                                      Number Type=homePhone Number=538432342User id=103                                                                                                                                                                                                                                                                               Parent----------------------------------------------------------------->        user id: 104FirstName mike                                                      Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=?                                                                              0-arg constructor :PhoneNumber                                                  0-arg constructor :PhoneNumber                                                                                                                                  child---->                                                                      Number Type=homePhone Number=238349384User id=104                                                                                                               child---->                                                                      Number Type=mobilePhone Number=9455682832User id=104                            

I expected for each phone number record to a user id there will be separate select statement but for 3 Phonenumber- 1 userid there is one select statement [instead of (3+1)]. why is coming like this ?

Thanks!

Answer 1

There are few issues in your mapping.

First of all, one-to-many and many-to-one is in relational DB expressed by one column. The same column on both ends, so this is wrong:

// class name="User"
<set name="phones" table="PHONE_NUMBERS"
     cascade="all" lazy="true">

    <key column="UNID"/>                   // this column must be same
    <one-to-many class="PhoneNumber" />
</set>

// class name="PhoneNumber"
...   
<many-to-one name="parent" class="User" 
       column="UNID2"                      // as this column
       cascade="all"/>

Both column values must be targeting the same column

Secondly, you are experiencing 1 + N issue. There is one SELECT for user, but 3 selects for their Phones. This is standard 1 + N problem.

The solution is to use:

20.1.5. Using batch fetching

small cite:

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.

Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

So, we should use this setting on collection:

<set name="phones" table="PHONE_NUMBERS" batch-size="25"
     cascade="all" lazy="true">

And I would suggest to use it also on every class mapping:

<class name="PhoneNumber" table="PHONE_NUMBERS" batch-size="25">