Grails 2 multiple dynamic datasources in services

Question

I am working on a Grails application where I must access several datasources. The datasources are defined in the default database (ie. they are stored there and I must make a call to the default database to retrieve a list of the datasource names I must prepare to connect to). When the server boots up I retrieve the list of databases, create the datasource beans and inject them. All dynamically added databases are structurally identical (ie. have the same table and domain object structure).

This question is the closest I got to a useful piece of code, but it's not quite what I need.

Issue #1

• When I register the datasource beans, they show up where I expect, but Grails does not pick them up.

This is how I add them:

// Register datasource bean
def beanName = 'dataSource_devDB1'

BeanBuilder bb = new BeanBuilder()
bb.beans {
    "${beanName}"(BasicDataSource) { 
        url = "jdbc:h2:devDB1Db;MVCC=TRUE"
        pooled = true
        driverClassName = "org.h2.Driver"
        username = "sa"
        password = ""            
    }
}

bb.registerBeans(grailsApplication.mainContext)

// check that it registered
def ctx = grailsApplication.mainContext
def ctxlist = ctx2.beanDefinitionNames.findAll{it.contains( 'dataSource' )}

log.info "ctxlist = " + ctxlist

This prints:

[dataSource, dataSourceUnproxied, dataSource_devDB1]

When I do this, I can execute operations on the default datasource, and that's it.

Issue #2

• If I declare all my datasources as part of the Datasource.groovy file, then I can execute operations on all my databases, but not as advertised by the documentation

It works if I do the static mapping on my domain objects:

static mapping = {datasources(['devDB1', 'devDB2', 'DEFAULT')] or datasource = 'ALL'

but what I want is to perform all this as part of a service, and declaring my domain objects to use ALL datasources.

Declaring the datasource in the service is not working:

class secureDBService{

  static datasource = "devDB1"

  def readWriteMethod(){
   .....
  // this always uses the default datasource ignoring the static property above.
  // the only time it uses devDB1 is if I declare it as part of the domain datasource
  // mapping
  }
}

This will always use the default datasource no matter what. The only time when it uses the correct datasource is if on the domain object I list the datasource in question.

---

So, has anyone:

1. tried adding dynamic datasources and succeeded?

2. switched between datasources using grails services?

3. (and this would be a fantastic extra , as a "cherry on top") had success using multiple datasource with spring security core? How do you switch the datasource for the security plugin?

Thanks

--

Answer 1

I've got two different datasources working with Grails 2.3.11. I'm using 1 datasource for my H2 database and another for an Oracle db. I had to use Hibernate 4 with Grails 2.3. In my BuildConfig.groovy I specified the dependency on hibernate 4:

runtime ":hibernate4:4.3.5.4"

In my DataSource.groovy file I used the following Hibernate caching settings:

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
    singleSession = true // configure OSIV singleSession mode
}

(SIDE NOTE: without the cache settings, I was getting following CacheManager error, "Another unnamed CacheManager already exists in the same VM". There's an open bug report on this at https://jira.grails.org/browse/GPCACHEEHCACHE-13, but once I put the settings in place the error was gone.)

then I defined my datasources:

environments {
    development {
        dataSource_oracle {
            pooled = true
            dialect = org.hibernate.dialect.Oracle10gDialect
            driverClassName = 'oracle.jdbc.OracleDriver'
            username = 'user'
            password = 'pass'
            url = 'jdbc:oracle:thin:@(serverName):(port):(SID)'
            dbCreate = 'validate'
        }
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
            properties {
               jmxEnabled = true
               initialSize = 5
               maxActive = 50
               minIdle = 5
               maxIdle = 25
               maxWait = 10000
               maxAge = 10 * 60000
               timeBetweenEvictionRunsMillis = 5000
               minEvictableIdleTimeMillis = 60000
               validationQuery = "SELECT 1"
               validationQueryTimeout = 3
               validationInterval = 15000
               testOnBorrow = true
               testWhileIdle = true
               testOnReturn = false
               jdbcInterceptors = "ConnectionState"
               defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
            }
        }
    }
}

By default, my Domain classes use the H2 db and I specify my Oracle datasource as:

class MyService {

    def dataSource_oracle
    static transactional = true

    def getMethod() {
        assert dataSource_oracle != null, "dataSource is null! Please check your configuration!"
        def sql = Sql.newInstance(dataSource_oracle)
        ...
    }
}

Above, I allow the dependency injection to provide the service with the oracle datasource, def dataSource_oracle. If I want to use the H2 datasource, I declare the datasource as def dataSource and allow the DI to inject my other datasource.

---

I could not get the two datasources to work as specified in the documentation at http://grails.github.io/grails-doc/2.3.11/guide/conf.html#multipleDatasources. By declaring the datasources as dataSource and dataSource_lookup then using it as:

class DataService {
   static datasource = 'lookup'

   void someMethod(...) {
      …
   }
} 

but I was able to get it working with the solution described above.

---

Answer 2

I worked on similar project where the application has to retrieve list of datasources (connection strings) from the default database, and connect to each datasource and perform operations using quartz jobs.

I implemented it like, connect to each datasource within the application (not from DataSorce.groovy) and write SQL rather tahn HQL.

import groovy.sql.Sql

class SqlService{
    Sql getDbConnection(String connectionString, String dbUser, String dbPassword){
        def sql = Sql.newInstance(connectionString, dbUser, dbPassword, "driver_class")
        return sql
    }
}

Get sql connection from the above code and execute SQL queries using sql.execute "SQL STATEMENT" and close the sql connection. Here is the Sql class documentation.

Answer 3

You can add multiple data sources in a single application, and access them in the services.

Firstly you need to add basic data sources in the resources.groovy.

first import BasicDataSource

import org.apache.commons.dbcp.BasicDataSource;

Then

    switch (grails.util.GrailsUtil.environment) {
    case "development":
        firstDataSource( BasicDataSource ) {
            driverClassName = "net.sourceforge.jtds.jdbc.Driver"
            url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=<Db_name>"
            username = "sa"
            password = "root"
            String SqlServerInstance = "SQLEXPRESS";
            url = url + ";" + SqlServerInstance;
        }
        break

    case "test":
        firstDataSource( BasicDataSource ) {

            driverClassName = "net.sourceforge.jtds.jdbc.Driver"
            url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=<Db_name>"
            username = "sa"
            password = "root"
            String SqlServerInstance = "SQLEXPRESS";
            url = url + ";" + SqlServerInstance;            }
        break;

}

In the same way you can add more data sources, above code will give you one more datasource to access other than the default one. I used switch to configure same data source for the different environments, same way more can be added.

and in the service it can be accessed in the services as:

BasicDataSource firstDataSource;
Connection con = firstDataSource.getConnection();

and then connection object can be used.

I think it should help

Answer 4

Use sharding plugin for grails that will address your issue