Announcement Announcement Module
Collapse
No announcement yet.
Dynamic datasources at runtime Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamic datasources at runtime

    It seems like a lot of people had this problem. Basically they want to have dynamic datasources defined only at runtime and not defined beforehand in the context XML. I never found a straight forward way of doing this (and yes I read the blog from 2007 about AbstractRoutingDataSource), so here's what I threw together. Seems pretty easy to achieve, but I wanted to see if anyone saw glaring problems with this approach.

    Code:
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.datasource.lookup.DataSourceLookupFailureException;
    import org.springframework.jdbc.datasource.lookup.MapDataSourceLookup;
    import org.springframework.util.Assert;
    
    //Whatever bean defines the JDBC URL
    import model.Client;
    
    public class ClientJdbcDaoSupport extends JdbcDaoSupport {
    
    	@Autowired
    	private DatabaseConfigurationHolder configHolder;
    
    	@Autowired
    	private MapDataSourceLookup dataSourceMap;
    
    	public JdbcTemplate getJdbcTemplate(Client client) {
    		Assert.notNull(client, "client cannot be null");
    		Assert.notNull(client.getUrl(), "client url cannot be null");
    
    		String dataSourceName = client.getUrl();
    		DataSource dataSource = null;
    		try {
    			dataSource = dataSourceMap.getDataSource(dataSourceName);
    		} catch (DataSourceLookupFailureException e) {
    			dataSource = createDataSource(client.getUrl(),
    					configHolder.getUsername(), configHolder.getPassword());
    			dataSourceMap.addDataSource(dataSourceName, dataSource);
    		}
    
    		setJdbcTemplate(new JdbcTemplate(dataSource));
    
    		return getJdbcTemplate();
    	}
    
    	public static DataSource createDataSource(String url, String username,
    			String password) {
    		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
    				username, password);
    		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    		return dataSource;
    	}
    }

  • #2
    The approach documented here. Is more flexible and easier and completly transparent for you dao, service, hibernate and/or entitymanager...

    And yes it is used in production on a highly concurrent system with a lot of different users in about 50 different clients (approx. 40 dedicated databases).

    Comment


    • #3
      Originally posted by Marten Deinum View Post
      The approach documented here. Is more flexible and easier and completly transparent for you dao, service, hibernate and/or entitymanager...

      And yes it is used in production on a highly concurrent system with a lot of different users in about 50 different clients (approx. 40 dedicated databases).
      Marten, maybe I overlook something, but I cannot see how you create datasources at runtime in your example.
      It seems to me that your datasources are predefined in the XML and that is not dynamic as you just swap already defined datasources.

      Am I wrong ?

      Comment


      • #4
        Datasources can come from anywhere (in our case jndi which we can configure when ever we want). So if you create a TargetSource which does a lookup first, create if not found, return then you can quite easily (and more transparantly then your solution) use the documented approach.

        I should also add that you shouldn't be using DriverManagerDataSource in production, as it isn't a connection pool. Creating a connection to the database is slow so for a performant system you probably want to use another implementation.
        Last edited by Marten Deinum; Feb 8th, 2011, 06:38 AM.

        Comment


        • #5
          Originally posted by Marten Deinum View Post
          Datasources can come from anywhere (in our case jndi which we can configure when ever we want). So if you create a TargetSource which does a lookup first, create if not found, return then you can quite easily (and more transparantly then your solution) use the documented approach.
          Well the above is not my solution but I do face a similar problem and am curious concerning the details of implementation of your "create if not found" datasource.

          What I need is to be able to create databases automatically upon customer subscription to our services and be able to access those databases using credentials retrieved from some authentication system.

          The most difficult part is the Spring configuration for using dynamic datasources imho, so I'll be glad if you have some time to share your insight on the subject.

          Alternatively, you can answer this Stackoverflow question if you prefer, so everyone can benefit from your knowledge

          Thank you in advance for your help !

          Comment


          • #6
            As stated simply use the solution as layed out in the blog post (the code is still there although refactored).

            Instead of using a Map/JNDI/BeanFactory for lookups use a TargetRegistry which creates the datasource if it doesn't exist else returns the existing one.

            Another solution would be to use jndi, register a datasource on customer subscription and use jndi to do a lookup later on.

            Comment


            • #7
              Thanks Marten. This is exactly what I was looking for. I found many posts about dynamic datasources, but only defined at compile time, not at runtime. I'm going to work through an example, but this like exactly what I've been looking for.

              Comment


              • #8
                Just this week I moved the code to GitHub so you cna use git to checkout the code.

                If you want to create datasources at runtime (and not register them in jndi) you should create a TargetRegistry instance which checks an internal map and if not available uses the context to retrieve the client information from the database and create the datasource. It shouldn't be that hard, the trick is to set the context in the ContextHolder, in general you could/should use a ServletFilter for that so it is set once for each call. After that the application switches datasources (or whatever you like) transparantly.

                If you need some pointers feel free to contact me and/or send me some code to look at...

                Comment


                • #9
                  I got it working, but I'll take you up on the offer to look at code.

                  The TargetRegistry
                  Code:
                  public class ClientDataSourceFactory implements TargetRegistry {
                  
                  	private Map<String, DataSource> map = new ConcurrentHashMap<String, DataSource>();
                  
                  	@Override
                  	public DataSource getTarget(String context) {
                  		Client client = ClientContextHolder.getClient();
                  
                  		Assert.notNull(client, "Client was not set.");
                  
                  		String key = client.getSchema();
                  		DataSource dataSource;
                  		synchronized (this) {
                  			dataSource = map.get(key);
                  		}
                  
                  		if (dataSource == null) {
                  			dataSource = getDataSource("spaceballs", "123456", client.getUrl());
                  			synchronized (this) {
                  				map.put(key, dataSource);
                  			}
                  		}
                  
                  		return dataSource;
                  	}
                  
                  	private DataSource getDataSource(String username, String password,
                  			String url) {
                  		BasicDataSource dataSource = new BasicDataSource();
                  		dataSource.setUsername(username);
                  		dataSource.setPassword(password);
                  		dataSource.setUrl(url);
                  		return dataSource;
                  	}
                  The ContextHolder
                  Code:
                  public class ClientContextHolder {
                  	
                  	private static final ThreadLocal<Client> contextHolder = new ThreadLocal<Client>();
                  
                  	public static void setClient(Client client){
                  		contextHolder.set(client);
                  	}
                  	
                  	public static Client getClient(){
                  		return contextHolder.get();
                  	}
                  
                  }
                  The Spring XML
                  Code:
                  	<bean id="dataSourceRegistry"
                  		class="server.dao.ClientDataSourceFactory"></bean>
                  	
                  	<bean id="dataSourceTargetSource"
                  		class="biz.deinum.springframework.aop.target.ContextSwappableTargetSource">
                  		<constructor-arg type="java.lang.Class">
                  			<value>javax.sql.DataSource</value>
                  		</constructor-arg>
                  		<property name="targetRegistry" ref="dataSourceRegistry"></property>
                  	</bean>
                  
                  	<bean id="proxyDataSource" class="org.springframework.aop.framework.ProxyFactoryBean">
                  		<property name="proxyInterfaces">
                  			<list>
                  				<value>javax.sql.DataSource</value>
                  			</list>
                  		</property>
                  		<property name="targetSource">
                  			<ref bean="dataSourceTargetSource" />
                  		</property>
                  	</bean>
                  
                  	<bean id="dao" class="server.dao.MockDaoImpl">
                  		<property name="dataSource" ref="proxyDataSource"></property>
                  	</bean>
                  The test
                  Code:
                  	@Test
                  	public void testSomethingNifty() {
                  		ClientContextHolder.setClient(aHole1);
                  		dao.doSomethingNifty();
                  	}
                  Sooo, amidoingitright? Are there any glaring errors?

                  Thanks for the help again.

                  Comment


                  • #10
                    Well it more or less looks ok... Although you are also using your own context holder but that shouldn't be a problem, but you have to make sure both values (ContextHolder and ClientContextHolder) are set. You could use the normal approach and use a general DataSource (I assume you have a general database storing the client information) to retrieve the datasource configuration based on the given context.

                    Instead of using a map and a synchronized get/put (you are already using a ConcurrentMap so shouldn't be neede) you could use google-collection MapMaker and have a map that automatically creates the datasource for you, so that way you only have to do a get...

                    no-sync
                    Code:
                    public class ClientDataSourceFactory implements TargetRegistry {
                    
                    	private ConcurrentMap<String, DataSource> map = new ConcurrentHashMap<String, DataSource>();
                    
                    	@Override
                    	public DataSource getTarget(String context) {
                    		Client client = ClientContextHolder.getClient();
                    
                    		Assert.notNull(client, "Client was not set.");
                    
                    		String key = client.getSchema();
                    		DataSource dataSource = map.get(key);
                    
                    		if (dataSource == null) {
                    			dataSource = getDataSource("spaceballs", "123456", client.getUrl());
                    			dataSource = map.putIfAbsent(key, dataSource);
                    			if (dataSource == null) {
                    				// put success
                    				dataSource = map.get(key);
                    			}
                    		}
                    
                    		return dataSource;
                    	}
                    
                    	private DataSource getDataSource(String username, String password,
                    			String url) {
                    		BasicDataSource dataSource = new BasicDataSource();
                    		dataSource.setUsername(username);
                    		dataSource.setPassword(password);
                    		dataSource.setUrl(url);
                    		return dataSource;
                    	}
                    }

                    Comment

                    Working...
                    X