Springboot 集成 Sharding-sphere 进行分库分表yaml配置--雪花算法

简介

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。 ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。

官方文档：shardingsphere.apache.org/document/le…

引入POM依赖

  <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

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

        <!--    lombok    -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--    hutool工具类    -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.22</version>
        </dependency>

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

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

        <!-- 健康检查 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>

        <!-- mybaits plus 插件 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>
        <!--    Hibernate-Validator-->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.1.0.Final</version>
        </dependency>


        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

数据库表操作

-- ----------------------------
-- 新增 user表
-- ----------------------------
-- DROP TABLE IF EXISTS `user_xx`;
CREATE TABLE `user_xx` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '密码',
  `sex` int NOT NULL DEFAULT 2 COMMENT '性别（0=女，1=男，2=未知）',
  `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `userName`(`user_name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COMMENT = 'user' ROW_FORMAT = Dynamic;

-- auto Generated on 2022-05-27
-- DROP TABLE IF EXISTS master_test;
CREATE TABLE master_test
(
    id      BIGINT (15) NOT NULL AUTO_INCREMENT COMMENT 'id',
    title   VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'title',
    content VARCHAR(50)  NOT NULL DEFAULT '' COMMENT 'content',
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'master_test';

如下：

我这配置八个分库（sharding-user-xx）10个分表(user_xx),一个主库sharding-master,主表：master_test

配置yaml

spring:
  main:
    allow-bean-definition-overriding: true
  application:
    name: Sharding-sphere-demo
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  sharding-sphere:
    # 连接池
    datasource:
      names: master,user-0,user-1,user-2,user-3,user-4,user-5,user-6,user-7
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-master?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-0?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-1?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-2?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-4:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-4?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-5:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-5?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-6:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-6?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
      user-7:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${MYSQL_IP:localhost}:${MYSQL_PORT:3306}/sharding-user-7?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=utf8
        username: ${MYSQL_USER:root}
        password: ${MYSQL_PASSWD:root}
    props:
      sql:
        show: true
    sharding:
      # 设置默认库
      default-data-source-name: master
      tables:
        # 自定义user分库分表规则
        user:
          # 实际数据节点 可以用逗号隔开多个库表配置
          actual-data-nodes: user-$->{0..7}.user_$->{0..9},
          # 表策略
          tableStrategy:
            inline:
              # sharding字段
              shardingColumn: id
              # 采用算法-表
              algorithmExpression: user_$->{id % 10}
          keyGenerator:
            column: id
            type: SNOWFLAKE
      # 默认数据库分库规则
      default-database-strategy:
        inline:
          # 采用算法-库
          algorithm-expression: user-$->{id % 8}
          # 使用字段进行计算
          sharding-column: id

management:
  health:
    db:
      enabled: false

# server配置
server:
  port: 8890

# mybatis-plus 配置
mybatis-plus:
  # xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置）
  mapper-locations: classpath:**/*Mapper.xml
  global-config:
    db-config:
      # 1 代表已删除，不配置默认是1，也可修改配置
      logic-delete-value: 1
      # 0 代表未删除，不配置默认是0，也可修改配置
      logic-not-delete-value: 0
      # 添加非空判断
      update-strategy: not_empty
      insert-strategy: not_empty
  # 日志配置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

代码

controller

package com.yunnuo.com.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.yunnuo.com.core.PageReq;
import com.yunnuo.com.core.ResponseResult;
import com.yunnuo.com.domain.dto.SysUserReqDto;
import com.yunnuo.com.domain.entity.SysUser;
import com.yunnuo.com.service.SysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;

/**
 * @author yunnuo
 * @since 2022-04-09
 */
@Valid
@Slf4j
@RequestMapping("/api/user")
@RestController
public class SysUserController {

    private final SysUserService service;

    public SysUserController(SysUserService service) {
        this.service = service;
    }

    @PostMapping("/page")
    public ResponseResult<IPage<SysUser>> pageUsers(@RequestBody @Validated PageReq pageReq) {
        return ResponseResult.success(service.pageUsers(pageReq));
    }

    @GetMapping("/getByUserId/{id}")
    public ResponseResult<String> getLibraryAndTableByUserId(@PathVariable Long id) {
        return ResponseResult.success(service.getLibraryAndTableByUserId(id));
    }


    @PostMapping("/add")
    public ResponseResult<SysUser> add(@RequestBody @Validated SysUserReqDto reqDto) {
        return ResponseResult.success(service.saveUserInfo(reqDto));
    }

}

entity实体

package com.yunnuo.com.domain.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.util.Date;

/**
 * 用户实体类
 *
 * @author yunnuo
 */
@EqualsAndHashCode(callSuper = true)
@Data
@Accessors(chain = true)
@TableName("user")
public class SysUser extends Model<SysUser> {
    /**
     * id
     */
    private Long id;

    /**
     * 名称
     */
    private String userName;

    /**
     * 密码
     */
    private String password;

    /**
     * 性别（0 = 男， 1= 女， 2 = 其他）
     */
    private Integer sex;

    /**
     * 创建时间
     */
    @TableField(value = "createTime")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "UTC")
    private Date createTime;
}

service 服务接口

package com.yunnuo.com.service;

import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.yunnuo.com.core.PageReq;
import com.yunnuo.com.domain.dto.SysUserReqDto;
import com.yunnuo.com.domain.entity.SysUser;
import com.yunnuo.com.exception.user.UserException;
import groovy.util.logging.Slf4j;

import java.util.Date;
import java.util.Objects;

/**
 * User 服务接口
 *
 * @author yunnuo
 * @since 2022-04-09
 */
@Slf4j
public interface SysUserService extends IService<SysUser> {


    /**
     * 用户数据分页
     *
     * @param pageReq 页面请求
     * @return {@link IPage}<{@link SysUser}>
     */
    IPage<SysUser> pageUsers(PageReq pageReq);

    /**
     * 通过用户id获取所在库和表
     *
     * @param id id
     * @return {@link String}
     */
    default String getLibraryAndTableByUserId(Long id) {
        if (Objects.isNull(id)) {
            throw new UserException("id mast be not null!");
        }
        long library = id % 8L;
        long table = id % 10L;
        return "所在库：" + library + " 所在表：" + table;
    }

    /**
     * 保存用户信息
     *
     * @param reqDto {@link SysUserReqDto}
     * @return {@link SysUser}
     */
    default SysUser saveUserInfo(SysUserReqDto reqDto) {
        try {
            SysUser sysUser = BeanUtil.copyProperties(reqDto, SysUser.class);
            sysUser.setCreateTime(new Date());
            boolean saveFlag = this.save(sysUser);
            if (saveFlag) {
                return sysUser;
            }
            throw new UserException("新增用户数据-0");
        } catch (Exception e) {
            throw new UserException("新增用户失败：" + e.getMessage());
        }
    }
}

serviceImpl 服务接实现

package com.yunnuo.com.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yunnuo.com.core.PageReq;
import com.yunnuo.com.domain.entity.SysUser;
import com.yunnuo.com.mapper.SysUserMapper;
import com.yunnuo.com.service.SysUserService;
import org.springframework.stereotype.Service;

/**
 * User 服务实现
 *
 * @author yunnuo
 * @since 2022-04-09
 */
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {

    @Override
    public IPage<SysUser> pageUsers(PageReq pageReq) {
        Page<SysUser> page = new Page<>(pageReq.getPageIndex(), pageReq.getPageSize());
        return this.page(page);
    }
}

mapper层

package com.yunnuo.com.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yunnuo.com.domain.entity.SysUser;
import org.apache.ibatis.annotations.Mapper;

/**
 * User 数据接口层
 *
 * @author yunnuo
 * @since 2022-04-09
 */
@Mapper
public interface SysUserMapper extends BaseMapper<SysUser> {

}

mapper.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="com.yunnuo.com.mapper.SysUserMapper">
  <resultMap id="BaseResultMap" type="com.yunnuo.com.domain.entity.SysUser">
    <!--@mbg.generated-->
    <!--@Table `user`-->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="userName" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="sex" jdbcType="INTEGER" property="sex" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
  </resultMap>

  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, `name`, `password`, sex, create_time
  </sql>

</mapper>

源码地址

gitee.com/linyunnuo/s…