With groovy.sql.newInstance in grails - who closes the connection and when?

Question

I'm using grails but I have lot's of stored procedures I'm trying to call from groovy.Sql.newInstance...

In all the examples I've found I never see anyone actually calling close on the connection. But when I tried running a bunch of methods within one response that each uses its own call to newInstance, then it got an error that there were too many connections. That leads me to believe that it isn't pooling the connections. That's a bummer. So do people create one connection and pass it around? Does grails and groovy close the connection at the end of the request?

Answer 1

I don't think that the connection is automatically closed after a request (which wouldn't be cool either), but you can manually close the connection used by the Sql instance:

Sql sql = Sql.newInstance("jdbc://...")
// some queries
sql.close()

See the JavaDoc.

If you would like to use pooled connections (which is surely a good idea), you should be able to create a pooled BasicDataSource (as Spring bean) and use the Sql(DataSource dataSource) constructor of Sql, rather than newInstance().

Answer 2

See http://jira.codehaus.org/browse/GRAILS-5454