CODEWITHSUNDEEP
springspring-jdbcspring-4
Shrinath
Shrinath

Reputation: 564

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

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 !!

Upvotes: 0

Views: 3383

Answers (1)

rafalopez79
rafalopez79

Reputation: 2076

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();
 }

Upvotes: 1

Related Questions