Spring+MyBatis多数据源配置实现示例

最近用到了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.jb51.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.jb51.lzrabbit.mysqlmapper.getlist - ==> preparing: select * from mytable 
2014-05-25 20:02:04,371 debug [main] com.jb51.lzrabbit.mysqlmapper.getlist - ==> parameters: 
2014-05-25 20:02:04,396 debug [main] com.jb51.lzrabbit.mysqlmapper.getlist - <==  total: 8
2014-05-25 20:02:04,620 debug [main] com.jb51.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.jb51.lzrabbit.sqlservermapper.getlist - ==> preparing: select * from tmallcitymap 
2014-05-25 20:02:04,621 debug [main] com.jb51.lzrabbit.sqlservermapper.getlist - ==> parameters: 
2014-05-25 20:02:04,681 debug [main] com.jb51.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>

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。