最近用到了MyBatis配置多資料來源，原以為簡單配置下就行了，實際操作後發現還是要費些事的，這裡記錄下，以作備忘
不多廢話，直接上程式碼，後面會有簡單的實現介紹
jdbc和log4j的配置
#定義輸出格式 ConversionPattern=%d %-5p [%t] %c - %m%n log4j.rootLogger=DEBUG,Console log4j.logger.com.cnblogs.lzrabbit=DEBUG log4j.logger.org.springframework=ERROR log4j.logger.org.mybatis=ERROR log4j.logger.org.apache.ibatis=ERROR log4j.logger.org.quartz=ERROR log4j.logger.org.apache.axis2=ERROR log4j.logger.org.apache.axiom=ERROR log4j.logger.org.apache=ERROR log4j.logger.httpclient=ERROR #log4j.additivity.org.springframework=false #Console log4j.appender.Console=org.apache.log4j.ConsoleAppender log4j.appender.Console.Threshold=DEBUG log4j.appender.Console.Target=System.out log4j.appender.Console.layout=org.apache.log4j.PatternLayout log4j.appender.Console.layout.ConversionPattern=${ConversionPattern} #log4j.appender.Console.encoding=UTF-8 #org.apache.log4j.DailyRollingFileAppender log4j.appender.DailyFile=org.apache.log4j.DailyRollingFileAppender log4j.appender.DailyFile.DatePattern='.'yyyy-MM-dd'.log' log4j.appender.DailyFile.File=${myApp.root}/logs/daily.log log4j.appender.DailyFile.Append=true log4j.appender.DailyFile.Threshold=DEBUG log4j.appender.DailyFile.layout=org.apache.log4j.PatternLayout log4j.appender.DailyFile.layout.ConversionPattern=${ConversionPattern} log4j.appender.DailyFile.encoding=UTF-8 # %c 輸出日誌資訊所屬的類的全名 # %d 輸出日誌時間點的日期或時間，預設格式為ISO8601，也可以在其後指定格式，比如：%d{yyy-MM-dd HH:mm:ss}，輸出類似：2002-10-18- 22：10：28 # %f 輸出日誌資訊所屬的類的類名 # %l 輸出日誌事件的發生位置，即輸出日誌資訊的語句處於它所在的類的第幾行 # %m 輸出程式碼中指定的資訊，如log(message)中的message # %n 輸出一個回車換行符，Windows平臺為“rn”，Unix平臺為“n” # %p 輸出優先順序，即DEBUG，INFO，WARN，ERROR，FATAL。如果是呼叫debug()輸出的，則為DEBUG，依此類推 # %r 輸出自應用啟動到輸出該日誌資訊所耗費的毫秒數 # %t 輸出產生該日誌事件的執行緒名
#============================================================================ # MySQL #============================================================================ jdbc.mysql.driver=com.mysql.jdbc.Driver jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true jdbc.mysql.username=root jdbc.mysql.password=root #============================================================================ # MS SQL Server #============================================================================ #jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver #jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test; #jdbc.sqlserver.username=sa #jdbc.sqlserver.password=sa #============================================================================ # MS SQL Server (JTDS) #============================================================================ jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test jdbc.sqlserver.username=sa jdbc.sqlserver.password=sa #============================================================================ # 通用配置 #============================================================================ jdbc.initialSize=5 jdbc.minIdle=5 jdbc.maxIdle=20 jdbc.maxActive=100 jdbc.maxWait=100000 jdbc.defaultAutoCommit=false jdbc.removeAbandoned=true jdbc.removeAbandonedTimeout=600 jdbc.testWhileIdle=true jdbc.timeBetweenEvictionRunsMillis=60000 jdbc.numTestsPerEvictionRun=20 jdbc.minEvictableIdleTimeMillis=300000
單資料來源時的Spring配置檔案
<?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:aop="http://www.springframework.org/schema/aop" 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/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:jdbc.properties"/> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.mysql.driver}"/> <property name="url" value="${jdbc.mysql.url}"/> <property name="username" value="${jdbc.mysql.username}"/> <property name="password" value="${jdbc.mysql.password}"/> <property name="initialSize" value="${jdbc.initialSize}"/> <property name="minIdle" value="${jdbc.minIdle}"/> <property name="maxIdle" value="${jdbc.maxIdle}"/> <property name="maxActive" value="${jdbc.maxActive}"/> <property name="maxWait" value="${jdbc.maxWait}"/> <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/> <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/> <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/> <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/> <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/> <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/> <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> </bean> <!-- mybatis.spring自動對映 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.cnblogs.lzrabbit"/> </bean> <!-- 自動掃描，多個包以 逗號分隔 --> <context:component-scan base-package="com.cnblogs.lzrabbit"/> <aop:aspectj-autoproxy/> </beans>
多資料來源時Spring配置檔案
<?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:aop="http://www.springframework.org/schema/aop" 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/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:jdbc.properties"/> </bean> <bean id="sqlServerDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.sqlserver.driver}"/> <property name="url" value="${jdbc.sqlserver.url}"/> <property name="username" value="${jdbc.sqlserver.username}"/> <property name="password" value="${jdbc.sqlserver.password}"/> <property name="initialSize" value="${jdbc.initialSize}"/> <property name="minIdle" value="${jdbc.minIdle}"/> <property name="maxIdle" value="${jdbc.maxIdle}"/> <property name="maxActive" value="${jdbc.maxActive}"/> <property name="maxWait" value="${jdbc.maxWait}"/> <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/> <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/> <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/> <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/> <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/> <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/> <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/> </bean> <bean id="mySqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.mysql.driver}"/> <property name="url" value="${jdbc.mysql.url}"/> <property name="username" value="${jdbc.mysql.username}"/> <property name="password" value="${jdbc.mysql.password}"/> <property name="initialSize" value="${jdbc.initialSize}"/> <property name="minIdle" value="${jdbc.minIdle}"/> <property name="maxIdle" value="${jdbc.maxIdle}"/> <property name="maxActive" value="${jdbc.maxActive}"/> <property name="maxWait" value="${jdbc.maxWait}"/> <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/> <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/> <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/> <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/> <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/> <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/> <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/> </bean> <bean id="multipleDataSource" class="com.cnblogs.lzrabbit.MultipleDataSource"> <property name="defaultTargetDataSource" ref="mySqlDataSource"/> <property name="targetDataSources"> <map> <entry key="mySqlDataSource" value-ref="mySqlDataSource"/> <entry key="sqlServerDataSource" value-ref="sqlServerDataSource"/> </map> </property> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="multipleDataSource"/> </bean> <!-- mybatis.spring自動對映 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.cnblogs.lzrabbit"/> </bean> <!-- 自動掃描，多個包以 逗號分隔 --> <context:component-scan base-package="com.cnblogs.lzrabbit"/> <aop:aspectj-autoproxy/> </beans>
MultipleDataSource實現
package com.cnblogs.lzrabbit; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * Created by rabbit on 14-5-25. */ public class MultipleDataSource extends AbstractRoutingDataSource { private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>(); public static void setDataSourceKey(String dataSource) { dataSourceKey.set(dataSource); } @Override protected Object determineCurrentLookupKey() { return dataSourceKey.get(); } }
MyBatis介面Mapper定義，直接使用註解方式實現
public interface MySqlMapper { @Select("select * from MyTable") List<Map<String,Object>> getList(); } public interface SqlServerMapper { @Select("select * from MyTable") List<Map<String,Object>> getList(); }
手動資料來源切換呼叫
package com.cnblogs.lzrabbit; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; /** * Created by rabbit on 14-5-25. */ public class Main { public static void main(String[] args) { //初始化ApplicationContext ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); MySqlMapper mySqlMapper = applicationContext.getBean(MySqlMapper.class); SqlServerMapper sqlServerMapper = applicationContext.getBean(SqlServerMapper.class); //設定資料來源為MySql,使用了AOP測試時請將下面這行註釋 MultipleDataSource.setDataSourceKey("mySqlDataSource"); mySqlMapper.getList(); //設定資料來源為SqlServer,使用AOP測試時請將下面這行註釋 MultipleDataSource.setDataSourceKey("sqlServerDataSource"); sqlServerMapper.getList(); } }
使用SpringAOP方式實現自動切換
package com.cnblogs.lzrabbit; import org.aspectj.lang.ProceedingJoinPoint; import org.aspectj.lang.annotation.Around; import org.aspectj.lang.annotation.Aspect; import org.springframework.stereotype.Component; @Component @Aspect public class MultipleDataSourceAspectAdvice { @Around("execution(* com.cnblogs.lzrabbit.*.*(..))") public Object doAround(ProceedingJoinPoint jp) throws Throwable { if (jp.getTarget() instanceof MySqlMapper) { MultipleDataSource.setDataSourceKey("mySqlDataSource"); } else if (jp.getTarget() instanceof SqlServerMapper) { MultipleDataSource.setDataSourceKey("sqlServerDataSource"); } return jp.proceed(); } }
呼叫日誌
2014-05-25 20:02:04,319 DEBUG [main] com.cnblogs.lzrabbit.MySqlMapper.getList - ooo Using Connection [jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true, UserName=root@192.168.1.32, MySQL Connector Java] 2014-05-25 20:02:04,333 DEBUG [main] com.cnblogs.lzrabbit.MySqlMapper.getList - ==> Preparing: select * from MyTable 2014-05-25 20:02:04,371 DEBUG [main] com.cnblogs.lzrabbit.MySqlMapper.getList - ==> Parameters: 2014-05-25 20:02:04,396 DEBUG [main] com.cnblogs.lzrabbit.MySqlMapper.getList - <== Total: 8 2014-05-25 20:02:04,620 DEBUG [main] com.cnblogs.lzrabbit.SqlServerMapper.getList - ooo Using Connection [jdbc:jtds:sqlserver://127.0.0.1:1433/test, UserName=sa, jTDS Type 4 JDBC Driver for MS SQL Server and Sybase] 2014-05-25 20:02:04,620 DEBUG [main] com.cnblogs.lzrabbit.SqlServerMapper.getList - ==> Preparing: select * from TmallCityMap 2014-05-25 20:02:04,621 DEBUG [main] com.cnblogs.lzrabbit.SqlServerMapper.getList - ==> Parameters: 2014-05-25 20:02:04,681 DEBUG [main] com.cnblogs.lzrabbit.SqlServerMapper.getList - <== Total: 397
這裡就上面的實現做個簡單解釋，在我們配置單資料來源時可以看到資料來源型別使用了org.apache.commons.dbcp.BasicDataSource，而這個程式碼實現了javax.sql.DataSource介面
配置sqlSessionFactory時org.mybatis.spring.SqlSessionFactoryBean注入引數dataSource型別就是javax.sql.DataSource
實現多資料來源的方法就是我們自定義了一個MultipleDataSource，這個類繼承自AbstractRoutingDataSource，而AbstractRoutingDataSource繼承自AbstractDataSource ，AbstractDataSource 實現了javax.sql.DataSource介面，所以我們的MultipleDataSource也實現了javax.sql.DataSource介面，可以賦值給sqlSessionFactory的dataSource屬性
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean { } public abstract class AbstractDataSource implements DataSource { }
再來說下MultipleDataSource的實現原理，MultipleDataSource實現AbstractRoutingDataSource抽象類，然後實現了determineCurrentLookupKey方法，這個方法用於選擇具體使用targetDataSources中的哪一個資料來源
<bean id="multipleDataSource" class="com.cnblogs.lzrabbit.MultipleDataSource"> <property name="defaultTargetDataSource" ref="mySqlDataSource"/> <property name="targetDataSources"> <map> <entry key="mySqlDataSource" value-ref="mySqlDataSource"/> <entry key="sqlServerDataSource" value-ref="sqlServerDataSource"/> </map> </property> </bean>
可以看到Spring配置中multipleDataSource設定了兩個屬性defaultTargetDataSource和targetDataSources，這兩個屬性定義在AbstractRoutingDataSource，當MyBatis執行查詢時會先選擇資料來源，選擇順序時現根據determineCurrentLookupKey方法返回的值到targetDataSources中去找，若能找到怎返回對應的資料來源，若找不到返回預設的資料來源defaultTargetDataSource，具體參考AbstractRoutingDataSource的原始碼
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean { private Map<Object, Object> targetDataSources; private Object defaultTargetDataSource; /** * Retrieve the current target DataSource. Determines the * {@link #determineCurrentLookupKey() current lookup key}, performs * a lookup in the {@link #setTargetDataSources targetDataSources} map, * falls back to the specified * {@link #setDefaultTargetDataSource default target DataSource} if necessary. * @see #determineCurrentLookupKey() */ protected DataSource determineTargetDataSource() { Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); Object lookupKey = determineCurrentLookupKey(); DataSource dataSource = this.resolvedDataSources.get(lookupKey); if (dataSource == null && (this.lenientFallback || lookupKey == null)) { dataSource = this.resolvedDefaultDataSource; } if (dataSource == null) { throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); } return dataSource; } /** * Determine the current lookup key. This will typically be * implemented to check a thread-bound transaction context. * <p>Allows for arbitrary keys. The returned key needs * to match the stored lookup key type, as resolved by the * {@link #resolveSpecifiedLookupKey} method. */ protected abstract Object determineCurrentLookupKey();
............. }
在動態切換資料來源方法時選擇了AOP方式實現，這裡實現的簡單粗暴，具體應用時根據實際需要靈活變通吧
題外話，這裡提下SqlServer驅動選擇的問題，目前SqlServer的驅動主要有微軟的官方驅動和JTDS驅動兩種，關於這兩個驅動我做過測試，批量更新，在小資料量(100以下)時，JTDS相對微軟碟機動效能稍微高一點點，在資料量增大時幾萬到上百萬時，微軟碟機動有著明顯優勢，所以若對效能比較敏感，建議使用微軟碟機動，否則隨意
微軟碟機動在Maven庫找不到，這點比較鬱悶，若使用maven的話還得先安裝到本地，這點很不爽
JTDS使用比較方便Maven直接引用即可
相關jar maven引用
<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <org.springframework.version>3.2.7.RELEASE</org.springframework.version> </properties> <dependencies> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.7.2</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <version>1.1.3</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.2.4</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.2.2</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.6</version> </dependency> <dependency> <groupId>net.sourceforge.jtds</groupId> <artifactId>jtds</artifactId> <version>1.2.8</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.29</version> </dependency> </dependencies>