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