万字详谈SpringBoot多数据源以及事务处理

曾经终败给现在 2024-03-23 13:30 60阅读 0赞

## 背景 ##

在高并发的项目中，单数据库已无法承载大数据量的访问，因此需要使用多个数据库进行对数据的读写分离，此外就是在微服化的今天，我们在项目中可能采用各种不同存储，因此也需要连接不同的数据库，居于这样的背景，这里简单分享实现的思路以及实现方案。

## 如何实现 ##

多数据源实现思路有两种，一种是通过配置多个SqlSessionFactory实现多数据源;

![32f7ba15d6fc8c04f91facb8df368748.png][]

另外一种是通过Spring提供的AbstractRoutingDataSource抽象了一个DynamicDataSource实现动态切换数据源；

![3ee3711dfce09313b2a653d11b0d168b.png][]

## 实现方案 ##

## 准备 ##

采用Spring Boot2.7.8框架，数据库Mysql，ORM框架采用Mybatis，整个Maven依赖如下:

<properties>
            <maven.compiler.source>8</maven.compiler.source>
            <maven.compiler.target>8</maven.compiler.target>
            <spring-boot.version>2.7.8</spring-boot.version>
            <mysql-connector-java.version>5.1.46</mysql-connector-java.version>
            <mybatis-spring-boot-starter.version>2.0.0</mybatis-spring-boot-starter.version>
            <mybatis.version>3.5.1</mybatis.version>
        </properties>
    
        <dependencyManagement>
            <dependencies>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-dependencies</artifactId>
                    <version>${spring-boot.version}</version>
                    <type>pom</type>
                    <scope>import</scope>
                </dependency>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>${mysql-connector-java.version}</version>
                </dependency>
                <dependency>
                    <groupId>org.mybatis</groupId>
                    <artifactId>mybatis</artifactId>
                    <version>${mybatis.version}</version>
                </dependency>
                <dependency>
                    <groupId>org.mybatis.spring.boot</groupId>
                    <artifactId>mybatis-spring-boot-starter</artifactId>
                    <version>${mybatis-spring-boot-starter.version}</version>
                </dependency>
            </dependencies>
        </dependencyManagement>

## 指定数据源操作指定目录XML文件 ##

该种方式需要操作的数据库的Mapper层和Dao层分别建立一个文件夹，分包放置，整体项目结构如下图:

![74180bdb8de97e5fb721abc132192399.png][]

## Maven依赖如下: ##

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
            <dependency>
                <groupId>com.zaxxer</groupId>
                <artifactId>HikariCP</artifactId>
                <version>4.0.3</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>

## Yaml文件 ##

spring:
      datasource:
        user:
          jdbc-url: jdbc:mysql://127.0.0.1:3306/study_user?useSSL=false&useUnicode=true&characterEncoding=UTF-8
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          #hikari连接池配置
          hikari:
            #pool name
            pool-name: user
            #最小空闲连接数
            minimum-idle: 5
            #最大连接池
            maximum-pool-size: 20
            #链接超时时间  3秒
            connection-timeout: 3000
            # 连接测试query
            connection-test-query: SELECT 1
        soul:
          jdbc-url: jdbc:mysql://127.0.0.1:3306/soul?useSSL=false&useUnicode=true&characterEncoding=UTF-8
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          #hikari连接池配置
          hikari:
            #pool name
            pool-name: soul
            #最小空闲连接数
            minimum-idle: 5
            #最大连接池
            maximum-pool-size: 20
            #链接超时时间  3秒
            connection-timeout: 3000
            # 连接测试query
            connection-test-query: SELECT 1

## 不同库的Mapper指定不同的SqlSessionFactory ##

针对不同的库分别放置对用不同的SqlSessionFactory

@Configuration
    @MapperScan(basePackages = "org.datasource.demo1.usermapper",
            sqlSessionFactoryRef = "userSqlSessionFactory")
    public class UserDataSourceConfiguration {
    
        public static final String MAPPER_LOCATION = "classpath:usermapper/*.xml";
    
        @Primary
        @Bean("userDataSource")

[32f7ba15d6fc8c04f91facb8df368748.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/0034d18f8cbd40c0bb41a70dcabad10e.png
[3ee3711dfce09313b2a653d11b0d168b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/cb90b05c694f47a3a995e0a0f3331774.png
[74180bdb8de97e5fb721abc132192399.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/077b813f8ca843ec9af0223f8883356a.png