Using SQL from within Hibernate.

Most of the time Hibernate just works and our experience is smooth and pleasant. Therefore when it is not that easy is comes as a surprise: Why so?! Hibernate documentation says that it is easy to use SQL from within Hibernate. Lets try it.

We can do it with using named SQL queries, or simply by embedding our SQL instructions directly in the source code.

Embedding example :

public void runQuery(){ 
39       SQLQuery q = TestUtilities.getHSession().createSQLQuery
                       ( "SELECT street, zip FROM sql_addresses"); 
40       q.addScalar( "street", Hibernate.STRING); 
41       q.addScalar( "zip", Hibernate.STRING); 
42       printResults( q ); 
43     }

Named SQL call example:

 
46    
47     public void runNamedQuery(){ 
48       SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" ); 
49       printResults( q ); 
50     }

and the definition of the named query is:

 
5     
6    <hibernate-mapping package="com.sourcelabs.hibernate.bhw.bags" > 
7     
8    <sql-query name="select_address"> 
9      <return-scalar column="street" type="java.lang.String"/> 
10     <return-scalar column="zip" type="java.lang.String"/> 
11     select street, zip from sql_addresses 
12   </sql-query> 
13   </hibernate-mapping>

as we can see in both cases we need to specify aliases and types for the query before it can be executed. If we do not do it then we will get exception like this:

  Caused by: org.hibernate.QueryException: addEntity() or addScalar() must be called on a sql query
   before executing the query. [SELECT street, zip FROM sql_addresses]
	at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:169)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:140)
	at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest.printResults(SQLCooperationTest.java:52)
	at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest.runQuery(SQLCooperationTest.java:43)
	at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest$$FastClassByCGLIB$$79fd0338.
	invoke(<generated>)
	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept
	(Cglib2AopProxy.java:635)
	at com.sourcelabs.hibernate.bhw.sql.SQLCooperationTest$$EnhancerByCGLIB$$511db1d7.
	runQuery(<generated>)
  

This default behavior is somehow odd and counterintuitive because H can return an array of object by default, but for unclear reasons H does not do that. Another potentially useful feature could be returning an array of maps. And again there is enough information at runtime to make intelligent mapping of columns to keys and values. I think Hibernate should learn something from iBatis. If you need to use complex and/or RDBMS specific SQL extensively throughout then you might consider using Hibernate and iBatis together.