How to work with multiple database in same enviornment in grails

Posted By : Tushar Paliwal | 25-Jul-2014

grails

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.

Demo>confg>BuildConfig.groovy

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("user","root")
                       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(rset.next()) {
			try
			{
			 def name=rset.getString("name")
			 Person person = new Person(name:name)
			 name.save(flush:true)
			}
			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.