SQL Zone is brought to you in partnership with:

Hi all, my name is Hubert A. Klein Ikkink. Not a very common name, right? To make things easier I just picked the first letters of my firstname and surname and came up with haki. So there you have it, now I am also known as Mr. Haki or mrhaki for short. You can read more blog postings at www.mrhaki.com. I am a passionate Groovy and Java developer based in Tilburg, The Netherlands. My goal is to write clean, elegant, user-centered and high quality software. You can find me on Google+ and Twitter. Hubert is a DZone MVB and is not an employee of DZone and has posted 151 posts at DZone. You can read more from them at their website. View Full User Profile

Grails Goodness: Using Hibernate Native SQL Queries

03.20.2014
| 6249 views |
  • submit to reddit

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 nameorganisation_breadcrumbs. This stored procedure takes one argument startId and will return a list of results with an id, name and level column.

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

import com.mrhaki.grails.Organisation

class OrganisationService {

    // Auto inject SessionFactory we can use
    // to get the current Hibernate session.
    def sessionFactory

    List<Organisation> breadcrumbs(final Long startOrganisationId) {

        // Get the current Hiberante session.
        final session = sessionFactory.currentSession

        // Query string with :startId as parameter placeholder.
        final String query = 'select id, name, level from organisation_breadcrumbs(:startId) order by level desc'

        // Create native SQL query.
        final sqlQuery = session.createSQLQuery(query)

        // Use Groovy with() method to invoke multiple methods
        // on the sqlQuery object.
        final results = sqlQuery.with {
            // Set domain class as entity. 
            // Properties in domain class id, name, level will
            // be automatically filled.
            addEntity(Organisation)

            // Set value for parameter startId.
            setLong('startId', startOrganisationId)

            // Get all results.
            list()
        }

        results
    }

}

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.

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

import org.hibernate.transform.AliasToEntityMapResultTransformer

class OrganisationService {

    def sessionFactory

    List<Map<String,Object>> breadcrumbs(final Long startOrganisationId) {
        final session = sessionFactory.currentSession

        final String query = 'select id, name, level from organisation_breadcrumbs(:startId) order by level desc'

        final sqlQuery = session.createSQLQuery(query)

        final results = sqlQuery.with {
            // Assign result transformer.
            // This transformer will map columns to keys in a map for each row.
            resultTransformer = AliasToEntityMapResultTransformer.INSTANCE             

            setLong('startId', startOrganisationId)

            list()
        }

        results
    }

}

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

// File: grails-app/services/com/mrhaki/grails/OrganisationService.groovy
package com.mrhaki.grails

class OrganisationService {

    def sessionFactory

    List<Map<String,String>> breadcrumbs(final Long startOrganisationId) {
        final session = sessionFactory.currentSession

        final String query = 'select id, name, level from organisation_breadcrumbs(:startId) order by level desc'

        final sqlQuery = session.createSQLQuery(query)

        final queryResults = sqlQuery.with {
            setLong('startId', startOrganisationId)
            list()
        }

        // Transform resulting rows to a map with key organisationName.
        final results = queryResults.collect { resultRow ->
            [organisationName: resultRow[1]]
        }

        // Or to only get a list of names.
        //final List<String> names = queryResults.collect { it[1] }

        results
    }

}

Code written with Grails 2.3.7.

Published at DZone with permission of Hubert Klein Ikkink, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)