【JAVA秒会技术之随意切换数据库】Spring如何高效的配置多套数据源

 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=falsejdbc.driverClassName:com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc.username:用户名jdbc.password:密码jdbc.filters:statjdbc.maxActive:10jdbc.initialSize:2jdbc.maxWait:60000jdbc.minIdle:2jdbc.timeBetweenEvictionRunsMillis:60000jdbc.minEvictableIdleTimeMillis:300000jdbc.validationQuery:SELECT 'x' FROM DUALjdbc.testWhileIdle:truejdbc.testOnBorrow:falsejdbc.testOnReturn:falsejdbc.maxOpenPreparedStatements:20jdbc.removeAbandoned:truejdbc.removeAbandonedTimeout:180jdbc.logAbandoned:true #---------------------------JDBC-2----------------------------jdbc2.url:jdbc:mysql://ip地址1:端口/数据库?autoReconnect=truejdbc2.driverClassName:com.mysql.jdbc.Driverjdbc2.username:用户名jdbc2.password:密码jdbc2.filters:statjdbc2.maxActive:10jdbc2.initialSize:2jdbc2.maxWait:60000jdbc2.minIdle:2jdbc2.timeBetweenEvictionRunsMillis:60000jdbc2.minEvictableIdleTimeMillis:300000jdbc2.validationQuery:SELECT 'x' FROM DUALjdbc2.testWhileIdle:truejdbc2.testOnBorrow:falsejdbc2.testOnReturn:falsejdbc2.maxOpenPreparedStatements:20jdbc2.removeAbandoned:truejdbc2.removeAbandonedTimeout:180jdbc2.logAbandoned:true

（二）配置通知与切面

1.配置通知

package com.netease.numen.core.annotation;import java.lang.annotation.*;/** * @author liyan */@Target({ ElementType.PARAMETER, ElementType.METHOD })@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @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 */@Aspectpublic 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);}@Overrideprotected Object determineCurrentLookupKey() {// TODO Auto-generated method stubreturn 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");}}