If I use groovy sql class in grails, does it use the grails connection pooling?

Question

From the examples below in the sql documentation. If I use either of these ways to create a sql instance in the middle of a grails service class, will it use the grails connection pooling? Will it participate in any transaction capabilities? Do I need to close the connection myself? Or will it automatically go back into the pool?

def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver']
  def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

or if you have an existing connection (perhaps from a connection pool) or a datasource use one of the constructors:

  def sql = new Sql(datasource)

Now you can invoke sql, e.g. to create a table:

 sql.execute '''
        create table PROJECT (
          id integer not null,
          name varchar(50),
          url varchar(100),
        )
 '''

Answer 1

If you execute:

Sql.newInstance(...)

You will create a new connection and you aren't using the Connection Pool.

If you want to use the connection pool, you can create a Service with the following command:

grails create-service org.foo.MyService

Then, in your MyService.groovy file, you can manage transactions as follows:

import javax.annotation.PostConstruct

class MyService {
    def dataSource              // inject the datasource
    static transactional = true // tell groovy that the service methods will be transactional


    def doSomething() {
       sql = new Sql(dataSource)
       //rest of your code
    }
}

For more details you can read: http://grails.org/doc/2.0.x/guide/services.html

EDIT:

For manage multiple datasources you can do one of the following based on your Grails version.

If you are using a Grails version greater than 1.1.1 (not 2.x) you can use the following plugin:

http://grails.org/plugin/datasources

If you are using Grails 2.x you can use the out of the box support:

http://grails.org/doc/2.0.0.RC1/guide/conf.html#multipleDatasources

Answer 2

If you create the Sql object like this I believe it will use connection pooling

class SomeSerive {

  SessionFactory sessionFactory

  def someMethod() {
    Sql sql = new Sql(sessionFactory.currentSession.connection())
  }
}