CODEWITHSUNDEEP
grailsdatasource
MVZ
MVZ

Reputation: 2250

Grails - Getting data from a different datasource and saving it in my Grails database

I have a a Grails project that needs to retrieve data from a database that is running on a different project. This other project is running on a different platform(Drupal), and has different domains. I just need read some of the tables in this database, and save it on my own datasource.

What would be the best way of accomplishing this?

Upvotes: 1

Views: 1853

Answers (1)

Burt Beckwith
Burt Beckwith

Reputation: 75671

The quickest way would be to use GORM's support for multiple datasources, which is intended to be used to partition your domain classes between two or more databases, but you don't have to assign any to this second datasource. One small downside would be that doing it this way will create an additional transaction manager, Hibernate session factory, and a couple more classes and Spring beans, but they won't take up much memory without anything using them. To do this, add a second dataSource block in DataSource.groovy with a unique suffix (it doesn't affect anything other than the Spring bean names), e.g.

dataSource_drupal {
   pooled = true
   driverClassName = '...'
   username = '...'
   password = '...'
   url = '...'
}

Since this datasource won't be used for GORM you don't need to specify dialect, dbCreate, or jmxExport, and you don't need a second hibernate block, only the information needed to create the connection pool (it'll create 10 initial connections by default).

If you're concerned about the extra memory of this approach (you shouldn't be, it will be minimal) you can do a bit more work and register a Spring bean manually in grails-app/conf/spring/resources.groovy. If you're using a recent version of Grails the datasource implementation is the Tomcat JDBC Connection Pool, so use its driver class and setter property names to specify the connection information. Use any valid Spring bean name, but I'd follow the conventions from the multiple datasources support:

import org.apache.tomcat.jdbc.pool.DataSource

beans = {

   dataSource_drupal(DataSource) { bean ->
        bean.destroyMethod = 'close'

        driverClassName = '...'
        url = '...'
        username = '...'
        password = '...'

        // optional extra settings, not really needed
        // unless you expect a lot of usage
        initialSize = 42
        testOnBorrow = true
        testWhileIdle = false
        testOnReturn = false
        validationQuery = 'SELECT 1'
   }
}

So to use your second datasource, dependency-inject it into the service(s) you'll use to do the data migration work:

def dataSource_drupal

and to do the SQL queries, your best bet is groovy.sql.Sql since it does a great job of hiding most of the ceremony involved with JDBC code. Add an import

import groovy.sql.Sql

and create a new instance passing the DataSource bean to its constructor so it can use that to get connections:

Sql sql = new Sql(dataSource_drupal)
sql.eachRow('select name, bar from foo' ) { row ->
   Foo foo = new Foo(name: row.name, bar: row.bar)
   if (!foo.save()) {
       log.error "Validation error(s) for data $row: $foo.errors"
   }
}

Upvotes: 7

Related Questions