Spring boot整合dynamic-datasource实现多数据源的读写分离

水深无声 2022-09-13 15:24 107阅读 0赞

#  #

# 多数据源的读写分离 #

参考文章：[mybatis.plus][]  
本文环境：

## 版本 ##

springboot    2.1.0.RELEASE  
druid    1.1.18  
dynamic-datasource    2.4.2

##    1、maven依赖配置     ##

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.18</version>
            </dependency>
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
                <version>2.4.2</version>
            </dependency>

## 2、application.properties配置 ##

## druid连接池配置
    # 默认数据源
    spring.datasource.dynamic.primary=master
    # 主库配置 master
    spring.datasource.dynamic.datasource.master.username=root
    spring.datasource.dynamic.datasource.master.password=root
    spring.datasource.dynamic.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.dynamic.datasource.master.url=jdbc:mysql://127.0.0.1:3306/java_demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
    spring.datasource.dynamic.datasource.master.druid.initial-size=5
    spring.datasource.dynamic.datasource.master.druid.max-active=20
    spring.datasource.dynamic.datasource.master.druid.min-idle=5
    spring.datasource.dynamic.datasource.master.druid.max-wait=60000
    spring.datasource.dynamic.datasource.master.druid.min-evictable-idle-time-millis=300000
    spring.datasource.dynamic.datasource.master.druid.max-evictable-idle-time-millis=300000
    spring.datasource.dynamic.datasource.master.druid.time-between-eviction-runs-millis=60000
    spring.datasource.dynamic.datasource.master.druid.validation-query=select 1
    spring.datasource.dynamic.datasource.master.druid.validation-query-timeout=-1
    spring.datasource.dynamic.datasource.master.druid.test-on-borrow=false
    spring.datasource.dynamic.datasource.master.druid.test-on-return=false
    spring.datasource.dynamic.datasource.master.druid.test-while-idle=true
    spring.datasource.dynamic.datasource.master.druid.pool-prepared-statements=true
    spring.datasource.dynamic.datasource.master.druid.filters=stat,wall,log4j
    spring.datasource.dynamic.datasource.master.druid.share-prepared-statements=true
    # 从库配置 slave
    spring.datasource.dynamic.datasource.slave.username=root
    spring.datasource.dynamic.datasource.slave.password=root
    spring.datasource.dynamic.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.dynamic.datasource.slave.url=jdbc:mysql://127.0.0.1:3307/java_demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
    spring.datasource.dynamic.datasource.slave.druid.initial-size=5
    spring.datasource.dynamic.datasource.slave.druid.max-active=20
    spring.datasource.dynamic.datasource.slave.druid.min-idle=5
    spring.datasource.dynamic.datasource.slave.druid.max-wait=60000
    spring.datasource.dynamic.datasource.slave.druid.min-evictable-idle-time-millis=300000
    spring.datasource.dynamic.datasource.slave.druid.max-evictable-idle-time-millis=300000
    spring.datasource.dynamic.datasource.slave.druid.time-between-eviction-runs-millis=60000
    spring.datasource.dynamic.datasource.slave.druid.validation-query=select 1
    spring.datasource.dynamic.datasource.slave.druid.validation-query-timeout=-1
    spring.datasource.dynamic.datasource.slave.druid.test-on-borrow=false
    spring.datasource.dynamic.datasource.slave.druid.test-on-return=false
    spring.datasource.dynamic.datasource.slave.druid.test-while-idle=true
    spring.datasource.dynamic.datasource.slave.druid.pool-prepared-statements=true
    spring.datasource.dynamic.datasource.slave.druid.filters=stat,wall,log4j
    spring.datasource.dynamic.datasource.slave.druid.share-prepared-statements=true
    # 当遇到同样名字的时候，是否允许覆盖注册
    spring.main.allow-bean-definition-overriding=true
    ## druid访问地址配置
    druid.login.username=root
    druid.login.password=root
    druid.allow.ip=127.0.0.1

## 3、启动类配置 ##

在启动类@SpringBootApplication注解中，添加排除原生Druid的配置类。

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
    public class ApiApplication {
        public static void main(String[] args) {
            SpringApplication.run(ApiApplication.class, args);
        }
    }

Q：为什么要排除DruidDataSourceAutoConfigure ？  
A：DruidDataSourceAutoConfigure会注入一个DataSourceWrapper，其会在原生的spring.datasource下找url,username,password等。而我们动态数据源的配置路径是变化的。

4、使用：使用 @DS 切换数据源

上面三步，已配置好读写分离，下面使用注解即可选择使用主从库。建议主库主要用来进行写入操作，从库用来进行读操作。

指定为主库

@DS("master")
    public interface UserMapper {
    
      @Insert("INSERT INTO user (name,age) values (#{name},#{age})")
      boolean addUser(@Param("name") String name, @Param("age") Integer age);
    
      @Update("UPDATE user set name=#{name}, age=#{age} where id =#{id}")
      boolean updateUser(@Param("id") Integer id, @Param("name") String name, @Param("age") Integer age);
    
      @Delete("DELETE from user where id =#{id}")
      boolean deleteUser(@Param("id") Integer id);
    
      @Select("SELECT * FROM user")
      @DS("slave_1")
      List<User> selectAll();
    }

指定为从库

@DS("slave")
    public interface UserMapper {
    
      @Insert("INSERT INTO user (name,age) values (#{name},#{age})")
      boolean addUser(@Param("name") String name, @Param("age") Integer age);
    
      @Update("UPDATE user set name=#{name}, age=#{age} where id =#{id}")
      boolean updateUser(@Param("id") Integer id, @Param("name") String name, @Param("age") Integer age);
    
      @Delete("DELETE from user where id =#{id}")
      boolean deleteUser(@Param("id") Integer id);
    
      @Select("SELECT * FROM user")
      @DS("slave_1")
      List<User> selectAll();
    }

[mybatis.plus]: https://mybatis.plus/guide/dynamic-datasource.html