SQL SERVER 2012 HOSTING :: Hibernate Ability to execute SQL Queries


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:
We may have certain tables or views not mapped in Hibernate that we may need to execute a query against.
We may need to apply some SQL level optimizations to the query and this can be done only via the SQL query.
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:

The query generated is a straight select all records query. This same SQL query can also be executed in Hibernate:

The result returned by the code is :

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 ?

As can be seen the result here as been assigned to a list of Entities. But will it work ? The result is:

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.

The output is :

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():

In fact we can even mix Entities and scalar values. I decided to load the Entity record along with its Master’s data attribute:

The result is :