Grails: Create dynamic SQL-Connection

Question

For my application I need dynamic database connections at runtime. I know, there are ways to create multiple datasources but they are not that dynamically I think. Scenario:

A user can enter database credentials and connect to a remote database to import single rows and tables to an other database. For this purpose I need to connect to the remote database dynamically.

I've tried to do that in a service like they've said in If I use groovy sql class in grails, does it use the grails connection pooling?

Note: GORM is dispensable in this case, I can use plain SQL instead.

Any ideas? Thank you..

Edit: Grails 2.3.4

Answer 1

As long as you have the JDBC drivers for all the datasources on your classpath, you can create an instance of groovy.sql.Sql that will connect to whatever database you like, e.g.

Sql sql = Sql.newInstance('jdbc:hsqldb:mem:testDB', 'sa', 'myPassword', 
    'org.hsqldb.jdbc.JDBCDriver')

// now use the Sql instance to execute a query, or whatever....

Answer 2

You can do this sort of thing to register DataSource beans at runtime:

Given a Grails Service:

package whatever

import groovy.sql.Sql
import org.springframework.context.*
import org.apache.tomcat.jdbc.pool.DataSource
import org.springframework.context.support.GenericApplicationContext

class DataSourceService implements ApplicationContextAware {

    ApplicationContext  applicationContext

    def registerBean( String beanName, String dsurl, String uid, String pwd ) {
        if( !applicationContext.containsBean( beanName ) ) {
            def bb = new grails.spring.BeanBuilder()
            bb.beans {
                "$beanName"( DataSource ) {
                    driverClassName = "com.mysql.jdbc.Driver"
                    url             = dsurl
                    username        = uid
                    password        = pwd
                    validationQuery = "SELECT 1"
                    testOnBorrow    = true
                    maxActive       = 1
                    maxIdle         = 1
                    minIdle         = 1
                    initialSize     = 1
                }
            }
            bb.registerBeans( applicationContext )
            log.info "Added $beanName"
        }
        else {
            log.error "Already got a bean called $beanName"
        }
    }

    def deRegisterBean( String beanName ) {
        if( applicationContext.containsBean( beanName ) ) {
            (applicationContext as GenericApplicationContext).removeBeanDefinition( beanName )
            log.info "Removed $beanName"
        }
        else {
            log.error "Trying to deRegister a bean $beanName that I don't know about"
        }
    }

    def getSql( String beanName ) {
        Sql.newInstance( applicationContext.getBean( beanName ) )
    }
}

Then, you should be able to call the service to register a new datasource:

dataSourceService.registerBean( 'myDS', 'jdbc:mysql://localhost:3306/mysql', 'test', 'test' )

Get a Groovy Sql object for it:

dataSourceService.getSql( 'myDS' ).rows( 'SELECT * FROM whatever' )

And remove the bean when done

dataSourceService.deRegisterBean( 'myDS' )

Fingers crossed... I've yanked that code from a project of mine and changed/not-tested it ;-)

Update

The runtime-datasources plugin has been created which uses the approach outlined in this post to allow datasources to be added/removed at runtime.