SpringBoot实现多数据源（一）【普通版切换】

柔情只为你懂 2024-03-31 12:26 42阅读 0赞

在实际开发中，经常可能遇到在一个应用中可能需要访问多个数据库的情况。以下是两种典型场景

> 业务复杂（数据量大）

数据分布在不同的数据库中，数据库拆了，应用没拆。一个公司多个子项目，各用各的数据库，设计数据共享…

![在这里插入图片描述][8ab766cfbc0f4070b23ac3bd349eb920.png_pic_center]

> 读写分离

为解决 *数据库的读性能瓶颈*（**读比写性能更高，写锁会影响读阻塞，从而影响读的性能**）

很多数据库拥有主从架构，也就是说，一台主数据库服务器，是对外提供增删改业务的生产服务；另一（多）台从数据库服务器，主要进行读的操作

可以通过中间件（ShardingSphere、mycat、mysql-proxy、TDDL…），但是有一些规模较小的公司，没有专门的中间件团队搭建读写分离基础设施，因此需要业务开发人员自行实现读写分离

![在这里插入图片描述][b1aad9e9dca347929083b000fb2f0563.png_pic_center]

这里的架构与上图类似，不同的是，在读写分离中，主库和从库的数据库是一致的（不考虑主从延迟）。数据更新操作（**insert、update、delete**）都是在主库上进行，主库将数据变更信息同步给从库。在查询时，可以在从库上进行，从而分担主库的压力

#### 一、实现多数据源 ####

--------------------

对于大多数 Java 应用，都使用了 Spring 框架，spring-jdbc 模块提供了 AbstractRoutingDataSource，其内部可以包含多个 DataSource，然后在运行时来动态的访问哪个数据库。这种方式访问数据库的架构如下图所示

![在这里插入图片描述][0077df968e4f4f52a97b23f6b72b0e15.png_pic_center]

> 测试样例一：普通的动态数据源

1.  创建两个数据库`read`和 `write`

CREATE DATABASE `read` /*!40100 DEFAULT CHARACTER SET utf8 */
    CREATE TABLE `people` (
      `name` VARCHAR(50) DEFAULT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    
    CREATE DATABASE `write` /*!40100 DEFAULT CHARACTER SET utf8 */
    CREATE TABLE `people` (
      `name` VARCHAR(50) DEFAULT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

1.  创建一个SpringBoot 项目 **dynamic\_datasource**
2.  导入依赖
    
     *  **pom.xml**

<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.1.4</version>
        </dependency>
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>

1.  应用配置文件
    
     *  **application.yaml**

spring:
      application:
        name: dynamic_datasource
      # 数据源
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        # 读数据源
        read:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/read?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
          username: root
          password: 123456
        # 写数据源
        write:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/write?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
          username: root
          password: 123456
    # 端口号
    server:
      port: 3355
    
    # 别名、xml文件配置
    mybatis:
      mapper-locations: classpath:com/vinjcent/mapper/**/*.xml
      type-aliases-package: com.vinjcent.pojo

1.  实体类
    
     *  People

package com.vinjcent.pojo;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public class People {
        
    
        private String name;
    
    }

1.  数据源配置类
    
     *  **DataSourceConfiguration**
    
        package com.vinjcent.config;
        
        import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
        import org.springframework.boot.context.properties.ConfigurationProperties;
        import org.springframework.context.annotation.Bean;
        import org.springframework.context.annotation.Configuration;
        
        import javax.sql.DataSource;
        
        @Configuration
        public class DataSourceConfiguration {
              
        
            @Bean(name = "readDatasource")
            @ConfigurationProperties(prefix = "spring.datasource.read")
            public DataSource readDatasource() {
              
                // 底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
                return DruidDataSourceBuilder.create().build();
            }
        
            @Bean(name = "writeDatasource")
            @ConfigurationProperties(prefix = "spring.datasource.write")
            public DataSource writeDatasource() {
              
                // 底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
                return DruidDataSourceBuilder.create().build();
            }
        }
2.  动态数据源
    
     *  **DynamicDataSource**

package com.vinjcent.config;
    
    import org.springframework.beans.factory.InitializingBean;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Primary;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.SQLFeatureNotSupportedException;
    import java.util.logging.Logger;
    
    
    @Component
    @Primary    // 将该Bean设置为主要注入Bean
    public class DynamicDataSource implements DataSource, InitializingBean {
        
    
        // 用于存储数据源的标识
        public static ThreadLocal<String> name = new ThreadLocal<>();
    
        // 写
        private final DataSource writeDataSource;
    
        // 读
        private final DataSource readDataSource;
    
        @Autowired
        public DynamicDataSource(@Qualifier("readDatasource") DataSource readDataSource,
                                 @Qualifier("writeDatasource") DataSource writeDataSource) {
        
            this.readDataSource = readDataSource;
            this.writeDataSource = writeDataSource;
        }
    
        @Override
        public Connection getConnection() throws SQLException {
        
            return name.get().equals("w") ? writeDataSource.getConnection() : readDataSource.getConnection();
        }
    
        @Override
        public Connection getConnection(String username, String password) throws SQLException {
        
            return null;
        }
    
        @Override
        public <T> T unwrap(Class<T> iface) throws SQLException {
        
            return null;
        }
    
        @Override
        public boolean isWrapperFor(Class<?> iface) throws SQLException {
        
            return false;
        }
    
        @Override
        public PrintWriter getLogWriter() throws SQLException {
        
            return null;
        }
    
        @Override
        public void setLogWriter(PrintWriter out) throws SQLException {
        
    
        }
    
        @Override
        public void setLoginTimeout(int seconds) throws SQLException {
        
    
        }
    
        @Override
        public int getLoginTimeout() throws SQLException {
        
            return 0;
        }
    
        @Override
        public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        
            return null;
        }
    
        /**
         * 初始化bean的initialization接口
         * @throws Exception
         */
        @Override
        public void afterPropertiesSet() throws Exception {
        
            // TODO 初始化
            name.set("w");
        }
    }

1.  测试接口
    
     *  **PeopleController**

package com.vinjcent.controller;
    
    import com.vinjcent.config.DynamicDataSource;
    import com.vinjcent.pojo.People;
    import com.vinjcent.service.PeopleService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    @RequestMapping("people")
    public class PeopleController {
        
    
        private final PeopleService peopleService;
    
        @Autowired
        public PeopleController(PeopleService peopleService) {
        
            this.peopleService = peopleService;
        }
    
    
        @GetMapping("/list")
        public List<People> getAllPeople() {
        
            // 修改对应数据源
            DynamicDataSource.name.set("r");
            return peopleService.list();
        }
    
        @GetMapping("/insert")
        public String addPeople() {
        
            // 修改对应数据源
            DynamicDataSource.name.set("w");
            peopleService.save(new People("vinjcent"));
            return "添加成功";
        }
    
    }

1.  主启动类（需要排除掉SpringBoot数据源自动配置类，否则会报错）

package com.vinjcent;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    
    @MapperScan("com.vinjcent.mapper")
    @SpringBootApplication(exclude = DataSourceAutoConfiguration.class)     // 排除SpringBoot数据源自动配置类
    public class DynamicDatasourceApplication {
        
    
        public static void main(String[] args) {
        
            SpringApplication.run(DynamicDatasourceApplication.class, args);
        }
    
    }

1.  测试访问接口，查看是否读写分离

> 测试实例二：实现 AbstractRoutingDataSource

应用直接操作的是 AbstractRoutingDataSource 的实现类，告诉 AbstractRoutingDataSource 访问哪个数据库，然后由 AbstractRoutingDataSource 从事先配好的数据源（readDataSource、writeDataSource）选择一个，来访问对应的数据库

![在这里插入图片描述][ceffb1ba4bdc47488164cef05dc2bf0f.png_pic_center]

**流程**

*  当执行数据库持久化操作，只要继承了 Spring 就一定会通过 DataSourceUtils 获取 Connection
 *  通过 Spring 注入的 DataSource 获取 Connection 即可执行数据库操作
    
     *  所以思路就是：只需配置一个 DataSource 的 bean，然后根据业务动态提供 Connection 即可
 *  其实 Spring 已经提供了一个 DataSource 实现类用于动态切换数据源—**AbstractRoutingDataSource**
 *  分析 **AbstractRoutingDataSource** 即可实现动态数据源切换

**AbstractRoutingDataSource 分析**

// targetDataSources 保存了key和数据库连接的映射关系
    private Map<Object, Object> targetDataSources;
    // 标识默认的连接
    private Object defaultTargetDataSource;
    // 这个数据结构是通过 targetDataSources 构建而来,存储结构也是数据库标识和数据源的映射关系
    private Map<Object, DataSource> resolvedDataSources;

**AbstractRoutingDataSource** 实现了 InitializingBean 接口，并实现了 **afterPropertiesSet**() 方法。**afterPropertiesSet**() 方法是初始化bean的时候执行，通常用作数据初始化。resolvedDataSources 就是在这里赋值

public void afterPropertiesSet() {
        
        if (this.targetDataSources == null) {
        
            throw new IllegalArgumentException("Property 'targetDataSources' is required");
        } else {
        
            this.resolvedDataSources = new HashMap(this.targetDataSources.size());
            this.targetDataSources.forEach((key, value) -> {
        
                Object lookupKey = this.resolveSpecifiedLookupKey(key);
                DataSource dataSource = this.resolveSpecifiedDataSource(value);
                this.resolvedDataSources.put(lookupKey, dataSource);
            });
            if (this.defaultTargetDataSource != null) {
        
                this.resolvedDefaultDataSource = this.resolveSpecifiedDataSource(this.defaultTargetDataSource);
            }
    
        }
    }

*  因此，我们只需要创建 **AbstractRoutingDataSource** 实现类 **DynamicDataSource** ，然后初始化 targetDataSources 和`key`为数据源的标识（可以是枚举、字符串都行，因为标识是Object类型）
 *  后续当调用 AbstractRoutingDataSource.getConnection 会接着调用提供的模板方法：**determineTargetDataSource**
 *  通过 determineDataSource 该方法返回的数据库标识，从 resolvedDataSources 中拿到对应的数据源（类似于一个**策略模式**）
 *  所以最终，只需要 **DynamicDataSource** 中实现 determineTargetDataSource 为其提供一个数据库标识

1.  修改 DynamicDataSource 动态数据源类

package com.vinjcent.config;
    
    import com.vinjcent.constants.DataSourceConstants;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Primary;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    
    
    @Component
    @Primary    // 将该Bean设置为主要注入Bean
    public class DynamicDataSource extends AbstractRoutingDataSource {
        
    
        // 用于存储数据源的标识
        public static ThreadLocal<String> name = new ThreadLocal<>();
    
        // 写
        private final DataSource writeDataSource;
    
        // 读
        private final DataSource readDataSource;
    
    
        @Autowired
        public DynamicDataSource(@Qualifier("readDatasource") DataSource readDataSource,
                                 @Qualifier("writeDatasource") DataSource writeDataSource) {
        
            this.readDataSource = readDataSource;
            this.writeDataSource = writeDataSource;
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
        
            return name.get();
        }
    
        // 初始化完bean之后调用该方法
        @Override
        public void afterPropertiesSet() {
        
            // 为targetDataSources 初始化所有数据源
            Map<Object, Object> sources = new HashMap<>();
            sources.put(DataSourceConstants.READ_DATASOURCE, readDataSource);
            sources.put(DataSourceConstants.WRITE_DATASOURCE, writeDataSource);
            super.setTargetDataSources(sources);
    
            // 为 defaultTargetDataSource 设置默认的数据源
            super.setDefaultTargetDataSource(readDataSource);
    
            // resolvedDataSources 负责最终切换的数据源map
            super.afterPropertiesSet();
        }
    }

1.  添加数据源常量类
    
     *  **DataSourceConstants**

package com.vinjcent.constants;
    
    public class DataSourceConstants {
        
    
        // 读数据源
        public static final String READ_DATASOURCE = "read";
    
        // 写数据源
        public static final String WRITE_DATASOURCE = "write";
    }

1.  修改 PeopleController 类

package com.vinjcent.controller;
    
    import com.vinjcent.config.DynamicDataSource;
    import com.vinjcent.constants.DataSourceConstants;
    import com.vinjcent.pojo.People;
    import com.vinjcent.service.PeopleService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    @RequestMapping("people")
    public class PeopleController {
        
    
        private final PeopleService peopleService;
    
        @Autowired
        public PeopleController(PeopleService peopleService) {
        
            this.peopleService = peopleService;
        }
    
    
        @GetMapping("/list")
        public List<People> getAllPeople() {
        
            // 修改对应数据源
            DynamicDataSource.name.set(DataSourceConstants.READ_DATASOURCE);
            return peopleService.list();
        }
    
        @GetMapping("/insert")
        public String addPeople() {
        
            // 修改对应数据源
            DynamicDataSource.name.set(DataSourceConstants.WRITE_DATASOURCE);
            peopleService.save(new People("vinjcent"));
            return "添加成功";
        }
    
    }

1.  测试访问接口，查看是否读写分离

下一篇文章《[SpringBoot实现多数据源（二）【Mybatis插件】][SpringBoot_Mybatis]》

[8ab766cfbc0f4070b23ac3bd349eb920.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/21c0e7ad6c59423292196d538b029948.png
[b1aad9e9dca347929083b000fb2f0563.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ed55c2eddd8d44248a0be920fc0ddabe.png
[0077df968e4f4f52a97b23f6b72b0e15.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/024d7792379345a6bc7fd7a50b2475bf.png
[ceffb1ba4bdc47488164cef05dc2bf0f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0864efcd0a6647c786642df6392e2128.png
[SpringBoot_Mybatis]: https://blog.csdn.net/Wei_Naijia/article/details/128069714