How Hibernate interpret Order of JOIN on a Table?

UPDATED: 04 October 2014
Hibernate Logo


Hibernate is one of the greatest framework to interact with database but every framework has its own advantages and disadvantages. Haphazard use of Hibernate may slower your application and you'll face major performance issues.

"Hibernate ain't important than database, you should care How Hibernate execute queries against database"

I've prepared sample mapping file to join two table "vehicle_master" and "profile_master" (It may not make sense but this is just an example so just ignore it).
/* user.hbm.xml */
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="javaQuery.hibernate.User" table="user_master">
    <id name="id" type="int">
      <column name="id"/>
      <generator class="assigned"/>
    </id>
    <property name="Username" type="java.lang.String">
      <column name="user_name"/>
    </property>
    <property name="Email" type="java.lang.String">
      <column name="email"/>
    </property>
    <list cascade="refresh" name="listVehicle" table="user_vehicle_map">
      <key column="user_id"/>
      <index column="idx" type="integer"/>
      <many-to-many class="javaQuery.hibernate.Vehicle" column="vehicle_id"/>
    </list>
    <many-to-one name="Profile" class="javaQuery.hibernate.Profile" column="profile_id" unique="true" not-null="true" />
  </class>
</hibernate-mapping>

Execute following criteria to get SQL query and lets see "In which order Hibernate interpret your join".
/* Simple Criteria to load user with its vehicles and profile */
Criteria criteria = session.createCriteria(User.class);
criteria.setFetchMode("listVehicle", FetchMode.JOIN);
criteria.setFetchMode("Profile", FetchMode.JOIN);
criteria.list();

Above criteria will generate following SQL as we know it.
SELECT *
FROM   user_master this_
LEFT OUTER JOIN user_vehicle_map listvehicl2_
            ON this_.id = listvehicl2_.user_id
LEFT OUTER JOIN vehicle_master vehicle3_
            ON listvehicl2_.vehicle_id = vehicle3_.id
INNER JOIN profile_master profile4_
       ON this_.profile_id = profile4_.id

Myth
Developer thinks that JOIN on vehicle table took first place in SQL query because we have vehicle table at the first position in Criteria but have you ever tried changing position in Criteria? Lets see what happen if we change position in Criteria. After executing following code you'll get the same SQL query generated by first code.
/* Position of JOIN changed in criteria */
Criteria criteria = session.createCriteria(User.class);
criteria.setFetchMode("Profile", FetchMode.JOIN);
criteria.setFetchMode("listVehicle", FetchMode.JOIN);        
criteria.list();

Why didn't Hibernate change order of JOIN?
Well so far you are living with wrong assumption about How order of JOIN take place in hibernate. Its not the Criteria used by Hibernate to interpret order of JOIN on table. Its the Hibernate Mapping (hbm.xml) file. So if you want to change order of JOIN on table, you'll have to change order in your Hibernate Mapping (hbm.xml) file.

Order changed in Hibernate Mapping file. Compare first and second user.hbm.xml file for Vehicle and Profile.
/* user.hbm.xml */
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="javaQuery.hibernate.User" table="user_master">
    <id name="id" type="int">
      <column name="id"/>
      <generator class="assigned"/>
    </id>
    <property name="Username" type="java.lang.String">
      <column name="user_name"/>
    </property>
    <property name="Email" type="java.lang.String">
      <column name="email"/>
    </property>
    <many-to-one name="Profile" class="javaQuery.hibernate.Profile" column="profile_id" unique="true" not-null="true" />
    <list cascade="refresh" name="listVehicle" table="user_vehicle_map">
      <key column="user_id"/>
      <index column="idx" type="integer"/>
      <many-to-many class="javaQuery.hibernate.Vehicle" column="vehicle_id"/>
    </list>
  </class>
</hibernate-mapping>

Now this hibernate mapping file will generate following SQL query not matter what order you've in your Criteria.
SELECT *
FROM   user_master this_
INNER JOIN profile_master profile2_
       ON this_.profile_id = profile2_.id
LEFT OUTER JOIN user_vehicle_map listvehicl3_
            ON this_.id = listvehicl3_.user_id
LEFT OUTER JOIN vehicle_master vehicle4_
            ON listvehicl3_.vehicle_id = vehicle4_.id 

Why ORDER of JOIN is so important?
You'll get same result in both the queries. However JOIN on table in wrong order may cause performance issue at Database. After all Database have to filter your data based on your joins. This is sample query and will not create major impact because of order of JOIN but in real time application we have lots of joins on table and at that time ORDDER of JOIN does matter. Contact your Database Administrator to understand this performance issue for JOINs.

0 comments :