Grails Sql Queries

Imagine I have something like this: def example = { def temp = ConferenceUser.findAllByUser(User.get(session.user)) [temp: temp] } Explaining my problem: Although dynamic fi

Solution 1:

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

classMyController {
    def dataSource
    defexample= {
        defsql=newSql(dataSource)
        [ temp: sql.rows("SELECT . . .") ]
    }
}

And within a transaction:

import groovy.sql.Sql

classMyController {
    def sessionFactory
    defexample= {
        defsql=newSql(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.

Solution 2:

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

defUserList= 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

Solution 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

classCarService{

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

   List<GroovyRowResult> allCars(finalString searchQuery) {
      finalString searchString = "%${searchQuery.toUpperCase()}%"finalString 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

defuserSqldefadminSql

Into the services that need them.

or without injection

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

// ...// in a methodSqlsql=newSql(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.