库表操作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
配置yamlspring:
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 IServiceserviceImpl 服务接实现{ 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()); } } }
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 ServiceImplmapper层implements SysUserService { @Override public IPage pageUsers(PageReq pageReq) { Page page = new Page<>(pageReq.getPageIndex(), pageReq.getPageSize()); return this.page(page); } }
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 BaseMappermapper.xml{ }
源码地址id, `name`, `password`, sex, create_time
https://gitee.com/linyunnuo/spring-boot-sharding-sphere



