來源：Student_Li 發(fā)布時間：2018-11-14 11:19:52 閱讀量：939
Spring如何高效的配置多套數(shù)據(jù)源
真正的開發(fā)中，難免要使用多個數(shù)據(jù)庫，進行不同的切換。無論是為了實現(xiàn)“讀寫分離”也好，還是為了使用不同的數(shù)據(jù)庫（“MySQL”或“Oracle”或“SQLServer”）。傳統(tǒng)的方法，是配置多套Spring配置文件與Mysql配置文件,不僅配置起來較為混亂，而且切換及對事物的管理，也很麻煩。下面，博主就介紹一種方法，幫助大家解決“Spring如何高效的配置多套數(shù)據(jù)源”！
（一）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配置內(nèi)容 -->
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
<property name="properties" ref="configProperties" />
</bean>
<!--=================== 多數(shù)據(jù)配置開始 =======================-->
<!-- 數(shù)據(jù)源1-- druid數(shù)據(jù)庫連接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<!-- 數(shù)據(jù)庫基本信息配置 -->
<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}" />
<!-- 最大并發(fā)連接數(shù) -->
<property name="maxActive" value="${jdbc.maxActive}" />
<!-- 初始化連接數(shù)量 -->
<property name="initialSize" value="${jdbc.initialSize}" />
<!-- 配置獲取連接等待超時的時間 -->
<property name="maxWait" value="${jdbc.maxWait}" />
<!-- 最小空閑連接數(shù) -->
<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>
<!-- 數(shù)據(jù)源2-- druid數(shù)據(jù)庫連接池 -->
<bean id="dataSource2" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<!-- 數(shù)據(jù)庫基本信息配置 -->
<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}" />
<!-- 最大并發(fā)連接數(shù) -->
<property name="maxActive" value="${jdbc2.maxActive}" />
<!-- 初始化連接數(shù)量 -->
<property name="initialSize" value="${jdbc2.initialSize}" />
<!-- 配置獲取連接等待超時的時間 -->
<property name="maxWait" value="${jdbc2.maxWait}" />
<!-- 最小空閑連接數(shù) -->
<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多數(shù)據(jù)源-配置 -->
<bean id="multipleDataSource" class="com.dshl.commons.utlis.MultipleDataSource">
<property name="targetDataSources">
<map>
<!-- 配置目標數(shù)據(jù)源 -->
<entry value-ref="dataSource" key="dataSource" />
<entry value-ref="dataSource2" key="dataSource2" />
</map>
</property>
<!-- 配置默認使用的據(jù)源 -->
<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>
<!-- 多數(shù)據(jù)源-配置-結(jié)束 -->
<!-- 配置 aspectj -->
<aop:aspectj-autoproxy />
</beans>
2.配置jdbc.properties
#------------------------JDBC-------------------------------
jdbc.url:jdbc:sqlserver://ip地址1:端口;database=數(shù)據(jù)庫;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:端口/數(shù)據(jù)庫?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;
/**
* 數(shù)據(jù)庫配置切面
* @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);
}
}
/**
* 獲取數(shù)據(jù)源描述
* @param joinPoint 切點
* @return DB-Key(數(shù)據(jù)庫)
* @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;
}
/**
* 獲取數(shù)據(jù)源
* @param joinPoint 切點
* @return DB-Key(數(shù)據(jù)庫)
* @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;
}
}
（三）編寫切換數(shù)據(jù)源工具類
package com.netease.numen.core.util;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 多數(shù)據(jù)源配置
*
* 說明：定義動態(tài)數(shù)據(jù)源，實現(xiàn)通過集成Spring提供的AbstractRoutingDataSource，只需要
* 實現(xiàn)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中，要切換數(shù)據(jù)源前，調(diào)用工具類：
public String isExist(String jobNumber) throws DataAccessException {
try {
//切換數(shù)據(jù)源，對中間庫操作
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{
//切回數(shù)據(jù)源
MultipleDataSource.setDataSourceKey("dataSource");
}
}
13450931319
微信登錄
QQ登錄
微博登錄
售前咨詢
