Grails sql queries

Question

Imagine I have something like this:

def example = {
   def temp = ConferenceUser.findAllByUser(User.get(session.user))
   [temp: temp]
}

Explaining my problem: Although dynamic finders are very easy to use and fast to learn, I must replace dynamic finders of my website for sql queries because it is a requirement. As I don't understand SQL that much, my main questions are:

a) I am using an SQLS database, with the drivers and datasource good configured and my website works as it is right now. If I want to replace the "findAllByUser" for an sql statement, should i do something like this:

def dataSource
...
def db = new Sql(dataSource)
def temp = db.rows("SELECT ... ")

b) And that will work? I mean, the temp object will be a list as it is if I use "findAllByUser", and do I need to open a connection to the database =?

Answer 1

yes, with grails you can do both plain sql and hql queries. HQL is 'hibernate query language' and allows you to write sql-like statements, but use your domain classes and properties instead of the table names and column names. To do an hql query, do something like

def UserList = ConferenceUser.executeQuery('from ConferenceUser cu where cu.user = ?', [user]),  

what you have here is a parameterized query -- executeQuery sees the ? in the hql string and substitutes the arguments in the array that is the second parameter to the method([user] in this case) for you.

See http://grails.org/doc/latest/ref/Domain%20Classes/executeQuery.html

and you can see this on how to do sql queries with Grails

Sql query for insert in grails

Answer 2

With Grails you can use Dynamic Finders, Criteria Builders, Hibernate Query Language (HQL), or Groovy SQL.

To use Groovy SQL:

1. import groovy.sql.Sql
2. Request a reference to the datasource with def dataSource or def sessionFactory for transactions
3. Create an Sql object using def sql = new Sql(dataSource) or def sql = new Sql(sessionFactory.currentSession.connection())
4. Use Groovy SQL as required

Grails will manage the connection to the datasource automatically.

Sql.rows returns a list that can be passed to your view.

For example:

import groovy.sql.Sql

class MyController {
    def dataSource
    def example = {
        def sql = new Sql(dataSource)
        [ temp: sql.rows("SELECT . . .") ]
    }
}

And within a transaction:

import groovy.sql.Sql

class MyController {
    def sessionFactory
    def example = {
        def sql = new Sql(sessionFactory.currentSession.connection())
        [ temp: sql.rows("SELECT . . .") ]
    }
}

I recommend the book Grails Persistence with GORM and GSQL for a lot of great tips and techniques.

Answer 3

Going Further / Tips

• Use Spring beans

You can make the groovy.sql.Sql instance a Spring bean in your Grails application. In grails-app/conf/spring/resources.groovy define the Sql bean:

// File: grails-app/conf/spring/resources.groovy

beans = {

    // Create Spring bean for Groovy SQL.
    // groovySql is the name of the bean and can be used
    // for injection.
    sql(groovy.sql.Sql, ref('dataSource'))

}

Next inject the Sql instance in your your class.

package com.example

import groovy.sql.GroovyRowResult

class CarService {

   // Reference to sql defined in resources.groovy.
   def sql

   List<GroovyRowResult> allCars(final String searchQuery) {
      final String searchString = "%${searchQuery.toUpperCase()}%"

      final String query = '''\
         select id, make, model
         from car
         where ...
         '''

        // Use groovySql bean to execute the query.
        final results = sql.rows(query, search: searchString)
        results
   }
}

• Multiple Datasources

  adminSql(groovy.sql.Sql, ref("dataSource_admin"))

  userSql(groovy.sql.Sql, ref("dataSource_user"))

and inject the beans

def userSql
def adminSql

Into the services that need them.

or without injection

import groovy.sql.Sql
// ...
// inject the datasource bean
def dataSource_admin

// ...
// in a method
Sql sql = new Sql(dataSource_admin)

Early Grails Version

Looping through GORM result sets in early grails versions can cause needless queries in the middle of template loops. Using groovy SQL can help with this.