Wysmedia.com

Icon

~ We make IT easy for you ~

May 4, 2009

Native SQL Query on Grails

As we know, Hibernate Query Language or using GORM are not enough, for example if we want to create complex queries or using native sql functions ie. date() function in MySQL. That’s why we need to use Groovy SQL to achive that.

Before we started, I want to let you know that once we using native functions in sql, we might got problem when we tried to deploy on another database server. So be wise.

Here is simple example how to do that :

1
2
3
4
5
6
7
8
9
10
11
12
13
14

import groovy.sql.Sql;
 
class ReportController { 
    def dataSource ; 
    .....
    def print = {
        .....
        def sql = new Sql(dataSource);
        List lparams = [startDate, endDate];
        def query2 = "select * from invoiceh where trx_date between ? and ? ";
        def result = sql.rows(query2,lparams);
        ......  // fetch the result into your gsp / you can process it further
    }
}

This code will return result as array / collections.
Simple isn’t it ?

Category: Programming, database, grails, java, mysql, sql

Tagged:

2 Responses

  1. ldittmar says:
    July 7, 2009 at 7:53 pm

    How can I get the dataSource properties from DataSource.groovy
    …
    environments {
    development {
    dataSource {
    pooled = false
    loggingSql = true
    driverClassName = “net.sourceforge.jtds.jdbc.Driver”
    username = “xxxxxxxxxxxx”
    password = “xxxxxxxxxxxx”
    dbCreate = “update” // one of ‘create’, ‘create-drop’,'update’
    url = “jdbc:jtds:sqlserver://1.1.1.1/GroovyDB”
    }
    }

    Thanks! ;-)

  2. adwin says:
    July 7, 2009 at 10:33 pm

    look at line 4. it is how to declare dataSource (injected by spring framework actually)

Leave a Reply