问题
如何在一个web项目中使用两个数据源,并且不同的接口可以按需选择数据库。
方案
最开始的做法
因为我们的项目用的是ibatis作为ORM框架,在其配置文件中可以配置数据源信息,原始配置如下:
spring-application.xml
<!-- DBCP数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbcMysql.driverClassName}" />
<property name="url" value="${jdbcMysql.url}" />
<property name="username" value="${jdbcMysql.username}" />
<property name="password" value="${jdbcMysql.password}" />
<property name="initialSize" value="${jdbcMysql.initialSize}" />
<property name="maxActive" value="${jdbcMysql.maxActive}" />
<property name="maxIdle" value="${jdbcMysql.maxIdle}" />
<property name="maxWait" value="${jdbcMysql.maxWait}" />
<!-- 连接空闲时候对空闲连接定期检查,无效的连接被剔除,保证连接的有效性,单位是毫秒 (建议5-30秒定期检查) update by zhanghu -->
<property name="timeBetweenEvictionRunsMillis">
<value>30000</value>
</property>
<!-- 当空闲连接未使用的时间超过此值以后,空闲连接被关闭,并重新建立空闲连接,此参数防止服务器段超时关闭客户端的SQL连接,单位是毫秒 (建议10-30分钟) add by zhanghu -->
<property name="minEvictableIdleTimeMillis">
<value>1800000</value>
</property>
<!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量,最好和maxActive一致 add by zhanghu-->
<property name="numTestsPerEvictionRun" value="${jdbcMysql.maxActive}" />
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
<property name="testOnBorrow">
<value>false</value>
</property>
<!--removeAbandoned: 是否自动回收超时连接-->
<property name="removeAbandoned" value="true"/>
<!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->
<property name="removeAbandonedTimeout" value="300"/>
</bean>
<!-- spring和ibatis整合 注入数据源到SqlMapClient工厂 -->
<bean id="sqlMapClientFactory" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<!-- <property name="dataSource" ref="dataSource" /> -->
<property name="dataSource" ref="dynamicDataSource" />
<property name="configLocation" value="classpath:/META-INF/ibatis/ibatis-sqlMapConfig.xml" />
</bean>
<!-- 将SqlMapClient工厂创建的对象实例注入到接口层 -->
<bean id="sqlMapClientDao" class="com.ake.dccs.ecs.dao.impl.SqlMapClientDaoImpl">
<property name="sqlMapClient" ref="sqlMapClientFactory" />
</bean>
<!-- 将访问层对象实例注入到业务层 -->
<bean id="sqlMapClientService" class="com.ake.dccs.ecs.service.common.SqlMapClientServiceImpl">
<property name="sqlMapClientDao" ref="sqlMapClientDao" />
</bean>
<bean id="serviceInstance" class="com.ake.dccs.ecs.service.common.SqlMapClientInstanceUtil">
<property name="serviceInstance" ref="sqlMapClientService" />
</bean>
<tx:annotation-driven transaction-manager="txManager" />
<!-- 配置jdbc数据源的事务管理器 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<aop:config>
<aop:pointcut id="serviceMethods" expression="execution(* com.ake.dccs.ecs.service..*.*(..)) " />
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethods" />
</aop:config>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="get*" read-only="true" />
<tx:method name="query*" read-only="true"/>
<tx:method name="add*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="save*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="update*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="del*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="insert*" rollback-for="Exception" propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
然后我就天真的认为是不是再新建一个dataSource的bean、sqlSessionFactory、mapperScannerConfigurer和transactionManager,把数据库连接信息改一下,就可以同时使用两个数据库了。但是尝试之后发现第二个数据库的mapping文件根本没有被初始化进spring的context中,报了Invalid bound statement (not found)这个错,查了一下说是配置文件不对等原因造成的。后来发现实际上因为上面的配置文件中的sqlSessionFactory在spring中是单例的,因此按照我的想法第二个sqlSessionFactory根本就不会被实例化。所以此方法行不通!
改进做法
最后是在这篇博客中找到了正确可行的解决方法:使用Spring提供的AbstractRoutingDataSource类来根据请求路由到不同的数据源。具体做法是
先设置两个不同的dataSource代表不同的数据源,再建一个总的dynamicDataSource,根据不同的请求去设置dynamicDataSource。代码如下:
spring-application.xml
<!-- DBCP数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbcMysql.driverClassName}" />
<property name="url" value="${jdbcMysql.url}" />
<property name="username" value="${jdbcMysql.username}" />
<property name="password" value="${jdbcMysql.password}" />
<property name="initialSize" value="${jdbcMysql.initialSize}" />
<property name="maxActive" value="${jdbcMysql.maxActive}" />
<property name="maxIdle" value="${jdbcMysql.maxIdle}" />
<property name="maxWait" value="${jdbcMysql.maxWait}" />
<!-- 连接空闲时候对空闲连接定期检查,无效的连接被剔除,保证连接的有效性,单位是毫秒 (建议5-30秒定期检查) update by zhanghu -->
<property name="timeBetweenEvictionRunsMillis">
<value>30000</value>
</property>
<!-- 当空闲连接未使用的时间超过此值以后,空闲连接被关闭,并重新建立空闲连接,此参数防止服务器段超时关闭客户端的SQL连接,单位是毫秒 (建议10-30分钟) add by zhanghu -->
<property name="minEvictableIdleTimeMillis">
<value>1800000</value>
</property>
<!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量,最好和maxActive一致 add by zhanghu-->
<property name="numTestsPerEvictionRun" value="${jdbcMysql.maxActive}" />
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
<property name="testOnBorrow">
<value>false</value>
</property>
<!--removeAbandoned: 是否自动回收超时连接-->
<property name="removeAbandoned" value="true"/>
<!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->
<property name="removeAbandonedTimeout" value="300"/>
</bean>
<bean id="dataSourceTwo" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${dbTwo.jdbcMysql.driverClassName}" />
<property name="url" value="${dbTwo.jdbcMysql.url}" />
<property name="username" value="${dbTwo.jdbcMysql.username}" />
<property name="password" value="${dbTwo.jdbcMysql.password}" />
<property name="initialSize" value="${jdbcMysql.initialSize}" />
<property name="maxActive" value="${jdbcMysql.maxActive}" />
<property name="maxIdle" value="${jdbcMysql.maxIdle}" />
<property name="maxWait" value="${jdbcMysql.maxWait}" />
<!-- 连接空闲时候对空闲连接定期检查,无效的连接被剔除,保证连接的有效性,单位是毫秒 (建议5-30秒定期检查) update by zhanghu -->
<property name="timeBetweenEvictionRunsMillis">
<value>30000</value>
</property>
<!-- 当空闲连接未使用的时间超过此值以后,空闲连接被关闭,并重新建立空闲连接,此参数防止服务器段超时关闭客户端的SQL连接,单位是毫秒 (建议10-30分钟) add by zhanghu -->
<property name="minEvictableIdleTimeMillis">
<value>1800000</value>
</property>
<!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量,最好和maxActive一致 add by zhanghu-->
<property name="numTestsPerEvictionRun" value="${jdbcMysql.maxActive}" />
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
<property name="testOnBorrow">
<value>false</value>
</property>
<!--removeAbandoned: 是否自动回收超时连接-->
<property name="removeAbandoned" value="true"/>
<!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->
<property name="removeAbandonedTimeout" value="300"/>
</bean>
<bean id="dynamicDataSource" class="com.ake.dccs.ecs.service.common.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<!-- 通过不同的key决定用哪个dataSource -->
<entry value-ref="dataSource" key="dataSource"></entry>
<entry value-ref="dataSourceTwo" key="dataSourceTwo"></entry>
</map>
</property>
<!-- 设置默认的dataSource -->
<property name="defaultTargetDataSource" ref="dataSource"></property>
</bean>
<!-- spring和ibatis整合 注入数据源到SqlMapClient工厂 -->
<bean id="sqlMapClientFactory" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<!-- <property name="dataSource" ref="dataSource" /> -->
<property name="dataSource" ref="dynamicDataSource" />
<property name="configLocation" value="classpath:/META-INF/ibatis/ibatis-sqlMapConfig.xml" />
</bean>
<!-- 将SqlMapClient工厂创建的对象实例注入到接口层 -->
<bean id="sqlMapClientDao" class="com.ake.dccs.ecs.dao.impl.SqlMapClientDaoImpl">
<property name="sqlMapClient" ref="sqlMapClientFactory" />
</bean>
<!-- 将访问层对象实例注入到业务层 -->
<bean id="sqlMapClientService" class="com.ake.dccs.ecs.service.common.SqlMapClientServiceImpl">
<property name="sqlMapClientDao" ref="sqlMapClientDao" />
</bean>
<bean id="serviceInstance" class="com.ake.dccs.ecs.service.common.SqlMapClientInstanceUtil">
<property name="serviceInstance" ref="sqlMapClientService" />
</bean>
<tx:annotation-driven transaction-manager="txManager" />
<!-- 配置jdbc数据源的事务管理器 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- <property name="dataSource" ref="dataSource" /> -->
<property name="dataSource" ref="dynamicDataSource" />
</bean>
<aop:config>
<aop:pointcut id="serviceMethods" expression="execution(* com.ake.dccs.ecs.service..*.*(..)) " />
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethods" />
</aop:config>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="get*" read-only="true" />
<tx:method name="query*" read-only="true"/>
<tx:method name="add*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="save*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="update*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="del*" rollback-for="Exception" propagation="REQUIRED" />
<tx:method name="insert*" rollback-for="Exception" propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
DynamicDataSource.java
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return CustomerContextHolder.getCustomerType();
}
}
CustomerContextHolder.java
import org.apache.commons.lang.StringUtils;
public class CustomerContextHolder {
public static final String DATA_SOURCE_DCCSGDS = "dataSource";
public static final String DATA_SOURCE_IEMS = "dataSourceTwo";
//用ThreadLocal来设置当前线程使用哪个dataSource
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**设置要使用的数据源*/
public static void setCustomerType(String customerType) {
contextHolder.set(customerType);
}
/**获取数据源*/
public static String getCustomerType() {
String dataSource = contextHolder.get();
if (StringUtils.isEmpty(dataSource)) {
return DATA_SOURCE_DCCSGDS;
}else {
return dataSource;
}
}
/**清除数据源,使用默认的数据源*/
public static void clearCustomerType() {
contextHolder.remove();
}
}
ServiceImpl.java
CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_IEMS);//切换数据源
CustomerContextHolder.clearCustomerType();//清除数据源,恢复默认数据源
值得注意的是在CustomerContextHolder.java中使用了ThreadLocal类的set方法来设置当前线程要选择的dataSource,看一下set方法的源码:
ThreadLocal.set()
public void set(T value) {
Thread t = Thread.currentThread();
ThreadLocalMap map = getMap(t);
if (map != null)
map.set(this, value);
else
createMap(t, value);
}
显而易见,获取当前线程,并且使用一个hashmap把需要存储的值设置进去。因为tomcat是用的线程池来处理每个请求,所以用ThreadLocal可以保证线程安全问题。同时这个AbstractRoutingDataSource类也值得好好研究一下。