栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

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

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

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

引入依赖
  
            org.springframework.boot
            spring-boot-starter-web
        
        
            org.springframework.boot
            spring-boot-devtools
            runtime
            true
        

        
            org.springframework.boot
            spring-boot-starter-test
            test
        

        
        
            org.projectlombok
            lombok
            true
        

        
        
            cn.hutool
            hutool-all
            5.7.22
        

        
        
            org.springframework.boot
            spring-boot-starter-jdbc
        

        
        
            mysql
            mysql-connector-java
        

        
        
            org.apache.commons
            commons-lang3
        

        
        
            org.springframework.boot
            spring-boot-starter-actuator
        

        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.5.1
        
        
            org.apache.velocity
            velocity-engine-core
            2.3
        
        
        
            org.hibernate
            hibernate-validator
            6.1.0.Final
        


        
        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.0.0-RC1
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-namespace
            4.0.0-RC2
        
        
            org.springframework.boot
            spring-boot-starter-test
        
库表操作
-- ----------------------------
-- 新增 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:**
@Valid
@Slf4j
@RequestMapping("/api/user")
@RestController
public class SysUserController {

    private final SysUserService service;

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

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

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


    @PostMapping("/add")
    public ResponseResult 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;


@EqualsAndHashCode(callSuper = true)
@Data
@Accessors(chain = true)
@TableName("user")
public class SysUser extends Model {
    
    private Long id;

    
    private String userName;

    
    private String password;

    
    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;


@Slf4j
public interface SysUserService extends IService {


    
    IPage pageUsers(PageReq pageReq);

    
    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;
    }

    
    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;


@Service
public class SysUserServiceImpl extends ServiceImpl implements SysUserService {

    @Override
    public IPage pageUsers(PageReq pageReq) {
        Page 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;


@Mapper
public interface SysUserMapper extends BaseMapper {

}

mapper.xml



  
    
    
    
    
    
    
    
  

  
    
    id, `name`, `password`, sex, create_time
  



源码地址

https://gitee.com/linyunnuo/spring-boot-sharding-sphere

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/938983.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号