Creating Custom Entity Loaders

At its basic level Hibernate deals with SQL. All the fancy methods and HQL strings that we write end up being converted into the SQL that is understood by the database. Hibernate generates the appropriate SQL best suited for the Database provider we are using based on the dialect we have set in our hibernate configuration file.

<property name="dialect">org.hibernate.dialect.MySQLDialect</property>

There is a very rare probability that we may find the SQL queries generated unsuitable for our use. In such cases Hibernate comes with the option of overriding these queries with our own SQL queries.
Let us start by customizing the select query. I created two tables - SHELF and ITEM  with a one to many association between the two. The hbm files for the same is as below:
Shelf.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 package="com.custom.crud">
    <class name="Shelf" table="SHELF">
        <id name="id" type="integer" column="ID">
            <generator class="identity" />
        </id>
        <property name="name" column="NAME" />
        <set name="items" cascade="all" inverse="true">
            <key column ="SHELF_ID" />
            <one-to-many class="Item" />
        </set>
    </class>
</hibernate-mapping> 

Item.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 package="com.custom.crud">
    <class name="Item" table="ITEM">
        <id name="id" type="integer" column="ID">
            <generator class="native" />
        </id>
        <property name="model" column="MODEL" />
        <many-to-one name="shelf" class="Shelf" column="SHELF_ID"
            foreign-key="ITEM_FK1" />
    </class>
</hibernate-mapping>

The Java classes for the same are also plain and simple:

public class Item {
    private Integer id;
    private String model;
    private Shelf shelf;
        //setter getter methods
}
public class Shelf {
    private Integer id;
    private String name;
    private Set<Item> items = new HashSet<Item>();
        //setter getter methods
}

Consider now the various SQL queries:  
Java Code:

Session session = sessionFactory.openSession();
Shelf shelf = (Shelf) session.get(Shelf.class, 1);

The simple fetch will generate the below query.

3250 [main] DEBUG org.hibernate.SQL  - 
    select
        shelf0_.ID as ID0_0_,
        shelf0_.NAME as NAME0_0_ 
    from
        SHELF shelf0_ 
    where
        shelf0_.ID=?

For the sake of the example ,I would like to use my own query instead of the above query to fetch the data. To load the shelf entity using my custom query, I need to define a loader element in my mapping. The modified mapping for Item is as below:

<?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 package="com.custom.crud">
    <class name="Shelf" table="SHELF">
        <id name="id" type="integer" column="ID">
            <generator class="identity" />
        </id>
        <property name="name" column="NAME" />
        <set name="items" cascade="all" inverse="true">
            <key column="SHELF_ID" />
            <one-to-many class="Item" />
        </set>
        <loader query-ref="loadShelf" />
    </class>

    <sql-query name="loadShelf">
        <return alias="sh" class="Shelf" />
        select 
            sh.ID as {sh.id},
            sh.NAME as {sh.name}
        from
            SHELF sh
        where 
            sh.ID = ?        
    </sql-query>
</hibernate-mapping>

The loader element includes a reference to a name query which can be defined anywhere in the mapping. I have added the query within the same file. The logs on start-up detects the change

1047 [main] DEBUG org.hibernate.cfg.NamedSQLQuerySecondPass  - Named SQL query: 
loadShelf -> select
            sh.ID as {sh.id},
            sh.NAME as {sh.name}
        from
            SHELF sh
        where 
            sh.ID = ?
...
2703 [main] DEBUG org.hibernate.impl.SessionFactoryImpl  - Checking 1 named SQL 
queries
2703 [main] DEBUG org.hibernate.impl.SessionFactoryImpl  - Checking named SQL qu
ery: loadShelf
2703 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate 
native-sql query plan in cache; generating (select
            sh.ID as {sh.id},
            sh.NAME as {sh.name}
        from
            SHELF sh
        where 
            sh.ID = ?)
2719 [main] DEBUG org.hibernate.loader.custom.sql.SQLCustomQuery  - starting pro
cessing of sql query [select
            sh.ID as {sh.id},
            sh.NAME as {sh.name}
        from
            SHELF sh
        where 
            sh.ID = ?]
2735 [main] DEBUG org.hibernate.loader.custom.sql.SQLQueryReturnProcessor  - map
ping alias [sh] to entity-suffix [0_] 

The fetch call now uses our query

2953 [main] DEBUG org.hibernate.SQL  - 
    select
        sh.ID as ID0_0_,
        sh.NAME as NAME0_0_   
    from
        SHELF sh   
    where
        sh.ID = ?

The name given to each column in the SQL result is determined by Hibernate, It replaces the value in "{}" with suitable column aliases.
In any such query the columns to be selected are:

• Primary Key column. Primary Key property/properties (for composite identifiers)
• All scalar properties including those inside components. If any of the properties are set to lazy="true" then they can be ignored.
• All foreign key columns must be mapped here. For example a query for the Item class would be as below: 

<sql-query name="loadItem">
    <return alias="it" class="Item" />
    select 
        item.ID as {it.id},
        item.MODEL as {it.model},
        item.SHELF_ID as {it.shelf}
    from
        ITEM item
    where 
        item.ID = ?        
</sql-query> 

The stat up logs indicate that Hibernate found and processed our query:

2562 [main] DEBUG org.hibernate.impl.SessionFactoryImpl  - Checking named SQL qu
ery: loadItem
2562 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate 
native-sql query plan in cache; generating (select 
            item.ID as {it.id},
            item.MODEL as {it.model},
            item.SHELF_ID as {it.shelf}
        from
            ITEM item
        where 
            item.ID = ?)
2578 [main] DEBUG org.hibernate.loader.custom.sql.SQLCustomQuery  - starting pro
cessing of sql query [select 
            item.ID as {it.id},
            item.MODEL as {it.model},
            item.SHELF_ID as {it.shelf}
        from
            ITEM item
        where 
            item.ID = ?]
2594 [main] DEBUG org.hibernate.loader.custom.sql.SQLQueryReturnProcessor  - map
ping alias [it] to entity-suffix [0_]

Then when we fired the get call for the Item record, Hibernate executed our query:

2906 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - located native-sq
l query plan in cache (select 
            item.ID as {it.id},
            item.MODEL as {it.model},
            item.SHELF_ID as {it.shelf}
        from
            ITEM item
        where 
            item.ID = ?)
2906 [main] DEBUG org.hibernate.impl.SessionImpl  - SQL query: 
        select 
            item.ID as ID1_0_,
            item.MODEL as MODEL1_0_,
            item.SHELF_ID as SHELF3_1_0_
        from
            ITEM item
        where 
            item.ID = ?

Hibernate also allows us to customize the sql for loading the collection. For the set of items, a loader query can be added. This needs to be declared as a part of the collection mapping.

<?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 package="com.custom.crud">
    <class name="Shelf" table="SHELF">
        <id name="id" type="integer" column="ID">
            <generator class="identity" />
        </id>
        <property name="name" column="NAME" />
        <set name="items" cascade="all" inverse="true">
            <key column="SHELF_ID" />
            <one-to-many class="Item" />
            <loader query-ref="loadItemsOnShelf" />
        </set>
        <loader query-ref="loadShelf" />
    </class>

    <sql-query name="loadShelf">
        <return alias="sh" class="Shelf" />
        select 
            sh.ID as {sh.id},
            sh.NAME as {sh.name}
        from
            SHELF sh
        where 
            sh.ID = ?        
    </sql-query>
    <sql-query name="loadItemsOnShelf">
        <load-collection alias="item" role="Shelf.items" />
        select 
            {item.*}
        from
            ITEM item
        where 
            item.SHELF_ID = :shelfId            
    </sql-query>
</hibernate-mapping>

The load-collection mapping indicates the query is for loading data into a collection.The question mark (or positional parameter) used in the earlier query has been replaced with a named parameter :shelfID. This leads to better readability.
On trying to load the collection our query can be seen in the logs.

3797 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - located native-sq
l query plan in cache (select 
            {item.*}
        from
            ITEM item
        where 
            item.SHELF_ID = :shelfId)
3797 [main] DEBUG org.hibernate.impl.SessionImpl  - SQL query: 
    select
        item.SHELF_ID as SHELF3_0__,
        item.ID as ID0__,
        item.ID as ID1_0_,
        item.MODEL as MODEL1_0_,
        item.SHELF_ID as SHELF3_1_0_   
    from
        ITEM item   
    where
        item.SHELF_ID = ?

As can be seen the properties were automatically mapped to the column via the common symbol item.
It is also possible to use a single join query to load the entity and the collections data at the same time. This would involve a change in the hbm file:

<sql-query name="loadShelfAndItems">
    <return alias="sh" class="Shelf" />
    <return-join alias="items" property="sh.items" />
    select 
        {sh.*}, {items.*}
    from 
        SHELF sh
    left outer join ITEM items
        on items.SHELF_ID = sh.ID
    where
        sh.ID = :shelfId         
</sql-query>

The outer join was used as certain Shelf may not have any associated Items.
This behaves like an eager fetch loading the Shelf and its Item entity in one SQL call.

2219 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate 
native-sql query plan in cache; generating (select 
            {sh.*}, {items.*}
        from 
            SHELF sh
        left outer join ITEM items
            on items.SHELF_ID = sh.ID
        where
            sh.ID = :shelfId)
2219 [main] DEBUG org.hibernate.loader.custom.sql.SQLCustomQuery  - starting pro
cessing of sql query [select 
            {sh.*}, {items.*}
        from 
            SHELF sh
        left outer join ITEM items
            on items.SHELF_ID = sh.ID
        where
            sh.ID = :shelfId]
2234 [main] DEBUG org.hibernate.loader.custom.sql.SQLQueryReturnProcessor  - map
ping alias [sh] to entity-suffix [0_]

And on loading the shelf:

select
        sh.ID as ID0_0_,
        sh.NAME as NAME0_0_,
        items.SHELF_ID as SHELF3_0__,
        items.ID as ID0__,
        items.ID as ID1_1_,
        items.MODEL as MODEL1_1_,
        items.SHELF_ID as SHELF3_1_1_   
    from
        SHELF sh   
    left outer join
        ITEM items    
            on items.SHELF_ID = sh.ID   
    where
        sh.ID = ?

The same technique can be used to eagerly load one-to-one and many-to-one associations.