【数据分片+读写分离】Spring Boot + mybaits + shardingsphere

spring boot + mybatis+shardingsphere 实现数据分片+读写分离

版本

spring boot 2.7.18
mybatis 2.2.0
shardingsphere 5.1.1

pom.xml 依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>spring-shardingSphere</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.18</version>
    </parent>
    <name>spring-shardingSphere</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <lombok.version>1.18.12</lombok.version>
        <shardingsphere.version>5.1.1</shardingsphere.version>
        <junit.version>3.8.1</junit.version>
        <mysql-connector-java.version>8.0.29</mysql-connector-java.version>
        <mybatis.version>2.2.0</mybatis.version>
    </properties>

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

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-core</artifactId>
            <version>5.8.25</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>


    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

yaml文件配置

这里配置数据分片时，需要注意：actualDataNodes值 需要是readwrite-splitting.data-sources.数据源名称。 作者就是这里不一致导致读操作一直读主库，不读从库。

# tomcat
server:
  port: 8080
  servlet:
    context-path: /project

# spring config
spring:
  application:
    name: spring-shardingSphere
  shardingsphere:
    enabled: true
    props:
      sql-show: true
    mode:
      type: Memory
    datasource:
      names: ds0,ds1,slave1,slave2,slave3,slave4
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3307/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3307/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3308/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      slave2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3308/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      slave3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3309/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      slave4:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        maximum-pool-size: 10
        jdbc-url: jdbc:mysql://localhost:3309/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    rules:
      readwrite-splitting:
        load-balancers:
          alg-random:
            type: RANDOM
          alg_round:
            type: ROUND_ROBIN
          alg_weight:
            type: WEIGHT
            props:
              slave1: 1
              slave2: 2
        data-sources:
          masterRead_0:
            props:
              auto-aware-data-source-name: ds0
              read-data-source-names: slave1,slave3
              write-data-source-name: ds0
            type: Static
            load-balancer-name: alg-random
          masterRead_1:
            props:
              auto-aware-data-source-name: ds1
              read-data-source-names: slave2,slave4
              write-data-source-name: ds1
            type: Static
            load-balancer-name: alg-random
      sharding:
        tables:
          user:
            actualDataNodes: masterRead_$->{0..1}.user_$->{0..1}
            databaseStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: ds-inline
            tableStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: user-inline
            keyGenerateStrategy:
              column: id
              keyGeneratorName: snowflake
        keyGenerators:
          snowflake:
            type: SNOWFLAKE
        shardingAlgorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user_$->{id % 2}
          ds-inline:
            type: INLINE
            props:
              algorithm-expression: masterRead_$->{id % 2}


# Mybatis配置
mybatis:
  # 配置XML映射文件中指定的实体类别名路径
  type-aliases-package: com.example.model
  # 配置MyBatis的xml配置文件路径
  mapper-locations: classpath:mapper/*.xml
  # 开启驼峰uName自动映射到u_name
  map-underscore-to-camel-case: true

# 日志配置
logging:
  level:
    root: INFO
    org.apache.shardingsphere: DEBUG

主要代码

User类

import java.io.Serializable;

public class User implements Serializable {

        private static final long serialVersionUID = 1L;

        private Long id;
        private String name;

        public Long getId() {
                return id;
        }

        public void setId(Long id) {
                this.id = id;
        }

        public String getName() {
                return name;
        }

        public void setName(String name) {
                this.name = name;
        }
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="org.example.model.User">

        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
    </resultMap>
    <insert id="insert" parameterType="org.example.model.User">
        insert into user (id,name)
        values (#{id,jdbcType=BIGINT},#{name,jdbcType=VARCHAR})
    </insert>
    <select id="query" parameterType="java.lang.Long" resultMap="BaseResultMap">
        
        select
        <include refid="Base_Column_List" />
        from USER
        where id = #{id,jdbcType=BIGINT}
    </select>
</mapper>

主类

import cn.hutool.core.util.IdUtil;
import org.example.mapper.UserMapper;
import org.example.model.User;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
@SpringBootApplication
public class App {
    @Resource
    private UserMapper userMapper;

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

    @GetMapping("/get")
    public String test() {
        for (int i = 0; i < 10 ; i++) {
            User user = new User();
            long snowflakeNextId = IdUtil.getSnowflakeNextId();
            user.setId(snowflakeNextId);
            user.setName("user"+snowflakeNextId);
            userMapper.insert(user);

        }
        return "success";
    }
       @GetMapping("/getById/{id}")
    public String getUser(@PathVariable Long id) {
        User user = userMapper.query(id);
        return user.getId().toString()+"<===>"+user.getName();
    }

}