SpringBoot实现数据库读写分离

SpringBoot实现数据库读写分离

参考博客https://blog.csdn.net/qq_31708899/article/details/121577253
实现原理：翻看AbstractRoutingDataSource源码我们可以看到其中的targetDataSource可以维护一组目标数据源(采用map数据结构)，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。看到了这个，我们就可以想到怎么实现数据源切换了

#### 一 maven依赖

<?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.4.12-SNAPSHOT</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.ReadAndWriteSeparate</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>ReadAndWriteSeparate</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.26</version><scope>runtime</scope></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><dependency><groupId>org.aspectj</groupId><artifactId>aspectjweaver</artifactId><version>1.9.5</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins><resources><resource><directory>src/main/java</directory><includes><include>**/*.xml</include></includes></resource></resources></build></project>

二 数据源配置

1. yaml配置
   这里我只用一个账号模拟，生产环境下必须要分开只读账号和可读可写账号，因为主从复制中，主机可不会同步从机的数据哟

spring:datasource:master:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverslave1:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverslave2:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driver

2. 数据源配置

package com.readandwriteseparate.demo.Config;import com.readandwriteseparate.demo.Enum.DbEnum;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;
import java.beans.ConstructorProperties;
import java.util.HashMap;
import java.util.Map;/*** @author OriginalPerson* @date 2021/11/25 20:25* @Email [e-mail]*/
@Configuration
public class DataSourceConfig {//主数据源，用于写数据,特殊情况下也可用于读@Bean@ConfigurationProperties("spring.datasource.master")public DataSource masterDataSource(){return DataSourceBuilder.create().build();}@Bean@ConfigurationProperties("spring.datasource.slave1")public DataSource slave1DataSource(){return DataSourceBuilder.create().build();}@Bean@ConfigurationProperties("spring.datasource.slave2")public DataSource slave2DataSource(){return DataSourceBuilder.create().build();}@Beanpublic DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,@Qualifier("slave1DataSource") DataSource slave1DataSource,@Qualifier("slave2DataSource") DataSource slave2DataSource){Map<Object,Object> targetDataSource=new HashMap<>();targetDataSource.put(DbEnum.MASTER,masterDataSource);targetDataSource.put(DbEnum.SLAVE1,slave1DataSource);targetDataSource.put(DbEnum.SLAVE2,slave2DataSource);RoutingDataSource routingDataSource=new RoutingDataSource();routingDataSource.setDefaultTargetDataSource(masterDataSource);routingDataSource.setTargetDataSources(targetDataSource);return routingDataSource;}}

这里我们配置了4个数据源，其中前三个数据源都是为了生成第四个路由数据源产生的，路由数据源的key我们使用枚举类型来标注,三个枚举类型分别代表数据库的类型。

package com.readandwriteseparate.demo.Enum;/*** @author OriginalPerson* @date 2021/11/25 20:45* @Email: [e-mail]*/
public enum DbEnum {MASTER,SLAVE1,SLAVE2;
}

三 数据源切换

这里我们使用ThreadLocal将路由key设置到每个线程的上下文中这里也进行一个简单的负载均衡，轮询两个只读数据源,而访问哪个取决于counter的值，每增加1，切换一下数据源，该值为juc并发包下的原子操作类，保证其线程安全。

1. 设置路由键，获取当前数据源的key

package com.readandwriteseparate.demo.Config;import com.readandwriteseparate.demo.Enum.DbEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.util.concurrent.atomic.AtomicInteger;/*** @author OriginalPerson* @date 2021/11/25 20:49* @Email: [e-mail]*/
public class DBContextHolder {private static final ThreadLocal<DbEnum> contextHolder=new ThreadLocal<>();private static final AtomicInteger counter=new AtomicInteger(-1);public static void set(DbEnum type){contextHolder.set(type);}public static DbEnum get(){return contextHolder.get();}public static void master(){set(DbEnum.MASTER);System.out.println("切换到master数据源");}public static void slave(){//轮询数据源进行读操作int index=counter.getAndIncrement() % 2;if(counter.get()>9999){counter.set(-1);}if(index==0){set(DbEnum.SLAVE1);System.out.println("切换到slave1数据源");}else {set(DbEnum.SLAVE2);System.out.println("切换到slave2数据源");}}
}

2. 确定当前数据源

这个比较重要，其继承AbstractRoutingDataSource类，重写了determineCurrentLookupKey方法，该方法决定当前数据源的key，对应于上文配置数据源的map集合中的key，让该方法返回我们定义的ThreadLocal中存储的key，即可实现数据源切换。

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;/*** @author OriginalPerson* @date 2021/11/25 20:47* @Email: [e-mail]*/
public class RoutingDataSource extends AbstractRoutingDataSource {@Nullable@Overrideprotected Object determineCurrentLookupKey() {return DBContextHolder.get();}
}

3. mybatis配置三个数据源

package com.readandwriteseparate.demo.Config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;import javax.annotation.Resource;
import javax.sql.DataSource;/*** @author OriginalPerson* @date 2021/11/25 22:17* @Email [e-mail]*/
@EnableTransactionManagement
@Configuration
public class MybatisConfig {@Resource(name = "routingDataSource")private DataSource routingDataSource;@Beanpublic SqlSessionFactory sessionFactory() throws Exception {SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(routingDataSource);return sqlSessionFactoryBean.getObject();}@Beanpublic PlatformTransactionManager platformTransactionManager(){return new DataSourceTransactionManager(routingDataSource);}
}

四 特殊处理master主库读数据操作、

在某些场景下，我们需要实时读取到更新过的值，例如某个业务逻辑，在插入一条数据后，需要立即查询据，因为读写分离我们用的是主从复制架构，它是异步操作，串行复制数据，所以必然存在主从延迟问题，对于刚插入的数据，如果要马上取出，读从库是没有数据的，因此需要直接读主库，这里我们通过一个Master注解来实现，被该注解标注的方法将直接在主库数据

1. 注解

package com.readandwriteseparate.demo.annotation;/*** @author OriginalPerson* @date 2021/11/26 13:28* @Email [e-mail]*/public @interface Master {
}

2. APO切面处理

package com.readandwriteseparate.demo.Aspect;import com.readandwriteseparate.demo.Config.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;/*** @author OriginalPerson* @date 2021/11/26 13:23* @Email [e-mail]*/@Aspect
@Component
public class DataSourceAop {// 非master注解且有关读的方法操作从库@Pointcut("!@annotation(com.readandwriteseparate.demo.annotation.Master)" +" && (execution(* com.readandwriteseparate.demo.Service..*.select*(..)))" +" || execution(* com.readandwriteseparate.demo.Service..*.get*(..)))")public void readPointcut(){}// 有master注解或者有关处理数据的操作主库  @Pointcut("@annotation(com.readandwriteseparate.demo.annotation.Master) " +"|| execution(* com.readandwriteseparate.demo.Service..*.insert*(..)) " +"|| execution(* com.readandwriteseparate.demo.Service..*.add*(..)) " +"|| execution(* com.readandwriteseparate.demo.Service..*.update*(..)) " +"|| execution(* com.readandwriteseparate.demo.Service..*.edit*(..)) " +"|| execution(* com.readandwriteseparate.demo.Service..*.delete*(..)) " +"|| execution(* com.readandwriteseparate.demo.Service..*.remove*(..))")public void writePointcut() {}@Before("readPointcut()")public void read(){DBContextHolder.slave();}@Before("writePointcut()")public void write(){DBContextHolder.master();}
}

五 读写分离案例使用

1. 实体类

package com.readandwriteseparate.demo.Domain;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;/*** @author OriginalPerson* @date 2021/11/26 23:15* @Email [e-mail]*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {private Integer id;private String name;private String sex;
}

2. Dao层

package com.readandwriteseparate.demo.Dao;import com.readandwriteseparate.demo.Domain.User;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** @author OriginalPerson* @date 2021/11/26 23:16* @Email [e-mail]*/
public interface UserMapper {public List<User> selectAllUser();public Integer insertUser(@Param("user") User user);public User selectOneById(@Param("id") Integer id);
}

xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.readandwriteseparate.demo.Dao.UserMapper"><resultMap id="user" type="com.readandwriteseparate.demo.Domain.User"><id property="id" column="id"></id><result property="name" column="name"></result><result property="sex" column="sex"></result></resultMap><select resultMap="user" id="selectAllUser" resultType="com.readandwriteseparate.demo.Domain.User">select * from user</select><insert id="insertUser" parameterType="com.readandwriteseparate.demo.Domain.User">insert into user(name,sex) values(#{user.name},#{user.sex})</insert><select id="selectOneById" parameterType="java.lang.Integer" resultMap="user">select * from user where id=#{id}</select>
</mapper>

service

package com.readandwriteseparate.demo.Service;import com.readandwriteseparate.demo.Dao.UserMapper;
import com.readandwriteseparate.demo.Domain.User;
import com.readandwriteseparate.demo.annotation.Master;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;/*** @author OriginalPerson* @date 2021/11/27 0:07* @Email [e-mail]*/@Service
public class UserService {@Autowiredprivate UserMapper userMapper;public List<User> getAllUser(){return userMapper.selectAllUser();}public Integer addUser(User user){return userMapper.insertUser(user);}/** 特殊情况下，需要从主库查询时* 例如某些业务更新数据后需要马上查询，因为主从复制有延迟，所以需要从主库查询* 添加@Master注解即可从主库查询** 该注解实现比较简单，在aop切入表达式中进行判断即可* */@Masterpublic User selectOneById(Integer id){return userMapper.selectOneById(id);}
}

单元测试代码

package com.readandwriteseparate.demo;import com.readandwriteseparate.demo.Dao.UserMapper;
import com.readandwriteseparate.demo.Domain.User;
import com.readandwriteseparate.demo.Service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest
class ReadAndWriteSeparateApplicationTests {@Autowiredprivate UserService userService;@Testvoid contextLoads() throws InterruptedException {User user=new User();user.setName("赵六");user.setSex("男");System.out.println("插入一条数据");userService.addUser(user);for (int i = 0; i <4 ; i++) {System.out.println("开始查询数据");System.out.println("第"+(i+1)+"次查询");userService.getAllUser();System.out.println("-------------------------分割线------------------------");}System.out.println("强制查询主库");userService.selectOneById(1);}}

查询结果：