一、应用场景
大部分单一架构项目连接一台数据库服务器,但随着业务的增加数据库数据量不断飙升,数据库达到性能瓶颈,大部分技术人员都会对数据库主从配置;既然读写分离那就需要连接两个不同的数据库,这时候Spring多数据源管理类AbstractRoutingDataSource就要派上用场了(排除使用数据库集群管理工具统一管理的应用场景)
源码分析:
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
private Map<Object, Object> targetDataSources;
private Object defaultTargetDataSource;
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
private Map<Object, DataSource> resolvedDataSources;
private DataSource resolvedDefaultDataSource;
...
}
通过源码可以看出该类是一个抽象类,定义了6个属性。
targetDataSources:是一个map类型该属性正是用来维护项目中多个数据源
defaultTargetDataSource:通过属性名很直观的可以理解它的作用(默认数据源)
lenientFallback:默认为true,无需改动
dataSourceLookup:查找数据源接口的名称
resolvedDataSources:如果该字段没有赋值,就是targetDataSources
resolvedDefaultDataSource:改变后的数据源
public interface DataSourceLookup {
/**
* Retrieve the DataSource identified by the given name.
* @param dataSourceName the name of the DataSource
* @return the DataSource (never {@code null})
* @throws DataSourceLookupFailureException if the lookup failed
*/
DataSource getDataSource(String dataSourceName) throws DataSourceLookupFailureException;
}
该类是一个interface并且只有一个方法getDataSource,通过方法的参数名称应该清楚传入一个字符类型的数据源名称获取DataSource。
二、多数据源实践
2.1 配置主数据源
spring:
devtools:
restart:
enabled: true
livereload:
enabled: false # we use webpack for livereload
datasource:
url: jdbc:mysql://127.0.0.1:3306/test?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8
name: 主数据源
username: root
password: 123456
druid:
connectionProperties: config.decrypt=false # database connection plain password
jpa:
database-platform: org.hibernate.dialect.MySQLDialect
database: MySQL
import app.datasource.DynamicDataSource;
import app.datasource.holder.DynamicDataSourceContextHolder;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.config.ConfigFilter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.fasterxml.jackson.datatype.hibernate5.Hibernate5Module;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContextException;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import javax.inject.Inject;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
/**
* @Author: oyc
* @Date: 2018/12/20 11:46
* @Description:设置默认的数据源
*/
@Configuration
public class DataSourceConfiguration {
private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceConfiguration.class);
@Inject
private Environment env;
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dynamicDataSource(@Qualifier("masterDataSource") DataSource masterDataSource) {
//获取动态数据库的实例(单例方式)
DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
//配置多数据源
Map<Object, Object> dataSourceMap = new HashMap<>();
//自定义数据源key值,将创建好的数据源对象,赋值到targetDataSources中,用于切换数据源时指定对应key即可切换
//填入主数据源
dataSourceMap.put(DynamicDataSourceContextHolder.DEFAULT_DATASOURCE_NAME, masterDataSource);
dynamicDataSource.setTargetDataSources(dataSourceMap);
//设置默认数据源
dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
return dynamicDataSource;
}
/**
* 配置Druid数据源。
* 通过@ConfigurationProperties对DataSource的Bean进行额外的配置。配置项见application.yml。
* <p>
*
* @param dataSourceProperties Spring-Boot自动配置数据源基本参数
* @return Druid数据源
* @see <a href="http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-external-config-3rd-party-configuration">Spring配置文档</a>
*/
@Bean(destroyMethod = "close", name = "masterDataSource")
@Qualifier("masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid")
public DataSource dataSource(DataSourceProperties dataSourceProperties) {
LOGGER.debug("Configuring Datasource");
if (dataSourceProperties.determineUrl() == null) {
LOGGER.error("Your database connection pool configuration is incorrect! The application" +
" cannot start. Please check your Spring profile, current profiles are: {}",
Arrays.toString(env.getActiveProfiles()));
throw new ApplicationContextException("Database connection pool is not configured correctly");
}
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUrl(dataSourceProperties.determineUrl());
druidDataSource.setUsername(dataSourceProperties.determineUsername());
druidDataSource.setPassword(dataSourceProperties.determinePassword());
// 配置Filter
List<Filter> filters = new ArrayList<>();
// Druid内置提供一个StatFilter,用于统计监控信息。配置参考 https://github.com/alibaba/druid/wiki/配置_StatFilter
StatFilter sf = new StatFilter();
sf.setSlowSqlMillis(1000);
sf.setLogSlowSql(true);
// 某些SQL可以合并统计。https://github.com/alibaba/druid/wiki/配置_StatFilter#4-sql合并配置
sf.setMergeSql(true);
filters.add(sf);
/* ConfigFilter的作用包括:
* 从配置文件中读取配置
* 从远程http文件中读取配置
* 为数据库密码提供加密功能
* 配置参考 https://github.com/alibaba/druid/wiki/使用ConfigFilter
*/
// 根据application.yml配置启用加密数据库密码功能
ConfigFilter cf = new ConfigFilter();
filters.add(cf);
// SQL执行日志,https://github.com/alibaba/druid/wiki/配置_LogFilter
// 日志可通过配置druid.sql的日志级别来关闭
Slf4jLogFilter logFilter = new Slf4jLogFilter();
logFilter.setStatementExecutableSqlLogEnable(true);
filters.add(logFilter);
druidDataSource.setProxyFilters(filters);
// 合并多个DruidDataSource的监控数据
druidDataSource.setUseGlobalDataSourceStat(true);
// SQL防火墙,在单元测试下禁用
try {
druidDataSource.setFilters("wall");
} catch (SQLException e) {
LOGGER.error("Setting filters `wall` fail, continue creating data source without that filters." +
"Please check the filters' name.", e);
}
return druidDataSource;
}
/**
* 通过<code>@Bean</code>注册,即可使用Spring-Boot的自动配置功能,当为SpringMVC的HttpMessageConverters注册一个
* MappingJackson2HttpMessageConverter,其中的ObjectMapper将自动注册了Hibernate5Module模块。
* <p>
* Any beans of type com.fasterxml.jackson.databind.Module will be automatically registered with
* the auto-configured Jackson2ObjectMapperBuilder and applied to any ObjectMapper instances that it creates.
* This provides a global mechanism for contributing custom modules when you add new features to your application.
* </p>
* 自动注册的详情参看See Also
* <p>
* Hibernate5Module模块主要用于解决Jackson序列化带Hibernate注解的Entity,当Entity中包含有Lazy-Loading对象时,
* 若Session已关闭不能加载,默认则序列化为null(可通过Feature配置)。
* <br>
* Hibernate5Module.Feature 枚举类型中定义了默认的模块配置。详细可参看源码。
* </p>
* 不建议使用"Open session in view"的解决方案,
* <a href="https://vladmihalcea.com/2016/05/30/the-open-session-in-view-anti-pattern/">详细说明</a>
*
* @return Hibernate5Module实例
* @see <a href="http://docs.spring.io/spring-boot/docs/current/reference/html/howto-spring-mvc.html#howto-customize-the-jackson-objectmapper">Spring文档</a>
*/
@Bean
public Hibernate5Module hibernate5Module() {
return new Hibernate5Module();
}
}
2.2 使用ThreadLocal存储当前线程使用的数据源的key
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @Author: oyc
* @Date: 2018/12/20 11:53
* @Description:多数据源管理工具类,用于保存当前线程使用的数据源名称,提供数据源的添加、查询功能、数据源动态切换等功能
*/
public class DynamicDataSourceContextHolder {
/**
* 用于日志记录的对象
*/
public static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/**
* 进程内数据存储
*/
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 默认的数据源的名称
*/
public static final String DEFAULT_DATASOURCE_NAME = "master";
/**
* 日志审计
*/
public static final String AUDIT_DATASOURCE_NAME = "audit";
/**
* ticket存储方案设置
*/
public static final String TICKET_DATASOURCE_NAME = "ticket";
/**
* 设置数据源名
*
* @param datasourceName 数据源的名字
*/
public static void setDatasourceName(String datasourceName) {
logger.info("Change DataSource:{}", datasourceName);
contextHolder.set(datasourceName);
}
/**
* 获取数据源名
*
* @return
*/
public static String getDatasourceName() {
if (StringUtils.isNotBlank(contextHolder.get())) {
return contextHolder.get();
} else {
return DEFAULT_DATASOURCE_NAME;
}
}
/**
* 清除数据源名称
*/
public static void clearDatasourceName() {
contextHolder.remove();
}
}
2.3 创建动态数据源类
继承spring框架为我提供的数据源路由抽象类AbstractRoutingDataSource
,创建我们自己的动态数据源类。
import DynamicDataSourceContextHolder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 动态数据源类继承自AbstractRoutingDataSource
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
/**
* 记录日志
*/
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);
/**
* 单例句柄
*/
private static DynamicDataSource instance;
private static byte[] lock = new byte[0];
/**
* 用于存储已实例的数据源map
*/
private static Map<Object, Object> dataSourceMap = new HashMap<Object, Object>();
/**
* 获取当前数据源
*
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
logger.info("Current DataSource is [{}]", DynamicDataSourceContextHolder.getDatasourceName());
return DynamicDataSourceContextHolder.getDatasourceName();
}
/**
* 设置数据源
*
* @param targetDataSources
*/
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
dataSourceMap.putAll(targetDataSources);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();// 必须添加该句,否则新添加数据源无法识别到
}
/**
* 获取存储已实例的数据源map
*
* @return
*/
public Map<Object, Object> getDataSourceMap() {
return dataSourceMap;
}
/**
* 单例方法
*
* @return
*/
public static synchronized DynamicDataSource getInstance() {
if (instance == null) {
synchronized (lock) {
if (instance == null) {
instance = new DynamicDataSource();
}
}
}
return instance;
}
/**
* 是否存在当前key的 DataSource
*
* @param key
* @return 存在返回 true, 不存在返回 false
*/
public static boolean isExistDataSource(String key) {
return dataSourceMap.containsKey(key);
}
}
2.4 添加从数据源
import app.datasource.DynamicDataSource;
import app.datasource.holder.DynamicDataSourceContextHolder;
import manage.domain.PropertiesConfig;
import manage.repository.PropertiesConfigRepository;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.config.ConfigFilter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
@Component
public class DynamicDataSourceService {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSourceService.class);
@Resource
private PropertiesConfigRepository<PropertiesConfig> repository;
/**
* 解析值的正则匹配
*/
private static final Pattern VALUE_REGEX = Pattern.compile("^\\$\\{.*\\}");
/**
* 添加数据源
*/
public void setDyNamicDataSource() {
LOGGER.info("添加数据源");
Map<Object, Object> dataSourceMap = DynamicDataSource.getInstance().getDataSourceMap();
//添加日志审计数据源
DataSource audit = getDruidDataSourceByKey("cas.audit.jdbc");
if (audit != null) {
dataSourceMap.put(DynamicDataSourceContextHolder.AUDIT_DATASOURCE_NAME, audit);
}
//添加ticket存储方案设置数据源
DataSource ticket = getDruidDataSourceByKey("cas.ticket.registry.jpa");
if (ticket != null) {
dataSourceMap.put(DynamicDataSourceContextHolder.TICKET_DATASOURCE_NAME, ticket);
}
DynamicDataSource.getInstance().setTargetDataSources(dataSourceMap);
}
/**
* 创建数据源对象
*
* @param key 数据库配置表中的key值
* @return
*/
private DruidDataSource getDruidDataSourceByKey(String key) {
List<PropertiesConfig> configs = repository.findByKeyLike("%" + key + "%");
if (configs.size() > 0) {
DruidDataSource dataSource = new DruidDataSource();
for (PropertiesConfig config : configs) {
if (StringUtils.equals(config.getKey(), key + ".url")) {
dataSource.setUrl(getPropValue(config.getValue()));
}
if (StringUtils.equals(config.getKey(), key + ".driverClass")) {
dataSource.setDriverClassName(getPropValue(config.getValue()));
}
if (StringUtils.equals(config.getKey(), key + ".user")) {
dataSource.setUsername(getPropValue(config.getValue()));
}
if (StringUtils.equals(config.getKey(), key + ".password")) {
dataSource.setPassword(getPropValue(config.getValue()));
}
}
// 配置Filter
List<Filter> filters = new ArrayList<>();
// Druid内置提供一个StatFilter,用于统计监控信息。配置参考 https://github.com/alibaba/druid/wiki/配置_StatFilter
StatFilter sf = new StatFilter();
sf.setSlowSqlMillis(1000);
sf.setLogSlowSql(true);
// 某些SQL可以合并统计。https://github.com/alibaba/druid/wiki/配置_StatFilter#4-sql合并配置
sf.setMergeSql(true);
filters.add(sf);
/* ConfigFilter的作用包括:
* 从配置文件中读取配置
* 从远程http文件中读取配置
* 为数据库密码提供加密功能
* 配置参考 https://github.com/alibaba/druid/wiki/使用ConfigFilter
*/
// 根据application.yml配置启用加密数据库密码功能
ConfigFilter cf = new ConfigFilter();
filters.add(cf);
// SQL执行日志,https://github.com/alibaba/druid/wiki/配置_LogFilter
// 日志可通过配置druid.sql的日志级别来关闭
Slf4jLogFilter logFilter = new Slf4jLogFilter();
logFilter.setStatementExecutableSqlLogEnable(true);
filters.add(logFilter);
dataSource.setProxyFilters(filters);
// 合并多个dataSource的监控数据
dataSource.setUseGlobalDataSourceStat(true);
// SQL防火墙,在单元测试下禁用
try {
dataSource.setFilters("wall");
} catch (SQLException e) {
LOGGER.error("Setting filters `wall` fail, continue creating data source without that filters.Please check the filters' name.", e);
}
return dataSource;
} else {
return null;
}
}
/**
* 获取配置属性值
*
* @param value
* @return
*/
private String getPropValue(String value) {
if (VALUE_REGEX.matcher(value).matches()) {//判断是否为引用数据源
List<PropertiesConfig> temp = repository.findByKey(value.replaceAll("[\\$\\{\\}]", ""));
return temp.size() > 0 ? temp.get(0).getValue() : null;
} else {
return value;
}
}
}
2.5 自定义注解指明查询方法使用的数据源
自定义注解用于标注在mybatis的dao层或service层接口的方法上,或者是hibernate的dao或service层方法上,指明使用哪个数据源.默认不带注解的方法使用master
数据源,从数据源使用@SwitchDataSource("salve")
放在方法上。
-
SwitchDataSource注解
import java.lang.annotation.*;
/**
* @Author: oyc
* @Date: 2018/12/20 15:21
* @Description:用于指明Service层接口使用哪个数据源
*/
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Target(ElementType.METHOD)
public @interface SwitchDataSource {
/**
* value 使用的数据源的名称,默认为master
* @return
*/
String value() default "master";
}
-
动态切换数据源的切面 DynamicDataSourceAspect.java
import app.datasource.DynamicDataSource;
import app.datasource.holder.DynamicDataSourceContextHolder;
import org.apache.commons.lang.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* @Author: oyc
* @Date: 2018/12/20 16:51
* @Description:动态切换数据源的切面,根据注解的内容来切换数据源
*/
@Aspect
@Component
public class DynamicDataSourceAspect {
@Pointcut("@annotation(app.datasource.aop.SwitchDataSource)")
public void executionPointcut() {
}
/**
* 在SwitchDataSource注解的方法之前执行
*
* @param joinPoint
*/
@Before("executionPointcut()")
public void beforeMybatisExecutionSql(JoinPoint joinPoint) {
//获取当前访问的class
Class<?> aClass = joinPoint.getTarget().getClass();
//获取方法的签名
MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
//获取访问的方法名字
String methodName = methodSignature.getName();
//得到当前方法的参数类型
Class[] argsClass = methodSignature.getParameterTypes();
String dataSourceName = DynamicDataSourceContextHolder.DEFAULT_DATASOURCE_NAME;
try {
Method method = aClass.getMethod(methodName, argsClass);
//判断是否存在数据源切换注解
if (method.isAnnotationPresent(SwitchDataSource.class)) {
SwitchDataSource annotation = method.getAnnotation(SwitchDataSource.class);
//赋值数据源的名称
dataSourceName = annotation.value();
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
//切换数据源
if (StringUtils.isNotBlank(dataSourceName) && DynamicDataSource.getInstance().isExistDataSource(dataSourceName)) {
DynamicDataSourceContextHolder.setDatasourceName(dataSourceName);
}
}
/**
* 在SwitchDataSource注解的方法之后执行,清除进程中数据源的名字
*/
@After("executionPointcut()")
public void afterMybatisExecutionSql() {
DynamicDataSourceContextHolder.clearDatasourceName();
}
}
2.6 使用注解自动切换
- 取消默认的数据源自动装配
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
- 添加注解
@SwitchDataSource(DynamicDataSourceContextHolder.AUDIT_DATASOURCE_NAME)
public Page<ComAuditTrail> queryPage(Map<String, Object> searchKey, Pageable pageable, String flag) {
return repository.findAll(Specification.queryAllCondition(searchKey, flag), pageable);
}
/**
* 统计所有的授权日志数量
*
* @return
* @throws ServiceException
*/
@SwitchDataSource(DynamicDataSourceContextHolder.AUDIT_DATASOURCE_NAME)
public long countAllAuth() throws ServiceException {
return repository.count();
}
更多请参考:springboot+mybatis多数据源配置,AOP注解动态切换数据源_xiaosheng_papa的博客-CSDN博客_springboot切换数据源注解