In practical projects, it is often necessary to connect multiple databases, and different business needs often need to access different databases in the implementation process.

jdbc.properties configuration file, configuring multiple dataSource s

##########################MySQL#####################################
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
connection.driver_class=com.mysql.jdbc.Driver
connection.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
connection.username=yahu
connection.password=123456

##########################Oracle#####################################
connection1.driver_class=oracle.jdbc.driver.OracleDriver
connection1.url=jdbc\:oracle\:thin\:@localhost\:1521/MEDB
connection1.username=yahu
connection1.password=123456

##########################Sql Server2008#####################################
connection2.driver_class=net.sourceforge.jtds.jdbc.Driver
connection2.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=test
connection2.username=yahu
connection2.password=123456

The spring-config.xml configuration file is as follows, adding DynamicDataSource Bean to Spring's context XML configuration file and configuring Map mapping for DynamicDataSource's targetDataSources (multiple data source target) property. Using dynamic data source DynamicDataSource is inherited from AbstractRoutingDataSource, which is inherited from org.springframework.jdbc.datasource.AbstractDataSource, AbstractDataSource implements a unified DataSource interface, so DynamicDataSource can also be used as a DataSource:

　　　<!-- Database Connection Pool Configuration -->
     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${connection.driver_class}" />
        <property name="url" value="${connection.url}" />
        <property name="username" value="${connection.username}" />
        <property name="password" value="${connection.password}" />
        
        <property name="initialSize" value="5" />
        <property name="minIdle" value="1" /> 
        <property name="maxActive" value="200" />
        
        <!-- Configure how long to wait for a connection to timeout -->
        <property name="maxWait" value="30000" />
        
        <!-- Configure how often to do a check to detect idle connections that need to be closed in milliseconds -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        
        <!-- Configure a connection's minimum lifetime in milliseconds in the pool -->
        <property name="minEvictableIdleTimeMillis" value="300000" />
    </bean>
    
    <!-- Oracle  -->
    <bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${connection1.driver_class}" />
        <property name="url" value="${connection1.url}" />
        <property name="username" value="${connection1.username}" />
        <property name="password" value="${connection1.password}" />
    </bean>

    <!-- Sql server 2008  -->
    <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${connection2.driver_class}" />
        <property name="url" value="${connection2.url}" />
        <property name="username" value="${connection2.username}" />
        <property name="password" value="${connection2.password}" />
    </bean>
    
     <!-- Dynamic Data Source -->
     <bean id="dynamicDataSource" class="com.yahu.core.dao.DynamicDataSource">  
        <!-- adopt key-value Form to associate data sources -->  
        <property name="targetDataSources">  
            <map>  
                <entry value-ref="dataSource" key="datasource" />  
                <entry value-ref="dataSource1" key="datasource1" />
                <entry value-ref="dataSource2" key="datasource2" />         
            </map>  
        </property>  
        <property name="defaultTargetDataSource" ref="dataSource" />  
    </bean> 

DynamicDataSource dynamic data source class, extends Spring's AbstractRoutingDataSource Abstract class, implements dynamic data source, and the abstract method determineCurrentLookupKey in AbstractRoutingDataSource is the core of route implementation of data source. Override this method here:

package com.yahu.core.dao;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/*******************************************************************
 * @describe : Establishing a dynamic data source class must inherit AbstractRoutingDataSource
 ********************************************************************/
public class DynamicDataSource extends AbstractRoutingDataSource {
    //coverity modify
    //private Log log = LogFactory.getLog(getClass());

    protected Object determineCurrentLookupKey() {
        String value = CustomerContextHolder.getCustomerType();
        //log.info(value);
        return value;
    }

}

Get and set the context for a thread-safe ThreadLocal:

package com.yahu.core.dao;

/*******************************************************************
 * @describe : Getting and setting context
 ********************************************************************/
public class CustomerContextHolder {

    /**
     * mysql
     */
    public static final String DATASOURCE = "datasource";

    /**
     * oracle
     */
    public static final String DATASOURCE_1 = "datasource1";
    
    /**
     * sql server
     */
    public static final String DATASOURCE_2 = "datasource2";

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

    public static void setCustomerType(String customerType) {
        contextHolder.set(customerType);
    }

    public static String getCustomerType() {
        return contextHolder.get();
    }

    public static void clearCustomerType() {
        contextHolder.remove();
    }
}

The management of dynamic data sources, how to choose to control the specific data sources needed in each business, can be controlled manually by adding the following code to the business layer

CustomerContextHolder.setCustomerType(CustomerContextHolder.DATASOURCE);

This enables dynamic switching of data sources. If there are more uniform rules in the service layer, you can also use aop to set up data sources. This is generally a service as a data source, so it is better to use aop to call uniformly after service layer execution.

CustomerContextHolder.clearCustomerType();

Empty the data source information.

Of course, one of the parameters in the configuration above is defaultTargetDataSource, which is the default data source if you do not set it.