Grails Goodness: Using Hibernate Native SQL Queries

Sometimes we want to use Hibernate native SQL in our code. For example we might need to invoke a selectable stored procedure, we cannot invoke in another way. To invoke a native SQL query we use the method createSQLQuery() which is available from the Hibernate session object. In our Grails code we must then first get access to the current Hibernate session. Luckily we only have to inject the sessionFactory bean in our Grails service or controller. To get the current session we invoke the getCurrentSession() method and we are ready to execute a native SQL query. The query itself is defined as a String value and we can use placeholders for variables, just like with other Hibernate queries.

In the following sample we create a new Grails service and use a Hibernate native SQL query to execute a selectable stored procedure with the name organisation_breadcrumbs. This stored procedure takes one argument startId and will return a list of results with an id, name and level column.

In the sample code we use the addEntity() method to map the query results to the domain class Organisation. To transform the results from a query to other objects we can use the setResultTransformer() method. Hibernate (and therefore Grails if we use the Hibernate plugin) already has a set of transformers we can use. For example with the org.hibernate.transform.AliasToEntityMapResultTransformer each result row is transformed into a Map where the column aliases are the keys of the map.

Finally we can execute a native SQL query and handle the raw results ourselves using the Groovy Collection API enhancements. The result of the list() method is a List of Object[] objects. In the following sample we use Groovy syntax to handle the results:

Code written with Grails 2.3.7.

Original article

This entry was posted in Grails and tagged , , by mrhaki. Bookmark the permalink.

About mrhaki

My name is Hubert A. Klein Ikkink also known as mrhaki. I work at the great IT company JDriven. Here I work on projects with Groovy & Grails, Gradle and Spring. At JDriven we focus on SpringSource technologies. All colleagues want to learn new technologies, support craftmanship and are very eager to learn. This is truly a great environment to work in. You can contact me via Google+ or @mrhaki.

Leave a Reply

Your email address will not be published. Required fields are marked *