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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

46

47 public void runNamedQuery(){

48 SQLQuery q1 q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q1 q );

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 [[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.

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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

46

47 public void runNamedQuery(){

48 SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q );

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 [[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. Also taken directly from Hibernate's documentation you can find..

"It is possible to leave out the type information for all or some of the scalars.

sess.createSQLQuery("SELECT * FROM CATS")

.addScalar("ID", Hibernate.LONG)

.addScalar("NAME")

.addScalar("BIRTHDATE")

"

Hmm, no alias there, but in your query you have a where clause so I highly recommend an alias anyway.

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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

46

47 public void runNamedQuery(){

48 SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q );

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 [[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.

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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

<code>

46

47 public void runNamedQuery(){

48 SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q );

50 }

</code>

</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 [[iBatis]]. If you need to use complex and/or RDBMS specific SQL extensively throughout then you might consider using Hibernate and iBatis together.

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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

46

47 public void runNamedQuery(){

48 SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q );

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:

<code>

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

</pre>

</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 [[iBatis]]. If you need to use complex and/or RDBMS specific SQL extensively throughout then you might consider using Hibernate and iBatis together.

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.

h2. Embedding example : SQLCooperationTest.java

<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

<pre>

46

47 public void runNamedQuery(){

48 SQLQuery q = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );

49 printResults( q );

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:

<code>

<pre>

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

</pre>

</code>

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.