最近因为公司业务,数据量比较大,需要进行数据库查询优化,于是采用了TiDB分布式数据库,使用集群的方式减轻数据库压力。
中间引入了双数据源处理,也走了很多弯路在此进行记录,仅供参考
1、pom.xml引入相关相关配置
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>provided</scope>
</dependency>
2、在application.yml文件配置相关连接配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
druid:
master:
url: jdbc:mysql://192.168.0.102:3306/spring_test1?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
slave:
url: jdbc:mysql://192.168.0.101:4000/sioo_test2?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
#初始化大小,最小,最大
initial-size: 5
max-active: 10
min-idle: 5
#配置获取连接等待超时的时间
max-wait: 6000
#检测连接是否有效的sql
validation-query: "select '1'"
validation-query-timeout: 2000
test-on-borrow: false
test-on-return: false
test-while-idle: true
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 600000
#配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 300000
remove-abandoned: true
一、采用分包进行隔离不同的数据源
- 创建mapper.xml所对应的的mapper.class包(master、salve)
- 创建mapper.xml所在包(master、salve)
- 编写MasterDataSourcesConfig文件
package com.whl.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* File: com.sioo.config
*
* @author : lh.Wu
* @date : 2019/12/16
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Configuration
//master mapper目录
@MapperScan(basePackages = {"com.whl.dao.master"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourcesConfig {
private static final String MAPPER_LOCAL = "classpath:mapper/master/*.xml";
@ConfigurationProperties("spring.datasource.druid.master")
@Primary
@Bean(name = "masterDataSource")
public DruidDataSource druidDataSource() {
return new DruidDataSource();
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(druidDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource);
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
return sessionFactoryBean.getObject();
}
}
注:
1、basePackages : 为主数据源所在mapper.class所在路径(例:master/userMapper.class)
2、MAPPER_LOCAL :为主数据源所在mapper.xml所在路径(例:master/userMapper.xml)
- 编写SlaveDataSourcesConfig文件
package com.whl.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
/**
* File: com.whl.config
*
* @author : lh.Wu
* @date : 2019/12/16
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Configuration
@MapperScan(basePackages = {"com.whl.dao.slave"}, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourcesConfig {
private static final String MAPPER_LOCAL = "classpath:mapper/slave/*.xml";
@Bean(name = "slaveDataSource")
@ConfigurationProperties("spring.datasource.druid.slave")
public DruidDataSource druidDataSource() {
return new DruidDataSource();
}
//其他数据源的事务管理器
@Bean(name = "slaveTransactionManager")
public DataSourceTransactionManager slaveTransactionManager() {
return new DataSourceTransactionManager(druidDataSource());
}
@Bean(name = "slaveSqlSessionFactory")
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DruidDataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource);
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
return sessionFactoryBean.getObject();
}
}
注:
1、basePackages : 为从数据源所在mapper.class所在路径(例:slave/userMapper.class)
2、MAPPER_LOCAL :为从数据源所在mapper.xml所在路径(例:slave/userMapper.xml)
方式二、采用Aop使用注解形式进行数据源分离
- pom.xml添加aop依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
- 创建数据库枚举类型 DbType.java
package com.whl.api.enums;
/**
*
* @author : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
public enum DbType {
//主数据库 --- 》读写
MASTER,
//副数据库---》只读
SLAVE
}
- 创建类型注解
package com.whl.server.config.datasource;
import com.sioo.statistic.api.enums.DbType;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* @author : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
DbType value() default DbType.MASTER;
}
- 创建DataSourceContextHolder 进行数据源存储
package com.whl.server.config.datasource;
import com.sioo.statistic.api.enums.DbType;
import lombok.extern.slf4j.Slf4j;
/**
*
* @author : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Slf4j
public class DataSourceContextHolder {
public static final DbType DEFAULT_DB = DbType.MASTER;
public static final ThreadLocal<DbType> dbLocal = new ThreadLocal<> ();
public static void setDB(DbType dbType){
dbLocal.set(dbType);
}
public static DbType getDB(){
return dbLocal.get();
}
public static void clearDB(){
dbLocal.remove();
}
}
- 创建 DynamicDataSource 进行数据源调用数据源
package com.whl.config.datasource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
*
* @author : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDB().toString().toLowerCase();
}
}
- 创建DataSourceConfig 进行数据源配置,实现自动切换
package com.whl.server.config.datasource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
*
* @author : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Configuration
public class DataSourceConfig {
/**
* create master database
*
* @return
*/
@Bean("master")
@ConfigurationProperties(prefix="spring.datasource.druid.master")
public DataSource master(){
return DataSourceBuilder.create().build();
}
/**
* create slave database
*
* @return
*/
@ConfigurationProperties(prefix="spring.datasource.druid.slave")
@Bean("slave")
public DataSource slave(){
return DataSourceBuilder.create().build();
}
/**
* 动态数据源: 通过AOP在不同数据源之间动态切换
*
* @return
*/
@Primary
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(master());
// 配置多数据源
Map<Object, Object> dsMap = new HashMap<>();
dsMap.put("master", master());
dsMap.put("slave", slave());
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory masterSqlSessionFactory()throws Exception {
DataSource dataSource = dynamicDataSource();
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*/*.xml"));
return sessionFactory.getObject();
}
/**
* 配置@Transactional注解事务
*
* @return
*/
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
@Bean
public TransactionTemplate transactionTemplate(PlatformTransactionManager transactionManager) {
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
return transactionTemplate;
}
}
- 创建DynamicDataSourceAspect 对请求的接口获取对应接口注解上面的数据源类型进行自动切换
package com.whl.server.config.datasource;
import com.sioo.statistic.api.enums.DbType;
import lombok.extern.slf4j.Slf4j;
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 : lh.Wu
* @date : 2019/12/19
* Copyright 2006-2019 Sioo Co., Ltd. All rights reserved.
*/
@Aspect
@Component
@Slf4j
public class DynamicDataSourceAspect {
//定义切入点
@Pointcut("execution( * com.sioo.statistic.server.service.*.*(..))")
public void dataSourcePointCut() {
}
@Before("dataSourcePointCut()")
public void beforeSwitchDS(JoinPoint point){
// 获得当前访问的class
Class<?> className = point.getTarget().getClass();
// 获得访问的方法名
String methodName = point.getSignature().getName();
// 获取参数的类型
Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
//设置默认源
DbType dataSource = DataSourceContextHolder.DEFAULT_DB;
try {
// 得到访问的方法对象
Method method = className.getMethod(methodName, argClass);
// 判断是否存在操作数据库注解,如果没有则默认为主数据库
if (method.isAnnotationPresent(DataSource.class)) {
DataSource annotation = method.getAnnotation(DataSource.class);
// 取出注解中的数据源名
dataSource = annotation.value();
}
log.warn("start to switch database ,get database info is [{}]",dataSource.name());
} catch (Exception e) {
log.error("switch database occur error , the error message is [{}]",e);
}
// 切换数据源
DataSourceContextHolder.setDB(dataSource);
}
@After("dataSourcePointCut()")
public void afterSwitchDS(JoinPoint point){
log.warn("start to clear database [{}]",DataSourceContextHolder.getDB());
// clear thread local info
DataSourceContextHolder.clearDB();
}
}
- 在启动Application.class 上面去掉自动配置数据源选项 (否则启动会找不到数据源,踩过的坑),以及相关mapper.xml文件对应的mapper.class文件路径
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan("com.whl.mapper")
- 在业务层方法头上添加 @DataSource(DbType.SLAVE) 注解进行查询
Aop方式出现异常情况:
1、找不到url 请将application.yml文件中的连接url改为 jdbc-url
Cause: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required.
2、目前Aop方式只适用于Service、Controller等业务层中,Mapper.class文件直接添加并不适用
gitee地址:https://gitee.com/mackjie/gmall-service 中的 gmall-batch服务支持双数据源注解动态切换,如果帮助到您了,请点个赞呗