Getting the SessionFactory for a particular Dataso

Question

So if I want to do a direct SQL query using the session that Grails is using prior to supporting multiple datasources I could do:

def conn = new Sql(sessionFactory.currentSession.connection())

Now the question is that I have multiple datasources and want to grab a connection to a specific one.

How do I do that?

TIA

Answer 1

Better declare the data source in your bean (Service, Controller...) to be injected (no need to depend from Hibernate here)

def dataSource

and use it directly:

Sql sql = new Sql(dataSource)

If you have multiple data sources, just follow the naming convention

def dataSource_foo

Answer 2

Given a datasource defined in DataSource.groovy as "dataSource_foo", you'll have a SessionFactory called sessionFactory_foo. So you can dependency-inject it like any other Spring bean:

def sessionFactory_foo

and use it like this:

def conn = new Sql(sessionFactory_foo.currentSession.connection())

Answer 3

You can bind to the session using a Domain class reference as follows:

Book.withSession { session -> 
    def conn = new Sql(session.connection())
    ...
}

This method does not require a hardcoded reference to the datasource suffix.