【SpringBoot+Druid+Mybatis实现动态数据源读写分离】

实现读写分离

依赖文件

<!-- MyBatis Spring Boot Starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.1</version>
            <exclusions>
                <exclusion>
                    <artifactId>HikariCP</artifactId>
                    <groupId>com.zaxxer</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- 数据库驱动，例如 MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.6.1</version> <!-- 请根据需要选择合适的版本 -->
        </dependency>

配置文件application.properties

# 数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.dynamic.primary=master
# false未指定数据源不报错使用默认
spring.datasource.dynamic.strict=false
# 读写库
spring.datasource.dynamic.datasource.master.url=jdbc:mysql://xxxx:3306/gyc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.dynamic.datasource.master.username=read_write
spring.datasource.dynamic.datasource.master.password=passwd
spring.datasource.dynamic.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.master.druid.initial-size=5
spring.datasource.dynamic.datasource.master.druid.min-idle=5
spring.datasource.dynamic.datasource.master.druid.max-active=20
spring.datasource.dynamic.datasource.master.druid.max-wait=60000
spring.datasource.dynamic.datasource.master.druid.validation-query=SELECT 1
spring.datasource.dynamic.datasource.master.druid.test-on-borrow=true
spring.datasource.dynamic.datasource.master.druid.test-while-idle=true
spring.datasource.dynamic.datasource.master.druid.test-on-return=true
spring.datasource.dynamic.datasource.master.druid.time-between-eviction-runs-millis=60000
spring.datasource.dynamic.datasource.master.druid.min-evictable-idle-time-millis=300000

# 写库
spring.datasource.dynamic.datasource.slave.url=jdbc:mysql://xxxx:3306/gyc_readonly?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.dynamic.datasource.slave.username=read_only
spring.datasource.dynamic.datasource.slave.password=passwd
spring.datasource.dynamic.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.slave.druid.initial-size=5
spring.datasource.dynamic.datasource.slave.druid.min-idle=5
spring.datasource.dynamic.datasource.slave.druid.max-active=20
spring.datasource.dynamic.datasource.slave.druid.max-wait=60000
spring.datasource.dynamic.datasource.slave.druid.validation-query=SELECT 1
spring.datasource.dynamic.datasource.slave.druid.test-on-borrow=true
spring.datasource.dynamic.datasource.slave.druid.test-while-idle=true
spring.datasource.dynamic.datasource.slave.druid.test-on-return=true
spring.datasource.dynamic.datasource.slave.druid.time-between-eviction-runs-millis=60000
spring.datasource.dynamic.datasource.slave.druid.min-evictable-idle-time-millis=300000

# mybatis
# 开启 MyBatis SQL日志输出
logging.level.org.mybatis=debug

# 输出格式化的 SQL语句
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

代码示例

在使用DynamicDataSource提供的注解@DS进行数据源区分

package com.gaoyuchi.test.controller;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.gaoyuchi.test.common.ResponseResult;
import com.gaoyuchi.test.entity.qo.UserQo;
import com.gaoyuchi.test.entity.vo.LoginVo;
import com.gaoyuchi.test.entity.vo.UserVo;
import com.gaoyuchi.test.service.user.impl.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/user")
public class UserController {

    @DS("slave")
    @GetMapping("/query/slave")
    public ResponseResult<UserVo> querySlave(@RequestParam String id) {
        return userService.querySlave(id);
    }

    @DS("master")
    @GetMapping("/query/master")
    public ResponseResult<UserVo> query(@RequestParam String id) {
        return userService.query(id);
    }

}

其他方案

还可以基于springboot自带的连接池进行实现，可以结合AOP自己实现自定义注解，通过继承AbstractRoutingDataSouce重写DetermineLookupKey方法指定数据源。参考：多数据源读写分离实现

还有一种思路是将数据源跟不同的Mapper进行绑定，实际是跟不同的SqlSessionFactory进行绑定。对不同的Mapper进行操作使用不同的数据源。参考：多数据源