Groovy SQL

Okay today i am going to discuss about Groovy SQL .

1. First of you will have to import sql to your controller :

                                    import groovy.sql.Sql;

2. Then you have to define datasource globally  :

class controllerClass  {

           def dataSource

}

3. Now in you action 

def action (){

                def sql = Sql.newInstance(dataSource)

                def persons = []

sql.eachRow('Select * from Person') { persons << it.toRowResult() }
sql.close() println persons

}

third step will print you all the details of person domain/table.

okay lets explain all these one by one ::

1. Imported Sql class.

2. You need to define dataSource outside of your controller action. Otherwise spring cannot do the required dependency injection . Spring will auto wire this dependency for us by name and with that we are ready to go. After we create a new instance of the sql class we can call a method on it called rows that can take a SQL statement.

3. Now in the action: Define a new instance using the current datasource. Actually new instance make connection with database. 

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user", "pswd", "com.mysql.jdbc.Driver")

but here in Grails this job done by datasource.groovy as follows::

dataSource {
    pooled = true
    dbCreate = "update"
    url = "jdbc:mysql://localhost/yourDB"
    driverClassName = "com.mysql.jdbc.Driver"
    username = "yourUser"
    password = "yourPassword"
}

Docs recommends to close the Sql connection after opening it .

Constructs an SQL instance using the given Connection. It is the caller's responsibility to close the Connection after the Sql instance has been used. Depending on which features you are using, you may be able to do this on the connection object directly but the preferred approach is to call the close() method which will close the connection but also free any caches resources.

for some queries click here