SpringBoot+MybatisPlus 多数据源读写分离
此文章主要讲了使用 SpringAop 来进行对数据源的切换。
pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.17</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.learn.project</groupId>
<artifactId>read-write-split</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>read-write-split</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<mysql.version>5.1.45</mysql.version>
<mybatis-plus.version>3.5.3</mybatis-plus.version>
<druid.version>1.2.16</druid.version>
<pagehelper.version>1.4.1</pagehelper.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/**</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/**</include>
</includes>
</resource>
</resources>
</build>
</project>
application.yml
server:
port: 9000
spring:
application:
name: read-write-split
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
# 主数据源（写 改 删）
master:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.177.130:3306/master_db?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: 123
initialSize: 5
minIdle: 5
maxActive: 20
# 从数据源_1 （读）
slave1:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.177.129:3306/master_db?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
username: slave
password: 123
initialSize: 5
minIdle: 5
maxActive: 20
# 从数据源_2 （读）
slave2:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.177.129:3306/master_db?characterEncoding=utf8&verifyServerCertificate=false&useSSL=true&serverTimezone=Asia/Shanghai
username: slave
password: 123
initialSize: 5
minIdle: 5
maxActive: 20
mybatis-plus:
global-config:
banner: true
db-config:
logic-delete-field: deleted
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
map-underscore-to-camel-case: true
default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler
mapper-locations: classpath:mapper/*Mapper.xml
type-aliases-package: com.learn.project.domain
enums
用于对数据源的切换
package com.learn.project.enums;
/**
* @author lijiajun
* @date 2023/11/21
*/
public enum DBTypeEnum {
/**
* 主
*/
Master("master"),
/**
* 从
*/
Slave("slave");
private final String value;
DBTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
config
- DbContextHolder
本地缓存存储使用的数据源以及对从数据库的轮询选择
package com.learn.project.config;
import com.learn.project.enums.DBTypeEnum;
import lombok.extern.slf4j.Slf4j;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Slf4j
public class DbContextHolder {
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
private static final AtomicInteger COUNTER = new AtomicInteger(-1);
private static final int MAX_COUNTER = 9999;
public static void set(String dbSource) {
// 判断枚举是否为null
if (null == dbSource) {
throw new RuntimeException("指定数据源不能不空");
}
CONTEXT_HOLDER.set(dbSource);
}
public static String get() {
return CONTEXT_HOLDER.get() == null ? DBTypeEnum.Master.toString() : CONTEXT_HOLDER.get();
}
public static void master() {
log.info(">>>>> 切换到Master");
set(DBTypeEnum.Master.toString());
}
public static void slave() {
int i = COUNTER.getAndIncrement() % 2;
if (COUNTER.get() > MAX_COUNTER) {
COUNTER.set(-1);
}
// 如果 i 是单数 切 数据源1
if (i == 0) {
log.info(">>>>> 切换到Slave2");
set(DBTypeEnum.Slave.getValue() + 2);
} else {
log.info(">>>>> 切换到Slave1");
set(DBTypeEnum.Slave.getValue() + 1);
}
}
public static void clear() {
CONTEXT_HOLDER.remove();
}
}
- MybatisPlusConfig
关于对 MybatisPlusConfig 的配置，这里只配置了分页。
package com.learn.project.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
//分页查询超过最大页码 显示第一页
paginationInnerInterceptor.setOverflow(true);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
}
- MyMetaObjectHandler
对实体类某些字段的自动填充
package com.learn.project.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.time.LocalDateTime;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
log.info("start insert fill....");
this.setFieldValByName("createdAt", LocalDateTime.now(), metaObject);
this.setFieldValByName("createdBy", "lijiajun1", metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
log.info("start update fill....");
this.setFieldValByName("updatedAt", LocalDateTime.now(), metaObject);
this.setFieldValByName("updatedBy", "lijiajun2", metaObject);
}
}
- DataSourceConfig
配置多数据源
package com.learn.project.config;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.learn.project.enums.DBTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Value;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Slf4j
@Configuration
public class DataSourceConfig {
@Value("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
/**
* 写数据源
* Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。
* 多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean
*/
@Primary
@Bean
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource writeDataSource() {
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.druid.slave1")
public DataSource readDataSource1() {
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.druid.slave2")
public DataSource readDataSource2() {
return DataSourceBuilder.create().type(dataSourceType).build();
}
/**
* 设置数据源路由，通过该类中的determineCurrentLookupKey决定使用哪个数据源
*/
@Bean
public AbstractRoutingDataSource routingDataSource() {
RoutingDataSource proxy = new RoutingDataSource();
Map<Object, Object> targetDataSources = new HashMap<>(6);
targetDataSources.put(DBTypeEnum.Master.getValue(), writeDataSource());
targetDataSources.put(DBTypeEnum.Slave.getValue() + 1, readDataSource1());
targetDataSources.put(DBTypeEnum.Slave.getValue() + 2, readDataSource2());
proxy.setDefaultTargetDataSource(writeDataSource());
proxy.setTargetDataSources(targetDataSources);
return proxy;
}
/**
* 由于Spring容器中现在有多个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。
*/
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(routingDataSource());
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
return sqlSessionFactory.getObject();
}
/**
* 指定数据源事务管理器
*
* @return DataSourceTransactionManager 事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(routingDataSource());
}
}
- RoutingDataSource
选择数据源
package com.learn.project.config;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
String dbSource = DbContextHolder.get();
log.info("current datasource：{}", dbSource);
return dbSource;
}
}
DataSourceSelector
自定义注解
package com.learn.project.annotation;
import com.learn.project.enums.DBTypeEnum;
import java.lang.annotation.*;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.METHOD, ElementType.TYPE})
public @interface DataSourceSelector {
/**
* 指定的数据源
*
* @return DBTypeEnum 数据源
*/
DBTypeEnum value() default DBTypeEnum.Master;
}
DataSourceAop
package com.learn.project.aop;
import com.learn.project.annotation.DataSourceSelector;
import com.learn.project.config.DbContextHolder;
import com.learn.project.enums.DBTypeEnum;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Aspect
@Component
public class DataSourceAop {
/**
* 切面
*/
@Pointcut("@annotation(com.learn.project.annotation.DataSourceSelector)")
public void dataSourcePointcut() {
}
@Around("dataSourcePointcut()&&@annotation(dataSourceSelector)")
public Object execute(ProceedingJoinPoint pjp, DataSourceSelector dataSourceSelector) throws Throwable {
try {
if (dataSourceSelector.value().equals(DBTypeEnum.Master)) {
DbContextHolder.master();
} else if (dataSourceSelector.value().equals(DBTypeEnum.Slave)) {
DbContextHolder.slave();
} else {
throw new RuntimeException("DataSourceSelector value error");
}
return pjp.proceed();
} finally {
DbContextHolder.clear();
}
}
}
service
- 实体类
package com.learn.project.domain;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Data
@TableName("job_test_data")
public class JobData implements Serializable {
@TableId(type = IdType.AUTO)
private Long id;
private String jobName;
private String jobClassName;
private String type;
@TableField(value = "created_at", fill = FieldFill.INSERT)
private LocalDateTime createdAt;
}
- JobDataMapper
package com.learn.project.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.learn.project.annotation.DataSourceSelector;
import com.learn.project.domain.JobData;
import com.learn.project.enums.DBTypeEnum;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author lijiajun
* @date 2023/11/21
*/
@Mapper
public interface JobDataMapper extends BaseMapper<JobData> {
/**
* 获取任务数据列表
*
* @return list
*/
@DataSourceSelector(DBTypeEnum.Slave)
@Select("select * from job_test_data")
List<JobData> getJobDataList();
/**
* 添加数据
*
* @param jobData 任务数据
*/
@DataSourceSelector(DBTypeEnum.Master)
@Insert("insert into job_test_data(job_name,job_class_name,type, created_at) values(#{jobName},#{jobClassName},#{type},now())")
void saveJobData(JobData jobData);
}
使用@DataSourceSelector进行选择数据源
- ProjectController
package com.learn.project.controller;
import com.learn.project.domain.JobData;
import com.learn.project.mapper.JobDataMapper;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @author lijiajun
* @date 2023/11/21
*/
@RestController
public class ProjectController {
@Resource
private JobDataMapper jobDataMapper;
@GetMapping
public List<JobData> getJobDataList() {
return this.jobDataMapper.getJobDataList();
}
@PostMapping
public void saveJobData(@RequestBody JobData data) {
this.jobDataMapper.saveJobData(data);
}
}
扫一扫
1246
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
