connection pooling and prepared statements with groovy.sql.Sql or JDBC in Grails

Question

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?

Answer 1

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