Using two databases for an application in grails

Question

I am creating an application in grails that should read from one database and write into another database. I have created datasources.groovy for this requirement and have installed the datasources plugin. However, I am stuck at how to use this datasource when executing an sql query (select * from........etc. etc).

For eg. Below is how I run a query in my action. I am using customized queries and not gorm.

EDITED:

class TuneController {   

    def dataSource_ds2

    def list = {

        String nameSql = "select name from emp where id=3345"
        Sql sql = new Sql(dataSource_ds2)
        String name = sql.rows(nameSql)
        println(name)
    }
}

In the above case, datasources is not read and has a null value. Is there any sample code available for this requirement.

Am I missing something here?

EDIT:

My Datasources.groovy entry is as below.

datasources = { 

    datasource(name:'ds2') {
        domainClasses([com.Tune])
        readOnly(true)
        driverClassName('oracle.jdbc.driver.OracleDriver')
        url('jdbc:oracle:thin:@test-ofr.wellmanage.com:1521:OFRS1')         
        username('test')
        password('test')
        environments(['development'])
        dbCreate('do-not-bother')
        logSql(true)
        dialect(org.hibernate.dialect.Oracle10gDialect)
        hibernate {
            cache {
                use_second_level_cache(false)
                use_query_cache(false)
            }
        }
    }
}

Answer 1

The secondary datasources are available using dependency injection, but their names are based on the names in Datasources.groovy. For example if you've defined a datasource named 'foo', then you would inject that with def dataSource_foo:

class MyController {

   def dataSource_foo

   def list = {
      String nameSql = "select name from emp where id=3345"
      Sql sql = new Sql(dataSource_foo)
      def rows = sql.rows(nameSql)
      ...
   }
}

Note that you must put def dataSource_foo as a class-scope field and not inside your action (or method). This is true for every dependency injection - if it's inside a method or a closure it's just a method-scope variable.

Answer 2

Just to update the answer for this question (I just got a new project that requires using two different mysql DBs). I had to upgrade to grails 2.0 (yeah, I've been too lazy upgrading from 1.3.7) since it has built in support for multiple datasources (no need to use the plugin).

Grails 2.0 - multiple datasources

From the example, you only need to set the DBs in the DataSource.groovy file

environments {
development {
    dataSource {
        dbCreate = "create-drop"
        url = "jdbc:h2:mem:devDb"
    }
    dataSource_lookup {
        dialect = org.hibernate.dialect.MySQLInnoDBDialect
        driverClassName = 'com.mysql.jdbc.Driver'
        username = 'lookup'
        password = 'secret'
        url = 'jdbc:mysql://localhost/lookup'
        dbCreate = 'update'
    }
}

Then in the domain class, specify which datasource:

class ZipCode {

    String code

    static mapping = { datasource 'lookup' } 
 }

Answer 3

I wonder why no one has mentioned 'c3p0:c3p0:0.9.1.2' plugin here.

This is best practice to implement multiple databases in grails application

Buildconfig.groovy

compile 'c3p0:c3p0:0.9.1.2'

datasource

 dataSource {
            dialect = 'com.example.hibernateutil.MySQL5InnoDBDialectBitFixed'
            dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
            driverClassName = "com.mysql.jdbc.Driver"
            url = "jdbc:mysql://127.0.0.1/demo
            username = "root"
            password = ""
        }

        dataSource_Demo {
            dialect = 'com.example.hibernateutil.MySQL5InnoDBDialectBitFixed'
            dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
            driverClassName = "com.mysql.jdbc.Driver"
            url = "jdbc:mysql://127.0.0.1/demo2"
            username = "root"
            password = ""
        }

resources.groovy

beans = {


   dataSource_Demo(ComboPooledDataSource) { bean ->
        bean.destroyMethod = 'close'
        //use grails' datasource configuration for connection user, password, driver and JDBC url
        user = grailsApplication.config.dataSource_Demo.username
        password = grailsApplication.config.dataSource_Demo.password
        driverClass = grailsApplication.config.dataSource_Demo.driverClassName
        jdbcUrl = grailsApplication.config.dataSource_Demo.url
        idleConnectionTestPeriod = 2 * 60 * 60 //2 hours
        testConnectionOnCheckin = true
    }

    /**
     * c3P0 pooled data source that allows 'DB keepalive' queries
     * to prevent stale/closed DB connections
     * Still using the JDBC configuration settings from DataSource.groovy
     * to have easy environment specific setup available
     */
    dataSource(ComboPooledDataSource) { bean ->
        bean.destroyMethod = 'close'
        //use grails' datasource configuration for connection user, password, driver and JDBC url
        user = grailsApplication.config.dataSource.username
        password = grailsApplication.config.dataSource.password
        driverClass = grailsApplication.config.dataSource.driverClassName
        jdbcUrl = grailsApplication.config.dataSource.url
        idleConnectionTestPeriod = 2 * 60 * 60 //2 hours
        testConnectionOnCheckin = true
    }
}