Over the past few months we have been adding some simplifications to the way you can use and specify native sql queries in Hibernate. Gavin even blogged about some of them earlier , but I thought it were about time we brought some more news on this blog about it.

Auto detect return types and aliases

The newest feature related to native sql is that Hibernate will now auto detect the return types and even aliases of any scalar value from a sql query.

Before you had to do something like:

List l = s.createSQLQuery("SELECT emp.regionCode as region, emp.empid as id, {employee.*} 
                           FROM EMPLOYMENT emp, EMPLOYEE employee ...")
         .addScalar("region", Hibernate.STRING)
         .addScalar("id", Hibernate.LONG)
         .addEntity("employee", Employee.class)
         .list();
Object[] data = l.get(0);

Today you do not need to specify the type of the scalar values, but simply the alias name so Hibernate knows what data you actually want to extract from the resultset.

List l = s.createSQLQuery("SELECT emp.regionCode as region, emp.empid as id, {employee.*} 
                           FROM EMPLOYMENT emp, EMPLOYEE employee ...")
         .addScalar("region")
         .addScalar("id")
         .addEntity("employee", Employee.class)
         .list();
Object[] data = l.get(0); 

If the query is only returning scalar values then addScalar is not needed at all; you just call list() on the query.

List l = s.createSQLQuery("SELECT * FROM EMPLOYMENT emp").list();
Object[] data = l.get(0); 

It of course needs some more processing from Hibernate, but it makes experimentation and some data processing problems easier to do.

No redundant column mappings

Previously when you specified native sql in named queries you had to use the return-property element to (redundantly) specify which column aliases you wanted Hibernate to use for your native sql query. It were redundant because in most cases you would simply just be specifying the exact same columns as you had just done in the class mapping.

Thus it could get pretty ugly and verbose when you were starting to have even just mildly complex mappings such as the following which is from our unit tests for a native sql stored procedure call.

<sql-query name="selectAllEmployees" callable="true">
 <return alias="employement" class="Employment">
 <return-property name="employee" column="EMPLOYEE"/>
 <return-property name="employer" column="EMPLOYER"/>                     
 <return-property name="startDate" column="STARTDATE"/>
 <return-property name="endDate" column="ENDDATE"/>                       
   <return-property name="regionCode" column="REGIONCODE"/>                       
   <return-property name="id" column="EMPID"/>                                            
   <return-property name="salary"> 
    <return-column name="VALUE"/>
    <return-column name="CURRENCY"/>                      
   </return-property>
 </return>
 { call selectAllEmployments() }
</sql-query>

In the upcoming Hibernate 3.1 you can do the exact same with loss less code:

<sql-query name="selectAllEmployees" callable="true">
 <return class="Employment"/>
 { call selectAllEmployments() }
</sql-query>

or in code (for normal sql):

List l = s.createSQLQuery("SELECT * FROM EMPLOYMENT emp")
          .addEntity(Employee.class)
          .list();
Object[] data = l.get(0); 

This also removes the need for always using the curly brackets syntax (e.g. {emp.name})to handle the aliasing as long as you are not returning the same entity type more than once per row.

Hope you like it, Enjoy :-)


Back to top