Using one DataSource object for multiple DB connections - using only one connection at a time

Question

Can we define only one DataSource object and wire it dynamically at runtime connecting to different databases ? I need to connect to only one database at a time.

I will be passing the name of the Database as argument. I will lookup the DB URL and other details from a property file and then I need to connect to the DB using the DB URL.

In Short - I do not know the number of databases I need to connect to. I will have all possible database connection details configured in the database.properties file following a certain syntax (like prefixed with DB01 etc.). The name of the DB will be passed as argument and I need to execute the query against that database.

database.properties file

DB01.driver=com.ibm.db2.jcc.DB2Driver
DB01.url=jdbc:db2://localhost:50000/SAMPLE
DB01.username=db2admin
DB01.password=db2admin

DAO class

    @Autowired
    @Qualifier("DB01") // how do I make this dynamic ?
    private DataSource   datasource;
    private JdbcTemplate jdbcTemplate;

    // some more code

    public SqlRowSet executeQuery(String sqlQuery)
    {
        // can I pass the DB name here (the database.properties file will have the DB details 
        // with this name as given above) and set the DataSource Object accordingly ?
        // so that the query will be executed against that DB ?
        setJdbcTemplate(new JdbcTemplate(this.datasource));
        return getJdbcTemplate().queryForRowSet(sqlQuery);
    }

Using Spring v4.1.4 RELEASE. Thanks !!

This is not a java question, it is a Spring question. As far as java goes, you have to have a separate java.sql.Connection object instantiated for each database you connect to. How spring might do this I have no idea. You could have a list of connection objects that are all active then choose the right one depending on the context. — FlyingGuy, Feb 08 '15 at 19:14
@FlyingGuy - point taken. Removed java tags. I thought since I am coding in java, that tag has to be default. Thanks for the java tip as well. — Shrinath, Feb 10 '15 at 19:44

score 1 · Accepted Answer · answered Feb 08 '15 at 23:01

You can define a Routing DataSource that redirects the getConnection method to one datasource or another based on a key, in your case, it seems to be the database name.

For instance, the spring xml:

     ....
    <bean id="DB01DataSource" parent="parentDatasource" p:url="${DB01.url}" ... />
    <bean id="DB02DataSource" parent="parentDatasource" p:url="${DB02.url}" .../>

    <bean id="dataSource" class="DBRoutingDataSource">
    <property name="targetDataSources">
       <map key-type="java.lang.String">
          <entry key="DB01" value-ref="DB01DataSource"/>
          <entry key="DB02" value-ref="DB02DataSource"/>
       </map>
    </property>
    <property name="defaultTargetDataSource" ref="DB01DataSource"/>
    </bean>
     ....

The DBRoutingDataSource class:

public class DBRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.getDB();
    }
}

And the DBContextHolder class:

    public final class DBContextHolder {

        private static final ThreadLocal<String>    CONTEXT = new ThreadLocal<String>();

        private DBContextHolder() {
            // empty
        }

        public static void setDB(final String db) {
            CONTEXT.set(db);
        }

        public static String getDB() {
            return CONTEXT.get();
        }

        public static void clearDB() {
            CONTEXT.remove();
        }
    }

In your service class before calling your DAO you set the key that will enable the Routing DataSource to get the right connection:

 DBContextHolder.setDB("DB01");
 try{
   dao.executeQuery(sqlSentence);
 }finally{
   DBContextHolder.clearDB();
 }

Thank you for the clear answer. I am trying to write a pure annotation-based application and hence I am figuring out how to map the spring,xml config you did into my java class (if you can help there a little, it will be great). I am new to Spring4 annotation and hence taking longer than expected. If I am not able to achieve this using annotation, I will go with the xml approach and see if the solution works. I will accept the answer once I am able to get it working - one way or the other. Thank you very much !! — Shrinath, Feb 10 '15 at 19:46
I was not able to get it working with annotations, but it worked as expected using the XML configuration you mentioned. Thanks for the answer. Accepted it. — Shrinath, Feb 16 '15 at 02:48

Using one DataSource object for multiple DB connections - using only one connection at a time

1 Answers1