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

多数据源的读写分离

参考文章：https://mybatis.plus/guide/dynamic-datasource.html
本文环境：

1、maven依赖配置

        <!-- druid -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <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>
1
2
3
4
5
6
7
8
9
10
11
12
13

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

3、启动类配置

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

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

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

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

上面三步，已配置好读写分离，下面使用注解即可选择使用主从库。

@Service
@DS("slave")
public class UserServiceImpl implements UserService {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  public List<Map<String, Object>> selectAll() {
    return  jdbcTemplate.queryForList("select * from user");
  }
  
  @Override
  @DS("slave_1")
  public List<Map<String, Object>> selectByCondition() {
    return  jdbcTemplate.queryForList("select * from user where age >10");
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@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();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• @DS(“slave_1”) 括号内即是application.properties中配置的数据源名称，不加注解默认是访问主库master，可加在service方法上，也可加在mapper方法上，但强烈不建议同时在service和mapper注解。 (可能会有问题)
• @DS 可以注解在方法上和类上，同时存在方法注解优先于类上注解。
• 本文的application.properties中没有配置名为slave_1的数据源，代码里只做演示示例，可将slave_1改成对应的数据源名称。

相关文章：

• Windows系统 Mysql8.0.11：同ip运行两个mysql服务
• Windows系统 Mysql8.0.11：数据库主从同步配置

PS:
如果博文有写的不对或者有更好的方式，欢迎大家评论或者私信指正。