Spring如何高效的配置多套数据源
真正的开发中，难免要使用多个数据库，进行不同的切换。无论是为了实现“读写分离”也好，还是为了使用不同的数据库（“MySQL”或“Oracle”或“SQLServer”）。传统的方法，是配置多套Spring配置文件与Mysql配置文件,不仅配置起来较为混乱，而且切换及对事物的管理，也很麻烦。下面，博主就介绍一种方法，帮助大家解决“Spring如何高效的配置多套数据源”！
（一）Spring核心配置文件
1.Spring-conf配置文件
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 使用spring注解 -->
<context:annotation-config />
<!-- 扫描注解 -->
<context:component-scan base-package="com.***.****">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
</context:component-scan>
<!-- 配置文件读取 -->
<bean id="configProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="locations">
<list>
<value>classpath:*.properties</value>
</list>
</property>
<property name="fileEncoding" value="UTF-8" />
</bean>
<!-- 通过@Value注解读取.properties配置内容 -->
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
<property name="properties" ref="configProperties" />
</bean>
<!--=================== 多数据配置开始 =======================-->
<!-- 数据源1-- druid数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<!-- 数据库基本信息配置 -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="filters" value="${jdbc.filters}" />
<!-- 最大并发连接数 -->
<property name="maxActive" value="${jdbc.maxActive}" />
<!-- 初始化连接数量 -->
<property name="initialSize" value="${jdbc.initialSize}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${jdbc.maxWait}" />
<!-- 最小空闲连接数 -->
<property name="minIdle" value="${jdbc.minIdle}" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${jdbc.validationQuery}" />
<property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
<property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
<property name="testOnReturn" value="${jdbc.testOnReturn}" />
<property name="maxOpenPreparedStatements" value="${jdbc.maxOpenPreparedStatements}" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
<!-- 1800秒，也就是30分钟 -->
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="${jdbc.logAbandoned}" />
</bean>
<!-- 数据源2-- druid数据库连接池 -->
<bean id="dataSource2" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<!-- 数据库基本信息配置 -->
<property name="url" value="${jdbc2.url}" />
<property name="username" value="${jdbc2.username}" />
<property name="password" value="${jdbc2.password}" />
<property name="driverClassName" value="${jdbc2.driverClassName}" />
<property name="filters" value="${jdbc2.filters}" />
<!-- 最大并发连接数 -->
<property name="maxActive" value="${jdbc2.maxActive}" />
<!-- 初始化连接数量 -->
<property name="initialSize" value="${jdbc2.initialSize}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${jdbc2.maxWait}" />
<!-- 最小空闲连接数 -->
<property name="minIdle" value="${jdbc2.minIdle}" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${jdbc2.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${jdbc2.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${jdbc2.validationQuery}" />
<property name="testWhileIdle" value="${jdbc2.testWhileIdle}" />
<property name="testOnBorrow" value="${jdbc2.testOnBorrow}" />
<property name="testOnReturn" value="${jdbc2.testOnReturn}" />
<property name="maxOpenPreparedStatements" value="${jdbc2.maxOpenPreparedStatements}" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="${jdbc2.removeAbandoned}" />
<!-- 1800秒，也就是30分钟 -->
<property name="removeAbandonedTimeout" value="${jdbc2.removeAbandonedTimeout}" />
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="${jdbc2.logAbandoned}" />
</bean>
<!-- Spring多数据源-配置 -->
<bean id="multipleDataSource" class="com.dshl.commons.utlis.MultipleDataSource">
<property name="targetDataSources">
<map>
<!-- 配置目标数据源 -->
<entry value-ref="dataSource" key="dataSource" />
<entry value-ref="dataSource2" key="dataSource2" />
</map>
</property>
<!-- 配置默认使用的据源 -->
<property name="defaultTargetDataSource" ref="dataSource" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--注意：SqlSessionFactory的ref一定要指向multipleDataSource -->
<property name="dataSource" ref="multipleDataSource" />
<property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" />
<!-- mapper扫描 -->
<property name="mapperLocations">
<list>
<value>classpath:/mybatis/mapper/*.xml</value>
</list>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.*****.dao" />
<property name="annotationClass" value="org.springframework.stereotype.Repository" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 多数据源-配置-结束 -->
<!-- 配置 aspectj -->
<aop:aspectj-autoproxy />
</beans>
2.配置jdbc.properties
#------------------------JDBC------------------------------- jdbc.url:jdbc:sqlserver://ip地址1:端口;database=数据库;integratedSecurity=false jdbc.driverClassName:com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc.username:用户名 jdbc.password:密码 jdbc.filters:stat jdbc.maxActive:10 jdbc.initialSize:2 jdbc.maxWait:60000 jdbc.minIdle:2 jdbc.timeBetweenEvictionRunsMillis:60000 jdbc.minEvictableIdleTimeMillis:300000 jdbc.validationQuery:SELECT 'x' FROM DUAL jdbc.testWhileIdle:true jdbc.testOnBorrow:false jdbc.testOnReturn:false jdbc.maxOpenPreparedStatements:20 jdbc.removeAbandoned:true jdbc.removeAbandonedTimeout:180 jdbc.logAbandoned:true #---------------------------JDBC-2---------------------------- jdbc2.url:jdbc:mysql://ip地址1:端口/数据库?autoReconnect=true jdbc2.driverClassName:com.mysql.jdbc.Driver jdbc2.username:用户名 jdbc2.password:密码 jdbc2.filters:stat jdbc2.maxActive:10 jdbc2.initialSize:2 jdbc2.maxWait:60000 jdbc2.minIdle:2 jdbc2.timeBetweenEvictionRunsMillis:60000 jdbc2.minEvictableIdleTimeMillis:300000 jdbc2.validationQuery:SELECT 'x' FROM DUAL jdbc2.testWhileIdle:true jdbc2.testOnBorrow:false jdbc2.testOnReturn:false jdbc2.maxOpenPreparedStatements:20 jdbc2.removeAbandoned:true jdbc2.removeAbandonedTimeout:180 jdbc2.logAbandoned:true
（二）配置通知与切面
1.配置通知
package com.netease.numen.core.annotation;
import java.lang.annotation.*;
/**
* @author liyan
*/
@Target({ ElementType.PARAMETER, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DatabaseConfiguration {
/**
* annotation description
* @return {@link java.lang.String}
*/
String description() default "";
/**
* annotation value ,default value "dataSource"
* @return {@link java.lang.String}
*/
String value() default "";
}
2.配置切面
package com.netease.numen.core.aop;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.netease.numen.core.annotation.DatabaseConfiguration;
import com.netease.numen.core.util.MultipleDataSource;
/**
* 数据库配置切面
* @author liyan
*/
@Aspect
public class DatabaseConfigurationAspect {
/**
* default dataSource
*/
public static final String DEFAULT_DATASOURCE = "dataSource";
/**
* 日志
*/
private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseConfigurationAspect.class);
@Pointcut("@annotation(com.netease.numen.core.annotation.DatabaseConfiguration)")
public void DBAspect() {
}
/**
* 前置通知
* @param joinPoint 切点
*/
@Before("DBAspect()")
public void doBefore(JoinPoint joinPoint) {
try {
MultipleDataSource.setDataSourceKey(getTargetDataSource(joinPoint));
LOGGER.info("Methods Described:{}", getDescription(joinPoint));
LOGGER.info("Replace DataSource:{}", getTargetDataSource(joinPoint));
} catch (Exception e) {
LOGGER.warn("DataSource Switch Exception:{}", e);
MultipleDataSource.setDataSourceKey(DEFAULT_DATASOURCE);
}
}
/**
* 异常通知
* @param joinPoint 切点
* @param e 异常
*/
@AfterThrowing(pointcut = "DBAspect()", throwing = "e")
public void doAfterThrowing(JoinPoint joinPoint, Throwable e) {
try {
MultipleDataSource.setDataSourceKey(DEFAULT_DATASOURCE);
} catch (Exception ex) {
LOGGER.warn("DataSource Switch Exception:{}", e);
}
}
/**
* 方法后通知
* @param joinPoint 切点
*/
@After("DBAspect()")
public void doAfter(JoinPoint joinPoint) {
try {
MultipleDataSource.setDataSourceKey(DEFAULT_DATASOURCE);
LOGGER.info("Restore Default DataSource:{}", DEFAULT_DATASOURCE);
} catch (Exception e) {
LOGGER.warn("Restore Default DataSource Exception:{}", e);
}
}
/**
* 获取数据源描述
* @param joinPoint 切点
* @return DB-Key(数据库)
* @throws Exception
*/
@SuppressWarnings("rawtypes")
public static String getDescription(JoinPoint joinPoint) throws Exception {
String targetName = joinPoint.getTarget().getClass().getName();
String methodName = joinPoint.getSignature().getName();
Object[] arguments = joinPoint.getArgs();
Class targetClass = Class.forName(targetName);
Method[] methods = targetClass.getMethods();
String description = "";
for (Method method : methods) {
if (method.getName().equals(methodName)) {
Class[] clazzs = method.getParameterTypes();
if (clazzs.length == arguments.length) {
description = method.getAnnotation(DatabaseConfiguration.class).description();
if (description == null || "".equals(description))
description = "Database switch";
break;
}
}
}
return description;
}
/**
* 获取数据源
* @param joinPoint 切点
* @return DB-Key(数据库)
* @throws Exception
*/
@SuppressWarnings("rawtypes")
public static String getTargetDataSource(JoinPoint joinPoint) throws Exception {
String targetName = joinPoint.getTarget().getClass().getName();
String methodName = joinPoint.getSignature().getName();
Object[] arguments = joinPoint.getArgs();
Class targetClass = Class.forName(targetName);
Method[] methods = targetClass.getMethods();
String value = "";
for (Method method : methods) {
if (method.getName().equals(methodName)) {
Class[] clazzs = method.getParameterTypes();
if (clazzs.length == arguments.length) {
value = method.getAnnotation(DatabaseConfiguration.class).value();
if (value == null || "".equals(value))
value = DEFAULT_DATASOURCE;
break;
}
}
}
return value;
}
}
（三）编写切换数据源工具类
package com.netease.numen.core.util;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 多数据源配置
*
* 说明：定义动态数据源，实现通过集成Spring提供的AbstractRoutingDataSource，只需要
* 实现determineCurrentLookupKey方法即可
* 由于DynamicDataSource是单例的，线程不安全的，所以采用ThreadLocal保证线程安全，由
* DynamicDataSourceHolder完成。
*
* @author Liyan
*/
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() {
// TODO Auto-generated method stub
return dataSourceKey.get();
}
}
（四）如何使用
这就很简单了，只要在serviceImpl中，要切换数据源前，调用工具类：
public String isExist(String jobNumber) throws DataAccessException {
try {
//切换数据源，对中间库操作
MultipleDataSource.setDataSourceKey("dataSource4");
Map<String, Object> param = new HashMap<String, Object>(0);
param.put("jobNumber", jobNumber);
return mapper.isExist(param);
} catch (DataAccessException e) {
throw e;
} finally{
//切回数据源
MultipleDataSource.setDataSourceKey("dataSource");
}
}
