Spring 多數(shù)據(jù)源已經(jīng)不是什么稀奇的事了雀摘,在實(shí)際應(yīng)用中主從數(shù)據(jù)庫(kù)就需要用到多數(shù)據(jù)源的配置與動(dòng)態(tài)切換实愚。在搜索引擎中都可以找到很多資料纬傲,各也有各的做法襟衰,本文也就不做過(guò)多的闡述其原理贴铜,只是介紹下目前我在項(xiàng)目中對(duì)于多數(shù)據(jù)源的使用情況,歡迎大家前來(lái)拍磚瀑晒。
先特別感謝好友 Tony
首先绍坝,繼承spring-jdbc 的 AbstractRoutingDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {
protected Object determineCurrentLookupKey() {
return DataSourceHolder.getDataSources();
}
}
創(chuàng)建自己的DataSourceHolder
public class DataSourceHolder {
private static ThreadLocal<String> dataSources = new ThreadLocal<String>();
public static String getDataSources() {
String source = dataSources.get();
if (source == null) source = DataSourceType.MASTER;
return source;
}
public static void setDataSources(String source) {
dataSources.set(source);
}
}
當(dāng)然還有DataSourceType
public class DataSourceType {
public static final String MASTER = "master";
public static final String SLAVE = "slaver";
}
到此為止,和所有搜索引擎中找到的資料一致苔悦,除了命名可能有些區(qū)別 _
那么接下來(lái)介紹一下不同的地方轩褐,網(wǎng)上也有很多資料是通過(guò)AOP來(lái)實(shí)現(xiàn)動(dòng)態(tài)切換,比如:Spring 配置多數(shù)據(jù)源實(shí)現(xiàn)數(shù)據(jù)庫(kù)讀寫(xiě)分離 這篇文章中的介紹玖详,就很完美的使用AOP實(shí)現(xiàn)了主從數(shù)據(jù)庫(kù)的切換把介,而我的做法有一些大同小異,我使用了Java動(dòng)態(tài)代理蟋座,先上代碼拗踢。
先創(chuàng)建自己的Annotation:
從庫(kù)注解
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Read {}
主庫(kù)注解
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Write {}
為了以防萬(wàn)一,增加了OtherSource
而這個(gè)也只是為了防止在一個(gè)項(xiàng)目中有多個(gè)數(shù)據(jù)庫(kù)的連接需求向臀,比如:將用戶(hù)信息獨(dú)立成庫(kù)巢墅,商品信息獨(dú)立成庫(kù)等
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface OtherSource {
String value();
}
以下則為真正重點(diǎn)——?jiǎng)討B(tài)代理
繼承MapperFactoryBean,實(shí)現(xiàn)InvocationHandler飒硅,在invoke之前砂缩,根據(jù)注解來(lái)實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)源的切換
public class MapperFactoryBeanInvocation<T> extends MapperFactoryBean<T> implements InvocationHandler {
private T t;
public MapperFactoryBeanInvocation() { }
public T getObject() throws Exception {
T t = super.getObject();
return this.getProxyObject(t);
}
private T getProxyObject(T t) {
this.t = t;
return (T) Proxy.newProxyInstance(t.getClass().getClassLoader(), t.getClass().getInterfaces(), this);
}
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
boolean isRead = true;
StringBuffer typeBuffer = new StringBuffer("");
//獲取是否有其他數(shù)據(jù)源
OtherSource ortherSourceAnn = method.getAnnotation(OtherSource.class);
if (ortherSourceAnn != null) {
typeBuffer.append(ortherSourceAnn.value()).append("_");
}
//獲取主從狀態(tài)
Annotation ann_method = method.getAnnotation(Read.class);
if (ann_method == null) isRead = false;
if (isRead) typeBuffer.append(DataSourceType.SLAVE);
else typeBuffer.append(DataSourceType.MASTER);
//切換數(shù)據(jù)源
DataSourceHolder.setDataSources(typeBuffer.toString());
return method.invoke(this.t, args);
}
}
至此,Spring多數(shù)據(jù)源主從動(dòng)態(tài)切換已經(jīng)實(shí)現(xiàn)三娩。
在實(shí)際使用過(guò)程中庵芭,對(duì)應(yīng)的管理好多數(shù)據(jù)源配置,即可實(shí)現(xiàn)動(dòng)態(tài)切換的目的
db.properties 配置
#jdbc driver
jdbc.driverClassName=com.mysql.jdbc.Driver
#master
design.master.jdbc.url=jdbc:mysql://192.168.1.101:3306/design_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true
design.master.jdbc.username=root
design.master.jdbc.password=111111
#slaver
design.slaver.jdbc.url=jdbc:mysql://192.168.1.102:3306/design_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true
design.slaver.jdbc.username=root
design.slaver.jdbc.password=111111
#master
shop.master.jdbc.url=jdbc:mysql://192.168.1.103:3306/shop_mall?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true
shop.master.jdbc.username=root
shop.master.jdbc.password=a12345678
#slaver
shop.slaver.jdbc.url=jdbc:mysql://192.168.1.104:3306/shop_mall?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true
shop.slaver.jdbc.username=root
shop.slaver.jdbc.password=a12345678
#c3p0 config
c3p0.initialPoolSize=8
c3p0.minPoolSize=5
c3p0.maxPoolSize=10
c3p0.acquireIncrement=10
c3p0.maxIdleTime=60
c3p0.idleConnectionTestPeriod=120
c3p0.maxStatements=100
c3p0.autoCommitOnClose=false
c3p0.testConnectionOnCheckout=false
c3p0.testConnectionOnCheckin=false
c3p0.preferredTestQuery=select now()
spring 配置
...
<bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="initialPoolSize" value="${c3p0.initialPoolSize}"/>
<property name="minPoolSize" value="${c3p0.minPoolSize}"/>
<property name="maxPoolSize" value="${c3p0.maxPoolSize}"/>
<property name="acquireIncrement" value="${c3p0.acquireIncrement}"/>
<property name="maxIdleTime" value="${c3p0.maxIdleTime}"/>
<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"/>
<property name="maxStatements" value="${c3p0.maxStatements}"/>
<property name="autoCommitOnClose" value="${c3p0.autoCommitOnClose}"/>
<property name="testConnectionOnCheckout" value="${c3p0.testConnectionOnCheckout}"/>
<property name="testConnectionOnCheckin" value="${c3p0.testConnectionOnCheckin}"/>
<property name="preferredTestQuery" value="${c3p0.preferredTestQuery}"/>
</bean>
<import resource="dataSource.xml"/>
<import resource="data_mapping.xml"/>
...
dataSource.xml 配置
...
<!-- 配置主庫(kù)數(shù)據(jù)源 -->
<bean id="designMasterDataSource" parent="parentDataSource">
<property name="jdbcUrl" value="${design.master.jdbc.url}"/>
<property name="user" value="${design.master.jdbc.username}"/>
<property name="password" value="${design.master.jdbc.password}"/>
</bean>
<!-- 配置從庫(kù)數(shù)據(jù)源 -->
<bean id="designSlaverDataSource" parent="parentDataSource">
<property name="jdbcUrl" value="${design.slaver.jdbc.url}"/>
<property name="user" value="${design.slaver.jdbc.username}"/>
<property name="password" value="${design.slaver.jdbc.password}"/>
</bean>
<!-- 配置主庫(kù)數(shù)據(jù)源 -->
<bean id="shopMasterDataSource" parent="parentDataSource">
<property name="jdbcUrl" value="${shop.master.jdbc.url}"/>
<property name="user" value="${shop.master.jdbc.username}"/>
<property name="password" value="${shop.master.jdbc.password}"/>
</bean>
<!-- 配置從庫(kù)數(shù)據(jù)源 -->
<bean id="shopSlaverDataSource" parent="parentDataSource">
<property name="jdbcUrl" value="${shop.slaver.jdbc.url}"/>
<property name="user" value="${shop.slaver.jdbc.username}"/>
<property name="password" value="${shop.slaver.jdbc.password}"/>
</bean>
<!-- 配置動(dòng)態(tài)數(shù)據(jù)源 -->
<bean id="designDataSource" class="com.design.datasource.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="slaver" value-ref="designSlaverDataSource"/>
<entry key="master" value-ref="designMasterDataSource"/>
<entry key="shop_slaver" value-ref="shopSlaverDataSource"/>
<entry key="shop_master" value-ref="shopMasterDataSource"/>
</map>
</property>
<!-- 默認(rèn)叢庫(kù) -->
<property name="defaultTargetDataSource" ref="designSlaverDataSource"/>
</bean>
<!-- session factory -->
<bean id="designSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="designDataSource"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
...
data_mapping.xml 配置
需要注意的是雀监,此處class指向的是自己創(chuàng)建的MapperFactoryBeanInvocation動(dòng)態(tài)代理
<bean id="accountLogMapper" class="com.design.datasource.factory.MapperFactoryBeanInvocation">
<property name="mapperInterface" value="com.design.shop.dao.AccountLogMapper"/>
<property name="sqlSessionFactory" ref="designSessionFactory"/>
</bean>
<bean id="imageMapper" class="com.design.datasource.factory.MapperFactoryBeanInvocation">
<property name="mapperInterface" value="com.design.shop.dao.MallWechatImageTextTempMapper"/>
<property name="sqlSessionFactory" ref="designSessionFactory"/>
</bean>
Mybatis Mapper
public interface AccountLogMapper {
...
@SelectProvider(type=AccountLogSqlProvider.class, method="selectByExample")
@Results({
@Result(column="log_id", property="logId", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="user_id", property="userId", jdbcType=JdbcType.INTEGER),
@Result(column="user_money", property="userMoney", jdbcType=JdbcType.DECIMAL),
@Result(column="frozen_money", property="frozenMoney", jdbcType=JdbcType.DECIMAL),
@Result(column="rank_points", property="rankPoints", jdbcType=JdbcType.INTEGER),
@Result(column="pay_points", property="payPoints", jdbcType=JdbcType.INTEGER),
@Result(column="change_time", property="changeTime", jdbcType=JdbcType.INTEGER),
@Result(column="change_desc", property="changeDesc", jdbcType=JdbcType.VARCHAR),
@Result(column="change_type", property="changeType", jdbcType=JdbcType.TINYINT)
})
//因?yàn)槟J(rèn)為一庫(kù)主從双吆,因此這個(gè)Mapper沒(méi)有使用@OtherSource注解眨唬,而@Read則指向從庫(kù),對(duì)應(yīng)dataSource.xml 中的 salver
@Read
List<AccountLog> selectByExample(AccountLogExample example);
...
}
public interface MallWechatImageTextTempMapper {
...
@SelectProvider(type=MallWechatImageTextTempSqlProvider.class, method="countByExample")
//此處@OtherSource("shop")指向了shop_mall數(shù)據(jù)庫(kù) @Read 指向從庫(kù) 所以對(duì)應(yīng) dataSource.xml 中的 shop_slaver
@Read
@OtherSource("shop")
long countByExample(MallWechatImageTextTempExample example);
...
}
Spring Test
測(cè)試代碼使用kotlin編寫(xiě)好乐,基本和Java區(qū)別不是很大匾竿,函數(shù)式寫(xiě)起來(lái)更方便 _
@RunWith(SpringJUnit4ClassRunner::class)
@ContextConfiguration("classpath:appServer.xml")
class Test {
@Autowired
var mapper: AccountLogMapper? = null
@Autowired
var imageMapper: MallWechatImageTextTempMapper? = null
@Test
fun test() {
println("*********************************** test shop_mall mall_wechat_image_temp countByExample ***********************************************")
val count = imageMapper?.countByExample(MallWechatImageTextTempExample().apply {
createCriteria().andTempSetCodeEqualTo("wechat_temp_1")
resultColumn = "tempid"
offset = 0
limit = 100}) ?: 0L
println(count)
assert(true)
}
@Test
fun testMyselfDB() {
println("*********************************** test myself design_shop account_log countByExample ***********************************************")
val list = mapper?.selectByExample(AccountLogExample()) ?: arrayListOf<AccountLog>()
if (list.size > 0) {
assert(true)
} else {
assert(false)
}
}
}
好了,所有和動(dòng)態(tài)數(shù)據(jù)源相關(guān)的代碼與示例已經(jīng)完成了蔚万,我們現(xiàn)在來(lái)看下實(shí)際運(yùn)行效果:
*********************************** test shop_mall mall_wechat_image_temp countByExample ***********************************************
02:31:56 [main] INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 10, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge1619lmh41qu1kjm50|366ac49b, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge1619lmh41qu1kjm50|366ac49b, idleConnectionTestPeriod -> 120, initialPoolSize -> 8, jdbcUrl -> jdbc:mysql://139.224.40.142:3306/mall?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 60, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> select now(), privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
02:31:56 [main] DEBUG com.design.shop.dao.MallWechatImageTextTempMapper.countByExample - ==> Preparing: SELECT count(tempid) FROM comp_mall_wechat_imagetext_temp WHERE ((temp_set_code = ?)) LIMIT 0,100
02:31:56 [main] DEBUG com.design.shop.dao.MallWechatImageTextTempMapper.countByExample - ==> Parameters: wechat_temp_1(String)
02:31:56 [main] DEBUG com.design.shop.dao.MallWechatImageTextTempMapper.countByExample - <== Total: 1
4
*********************************** test myself design_shop account_log countByExample ***********************************************
02:31:56 [main] INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 10, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge1619lmh41qu1kjm50|5f0e9815, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge1619lmh41qu1kjm50|5f0e9815, idleConnectionTestPeriod -> 120, initialPoolSize -> 8, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/design_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 60, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> select now(), privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
02:31:56 [main] DEBUG com.design.shop.dao.AccountLogMapper.selectByExample - ==> Preparing: SELECT log_id, user_id, user_money, frozen_money, rank_points, pay_points, change_time, change_desc, change_type FROM des_account_log
02:31:56 [main] DEBUG com.design.shop.dao.AccountLogMapper.selectByExample - ==> Parameters:
02:31:56 [main] DEBUG com.design.shop.dao.AccountLogMapper.selectByExample - <== Total: 35
2個(gè)不同的從庫(kù)實(shí)現(xiàn)了動(dòng)態(tài)切換岭妖,是不是有點(diǎn)暗爽?當(dāng)然 Mybatis Mapper 是由 MybatisGenerator生成的反璃,原版生成可不會(huì)帶@Read @Write @OtherSource 等注解 也沒(méi)有 offset昵慌、limit 分頁(yè)屬性,以上內(nèi)容可以通過(guò)對(duì)MybatisGenerator做一些小改造淮蜈,提供插件即可全部實(shí)現(xiàn)斋攀。在后續(xù)文章中會(huì)提及MybatisGenerator的插件制作,盡請(qǐng)期待 _
第一次發(fā)文梧田,文筆還有沒(méi)有介紹清楚的地方淳蔼,還請(qǐng)海涵。