6

After running into this question today: Grails query not using GORM I wonder if using groovy.sql.Sql or JDBC comes with the benefits of connection pooling?

I can see under some circumstances how going GORMless could be beneficial, but lack of conn pooling would eliminate it as an option.

Would we also get the benefits of prepared statements?

Community
  • 1
  • 1
vector
  • 7,334
  • 8
  • 52
  • 80

2 Answers2

8

One of the main uses of a DataSource is to provide connection pooling. If you have set pooled = true in DataSource.groovy then the injected dataSource will give you a connection from the pool when you execute the query.

Groovy SQL also provides querying using prepared statements:

def sql = new Sql(dataSource)
def params = [10, 'Groovy', 'http://groovy.codehaus.org']
sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params

You can also enable PreparedStatement caching on the Sql object for performance:

sql.cacheStatements = true
Ken Liu
  • 22,503
  • 19
  • 75
  • 98
  • could you give 1 complete example of cacheStatements for fetching the rows from database. Actually i need to fetch a lot of data using query caching. – Bharti Rawat Apr 12 '19 at 06:44
6

If your datasource is configured to use connection pooling, the groovy sql will benefit from that.

Using the example of service:

class MyService {
  //inject dataSource
  def dataSource

  def myMethod() {
    Sql sql = new Sql(dataSource) 
    sql.execute("insert...") //this will get a connection from the pool
    sql.close() //this will release the connection back to pool
  }
}

To use the same connection for your methods, check cacheConnection