从0开始学习shardingjdbc之读写分离

笔者在之前的代码中已经完成了分库分表，加密存储。这次笔者将带大家完成shardingjdbc的读写分离。

项目的github地址：github.com/faxuexiaoxi… ,其中readWriteDemo是本次的代码地址

代码结构如下：

使用的技术栈如下： 下面简单介绍下用到的框架以及版本

框架	版本
springboot	3.2.2
mybatis	3.0.3
mybatis plus	3.5.5
druid	1.2.20
shardingjdbc	5.2.1

mysql的表数据展示：其中sharding是主库，sharding1，sharding2是从库。（ps：主库和从库的表结构是一模一样的，因此为了方便部署，在同一个mysql建立了3个一样的库，方便测试）

1. maven依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!--    Mybatis    -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<!--     Mybatis Plus    -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Druid 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.20</version>
</dependency>

<!--        shardingsphere-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

<!--    shardingsphere和2.2不兼容，需要使用1.33，-->
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

2.建表语句位于resources目录下init.sql

create database IF NOT EXISTS sharding;
USE sharding;
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE if not exists `user_0`
(
    `userId`        BIGINT         NOT NULL AUTO_INCREMENT,
    `userName`      VARCHAR(45) NOT NULL,
    `userPassword`  VARCHAR(45) NOT NULL,
    `userPassword_` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`userId`)
);

create database IF NOT EXISTS sharding1;
USE sharding1;
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE if not exists `user_0`
(
    `userId`        BIGINT         NOT NULL AUTO_INCREMENT,
    `userName`      VARCHAR(45) NOT NULL,
    `userPassword`  VARCHAR(45) NOT NULL,
    `userPassword_` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`userId`)
);

create database IF NOT EXISTS sharding2;
USE sharding2;
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE if not exists `user_0`
(
    `userId`        BIGINT         NOT NULL AUTO_INCREMENT,
    `userName`      VARCHAR(45) NOT NULL,
    `userPassword`  VARCHAR(45) NOT NULL,
    `userPassword_` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`userId`)
);

3. 实体类

package org.faxuexiaoxin.readwritedemo.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("user")
public class User {

    /**
     * 玩家id
     */
    @TableField("userId")
    private Long userid;

    /**
     * 玩家名称
     */
    @TableField("userName")
    private String userName;

    /**
     * 玩家密码
     */
    @TableField("userPassword")
    private String userPassword;

    /**
     * 玩家加密后的密码
     */
    @TableField("userPassword_")
    private String userPassword_;

    public Long getUserid() {
        return userid;
    }

    public void setUserid(Long userid) {
        this.userid = userid;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public String getUserPassword_() {
        return userPassword_;
    }

    public void setUserPassword_(String userPassword_) {
        this.userPassword_ = userPassword_;
    }

    @Override
    public String toString() {
        return "User{" +
                "userid=" + userid +
                ", userName='" + userName + ''' +
                ", userPassword='" + userPassword + ''' +
                ", userPassword_='" + userPassword_ + ''' +
                '}';
    }
}

4.mapper复用mybatis plus提供的baseMapper

package org.faxuexiaoxin.readwritedemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.faxuexiaoxin.readwritedemo.entity.User;

public interface UserMapper extends BaseMapper<User> {

}

5. 入口类 MapperScan即可

package org.faxuexiaoxin.readwritedemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("org.faxuexiaoxin.readwritedemo.mapper")
public class ReadWriteDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ReadWriteDemoApplication.class, args);
    }

}

6.测试用例

package org.faxuexiaoxin.readwritedemo.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.faxuexiaoxin.readwritedemo.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class UserMapperTest {
    @Autowired
    private UserMapper mapper;

    /**
     * 插入10个数据，看看是否都是路由到m0
     */
    @Test
    void testInsert() {

        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserName("faxuexiaoxin");
            user.setUserPassword_("13123");
            user.setUserPassword("agasd");
            mapper.insert(user);
        }
    }

    @Test
    void testQuery() {
        for (int i = 0; i < 10; i++) {
            QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
            userQueryWrapper.eq("userId",4674879879L);

            List<User> users = mapper.selectList(userQueryWrapper);

            System.out.println(users);
        }
    }
}

7. application.properties

spring.application.name=readWriteDemo

#打打印sql
spring.shardingsphere.props.sql-show=true
#配置sharding的数据源
spring.shardingsphere.datasource.names=m0,m1,m2

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.dirver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/sharding
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.dirver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding1
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.dirver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/sharding2
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

#读写分离策略
#配置主库
spring.shardingsphere.rules.readwrite-splitting.data-sources.m0.static-strategy.write-data-source-name=m0
#配置从库
spring.shardingsphere.rules.readwrite-splitting.data-sources.m0.static-strategy.read-data-source-names=m1,m2
#负载均衡算法
spring.shardingsphere.rules.readwrite-splitting.data-sources.m0.load-balancer-name=test
#访问读库的策略：RANDOM随机，ROUND_ROBIN轮询，权重等
spring.shardingsphere.rules.readwrite-splitting.load-balancers.test.type=ROUND_ROBIN

#配置sharding需要的表,只需要配置主库的表，从库的表和主库结构一模一样.如果多个主库之间也需要分库分表,还需要配置分库分表的信息
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=m0.user_0

#配置分布式id的算法
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=userid
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snow
spring.shardingsphere.rules.sharding.key-generators.alg_snow.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_snow.props.worker-id=1

8.测试插入数据，运行UserMapperTest.testInsert，标红的地方显示均插入到配置的主库

运行UserMapperTest.testQuery测试查询逻辑，算法使用了ROUND_ROBIN，会轮询m1,m2两个库的user_0表。如图所示

综上实现了读写分离