Hibernate Data Fetching Options

In this post I will try to point out various ways you can fetch data in hibernate and in what scenarios each of then are is useful.


HQL in hibernate
It is the most preferred way to use that combines you the ease of SQL and flexibility of Hibernate. I don't want to add anything more but will direct you to an excellent article on HQL.


Criteria Queries
Criteria queries are less verbose and preferred by people who don't have much exposure to SQL. It is less performative compared to HQL. Read more about Criteria here.


Native SQL and Stored Procedures.
This should considered as a last option as you will loose the features of hibernate if you start using Native SQL.Also you should take care the responsibility of transforming the result set to object by yourself.
But there are situations you want to go with this approach. Read more about them here.

These typical ways can be tweaked in various ways to customize them to your requirement. Let me discus few of them that you may not find very often documented.

I have used the classes from examples from my previous post for simplicity.Read it here in case you need any explanation on the examples.

1. Partial Initialization of a bean.

Suppose you have a bean AMain, as in my previous post.

 public class AMain {  
    Integer aId;  
    String name;  
    ASub1 one2oneSubA1;  
    List<ASub3> subList;  
      //More code below...  
 }  

If you write an HQL to fetch this bean as 

 List<AMain> beanList2 = session.createQuery("from AMain am ").list();  

it will fire the below queries.


Hibernate: select amain0_.a_id as a1_1_, amain0_.name as name1_, amain0_.prop1 as prop3_1_, amain0_.prop2 as prop4_1_, 'A' as formula0_, amain0_.a_id as formula1_ from A_Main amain0_
Hibernate: select asub1x0_.as1_id as as1_2_0_, asub1x0_.sub_name as sub2_2_0_, asub1x0_.a_id as a3_2_0_ from A_Sub1 asub1x0_ where asub1x0_.a_id=?
Hibernate: select sublist0_.a_id as a3_1_, sublist0_.as3_id as as1_1_, sublist0_.as3_id as as1_4_0_, sublist0_.sub_name as sub2_4_0_, sublist0_.a_id as a3_4_0_ from A_Sub3 sublist0_ where sublist0_.a_id=?

 Also the last query can repeat number of times if the lazy loading in enabled.

Now If I have a requirement to get the partially initialized AMain bean, I mean with only the properties aId and name initialized that definitely should not take mare than 1 query as all the information is available on the AMain table. Most of the people will tend to go to Native SQL at this point.But you can achieve this using HQL.


write the HQL as   

 List<AMain> beanList2 = session.createQuery("select new AMain (aId,name) from AMain am ").list();  

Also add the corresponding constructor in class AMain as

 public AMain(Integer aId,String name) {  
           this.aId = aId;  
           this.name = name;  
      }  

Now verify the SQL output


Hibernate: select amain0_.a_id as col_0_0_, amain0_.name as col_1_0_ from A_Main amain0_


Yes..only 1 query is fired.This is very useful in when we have to fetch a snapshot of a Large object in a performative way.




2. HQL Joins on multiple columns.


If there are two related table and we have mapped them in the hbm files then the below hql is possible.


"from AMain a left outer join a.refMain ref...more joins here"


It will do a left outer join on the tables RefMain and AMain provided you have a corresponding mapping. Now if you need to add another condition in the join(to satisfy the join but which we could not add as a relationship). It can be achieved using a 'with' operator.


so, the new HQL is,


"from AMain a left outer join a.refMain ref with ref.aOrbId = 'a'...more joins here"


This way the query results can be achieved with out disturbing the original mapping.


3. Using a DTO instead on a Domain Object.

Meany times we might need a set of data that might be needed which is spread across multiple tables. Which means I don not have a Domain Object to hold the data. Using hibernate we can populate a DTO object from any SQL of HQL query using Transformers. The Transformers can convert the List of Object arrays to List of DTO that will improve the readability and are much easier to use with.

 public class AFamilyDTO {  
      Integer aId;  
      String name;  
      Integer as1Id;  
      String aSub1Name;  
      /* getter/ setters here..*/   
 }  

As you can see the bean represent a snapshot of AMain and ASub1 beans Or it can be any DTO which we have created for the easy representation of the data model. Now Lets see how we can use Transformers to populate this bean.

 SQLQuery query = session.createSQLQuery("select amain.a_id as aId , amain.name as name, " +  
               "sub1.as1_id as as1Id, sub1.sub_name as as1Name " +  
               " from A_Main amain join A_Sub1 sub1 on amain.a_id = sub1.a_id");  
        query.addScalar("aId", Hibernate.INTEGER);  
        query.addScalar("name", Hibernate.STRING);  
        query.addScalar("as1Id", Hibernate.INTEGER);  
        query.addScalar("as1Name", Hibernate.STRING);  
        query.setResultTransformer(Transformers.aliasToBean(AFamilyDTO.class));  
 List<AFamilyDTO> list = query.list();  

As I have told in the beginning of the post, there are multiple ways to fetch data in hibernate. So, before you blame hibernate for the performance issues in your application have a look all the options it provides you to fetch the data. Turn on the show sql flag on and see how many queries are getting fired. Then see if you can use any alternative ways of fetching data.