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

I updated my dataSource to the following, and it worked. I am not sure though what the reasoning behind it is.

 datasources = {  

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

Answer 2

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
    }
}

Answer 3

Example of how one might use multiple datasource in Grails Services with SQL.

Hint: You can use either TestServiceWithInjection or TestService. Both works fine.

DataSource.groovy

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "com.mysql.jdbc.Driver"
    dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
//    cache.region.factory_class = 'org.hibernate.cache.SingletonEhCacheRegionFactory' // Hibernate 3
    cache.region.factory_class = 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory' // Hibernate 4
    singleSession = true // configure OSIV singleSession mode
    flush.mode = 'manual' // OSIV session flush mode outside of transactional context
}

// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:mysql://localhost:3306/database1"
            username = "root"
            password = "password"
        }
        dataSource_second {
            driverClassName = "com.mysql.jdbc.Driver"
            dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
            dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:mysql://localhost:3306/database2"
            username = "root"
            password = "password"
        }
    }
    test {
        dataSource {
            //Used by local test run (grails test-app)
            dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:mysql://test-server.com:3306/test_ci"
            username = "root"
            password = "password"
        }
    }
}

TestServiceWithInjection.groovy

package com.github.biniama

import grails.transaction.Transactional
import groovy.sql.Sql

import javax.annotation.PostConstruct

@Transactional
class TestService {

    def dataSource_second

    Sql sql

    @PostConstruct
    def initSql() {
        sql = new Sql(dataSource_second)
    }

    def getData() {
        def q = "SELECT id FROM job LIMIT 1"
        return sql.rows(q)
    }
}

TestService.groovy

package com.github.biniama

import grails.transaction.Transactional
import groovy.sql.Sql

@Transactional
class TestService {

private Sql sql

void setDataSource_second(def dataSource) {
    sql = new Sql(dataSource)
}

Integer getData() {
    def q = "SELECT id FROM job LIMIT 1"
    return sql.rows(q)
}

}

TestController.groovy

package com.github.biniama

class TestController {

  TestService testService

  def index() {
    Integer result = testService.getData()
    render "Returned value is ${result}"
  }
}

Answer 4

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 5

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 6

I did this in the BootStrap of the last project I worked on. Just remember, valid Java code is also valid Groovy (mostly), and you don't have to do everything the "Grails Way". Just connect to the "from" database in a way you're comfortable with, and store stuff in the Grails datasource via Grails domain object manipulation. Sample code:

try {
    Connection con = DriverManager.getConnection ("jdbc:xxxx", "username", "password")
    ResultSet resultSet = con.createStatement().executeQuery("SELECT * FROM the_table")
    while(resultSet.next()) {
        DomainObject domainObjectInstance = new DomainObject(attributeA: resultSet.getString('attributeA'), attributeB: resultSet.getString('attributeB'))
        if (!domainObjectInstance.save(flush: true)) {
            println "Unable to save DomainObject: ${domainObjectInstance.errors}"
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}