Sql – Grails sql queries

grailssql

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

Best Solution

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.