多数据源,说白了,就是多数据库。
想要实现多数据库查询,只需简单四步即可实现! 但这种实现方式有缺点,不能够实时切换数据库。有时间我会补一篇,通过AOP实现数据库切换的博客,通过aop+注解实现比较好。
第一步: 配置 jdbc.properties;
1 # MySQL
2 #============================================================================
3 jdbc.mysql.driver=com.mysql.jdbc.Driver
4 jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
5 jdbc.mysql.username=root
6 jdbc.mysql.password=root
7
8 # MS SQL Server (JTDS)
9 #============================================================================
10 jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver
11 jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test
12 jdbc.sqlserver.username=sa
13 jdbc.sqlserver.password=sa
14
15 # 通用配置
16 #============================================================================
17 jdbc.initialSize=5
18 jdbc.minIdle=5
19 jdbc.maxIdle=20
20 jdbc.maxActive=100
21 jdbc.maxWait=100000
第二步:配置 spring-mybatis.xml;
1 <!-- 多数据源配置 -->
2 <bean id="sqlServerDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
3 <property name="driverClassName" value="${jdbc.sqlserver.driver}"/>
4 <property name="url" value="${jdbc.sqlserver.url}"/>
5 <property name="username" value="${jdbc.sqlserver.username}"/>
6 <property name="password" value="${jdbc.sqlserver.password}"/>
7 <property name="initialSize" value="${jdbc.initialSize}"/>
8 <property name="minIdle" value="${jdbc.minIdle}"/>
9 <property name="maxIdle" value="${jdbc.maxIdle}"/>
10 <property name="maxActive" value="${jdbc.maxActive}"/>
11 <property name="maxWait" value="${jdbc.maxWait}"/>
12 <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
13 <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
14 <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
15 <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
16 <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
17 <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
18 <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
19 </bean>
20 <bean id="mySqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
21 <property name="driverClassName" value="${jdbc.mysql.driver}"/>
22 <property name="url" value="${jdbc.mysql.url}"/>
23 <property name="username" value="${jdbc.mysql.username}"/>
24 <property name="password" value="${jdbc.mysql.password}"/>
25 <property name="initialSize" value="${jdbc.initialSize}"/>
26 <property name="minIdle" value="${jdbc.minIdle}"/>
27 <property name="maxIdle" value="${jdbc.maxIdle}"/>
28 <property name="maxActive" value="${jdbc.maxActive}"/>
29 <property name="maxWait" value="${jdbc.maxWait}"/>
30 <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
31 <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
32 <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
33 <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
34 <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
35 <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
36 <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
37 </bean>
38
39 <bean id="dataSource" class="xx.xxx.DynamicDataSource"><!--注意: 这里写选择数据源的类地址 下面跟着给出-->
40 <property name="defaultTargetDataSource" ref="mySqlDataSource"/><!-- 设置默认为此mySqlDataSource数据源-->
41 <property name="targetDataSources">
42 <map>
43 <entry key="mySqlDataSource" value-ref="mySqlDataSource"/>
44 <entry key="sqlServerDataSource" value-ref="sqlServerDataSource"/>
45 </map>
46 </property>
47 </bean>
第三步:添加数据源的类;(借助ThreadLocal类,通过ThreadLocal类传递数据源的参数)
1 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
2 /*
3 * 配置多数据源
4 */
5
6 public class DynamicDataSource extends AbstractRoutingDataSource{
7
8 public static final String mySqlDataSource= "mySqlDataSource";
9
10 public static final String sqlServerDataSource = "sqlServerDataSource";
11 //本地线程,获取当前正在执行的currentThread
12 public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
13
14 public static void setCustomerType(String customerType) {
15
16 contextHolder.set(customerType);
17
18 }
19
20 public static String getCustomerType() {
22 return contextHolder.get();
24 }
25
26 public static void clearCustomerType() {
28 contextHolder.remove();
30 }
31
32 @Override
33 protected Object determineCurrentLookupKey() {
35 return getCustomerType();
37 }
38 }
最后一步:使用;
public void findOrder(){
List<PayOrder> list = new ArrayList<>();
DynamicDataSource.clearCustomerType();//重点: 实际操作证明,切换的时候最好清空一下
DynamicDataSource.setCustomerType(DynamicDataSource.sqlServerDataSource);//切换数据源,设置后 就OK了。可以随时切换过来(在controller层切换)
list =mapper.findByUid("-14663"); System.out.println(list);
DynamicDataSource.clearCustomerType();//
DynamicDataSource.setCustomerType(DynamicDataSource.sqlServerDataSource);//切换回主数据源
6 }