Most of the time Hibernate “just works”. Therefore when it is not that easy is comes as somewhat of a surprise. Hibernate documentation says it is easy to use SQL from within Hibernate, but 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 : SQLCooperationTest.java

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: SQLCooperationTest.java

 
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: sql.hbm.xml

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.

Hibernate perhaps could 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.

I don’t understand why that is a bad thing and why you would want to mix in two different ORM tools in your application. Just always use aliases, and if it isn’t to an object already mapped then include the types.