As part of my day job I needed to write an administration app that could be easily pointed at multiple environments. To achieve this I used a technique I found a while ago based on Spring's AbstractRoutingDataSource. It basically works by using a facade to switch the connection between one to many child data sources. First off SwitchableDataSource.groovy




As this is an internal admin application it has not been tested as thoroughly as standard prod code so please do so before using it in a "live" application. Please post any corrections/bug fixes.
package com.leebutts import org.springframework.jdbc.datasource .lookup.AbstractRoutingDataSource import com.leebutts.Environment import org.springframework.context.ApplicationContextAware import org.springframework.context.ApplicationContext import javax.sql.DataSource import org.springframework.jdbc.datasource.DriverManagerDataSource class SwitchableDataSource extends AbstractRoutingDataSource implements ApplicationContextAware { def applicationContext public void setApplicationContext(ApplicationContext applicationContext) { this.applicationContext = applicationContext } protected DataSource determineTargetDataSource() { DriverManagerDataSource ds = super.determineTargetDataSource(); def env = EnvironmentHolder.getEnvironment() if (env && env.passwordRequired && ds) { ds.setPassword(env.password) } return ds } protected Object determineCurrentLookupKey() { def env = EnvironmentHolder.getEnvironment() return env?.id ?: Environment.list()[0]?.id } }SwitchableDataSource is the facade that delegates to the list of standard DriverManager data sources. They are defined in grails-app/conf/spring/resources.groovy using the environment settings from the Environment class. Here's my spring config in resources.groovy:
import com.leebutts.SwitchableDataSource import com.leebutts.Environment import org.springframework.jdbc.datasource.DriverManagerDataSource beans = { parentDataSource(DriverManagerDataSource) { bean -> bean.'abstract' = true; driverClassName = 'com.mysql.jdbc.Driver' username = "root" } Environment.list().each {env -> "${env.prefix}DataSource"(DriverManagerDataSource) {bean -> bean.parent = parentDataSource bean.scope = 'prototype' def port = env.port ?: 3306 url = "jdbc:mysql://${env.host}:${port}/switchingDemo" if (env.user) { username = env.user } if (env.password) { password = env.password } } } def dataSources = [:] Environment.list().each {env -> dataSources[env.id] = ref(env.prefix + 'DataSource') } dataSource(SwitchableDataSource) { targetDataSources = dataSources } }Environment currently uses a static list to hold the environment config. This could be done better via a reloadable properties file or by adding a view/controller to modify the environment settings on the fly. Environment.groovy:
package com.leebutts class Environment { static environments = [] static { environments << [id: 1, name: 'local', prefix: 'local', host: 'localhost'] environments << [id: 2, name: 'UAT', prefix: 'uat', host: 'uat.leebutts.com'] environments << [id: 3, name: 'Testing', prefix: 'testing', host: 'testing.leebutts.com'] environments << [id: 4, name: 'Beta', prefix: 'beta', host: 'beta.leebutts.com', passwordRequired: true] environments << [id: 5, name: 'Prod', prefix: 'prod', host: 'db.leebutts.com', user:'grails', port: 13306, passwordRequired: true] //unique id check environments.each {env -> assert environments .findAll {it.id == env.id}.size() == 1} } static list() { return environments } }SwitchableDataSource needs a way to determine which environment the current request wishes to use. It does this via a ThreadLocal holder EnvironmentHolder.groovy:
package com.leebutts class EnvironmentHolder { private static final ThreadLocal contextHolder = new ThreadLocal(); static void setEnvironment(Map environment) { contextHolder.set(environment); } static getEnvironment() { return contextHolder.get(); } static void clear() { contextHolder.remove(); } }Now that the infrastructure is in place, the next step is to add a controller to allow users to select the environment they wish to use and a filter to set a default environment if one has not yet been chosen. The controller is called via ajax (in my application) but could be used as a standard controller just as easily. It looks up the Environment based on an ID and then tests the connection to make sure the password supplied is valid (if a password is required as specified in the environment config). If a password is being used it takes a copy of the environment config map, adds the password and stores it in the session so that the user doesn't have to re-enter the password on every screen and so that only this user has access to the password. EnvironmentController.groovy:
import com.leebutts.Environment import com.leebutts.EnvironmentHolder import javax.servlet.http.HttpServletResponse import org.codehaus.groovy.grails.commons.ApplicationAttributes import org.codehaus.groovy.grails.web.context.ServletContextHolder class EnvironmentController { def change = { if (params.environment) { def env = Environment.list() .find {it.id == new Integer(params.environment)} if (env) { if (env.passwordRequired) { if (params.password) { //take a copy and add a pword env = addPasswordToEnvCopy(params, env) } else { render 'PASSWORD REQUIRED' response.setStatus( HttpServletResponse.SC_UNAUTHORIZED) return } } //test connection def oldEnv = EnvironmentHolder.getEnvironment() EnvironmentHolder.setEnvironment env def ds = getDataSourceForEnv() try { def con = ds.getConnection() session.environment = env render 'Environment change complete.' } catch (e) { EnvironmentHolder.setEnvironment oldEnv render 'Unable to connect to database: ' + e.message response.setStatus( HttpServletResponse.SC_UNAUTHORIZED) return } } else { render 'No such environment' response.setStatus( HttpServletResponse.SC_BAD_REQUEST) } } else { render 'Missing parameter environment' response.setStatus(HttpServletResponse.SC_BAD_REQUEST) } } private def getDataSourceForEnv() { def servletContext = ServletContextHolder.servletContext def ctx = servletContext .getAttribute( ApplicationAttributes.APPLICATION_CONTEXT) return ctx.dataSource } private Map addPasswordToEnvCopy(Map params, env) { def myEnv = [:] env.each {key, val -> myEnv[key] = val } myEnv.password = params.password return myEnv } }As mentioned, there is also a simple filter for defaulting the environment to the first one in the list if one has not been selected and storing it in the ThreadLocal holder. Filters.groovy:
import com.leebutts.EnvironmentHolder import com.leebutts.Environment class Filters { def filters = { all(uri: '/**') { before = { if (!session.environment) { session.environment = Environment.list()[0] } EnvironmentHolder.setEnvironment(session.environment) } } } }The final step is to add an environment selection form to your layout so that users can choose their environment. views/layouts/main.gsp:
<html> <head> <title>Administration System</title> <link rel="stylesheet" href="${createLinkTo(dir: 'css', file: 'main.css')}"/> <g:layoutHead/> <g:javascript library="application"/> <g:javascript library="prototype"/> <script type="text/javascript"> function refresh() { window.location.reload(false); } function loading() { document.getElementById('spinner').style.display = 'inline'; document.getElementById('error').style.display = 'none'; } function showError(e) { var errorDiv = document.getElementById('error') errorDiv.innerHTML = '<ul><li>' + e.responseText + '</li></ul>'; errorDiv.style.display = 'block'; } </script> </head> <body> <div class="logo"> <div style="margin-left:10px;"> <h1>Current Environment: ${session.environment?.name ?: 'None'}</h1> <form action=""> <g:select name="environment" from="${com.leebutts.Environment.list()}" optionKey="id" optionValue="name" value="${session.environment?.id}"/> <g:passwordField name="password"/> <g:submitToRemote value="Select" controller="environment" action="change" update="currentEnv" onLoading="loading();" onComplete ="document.getElementById('spinner').style.display='none';" onFailure="showError(e)" onSuccess="refresh()"/> <br/> <div class="errors" id="error" style="display:none;width:500px;"> </div> <img id="spinner" style="display:none;" src="${createLinkTo(dir: 'images', file: 'spinner.gif')}" alt="Spinner"/> </form> </div> </div> <g:layoutBody/> </body> </html>The finished screen looks something like this:
Comments
Thanks, a switchable datasource is just what I am after. I seemed to have cocked something up however, as I can never get Grails to use the chosen environment - it will only ever use the one specified in DataSource.groovy. If I don't specify one in DataSource.groovy I get a startup error on not being able to find any datasource.
Am I barking up the wrong tree or just barking?
Any tips you have regards DataSource.groovy would be greatly appreciated.
Regards
Bruce
the changes made to resources.groovy should be overriding any settings in DataSource.groovy. I think I have my DataSource.groovy in the default HSQLDB memory database state.
Check that the dataSource bean doesn't have any typos (it must have an upper case S in it's name)
cheers
Lee
Regards
Bruce
You would also need to update the targetDatasources list inside the SwitchableDataSource bean.
-Harish
Thanks for the post.
why do you have a parameter in the function
private def getDataSourceForEnv(env) {
def servletContext = ServletContextHolder.servletContext
def ctx = servletContext
.getAttribute(
ApplicationAttributes.APPLICATION_CONTEXT)
return ctx.dataSource
}
you ar not using the parameter "env" ... is that ok? shouldnt you be using the parameter there?
Thanks
why do you have a parameter in the function getDataSourceForEnv(env) ?
It is not being used. Shouldnt it be used to cretae a neew Datasource with the environment Data?
Thanks!
that's a mistake, the env param is not actually used. The spring datasource uses the EnvironmentHolder class to lookup the right data source for the current thread.
I'm currently using this code. It is working perfectly fine.
Now, I need add another functionality to it. Can anyone help me on this?
The requirement is:
Currently it is possible to create datasources from resources.groovy file (which is one time). Bit, I should be able to add a datasource dynamically from controller call.
Please share your ideas.
- kishore
I created controller which implements ApplicationContextAware.
import org.springframework.context.ApplicationContextAware
import org.springframework.context.ApplicationContext
import org.springframework.beans.BeansException
class DataSrcController implements ApplicationContextAware {
private static ApplicationContext ctx = null;
public static ApplicationContext getApplicationContext() {
return ctx;
}
public void setApplicationContext(ApplicationContext ctx) throws BeansException {
// Assign the ApplicationContext into a static method
this.ctx = ctx;
}
}
But, no idea how to create beans here.Could you please help
Can I get help on this?
Thanks,
Kishore
dialect = "org.hibernate.dialect.Oracle10gDialect"
are you trying to use a different dialect for each data source?
That isn't possible as the Hibernate plugin creates a single session factory and looks up the dialect as it bootstraps.
You would need to somehow create a new sessionFactory for each dataSource and switch them like we switch the dataSources.
If you just want to use the oracle dialect then the usual place in DataSource.groovy will still work.
cheers
Lee
If I comment out the following in resources.groovy:
dataSource(SwitchableDataSource) {
targetDataSources = dataSources
}
Then I don't see the problem (but then of course the SwitchableDataSource is not used). Have you or anyone here seen this behavior before? Thanks again!
However, the fact that Grails seems to be hung up on resources.groovy and doesn't even run the other application code made me wonder if this was a known issue.
cheers
Lee
Is it possible to use springsecurity plugin for switching database?
1. Keep user/authentication/requestmap details in master database.
2. Login using spring security from database.
3. Once the login is successful, switch the database based on user.
Regards,
Kishore
We will try your solution.
Is it possible to make it a plugin?
I don't have any free time at the moment to do a plugin, sorry!
I substituted BasicDataSource for it in the code (making sure the maxActive, initialSize, etc. properties were read into the bean), but Hibernate is unable to make a connection after Grails starts up. I just wanted to see if there was a compelling reason not to use a pooling datasource before I go too deep into the rabbit hole. Thanks again!
org.hibernate.util.JDBCExceptionReporter Cannot get a connection, pool error Timeout waiting for idle object
Nothing else interesting is logged, even when I turn on debug for all classes. MySQL general logging, interestingly, shows that grails is connecting to the database during startup, but nothing further is logged afterward.
please tell me what should be required in resources.groovy in spring configuration
Please, let me know what are the steps required to this example.
I try to use your solution that works to get data from different bases but not work to create or update data.
You sais "You would need to somehow create a new sessionFactory for each dataSource and switch them like we switch the dataSources."
May i ask you to give us an exmple to do that.
thanks a lot for your work.
create and update should just work without needing multiple session factories. My comment was relating to the need for each dasta source to use a different dialect (e.g. switch between MySql and Oracle).
What error message are you encountering when creating/updating?
Thanks a lot for your reply, I had an error which produces the following traces :
ERROR util.JDBCExceptionReporter - ERREUR: droit refusé pour la séquence hibernate_sequence
| Error 2012-11-17 21:51:55,405 [http-bio-8080-exec-10] ERROR errors.GrailsExceptionResolver - PSQLException occurred when processing request: [GET] /testGrails/test2
ERREUR: droit refusé pour la séquence hibernate_sequence. Stacktrace follows:
Message: ERREUR: droit refusé pour la séquence hibernate_sequence
droit refusé pour la séquence hibernate_sequence => right refused for sequence hibernate_sequence
That's why I thought it was a session problem. Each database has a different hiberante_sequence index.
Besides, I commented my whole file datasource.groovy, there is no set dialect for my datasources.
Could you help me or guide me in my research?
cheers