springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离

前提是mysql已实现主从复制《MySQL8主从复制》
springboot版本是2.7.2
mysql8.0.23

pom.xml

<dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <scope>runtimescope>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <optional>trueoptional>
        dependency>
        
        <dependency>
            <groupId>com.baomidougroupId>
            <artifactId>mybatis-plus-boot-starterartifactId>
            <version>3.5.2version>
        dependency>
        
        <dependency>
            <groupId>org.yamlgroupId>
            <artifactId>snakeyamlartifactId>
            <version>1.33version>
        dependency>
        
        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starterartifactId>
            <version>5.2.1version>
        dependency>
    dependencies>
    
	<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombokgroupId>
                            <artifactId>lombokartifactId>
                        exclude>
                    excludes>
                configuration>
            plugin>
        plugins>
    build>
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
50
51

提供yml和properties2种配置

yml

spring:
  shardingsphere:
    props:
      #是否打印sql
      sql-show: true
    datasource:
      #自定义数据库名称
      names: master,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      readwrite-splitting:
        data-sources:
          #自定义数据源名称
          myds:
            static-strategy:
              #设置只写的库，库名为上面自定义的数据库名称
              write-data-source-name: master
              #设置只读的库，库名为上面自定义的数据库名称
              read-data-source-names:
                - slave1
            #负载均衡策略名称
            load-balancer-name: round_robin
        load-balancers:
          #上面自定义的负载均衡策略名称
          round_robin:
          	#负载均衡策略类型：ROUND_ROBIN、RANDOM、WEIGHT、TRANSACTION_RANDOM等
            type: ROUND_ROBIN
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

properties

spring.shardingsphere.datasource.names=master,slave1
# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.write-data-source-name=master
#读数据源名称，多个从数据源用逗号分隔。如：slave1,slave2
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.read-data-source-names=slave1
#负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=round_robin
#负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN
#打印sql
spring.shardingsphere.props.sql-show=true
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

UserController.java

@RestController
@RequestMapping("user")
public class UserController {
    @Resource
    private UserService userService;

    @GetMapping("{id}")
    public User select(@PathVariable Long id){
        return userService.getById(id);
    }

    @PutMapping("insert")
    public boolean insert(){
        User user = new User();
        user.setUsername("Meta39");
        return userService.save(user);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

UserService.java

public interface UserService extends IService<User> {
}
1
2

UserServiceImpl.java

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
1
2
3

UserMapper.java

@Mapper
public interface UserMapper extends BaseMapper<User> {
}
1
2
3

User.java

@Data
@TableName("user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(type = IdType.AUTO)
    private Long id;
    private String username;
    private LocalDateTime createTime;
}
1
2
3
4
5
6
7
8
9
10
11

启动项目测试

测试查询接口localhost:88/user/1

走的是从库

测试写入接口localhost:88/user/insert
走的是主库