背景: 目前基于连接了mobile库 从库的 项目(spring+mybatis), 需要将从库配置调整为多个直连数据源,? 即 配置多个从库的需要
下面以配置两个mobile从库数据源为例,进行配置示例:
数据源配置文件appContext-jdbc.xml
class="java"><!-- mobile 从库 -->
<bean id="mobileReadDataSource0" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="maxPoolSize" value="200" />
<property name="minPoolSize" value="50" />
<property name="idleConnectionTestPeriod" value="60" />
<property name="autoCommitOnClose" value="true" />
<property name="initialPoolSize" value="10" />
<property name="jdbcUrl"
value="jdbc:mysql://${jdbc.mobile.host}:${jdbc.mobile.port}/${jdbc.mobile.dbname}?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true" />
<property name="user" value="${jdbc.mobile.username}" />
<property name="checkoutTimeout" value="5000" />
<property name="password" value="${jdbc.mobile.password}" />
<property name="acquireRetryAttempts" value="30" />
<property name="acquireRetryDelay" value="1000" />
<property name="breakAfterAcquireFailure" value="false" />
<property name="driverClass" value="com.mysql.jdbc.Driver" />
</bean>
<!-- mobile 从库 -->
<bean id="mobileReadDataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="maxPoolSize" value="200" />
<property name="minPoolSize" value="50" />
<property name="idleConnectionTestPeriod" value="60" />
<property name="autoCommitOnClose" value="true" />
<property name="initialPoolSize" value="10" />
<property name="jdbcUrl"
value="jdbc:mysql://${jdbc.mobile.host}:${jdbc.mobile.port}/${jdbc.mobile.dbname}?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true" />
<property name="user" value="${jdbc.mobile.username}" />
<property name="checkoutTimeout" value="5000" />
<property name="password" value="${jdbc.mobile.password}" />
<property name="acquireRetryAttempts" value="30" />
<property name="acquireRetryDelay" value="1000" />
<property name="breakAfterAcquireFailure" value="false" />
<property name="driverClass" value="com.mysql.jdbc.Driver" />
</bean>
<!-- mobile 写主库 -->
<bean id="mobileWriteDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="maxPoolSize" value="200" />
<property name="minPoolSize" value="50" />
<property name="idleConnectionTestPeriod" value="60" />
<property name="autoCommitOnClose" value="true" />
<property name="initialPoolSize" value="10" />
<property name="jdbcUrl"
value="jdbc:mysql://${jdbc.write.mobile.host}:${jdbc.write.mobile.port}/${jdbc.write.mobile.dbname}?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true" />
<property name="user" value="${jdbc.write.mobile.username}" />
<property name="checkoutTimeout" value="5000" />
<property name="password" value="${jdbc.write.mobile.password}" />
<property name="acquireRetryAttempts" value="30" />
<property name="acquireRetryDelay" value="1000" />
<property name="breakAfterAcquireFailure" value="false" />
<property name="driverClass" value="com.mysql.jdbc.Driver" />
</bean>
<!-- mobile 动态数据源配置,目前是1主2从, 默认连接从库 -->
<bean id="mobileDataSource" class="com.yy.sv.base.db.dynamic.DynamicRoutingDataSource">
<property name="master" ref="mobileWriteDataSource" />
<property name="slaves">
<list>
<ref bean="mobileReadDataSource0" />
<ref bean="mobileReadDataSource1" />
</list>
</property>
<property name="defaultTargetDataSource" ref="mobileReadDataSource0"/>
<!-- <property name="maxFailCount" value="5" /> --> <!--可选配置,设置从库检查maxFailCount次失败后(默认5次),回调处理接口-->
<!-- <property name="HandleFailedPattern" ref="handleFailedPattern" /> --><!--可选配置,业务方实现回调处理接口-->
</bean>
2.? 动态数据源工具类?com.yy.sv.base.db.dynamic.DynamicRoutingDataSource
使用该工具类需要依赖jar
<dependency>
<groupId>com.yy.cs</groupId>
<artifactId>sv-base</artifactId>
<version>1.2.1</version>
</dependency>
DynamicRoutingDataSource内部实现了多个数据源间的负载均衡机制(从数据源的bean id名并不作为均衡机制中的key,可以随意命名),想了解的可以读下源码
3.? 基于目前默认连接的是从库的配置, 业务代码在需要切换至主库进行写操作时,代码如下:
@Override
public Set<Long> checkReidsInStore(List<Long> resids) {
? ?DynamicRoutingDataSourceHolder.useMaster();//切换至主库
? ?Set<Long> residSet = shenquStoreMapper.checkResidsExist(resids);
? ?DynamicRoutingDataSourceHolder.reset();//切换至默认连接从库
? ?return residSet;
}
4. 之前代码中已经使用了如下代码的可以这样切换:
@Override
public Set<Long> checkReidsInStore(List<Long> resids) {
? DynamicDataSourceHolder.setDataSourceKey(Constants.MOBILE_DATA_SOURCE_SPRING_BEAD_ID, Constants.WRITE_DATA_SOURCE);
? Set<Long> residSet = shenquStoreMapper.checkResidsExist(resids);
? DynamicDataSourceHolder.clearDataSourceMapKey();
return residSet;
}
将DynamicDataSourceHolder的setDataSourceKey以及clearDataSourceMapKey方法的实现调整为:
public static void setDataSourceKey(String dataSource,String dataSourceKey) {
? DynamicRoutingDataSourceHolder.useMaster();//基于目前默认连从库,切换至主库才需要手动设置dataSourceKey
}
public static void clearDataSourceMapKey() {
? DynamicRoutingDataSourceHolder.reset();
}
这样业务代码中进行主从切换的代码就不需要改动, 减少改动和入侵
5. 如果项目中没有使用到写主库数据源, 那么可以这样配置(紫色部分),因为没有进行过主从切换, 所以用从库数据源顶替主写数据源:
<!-- mobile 动态数据源配置,目前是1主2从, 默认连接从库 -->
<bean id="mobileDataSource" class="com.yy.sv.base.db.dynamic.DynamicRoutingDataSource">
<property name="master" ref="mobileReadDataSource0" />
<property name="slaves">
<list>
<ref bean="mobileReadDataSource0" />
<ref bean="mobileReadDataSource1" />
</list>
</property>
<property name="defaultTargetDataSource" ref="mobileReadDataSource0"/>
<!-- <property name="maxFailCount" value="5" /> --> <!--可选配置,设置从库检查maxFailCount次失败后(默认5次),回调处理接口-->
<!-- <property name="HandleFailedPattern" ref="handleFailedPattern" /> --><!--可选配置,业务方实现回调处理接口-->
</bean>