In Grails I could create Sql object in 2 ways:
def sql = new Sql(sessionFactory.currentSession.connection())
def sql = new Sql(dataSource)
I have read this thread here on Stackoverflow: Getting the SessionFactory for a particular Datasource in Grails ... and one of the answers was that dataSource "...gobbles up excessive connections better to use sessionFactory.currentSession.connection()"
Is this recommendation correct and what is the difference between those two?
When Inspecting created objects I could see that they are almost the same, just 2 properties were different: dataSource and useConnection.
In case of dataSource it was dataSource=TransactionAwareDataSourceProxy and useConnection=null, while for sessionFactory it is dataSource=null and useConnection=$Proxy 36.
Why this makes difference with consequence of "gobblin up excessive connections"?
The comment about "gobbling up excessive connections" is based on a few assumptions, which may or may not be true in your case.
The assumption is that Hibernate is going to, or already has, or will, during the request create a connection to the database because of the session in view pattern used by Grails and GORM. In that case you would be using one connection for Hibernate and n-number for your other connections.
If you use a mix of GORM and SQL connections it's safer to get the connection from the sessionFactory.
I seem to recall older versions of Grails use to create the connection even if no GORM methods were executed during the request. I'm not entirely sure that's still the case with more recent versions (2.x+)