• In my project I need some requirement to work with multiple database instance in same enviornment. I investigated over internet and found a solution. In this solution we can work with multiple database like mysql, oracle, mongodb,postgre etc.

    Now I will show you one demo project to work with two instance of same database. I'm using in my demo project.

    In this project I'm using Grails 2.3.7 and IDE Spring Tool Suits(STS).


    Step 1:

    Create grails project by selecting menu.

    File>New>Grails Project


    Step 2:

    Now open the BuilConfig.groovy file.



    If you are using mysql as a database then make sure there must be an entry of mysql connector jar.


    dependencies {

    // specify dependencies here under either 'build', 'compile', 'runtime', 'test' or 'provided' scopes e.g.

    runtime 'mysql:mysql-connector-java:5.1.27'

    // runtime 'org.postgresql:postgresql:9.3-1100-jdbc41'



    Step 3:

    Now we will create a connection class for mysql using JDBC which is kept centralized so that connection can be get any time whenever it is required.

    import grails.transaction.Transactional 
    import groovy.sql.Sql 
    import java.sql.Connection
    class CreateConnectionService {
            public Connection getConnection() {
                Connection con = null;
              try {
                       def driver = Class.forName("com.mysql.jdbc.Driver").newInstance();
                          Properties properties = new Properties();
                           properties.put("password", "testing")
                           con = driver.connect("jdbc:mysql://localhost:3306/test1?useUnicode=yes&characterEncoding=UTF-8", properties);
                   catch (Exception e){
                        System.out.println("Error in connection" + e);
                   return con;

    Step 4:


    Here is the grails datasource which will provide us another connection.



    dataSource {
        pooled = true
       jmxExport = true
            driverClassName = "com.mysql.jdbc.Driver"
           username = "root"
           password = "testing"
    hibernate {
     cache.use_second_level_cache = true
         cache.use_query_cache = false
       // cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
      cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory' // Hibernate 4
    /// environment specific settings
    environments {
      development {
               dataSource {
                        dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
                     url = "jdbc:mysql://localhost:3306/test2?useUnicode=yes&characterEncoding=UTF-8"
                     dialect = "com.domain.mysql.dialect.MySQLUTF8InnoDBDialect"

    Step 5:

    Now we will have one service which will implement the functionality of using two database in same enviornment. In which we will fetch data from one database which is created using JDBC and insert into another database which is created using grails.

    Here code will look something like this.

    class DataTransferService {
    def createConnectionService
    def dataTransfer()
    		Connection connection=createConnectionService.getConnection()
    		Statement stmt=connection.createStatement()
    		ResultSet rset=stmt.executeQuery("select * from User")
    		while( {
    			 def name=rset.getString("name")
    			 Person person = new Person(name:name)
    			catch(Exception e)
    				log.debug "Problem occured dataTransfer::"+e.getMessage()

    In above service we create simple service method in which we get data of User table using JDBD and inserted into Person table. In the same way we can perform any action by using this method.



    Thank you

Tags: grails