Grail's Create Or Change Database Connection Manually Or Run-time | Using multiple data-sources in a Grail's project | Grail's Multiple Data Sources | Grail's multi tenant data-sources

In src/groovy, create a groovy class named 'UserHolder' with following contents:

package a.b.c

/**
 * Created by pritom on 14/08/2014.
 */
class UserHolder {
    public static Integer DEFAULT = 1;
    private static final ThreadLocal contextHolder = new ThreadLocal();
    public static String DS_PREFIX = "dataSource_";
    public static String DS_POSTFIX = "User";

    static void setEnvironment(Map environment) {
        contextHolder.set(environment);
    }

    static getEnvironment() {
        return contextHolder.get();
    }

    static void clear() {
        contextHolder.remove();
    }
}

Also create a groovy class named 'SwitchableDataSource' in src/groovy with following contents:

package a.b.c

import grails.util.Holders
import org.springframework.context.ApplicationContext
import org.springframework.jdbc.datasource.DriverManagerDataSource
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource

import javax.sql.DataSource

/**
 * Created by pritom on 14/08/2014.
 */
class SwitchableDataSource extends AbstractRoutingDataSource {
    def applicationContext

    public void setApplicationContext(ApplicationContext applicationContext) {
        this.applicationContext = applicationContext
    }

    protected DataSource determineTargetDataSource() {
        def user = UserHolder.getEnvironment();
        try {
            DriverManagerDataSource ds = super.determineTargetDataSource();
            return ds;
        }
        catch (Exception ex) {
            println "--->Error:: ${ex.getMessage()}";
            try {
                def ga = Holders.getGrailsApplication();
                String beanFullName = UserHolder.DS_PREFIX + user.id + UserHolder.DS_POSTFIX;
                if(user && user.id && ga.mainContext.containsBean( beanFullName ) ) {
                    println "Using data source: '${beanFullName}'";
                    return ga.mainContext.getBean(beanFullName);
                }
            }
            catch (Exception ex2) {
                println "--->Error:: ${ex2.getMessage()}";
            }
        }
    }

    @Override
    protected Object determineCurrentLookupKey() {
        def user = UserHolder.getEnvironment();
        return user?.id ?: UserHolder.DEFAULT;
    }

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
    }
}

Edit resource.groovy file under grails-app/conf/spring folder:

import a.b.c.SwitchableDataSource
import a.b.c.UserHolder
import org.springframework.jdbc.datasource.DriverManagerDataSource

// Place your Spring DSL code here
beans = {
    parentDataSource(DriverManagerDataSource) { bean ->
        bean.'abstract' = true;
        driverClassName = 'com.mysql.jdbc.Driver'
        username = "root"
    }

    "rootDataSource"(DriverManagerDataSource) { bean ->
        bean.parent = parentDataSource;
        bean.scope = 'prototype';
        url = "jdbc:mysql://localhost/user${UserHolder.DEFAULT}?useUnicode=yes&characterEncoding=UTF-8";
        username = "root"
    }

    def dataSources = [:]
    dataSources[UserHolder.DEFAULT] = ref("rootDataSource");

    dataSource(SwitchableDataSource) {
        targetDataSources = dataSources
    }
}

Now create another groovy class named 'DataSourceService' to bind datasource to your project dynamically/runtime with following contents:

package a.b.c

import grails.spring.BeanBuilder
import org.codehaus.groovy.grails.commons.GrailsApplication
import org.springframework.beans.BeansException
import org.springframework.context.ApplicationContext
import org.springframework.context.ApplicationContextAware
import org.springframework.context.support.GenericApplicationContext

/**
 * Created by pritom on 14/08/2014.
 */
class DataSourceService implements ApplicationContextAware {
    ApplicationContext applicationContext;
    public GrailsApplication grailsApplication;

    @Override
    void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }

    /**
     * Add new bean to system
     * @param beanName
     * @param dsurl
     * @param uid
     * @param pwd
     * @return
     */
    def registerBean( String beanName, String dsurl, String uid, String pwd ) {
        String beanFullName = UserHolder.DS_PREFIX + beanName + UserHolder.DS_POSTFIX;
        if( !applicationContext.containsBean( beanFullName ) ) {
            def bb = new BeanBuilder()
            bb.beans {
                "$beanFullName" { bean ->
                    bean.parent = ref('parentDataSource');
                    bean.scope = 'prototype';
                    url = dsurl;
                    username = uid
                    password = pwd
                }
            }
            bb.registerBeans( applicationContext );

            println "Added $beanFullName"
        }
        else {
            println "Already got a bean called $beanFullName"
        }
    }

    /**
     * Remove bean from system
     * @param beanName
     * @return
     */
    def deRegisterBean( String beanName ) {
        if( applicationContext.containsBean( beanName ) ) {
            (applicationContext as GenericApplicationContext).removeBeanDefinition( beanName )
            println "Removed $beanName"
        }
        else {
            println "Trying to deRegister a bean $beanName that I don't know about"
        }
    }
}

Now create a filter in grails-app/filters named 'SecurityFilters' with following contents:

package filters

import a.b.c.UserHolder

class SecurityFilters {

    def filters = {
        all(controller: '*', action: '*') {
            before = {
     /* This line is for specify which user request to handle */
                if (params.int('user')) {
                    UserHolder.setEnvironment([id: params.int('user')]);
                }
            }
            after = { Map model ->

            }
            afterView = { Exception e ->

            }
        }
    }
}

Example of adding a new datasource to system:

DataSourceService dataSourceService = new DataSourceService();
dataSourceService.setApplicationContext(grailsApplication.mainContext);
dataSourceService.grailsApplication = grailsApplication;
dataSourceService.registerBean(params.user, "jdbc:mysql://localhost/user${params.user}?useUnicode=yes&characterEncoding=UTF-8", "root", "");

Suppose params.user = 3 here.

That's it Download code