应用多数据源支持读写分离

经过测试，MySQL的ReplicationConnection在和Druid连接池使用时，会经常出现连接断开不可用的问题，推测是主从配置不一样，导致超出生存时间的问题。

鉴于实际情况中，主从的配置有分开配置的需求，弃用Replication方案，使用多数据源解决

applicationContext.xml 加入多个数据源：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:context="http://www.springframework.org/schema/context"     xmlns:tx="http://www.springframework.org/schema/tx"     xsi:schemaLocation="     http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd     http://www.springframework.org/schema/context     http://www.springframework.org/schema/context/spring-context-3.0.xsd     http://www.springframework.org/schema/tx     http://www.springframework.org/schema/tx/spring-tx-2.0.xsd     ">     <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">         <!-- 基本属性 url、user、password -->           <property name="driverClassName" value="${jdbc.driverclass}" />         <property name="url" value="${jdbc.url}"/>         <!--             <property name="url" value="${jdbc.url}?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true" />             <property name="url" value="${jdbc.url}?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true"/>        -->        <property name="username" value="${jdbc.username}" />        <property name="password" value="${jdbc.password}" />          <!-- 配置初始化大小、最小、最大 -->         <property name="initialSize" value="5" />         <property name="minIdle" value="15" />         <property name="maxActive" value="100" />           <!-- 配置获取连接等待超时的时间 -->         <property name="maxWait" value="60000" />           <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->         <property name="timeBetweenEvictionRunsMillis" value="60000" />           <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->         <property name="minEvictableIdleTimeMillis" value="300000" />           <property name="validationQuery" value="SELECT 'x'" />         <property name="testWhileIdle" value="true" />         <property name="testOnBorrow" value="true" />         <property name="testOnReturn" value="false" />           <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->         <property name="poolPreparedStatements" value="true" />         <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />           <!-- 配置监控统计拦截的filters -->         <property name="filters" value="stat,log4j" />     </bean>       <bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">         <!-- 基本属性 url、user、password -->           <property name="driverClassName" value="${jdbc.driverclass}" />         <property name="url" value="${read.jdbc.url}"/>         <!--             <property name="url" value="${jdbc.url}?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true" />             <property name="url" value="${jdbc.url}?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true"/>        -->         <property name="username" value="${read.jdbc.username}" />         <property name="password" value="${read.jdbc.password}" />           <!-- 配置初始化大小、最小、最大 -->         <property name="initialSize" value="5" />         <property name="minIdle" value="15" />         <property name="maxActive" value="100" />           <!-- 配置获取连接等待超时的时间 -->         <property name="maxWait" value="60000" />           <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->         <property name="timeBetweenEvictionRunsMillis" value="60000" />           <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->         <property name="minEvictableIdleTimeMillis" value="300000" />           <property name="validationQuery" value="SELECT 'x'" />         <property name="testWhileIdle" value="true" />         <property name="testOnBorrow" value="true" />         <property name="testOnReturn" value="false" />           <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->         <property name="poolPreparedStatements" value="true" />         <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />           <!-- 配置监控统计拦截的filters -->         <property name="filters" value="stat,log4j" />     </bean>       <bean id="dataSource" class="com.qding.order.dao.util.DynamicDataSource">         <property name="targetDataSources">             <map key-type="java.lang.String">                 <entry key="master" value-ref="masterDataSource" />                 <entry key="slave" value-ref="slaveDataSource" />             </map>         </property>         <property name="defaultTargetDataSource" ref="masterDataSource" />     </bean> </beans>

加入数据源动态切换 DynamicDataSource:

1 2 3 4 5 6 7 8

public class DynamicDataSource extends AbstractRoutingDataSource {       @Override     protected Object determineCurrentLookupKey() {         return DynamicDataSourceHolder.getDataSouce();     }   }

DynamicDataSourceHolder:

1 2 3 4 5 6 7 8 9 10 11

public class DynamicDataSourceHolder {     public static final ThreadLocal<String> holder = new ThreadLocal<String>();        public static void putDataSource(String name) {          holder.set(name);      }        public static String getDataSouce() {          return holder.get();      }  }

改变代理实现 ReadOnlyTransFactoryBean：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

public Object intercept(Object obj, Method method, Object[] args, MethodProxy proxy) throws Throwable {     T service = applicationContext.getBean(delegateBeanName, serviceInterface);     try {         DynamicDataSourceHolder.putDataSource("slave");         DataSourceTransactionManager txManager = applicationContext.getBean(DataSourceTransactionManager.class);         DefaultTransactionDefinition definition = new DefaultTransactionDefinition(DefaultTransactionDefinition.PROPAGATION_REQUIRED);         definition.setReadOnly(true);         logger.info("Start ReadOnly Transactional!");         TransactionStatus transaction = txManager.getTransaction(definition);         Object result = method.invoke(service, args);         if (!transaction.isCompleted()) {             txManager.commit(transaction);         }         logger.info("End ReadOnly Transactional!");           return result;     } finally {         DynamicDataSourceHolder.putDataSource("master");     } }

使用方法不变