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.

// 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 the list() 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.