Using SQL From Within Hibernate

http://creativecommons.org/licenses/by-sa/2.5/ Using SQL From Within Hibernate Most of the time <a class="wikilink" href="http://swik.net/Hibernate">Hibernate</a> “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 <a class="wikilink" href="http://swik.net/SQL">SQL</a> queries, or simply by embedding our SQL instructions directly in the source code. <h2>Embedding example : SQLCooperationTest.java</h2> <pre> 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 } </pre> <h2>Named SQL call example: SQLCooperationTest.java</h2> <pre> 46 47 public void runNamedQuery(){ 48 SQLQuery q1 = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" ); 49 printResults( q1 ); 50 } </pre> and the definition of the named query is: sql.hbm.xml <code> <pre> 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> </pre> </code> 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: <pre> <code> 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>) </code> </pre> 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 <a class="wikilink" href="http://swik.net/iBatis">iBatis</a>. 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. Hibernate SQL example tag4sree Wed, 07 Jun 2006 11:00:55 -0700 Tue, 12 Aug 2008 03:28:19 -0700