掌柜大作战(6)：Spring+Mybatis+多数据源配置+事务配置

多数据源配置，经常遇到的问题。

网上看到的都是别人总结的，最近一个项目，有需要用到2个数据源，就自己配置了下。

非常符合自己的思路：清晰、简洁、方便扩展。

1、Spring核心数据源和事务配置

spring-info-datasource.xml

<?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:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util"xmlns:task="http://www.springframework.org/schema/task" 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.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsdhttp://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsdhttp://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsdhttp://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsdhttp://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.0.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"><!-- 多数据源，根据key动态切换 --><bean id="lifecycleDataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"><!-- 数据库基本信息配置 --><property name="driverClassName" value="${lifecycleDatasourceAsset.driverClassName}" /><property name="url" value="${lifecycleDatasourceAsset.url}" /><property name="username" value="${lifecycleDatasourceAsset.username}" /><property name="password" value="${lifecycleDatasourceAsset.password}" /><!-- 最大并发连接数 --><property name="maxActive" value="${lifecycleDatasourceAsset.maxActive}" /><!-- 初始化连接数量 --><property name="initialSize" value="${lifecycleDatasourceAsset.initialSize}" /><!-- 配置获取连接等待超时的时间 --><property name="maxWait" value="${lifecycleDatasourceAsset.maxWait}" /><!-- 最小空闲连接数 --><property name="minIdle" value="${lifecycleDatasourceAsset.minIdle}" /><!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 --><property name="timeBetweenEvictionRunsMillis"value="${lifecycleDatasourceAsset.timeBetweenEvictionRunsMillis}" /><!-- 配置一个连接在池中最小生存的时间，单位是毫秒 --><property name="minEvictableIdleTimeMillis"value="${lifecycleDatasourceAsset.minEvictableIdleTimeMillis}" /><property name="validationQuery" value="SELECT 1"></property><!-- 打开removeAbandoned功能 --><property name="removeAbandoned" value="true" /><!-- 1800秒，也就是30分钟 --><property name="removeAbandonedTimeout" value="180" /></bean><bean id="jdbuyDataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"><!-- 数据库基本信息配置 --><property name="driverClassName" value="${jdbuyDatasourceAsset.driverClassName}" /><property name="url" value="${jdbuyDatasourceAsset.url}" /><property name="username" value="${jdbuyDatasourceAsset.username}" /><property name="password" value="${jdbuyDatasourceAsset.password}" /><!-- 最大并发连接数 --><property name="maxActive" value="${jdbuyDatasourceAsset.maxActive}" /><!-- 初始化连接数量 --><property name="initialSize" value="${jdbuyDatasourceAsset.initialSize}" /><!-- 配置获取连接等待超时的时间 --><property name="maxWait" value="${jdbuyDatasourceAsset.maxWait}" /><!-- 最小空闲连接数 --><property name="minIdle" value="${jdbuyDatasourceAsset.minIdle}" /><!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 --><property name="timeBetweenEvictionRunsMillis"value="${jdbuyDatasourceAsset.timeBetweenEvictionRunsMillis}" /><!-- 配置一个连接在池中最小生存的时间，单位是毫秒 --><property name="minEvictableIdleTimeMillis"value="${jdbuyDatasourceAsset.minEvictableIdleTimeMillis}" /><property name="validationQuery" value="SELECT 1"></property><!-- 打开removeAbandoned功能 --><property name="removeAbandoned" value="true" /><!-- 1800秒，也就是30分钟 --><property name="removeAbandonedTimeout" value="180" /></bean><bean id="multiDataSource" class="com.jd.zc.lifecycle.dao.datasource.MultiDataSource">        <property name="defaultTargetDataSource" ref="lifecycleDataSource"/>        <property name="targetDataSources">            <map>                <entry key="jdbuyDataSource" value-ref="jdbuyDataSource"/>                <entry key="lifecycleataSource" value-ref="lifecycleDataSource"/>            </map>        </property>    </bean>        <!-- 事务，每个数据源配置单独的事务 --><bean name="lifecycleTransactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="lifecycleDataSource" /><qualifier value="lifecycle"/>  </bean><tx:annotation-driven transaction-manager="lifecycleTransactionManager"proxy-target-class="true" /><bean name="jdbuyTransactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="jdbuyDataSource" /><qualifier value="jdbuy"/>  </bean><!-- 注解事务 --><tx:annotation-driven transaction-manager="jdbuyTransactionManager"proxy-target-class="true" /><!-- 配置mybatis --><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="multiDataSource" /><property name="configLocation" value="classpath:mybatis/mybatis-config.xml" /><!-- mapper扫描 --><property name="mapperLocations"><value>classpath:mybatis/mapper/**/*Mapper.xml</value></property></bean><!--创建数据映射器，数据映射器必须为接口 --><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="annotationClass" value="org.springframework.stereotype.Repository" /><property name="basePackage" value="com.jd.zc.lifecycle.dao" /></bean><bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"><constructor-arg ref="sqlSessionFactory" /></bean><context:component-scan base-package="com.jd.zc.lifecycle.dao" /></beans>

数据源属性文件jdbc.properties

#lifecyclelifecycleDatasourceAsset.driverClassName=com.mysql.jdbc.DriverlifecycleDatasourceAsset.url=jdbc:mysql://lifecycle?characterEncoding=UTF-8&allowMultiQueries=truelifecycleDatasourceAsset.initialSize=0lifecycleDatasourceAsset.maxActive=20lifecycleDatasourceAsset.minIdle=2lifecycleDatasourceAsset.maxWait=1000lifecycleDatasourceAsset.minEvictableIdleTimeMillis=300000lifecycleDatasourceAsset.timeBetweenEvictionRunsMillis=120000#jdbuyjdbuyDatasourceAsset.driverClassName=com.mysql.jdbc.DriverjdbuyDatasourceAsset.url=jdbc:mysql://192.168./jdbuy?characterEncoding=UTF-8&allowMultiQueries=truejdbuyDatasourceAsset.initialSize=0jdbuyDatasourceAsset.maxActive=20jdbuyDatasourceAsset.minIdle=2jdbuyDatasourceAsset.maxWait=1000jdbuyDatasourceAsset.minEvictableIdleTimeMillis=300000jdbuyDatasourceAsset.timeBetweenEvictionRunsMillis=120000

2、Mapper映射xml文件

具体到某个Mapper文件，都是很标准正常的配置。

出于方便好维护，每个数据源的mapper文件单独放在一个目录。

3、Mapper接口类

BaseMapper：通用接口

public interface BaseMapper<ID, Entity, Bean> {// readpublic Entity get(ID id);// writepublic int add(Entity entity);public int update(Entity entity);public int remove(ID id);public int removeByIdList(List<ID> idList);}public interface JdbuyBaseMapper<ID, Entity, Bean> extends BaseMapper<ID, Entity, Bean>{}public interface LifecycleBaseMapper<ID, Entity, Bean> extends BaseMapper<ID, Entity, Bean>{}

4、多数据源定义和动态切换

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class MultiDataSource extends AbstractRoutingDataSource {    public static void setDataSourceKey(String dataSource) {    MultiDataSourceHolder.setDataSource(dataSource);    }    @Override    protected Object determineCurrentLookupKey() {        return MultiDataSourceHolder.getDataSource();    }}public class MultiDataSourceHolder {/** * 注意：数据源标识保存在线程变量中，避免多线程操作数据源时互相干扰 */private static final ThreadLocal<String> dataSourceHolder = new ThreadLocal<String>();public static String getDataSource() {return dataSourceHolder.get();}public static void setDataSource(String dataSource) {dataSourceHolder.set(dataSource);}public static void clearDataSource() {dataSourceHolder.remove();}}@Aspectpublic class MultiDataSourceAspectAdvice {private static final String JDBUY_DATA_SOURCE = "jdbuyDataSource";private static final String LIFECYCLE_DATA_SOURCE = "lifecycleDataSource";// @Pointcut("execution(* com.jd.zc.lifecycle.dao.*.*(..))")// 切入点路径配置很关键,dao包下的所有子包、所有类、所有方法，".."所有参数@Pointcut("execution(* com.jd.zc.lifecycle.dao.*.*.*(..))")protected void pointcut() {}// @Around("execution(* com.jd.zc.lifecycle.*.*(..))")@Around(value = "pointcut()")public Object doAround(ProceedingJoinPoint jp) throws Throwable {Object target = jp.getTarget();if (target instanceof LifecycleBaseMapper) {MultiDataSource.setDataSourceKey(LIFECYCLE_DATA_SOURCE);} else if (target instanceof JdbuyBaseMapper) {MultiDataSource.setDataSourceKey(JDBUY_DATA_SOURCE);}return jp.proceed();}}

核心点就是，配置Mapper文件执行时的AOP，根据父类Mapper是LifecycleBaseMapper，还是JdbuyBaseMapper，动态切换数据源。

5、事务的定义

spring-info-datasource.xml已经定义了2个事务，实际使用的是时候，根据配置的标记来使用。

<qualifier value="lifecycle"/>

比如：

//需要指定使用的事务标记，2个数据源

@Transactional("lifecycle")

// 可以执行任务，就返回config配置

public boolean startTask(String key,Long timeout) {

logger.info(String.format("Try to get taskLock,key=%s,start",key));

// 简单锁1行，防止多个任务同时执行

TaskConfig config = configMapper.getForUpdate(key);

if (config == null) {

logger.error("The task config is null,key=" + key);

return false;

}

}

特别需要说的是，就算是配置了事务，它只是让属于1个数据库的数据源的CRUD在一个事务中，

多个事务没有归属到一个总体的事务中。

至于分布式事务，多数据源事务，还需要再研究。