SpringBoot多数据源动态切换，不影响业务逻辑正常运行，服务高可用

本文使用Spring Boot 2.4.10版本和MyBatis实现多数据源动态切换，当主库MySQL宕机后自动切换到容灾PostgreSQL数据库。

数据库及数据表示例如下：

MySQL数据库（主库）：

创建goodsdb数据库，创建book_t数据表并初始化数据。

DROP TABLE IF EXISTS book_t;

CREATE TABLE book_t (
	book_id INT PRIMARY KEY AUTO_INCREMENT,
	book_name VARCHAR (64) NOT NULL,
	book_price DECIMAL (5, 2) NOT NULL 
);

INSERT INTO book_t VALUES (1,'MySQL权威指南',99.99);
INSERT INTO book_t VALUES (2,'MySQL从入门到精通',100.01);

PostgreSQL数据库（容灾）：

创建goodsdb数据库，创建book_t数据表并初始化数据。

DROP TABLE IF EXISTS book_t;

CREATE TABLE book_t (
	book_id SERIAL PRIMARY KEY,
	book_name VARCHAR (64) NOT NULL,
	book_price DECIMAL (5, 2) NOT NULL 
);

INSERT INTO book_t VALUES (1,'PostgreSQL巅峰之作',88.88);
INSERT INTO book_t VALUES (2,'PostgreSQL实战教程',66.66);

准备SpringBoot项目

官方创建项目地址：https://start.spring.io/，下载。

对应pom.xml文件引入依赖如下：

	<dependencies>
		<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.1.4</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </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>

application.properties 配置数据源

server.port=8080
server.servlet.context-path=/book

mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

#配置MySQL数据源
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/goodsdb?allowMultiQueries=true&serverTimezone=GMT&characterEncoding=utf8
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.primary.username=root
spring.datasource.primary.password=123456

#配置PostgreSQL数据源
spring.datasource.standby.jdbc-url=jdbc:postgresql://localhost:5432/goodsdb?sslmode=disable
spring.datasource.standby.driver-class-name=org.postgresql.Driver
spring.datasource.standby.username=postgres
spring.datasource.standby.password=123456

实体BookVO.java：

package com.lwy.it.book.vo;

import java.io.Serializable;

import lombok.Data;

@Data
public class BookVO implements Serializable {
    private static final long serialVersionUID = 5709412010383043539L;
    private Integer bookId;
    private String bookName;
    private Double bookPrice;
}

数据库访问层Dao：

package com.lwy.it.book.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import com.lwy.it.book.vo.BookVO;

@Mapper
@Repository
public interface BookDao {
    BookVO findBookById(@Param("bookId") Integer bookId);

    List<BookVO> findAllBook();
}

对应Mapper 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.lwy.it.book.dao.BookDao">
	<select id="findBookById" resultType="com.lwy.it.book.vo.BookVO" parameterType="java.lang.Integer">
		SELECT
		    book_id AS bookId,
		    book_name AS bookName,
		    book_price AS bookPrice 
		FROM
		    book_t 
		WHERE
		    book_id = #{bookId}
	</select>
	<select id="findAllBook" resultType="com.lwy.it.book.vo.BookVO">
        SELECT
            book_id AS bookId,
            book_name AS bookName,
            book_price AS bookPrice 
        FROM
            book_t
	</select>
</mapper>

动态切换数据源依赖 com.lwy.it.configuration 包下的四个类来实现，分别是：

1. DataSourceConfiguration

package com.lwy.it.configuration;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.mybatis.spring.SqlSessionFactoryBean;
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 org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class DataSourceConfiguration {

    /**
     * @Primary 注解用于标识默认使用的DataSource Bean，因为有两个DataSource Bean，该注解可用于mysqlDataSource或pgsqlDataSource,
     *          但不能用于dynamicDataSource dynamicDataSource Bean, 否则会产生循环调用
     * @ConfigurationProperties 注解用于从 application.properties 文件中读取配置，为 Bean 设置属性
     * @return DataSource MySQL
     */
    @Primary
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "pgsqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.standby")
    public DataSource pgsqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 
     * @param mysqlDataSource MySQL数据源
     * @param pgsqlDataSource PostgreSQL数据源
     * 
     * @return 返回DynamicDataSource类型，否则@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource报错
     */
    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dataSource(@Qualifier("mysqlDataSource") DataSource mysqlDataSource,
        @Qualifier("pgsqlDataSource") DataSource pgsqlDataSource) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("mysqlDataSource", mysqlDataSource);
        targetDataSources.put("pgsqlDataSource", pgsqlDataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        // 将MySQL数据源作为默认指定的数据源
        dynamicDataSource.setDefaultTargetDataSource(mysqlDataSource);
        return dynamicDataSource;
    }

    /**
     * 配置mybatis的mapper的位置
     * 
     * @param dynamicDataSource
     * @return SqlSessionFactoryBean
     * @throws IOException
     */
    @Bean("sqlSessionFactoryBean")
    public SqlSessionFactoryBean
        sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        
        // 配置数据源，此处配置为关键配置，如果没有将dynamicDataSource作为数据源则不能实现切换
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        // 配置 MyBatis
        sqlSessionFactoryBean
            .setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean;
    }

    /**
     * 注入DataSourceTransactionManager用于事务管理
     */
    @Bean
    public PlatformTransactionManager
        transactionManager(@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}

2. DynamicDataSource

package com.lwy.it.configuration;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        String dateSoureType = DynamicDataSourceContextHolder.getDateSoureType();
        log.info("Current DataSource is {}!", dateSoureType);
        return dateSoureType;
    }
}

3. DynamicDataSourceContextHolder

package com.lwy.it.configuration;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DynamicDataSourceContextHolder {

    // 为每个线程维护变量，以避免影响其他线程
    private static final ThreadLocal<String> DATASOURCE_CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 设置数据源
     */
    public static void setDateSoureType(String dateSoureType) {
        log.info("设置数据源 = {}", dateSoureType);
        DATASOURCE_CONTEXT_HOLDER.set(dateSoureType);
    }

    /**
     * 获得数据源
     */
    public static String getDateSoureType() {
        String dateSoureType = DATASOURCE_CONTEXT_HOLDER.get();
        log.info("获得数据源 = {}", dateSoureType);
        return dateSoureType;
    }

    /**
     * 清空数据源
     */
    public static void clearDateSoureType() {
        DATASOURCE_CONTEXT_HOLDER.remove();
    }
}

4.DynamicDataSourceAspect

动态数据源切换的切面，当连接MySQL数据库异常时，自动切换到PostgreSQL数据库。

需要注意的是，使用 DAO 层切面后不应该在 Service 类层面上加 @Transactional 注解，而应该添加在方法上，这也是 Spring 推荐的做法

package com.lwy.it.configuration;

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.mybatis.spring.MyBatisSystemException;
import org.springframework.stereotype.Component;

import lombok.extern.slf4j.Slf4j;

@Component
@Aspect
@Slf4j
public class DynamicDataSourceAspect {

    // 表达式：https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#aop-pointcuts-combining
    @Pointcut("execution(public * com.lwy.it..*Dao.*(..))")
    public void pointCut() {}

    @Around("pointCut()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        // 访问到目标方法的调用参数
        Object[] args = pjp.getArgs();
        // ------------------以上在目标方法之前织入------------------
        // 回到原来的目标方法，如果去掉此行，目标方法就不会执行
        Object returnObject = null;
        try {
            returnObject = pjp.proceed(args);
        } catch (Throwable exception) {
            log.error("执行异常信息为：{}", exception.getMessage(), exception);
            if (exception instanceof MyBatisSystemException) {
                // 数据源切换
                DynamicDataSourceContextHolder.setDateSoureType("pgsqlDataSource");
                // 切换后再次调用
                returnObject = pjp.proceed(args);
            }
        }
        // ------------------以下在目标方法之前织入------------------
        return returnObject;
    }
}

编写单元测试进行验证：

package com.lwy.it.book.dao;

import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import com.lwy.it.book.vo.BookVO;

@SpringBootTest
public class BookDaoTest {

    @Autowired
    private BookDao bookDao;

    @Test
    public void test() {
        List<BookVO> list = bookDao.findAllBook();
        System.out.println(list);
    }
}

当MySQL数据库启动时结果为：

[BookVO(bookId=1, bookName=MySQL权威指南, bookPrice=99.99), BookVO(bookId=2, bookName=MySQL从入门到精通, bookPrice=100.01)]

当MySQL数据库停止后结果为：

[BookVO(bookId=1, bookName=PostgreSQL巅峰之作, bookPrice=88.88), BookVO(bookId=2, bookName=PostgreSQL实战教程, bookPrice=66.66)]

参考文档：https://github.com/helloworlde/SpringBoot-DynamicDataSource