Search This Blog

Monday 29 April 2013

session.createSQLQuery()

Hibernate provides us with the option to fire explicit SQL queries. But with the presence of HQL and Criteria why would one want to do that ?
Well I found a host of valid reasons, some from Books and a couple of my own:
  1. We may have certain tables or views not mapped in Hibernate that we may need to execute a query against. 
  2. We may need to apply some SQL level  optimizations to the query and this can be done only via the SQL query.
  3. There could be a scenario where the query might seem too complex to write out using Criteria or HQL and in such cases achieving an immediate solution with SQL might be an option.
And with Hibernate's ability to execute SQL queries, we do not need to write any JDBC based code. Also the run time exceptions thrown by Hibernate ensures that your code does not look like a collection of try catch blocks. Consider the below HQL query:
Query q = session.createQuery("from Entity");
The query generated is a straight select all records query. This same SQL query can also be executed in Hibernate:
public static void testSimplestSelect() {
    final Session session = sessionFactory.openSession();
    SQLQuery q = session.createSQLQuery("select * from ENTITY");
    List<Object[]> entities = q.list();
    for (Object[] entity : entities) {
        for (Object entityCol : entity) {
            System.out.print(" " + entityCol);
        }
        System.out.println("");
    }
}
The result returned by the code is :
Hibernate: 
    /* dynamic native SQL query */ 
    select
        * 
    from
        ENTITY
 2 entity1 null 1
 3 entity2 null 1
 4 entity100 null 2
 5 entity102 null 2
As can be seen the session class has a createSQLQuery() method that returns an instance of the SQLQuery interface. Interestingly our HQL code results in an instance of the Query Interface. SQLQuery extends the Query Interface The above code returned a list of Object arrays. But if we are going to be saying select *, it means we are returning all the properties of the Entity. So rather than an Object array we could work with the Entity class directly.
Is there some way of conveying the same to the Hibernate Engine ?
public static void testSimpleSQL() {
    final Session session = sessionFactory.openSession();
    SQLQuery q = session.createSQLQuery("select * from ENTITY");
    q.addEntity(Entity.class);
    List<Entity> entities = q.list();
    for (Entity entity : entities) {
        System.out.println(entity);
    }
}
As can be seen the result here as been assigned to a list of Entities. But will it work ? The result is:
Hibernate: 
    /* dynamic native SQL query */ 
    select
        * 
    from
        ENTITY
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )]
[Entity] : ( id 3 , data : entity2 , master.Id : 1 , date : null )]
[Entity] : ( id 4 , data : entity100 , master.Id : 2 , date : null )]
[Entity] : ( id 5 , data : entity102 , master.Id : 2 , date : null )]
As can be seen, for the same SQL, the SQLQuery execution resulted in a list of Entities. The reason for this is the addEntity() method.
  • Hibernate now loads the result set. 
  • It discovers the column names and data types based on mapping information for the Entity class. 
  • It then creates Entity objects for every row, populates the fields and returns the list.
What if we need to fetch data from two tables. E.g We need to load the Master and Entity records ? We would execute a join query.
public static void testJoinSelect() {
    final Session session = sessionFactory.openSession();
    SQLQuery q = session
        .createSQLQuery("select {e.*}, {c.*} from ENTITY e join CHILD_ENTITY c "
            + "on e.ID = c.ENTITY_ID " 
            + "where e.ID = 2");
    q.addEntity("e",Entity.class);
    q.addJoin("c", "e.children");
    List<Object[]> rows = q.list();
    for (Object[] row : rows) {
        System.out.println(row[0] + " " + row[1]);
    }
}
The output is :
Hibernate: 
    /* dynamic native SQL query */ 
   select
        e.ID as ID0_0_,
        e.NAME as NAME0_0_,
        e.DATE as DATE0_0_,
        e.MASTER_ID as MASTER4_0_0_,
        c.ENTITY_ID as ENTITY3_0__,
        c.ID as ID0__,
        c.ID as ID3_1_,
        c.`KEY` as KEY2_3_1_,
        c.ENTITY_ID as ENTITY3_3_1_ 
    from
        ENTITY e 
    join
        CHILD_ENTITY c 
            on e.ID = c.ENTITY_ID 
    where
        e.ID = 2
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )] [Child] : ( 
id 1 , key : 1001 , parent.Id : 2 )]
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )] [Child] : ( 
id 3 , key : 1003 , parent.Id : 2 )]
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )] [Child] : ( 
id 4 , key : 1004 , parent.Id : 2 )]
The addJoin() method simply causes all associations in the set to be populated.
If we simply need columns and not entities then :
public static void testLoadColumns() {
    final Session session = sessionFactory.openSession();
    SQLQuery q = session
    .createSQLQuery("select e.name as entityName from ENTITY e where e.ID = 2");        
    q.addScalar("entityName");       
    List<String> names = q.list();
    System.out.println(names);
}
The result is :
Hibernate: 
    /* dynamic native SQL query */ 
    select
        e.name as entityName 
    from
        ENTITY e 
    where
        e.ID = 2
[entity1]
The addScalar() method is used to retrieve columns from the result set. Hibernate automatically converts the data into appropriate type. The automatic conversion does not work in all cases and in that case we have an overloaded version of addScalar():
public SQLQuery addScalar(String columnAlias, Type type)
In fact we can even mix Entities and scalar values. I decided to load the Entity record along with its Master's data attribute:
public static void testLoadColumnAndEntity() {
    final Session session = sessionFactory.openSession();
    SQLQuery q = session
        .createSQLQuery(
            "select {e.*},m.data as mData "
            + "from ENTITY e join Entity_Master m on e.master_id = m.id "
            + "where e.ID = 2").addScalar("mData")
        .addEntity("e", Entity.class)
        .addScalar("mData");
    List<Object[]> rows = q.list();
    for (Object[] row : rows) {
        System.out.println(row[1] + " " + row[0] );
    }
}
The result is :
Hibernate: 
    /* dynamic native SQL query */ 
    select
        e.ID as ID0_0_,
        e.NAME as NAME0_0_,
        e.DATE as DATE0_0_,
        e.MASTER_ID as MASTER4_0_0_,
        m.data as mData 
    from
        ENTITY e 
    join
        Entity_Master m 
            on e.master_id = m.id 
    where
        e.ID = 2
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )] master No 1

17 comments:

  1. helped me, thanks!

    ReplyDelete
  2. good article,Thanks

    ReplyDelete
  3. And there is another reason behind using sql queries; In some situations we have to use database specific keywords like reserved keywords in oracle; http://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm.

    ReplyDelete
  4. The method "session.createSQLQuery()" seems very useful but the keyword session is not recognise by my compiler. I've tried unsuccessfully to look for the library org.hibernate.classic, that is suppose to content the above-mentioned method. Does anyone know whether or not I could download the library from Maven repository?

    Maven repository: http://search.maven.org/
    Library description: http://docs.jboss.org/hibernate/core/3.3/api/org/hibernate/classic/Session.html

    ReplyDelete
    Replies
    1. You should have the class as a part of your hibernate download. I think I had written this code using version 3.3

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. I have tried your example for testJoinSelect() and it throws an error ResultSet not Found.
    I have the mapping to the children in the Entity class file as

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "ID", referencedColumnName = "ENTITY_ID", insertable = false, updatable = false)
    private Listchildren;

    if I have remove that and make the children as Transient property
    @Transient
    private List children;

    then it give me a different error
    org.hibernate.QueryException: could not resolve property: children of: com.example.model.web.Entity

    I really appreciate your help. Thanks in advance

    ReplyDelete
  7. Really helpful. great blog. do you have any pointers on performance of the 2 usages - createquery vs createsqlquery. Can't seem to wrap my mind around which should i prefer when.

    ReplyDelete
    Replies
    1. If the name of your table changes, then chances are that you also need to update the name of your mapper Class. That alone requires to manually update the name in every HQL query String, so not much of an advantage there.

      Delete
  8. Thanks for This article! Really helpful for beginners and advanced users!!
    Keep posting!

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Great examples. Many thanks for this blogpost.

    ReplyDelete
  11. hi
    i am using manytomany relationship to testrunplan and attachments
    it is stored into join table successfully
    but after sometime records are getting automatically.
    how to resolve the issue

    ReplyDelete
  12. Hi,

    Am using fluent nhibernate in c#, when I am joining with multiple table I am getting "Value cannot be null.\r\nParameter name: key" exception , Please help me out

    ReplyDelete