How to programmatically change databases in Spring with one DataSource?

Question

I'm looking to see what's the best way to use one DataSources in Spring but be able to switch the database from within the Java code? Below are my two DataSources and they go to the same database server but different databases.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDataSource" />
    <property name="url"
              value="jdbc:sybase:Tds:10.20.30.40:50/DATABASE_EMS" />
    <property name="username" value="userid" />
    <property name="password" value="derp" />
</bean>

<bean id="dataSourceMain" class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDataSource" />
    <property name="url"
              value="jdbc:sybase:Tds:10.20.30.40:50/DATABASE" />
    <property name="username" value="userid" />
    <property name="password" value="derp" />
</bean>

I have them bound to their own respective bean but I'm looking at my legacy code and it's going to be VERY awkward to implement this with 2 separate beans. Is there any ideas/thoughts on how to be able to use one DataSource and switch databases when I need to?

How do you want to switch the database? What is the criteria to decide the database? — Arun P Johny, Jan 15 '13 at 03:33
The good thing is that it doesn't have to be dynamic. If need be, I can explicitly change it before calling my DAO. — Carlos, Jan 15 '13 at 03:46

score 14 · Accepted Answer · edited May 23 '17 at 12:26

14

You can do this by extending the Spring's AbstractRoutingDataSource and wrapping your existing data sources in it. Check this article for details. Quoting from the article:

The general idea is that a routing DataSource acts as an intermediary – while the 'real' DataSource can be determined dynamically at runtime based upon a lookup key.

Also see similar questions on SO:

edited May 23 '17 at 12:26

Community

1
1

answered Jan 15 '13 at 03:50

Abhinav Sarkar

23,534
11
81
97

By far, the most complete answer. I will mark it as accepted and will be doing some POC's to validate these Spring functionalities. Thanks! – Carlos Feb 12 '13 at 14:39

score 0 · Answer 2 · edited Jan 30 '18 at 05:39

0

There are many ways you can do this. For example, you can create a service class DatasourceSelectorService, and based on some input (eg: configuration file/user input) it chooses the datasource's bean accordingly.

All other classes requiring a datasource should obtain it via this DatasourceSelectorService.

edited Jan 30 '18 at 05:39

VPK

3,010
1
28
35

answered Jan 15 '13 at 03:44

gerrytan

40,313
9
84
99

How to programmatically change databases in Spring with one DataSource?

2 Answers2

Linked