前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

1、开始Demo

1.1、hlj-04-sharding-table.sql

drop database if exists ds_0;create database ds_0 character set 'utf8' collate 'utf8_general_ci';use ds_0;drop table if exists user_0;create table `user_0`(  `id`bigint(20) unsigned not null,  cityvarchar(20) not null default '',  namevarchar(20) not null default '',  status  int(10) not null default '0' comment '状态',  create_time datetimenot null default current_timestamp comment '创建时间',  update_time datetimenot null default current_timestamp on update current_timestamp comment '修改时间',  primary key (`id`)) engine = innodb  default charset = utf8;drop table if exists user_1;create table `user_1`(  `id`bigint(20) unsigned not null,  cityvarchar(20) not null default '',  namevarchar(20) not null default '',  status  int(10) not null default '0' comment '状态',  create_time datetimenot null default current_timestamp comment '创建时间',  update_time datetimenot null default current_timestamp on update current_timestamp comment '修改时间',  primary key (`id`)) engine = innodb  default charset = utf8;drop table if exists user_2;create table `user_2`(  `id`bigint(20) unsigned not null,  cityvarchar(20) not null default '',  namevarchar(20) not null default '',  status  int(10) not null default '0' comment '状态',  create_time datetimenot null default current_timestamp comment '创建时间',  update_time datetimenot null default current_timestamp on update current_timestamp comment '修改时间',  primary key (`id`)) engine = innodb  default charset = utf8;drop table if exists company_0;create table `company_0`(  `id` bigint(20) unsigned not null comment '主键',  name varchar(20) not null default '' comment '企业名称',  company_name_english varchar(128)not null default '' comment '企业英文名称',  statusint(10) not null default '0' comment '状态',  create_time  datetimenot null default current_timestamp comment '创建时间',  update_time  datetimenot null default current_timestamp on update current_timestamp comment '修改时间',  primary key (`id`)) engine = innodb  default charset = utf8;drop table if exists company_1;create table `company_1`(  `id` bigint(20) unsigned not null comment '主键',  name varchar(20) not null default '' comment '企业名称',  company_name_english varchar(128)not null default '' comment '企业英文名称',  statusint(10) not null default '0' comment '状态',  create_time  datetimenot null default current_timestamp comment '创建时间',  update_time  datetimenot null default current_timestamp on update current_timestamp comment '修改时间',  primary key (`id`)) engine = innodb  default charset = utf8;CREATE TABLE `demo_entity`(  `id`  bigint(20) unsigned NOT NULL COMMENT '主键',  `name`varchar(64) NOT NULL,  `phone`varchar(20)  DEFAULT '' COMMENT '手机号',  `email`varchar(64)  DEFAULT '' COMMENT '邮箱',  `age` int(10)  DEFAULT NULL,  `status`  varchar(8)  NOT NULL COMMENT '状态',  `create_user` bigint(16) unsigned  DEFAULT NULL COMMENT '创建人',  `create_name` varchar(64)  DEFAULT '' COMMENT '创建人名称',  `create_time` datetimeNOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `update_user` bigint(16) unsigned  DEFAULT NULL COMMENT '更新人',  `update_name` varchar(64)  DEFAULT '' COMMENT '更新人名称',  `update_time` datetimeNOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE = InnoDB  DEFAULT CHARSET = utf8;

1.1.1、数据库图文

1.2、依赖

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

1.3、配置文件：application.properties

server.port=8888################################### mybatis-plus配置################################### 配置 mybatis的一些配置，也可以在 application.properties 中配置，如果配置了就不需要了mybatis.xml#mybatis-plus.config-location=classpath:mybatis.xml#Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）mybatis-plus.mapper-locations=classpath*:mapper/*.xmlmybatis-plus.type-aliases-package=com.healerjean.proj.pojo##主键类型  0:"数据库ID自增，非常大", 1:"用户输入ID（如果用户不输入，则默认是0）",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";mybatis-plus.id-type: 0#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"mybatis-plus.field-strategy: 2#数据库大写下划线转换mybatis-plus.capital-mode: truemybatis-plus.refresh-mapper: true################################### 只分表，不分库################################### #当遇到同样名字的时候，是否允许覆盖注册spring.main.allow-bean-definition-overriding=true# 显示SQLspring.shardingsphere.props.sql.show=true# 数据源spring.shardingsphere.datasource.names=master# 数据源spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=truespring.shardingsphere.datasource.master.useame=rootspring.shardingsphere.datasource.master.password=123456# user  company 分表# user_0,user_1,user_2（自定义分表算法）spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_${0..2}spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=idspring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.healerjean.proj.config.datasource.CustomShardingTableAlgorithm# company_0,company_1 （inline分表策略 表达式 id%4）spring.shardingsphere.sharding.tables.company.actual-data-nodes=master.company_${0..1}spring.shardingsphere.sharding.tables.company.table-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.company.table-strategy.inline.algorithm-expression=company_${id.longValue() % 2}

1.4、具体测试方法和类

1.4.1、实体类

1.4.1.1、User.java

@Data@Accessors(chain = true)public class User implements Serializable {private static final long serialVersionUID = 1L;/** 主键  */private Long id;private String name;private String city;private String status;private Date createTime;private Date updateTime;}

1.4.1.2、Company.java

@Datapublic class Company {	private Long id;	private String name;	private String companyNameEnglish;	private String status;	private Date createTime;	private Date updateTime;}

1.4.1.3、DemoEntity.java

@Data@Accessors(chain = true)public class DemoEntity implements Serializable {private static final long serialVersionUID = 1L;/** * 主键 */private Long id;/** 姓名 */private String name;/** 手机号  */private String phone;/**  邮箱 */private String email;/** 年龄  */private Integer age;/**  10可用，99删除  */private String status;/** 创建人 */private Long createUser;/** 创建人名称  */private String createName;/**  创建时间 */private java.util.Date createTime;/**  更新人 */private Long updateUser;/** 更新人名称 */private String updateName;/**  更新时间 */private java.util.Date updateTime;}

1.4.2、DTO数据

1.4.2.1、UserDTO.java

@Data@Accessors(chain = true)@ApiModel(value = "demo实体类")@JsonInclude(JsonInclude.Include.NON_NULL)public class UserDTO {@ApiModelProperty(value = "主键", hidden = true)@JsonSerialize(using = JsonLongSerializer.class )private Long id;@ApiModelProperty(value = "姓名")@NotBlank(message = "姓名不能为空", groups = ValidateGroup.HealerJean.class)private String name;@ApiModelProperty(value = "城市")private String city;@ApiModelProperty(value = "状态", hidden = true)private String status;@ApiModelProperty(value = "创建时间", hidden = true)@JsonFormat(patte = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")private Date createTime;@ApiModelProperty(value = "修改时间", hidden = true)@JsonFormat(patte = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")private Date updateTime;}

1.4.2.2、CompanyDTO.java

@Datapublic class CompanyDTO {	@JsonSerialize(using = JsonLongSerializer.class )	private Long id;	private String name;	private String companyNameEnglish;	private String status;	@ApiModelProperty(value = "创建时间", hidden = true)	@JsonFormat(patte = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")	private Date createTime;	@ApiModelProperty(value = "修改时间", hidden = true)	@JsonFormat(patte = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")	private Date updateTime;}

1.4.2.3、DemoDTO.java

@Data@Accessors(chain = true)@ApiModel(value = "demo实体类")@JsonInclude(JsonInclude.Include.NON_NULL)public class DemoDTO extends PageQuery {@JsonSerialize(using = JsonLongSerializer.class )private Long id;@ApiModelProperty(value = "姓名")@NotBlank(message = "姓名不能为空", groups = ValidateGroup.HealerJean.class)private String name;@ApiModelProperty(value = "年龄")private Integer age;@ApiModelProperty(value = "手机号")private String phone;@ApiModelProperty(value = "邮箱")private String email;@ApiModelProperty(value = "是否删除，10可用，99删除 ", hidden = true)private String status;@ApiModelProperty(value = "创建人", hidden = true)private Long createUser;@ApiModelProperty(value = "创建人名字", hidden = true)private String createName;@ApiModelProperty(value = "创建时间", hidden = true)private java.util.Date createTime;@ApiModelProperty(value = "更新人", hidden = true)private Long updateUser;@ApiModelProperty(value = "更新人名称", hidden = true)private String updateName;@ApiModelProperty(hidden = true)private java.util.Date updateTime;}

1.4.3、Mapper

1.4.3.1、UserMapper.java

public interface UserMapper extends BaseMapper<User> {}

1.4.3.1、CompanyMapper.java

public interface CompanyMapper  extends BaseMapper<Company> {}

1.4.3.1、DemoEntityMapper.java

public interface DemoEntityMapper extends BaseMapper<DemoEntity> {}

1.4.4、Service

1.4.4.1、 UserService.java

public interface UserService {UserDTO insert(UserDTO userDTO);UserDTO findById(Long id);List<UserDTO> list();}

1.4.4.2、 CompanyService.java

public interface CompanyService {CompanyDTO insert(CompanyDTO companyDTO);CompanyDTO findById(Long id);List<CompanyDTO> list();}

1.4.4.3、 DemoEntityService.java

public interface DemoEntityService {DemoDTO insert(DemoDTO demoEntity);DemoDTO findById(Long id);List<DemoDTO> list();}

1.4.5、ServiceImpl.java

1.4.5.1、UserServiceImpl.java

@Service@Slf4jpublic class UserServiceImpl implements UserService {@Resourceprivate UserMapper userMapper;@Overridepublic UserDTO insert(UserDTO userDTO) {User user = BeanUtils.dtoToUserDTO(userDTO);user.setStatus(StatusEnum.生效.code);userMapper.insert(user);userDTO.setId(user.getId());retu userDTO;}@Overridepublic UserDTO findById(Long id) {User user = userMapper.selectById(id);retu user == null ? null : BeanUtils.userToDTO(user);}@Overridepublic List<UserDTO> list() {List<User> users = userMapper.selectList(null);List<UserDTO> list = null;if (!EmptyUtil.isEmpty(users)) {list = users.stream().map(BeanUtils::userToDTO).collect(Collectors.toList());}retu list;}}

1.4.5.2、CompanyServiceImpl.java

@Servicepublic class CompanyServiceImpl implements CompanyService {@Resourceprivate CompanyMapper companyMapper;@Overridepublic CompanyDTO insert(CompanyDTO companyDTO) {Company company = BeanUtils.dtoToCompany(companyDTO);company.setStatus(StatusEnum.生效.code);companyMapper.insert(company);companyDTO.setId(company.getId());retu companyDTO;}@Overridepublic CompanyDTO findById(Long id) {Company company = companyMapper.selectById(id);retu company == null ? null : BeanUtils.companyToDTO(company);}@Overridepublic List<CompanyDTO> list() {List<Company> companys = companyMapper.selectList(null);List<CompanyDTO> list = null;if (!EmptyUtil.isEmpty(companys)) {list = companys.stream().map(BeanUtils::companyToDTO).collect(Collectors.toList());}retu list;}}

1.4.5.3、DemoEntityServiceImpl.java

@Service@Slf4jpublic class DemoEntityServiceImpl implements DemoEntityService {@Resourceprivate DemoEntityMapper demoEntityMapper;@Resourceprivate CompanyService companyService;@Resourceprivate UserService userService;@Overridepublic DemoDTO insert(DemoDTO demoDTO) {DemoEntity demoEntity = BeanUtils.dtoToDemo(demoDTO);demoEntity.setStatus(StatusEnum.生效.code);demoEntityMapper.insert(demoEntity);demoDTO.setId(demoEntity.getId());retu demoDTO;}@Overridepublic DemoDTO findById(Long id) {DemoEntity demoEntity = demoEntityMapper.selectById(id);retu demoEntity == null ? null : BeanUtils.demoToDTO(demoEntity);}@Overridepublic List<DemoDTO> list() {List<DemoDTO> collect = null;List<DemoEntity> list = demoEntityMapper.selectList(null);if (!EmptyUtil.isEmpty(list)) {collect = list.stream().map(BeanUtils::demoToDTO).collect(Collectors.toList());}retu collect;}}

1.4.6、Controller

1.4.6.1、UserController.java

@ApiResponses(value = {@ApiResponse(code = 200, message = "访问正常"),@ApiResponse(code = 301, message = "逻辑错误"),@ApiResponse(code = 500, message = "系统错误"),@ApiResponse(code = 401, message = "未认证"),@ApiResponse(code = 403, message = "禁止访问"),@ApiResponse(code = 404, message = "url错误")})@Api(description = "demo控制器")@Controller@RequestMapping("hlj/demo")@Slf4jpublic class UserController {@Autowiredprivate UserService userService;@ApiOperation(value = "insert",notes = "insert",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@PostMapping(value = "insert", produces = "application/json; charset=utf-8")@ResponseBodypublic ResponseBean insert(UserDTO userDTO) {log.info("样例--------mybaits-plus添加demo实体------数据信息{}", userDTO);String validate = ValidateUtils.validate(userDTO, ValidateGroup.HealerJean.class);if (!validate.equals(CommonConstants.COMMON_SUCCESS)) {throw new BusinessException(ResponseEnum.参数错误, validate);}retu ResponseBean.buildSuccess(userService.insert(userDTO));}@ApiOperation(notes = "findById",value = "findById",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@ApiImplicitParams({@ApiImplicitParam(name = "id", value = "demo主键", required = true, paramType = "path", dataType = "long"),})@GetMapping("findById/{id}")@ResponseBodypublic ResponseBean findById(@PathVariable Long id) {log.info("样例--------findById------数据：id：{}", id);retu ResponseBean.buildSuccess(userService.findById(id));}@ApiOperation(notes = "list",value = "list",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@GetMapping("list")@ResponseBodypublic ResponseBean list() {log.info("样例--------list------");retu ResponseBean.buildSuccess(userService.list());}}

1.4.6.2、CompanyController.java

@ApiResponses(value = {@ApiResponse(code = 200, message = "访问正常"),@ApiResponse(code = 301, message = "逻辑错误"),@ApiResponse(code = 500, message = "系统错误"),@ApiResponse(code = 401, message = "未认证"),@ApiResponse(code = 403, message = "禁止访问"),@ApiResponse(code = 404, message = "url错误")})@Api(description = "demo控制器")@Controller@RequestMapping("hlj/company")@Slf4jpublic class CompanyController {@Autowiredprivate CompanyService companyService;@ApiOperation(value = "insert",notes = "insert",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@PostMapping(value = "insert", produces = "application/json; charset=utf-8")@ResponseBodypublic ResponseBean insert(CompanyDTO companyDTO) {log.info("user--------insert------请求参数：{}", companyDTO);retu ResponseBean.buildSuccess(companyService.insert(companyDTO));}@ApiOperation(notes = "findById",value = "findById",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@ApiImplicitParams({@ApiImplicitParam(name = "id", value = "demo主键", required = true, paramType = "path", dataType = "long"),})@GetMapping("findById/{id}")@ResponseBodypublic ResponseBean findById(@PathVariable Long id) {log.info("company--------findById------id：{}", id);retu ResponseBean.buildSuccess(companyService.findById(id));}@ApiOperation(notes = "list",value = "list",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@GetMapping("list")@ResponseBodypublic ResponseBean list() {log.info("company--------list------");retu ResponseBean.buildSuccess(companyService.list());}}

1.4.6.3、DemoController.java

@ApiResponses(value = {@ApiResponse(code = 200, message = "访问正常"),@ApiResponse(code = 301, message = "逻辑错误"),@ApiResponse(code = 500, message = "系统错误"),@ApiResponse(code = 401, message = "未认证"),@ApiResponse(code = 403, message = "禁止访问"),@ApiResponse(code = 404, message = "url错误")})@Api(description = "demo控制器")@Controller@RequestMapping("hlj/demo")@Slf4jpublic class DemoController {@Autowiredprivate DemoEntityService demoEntityService;@ApiOperation(value = "insert",notes = "insert",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@PostMapping(value = "insert", produces = "application/json; charset=utf-8")@ResponseBodypublic ResponseBean insert(DemoDTO demoDTO) {log.info("demo--------insert------请求参数：{}", demoDTO);retu ResponseBean.buildSuccess(demoEntityService.insert(demoDTO));}@ApiOperation(notes = "findById",value = "findById",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@ApiImplicitParams({@ApiImplicitParam(name = "id", value = "demo主键", required = true, paramType = "path", dataType = "long"),})@GetMapping("findById/{id}")@ResponseBodypublic ResponseBean findById(@PathVariable Long id) {log.info("demo--------findById------id：{}", id);retu ResponseBean.buildSuccess(demoEntityService.findById(id));}@ApiOperation(notes = "list",value = "list",consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,produces = MediaType.APPLICATION_JSON_VALUE,response = UserDTO.class)@GetMapping("list")@ResponseBodypublic ResponseBean list() {log.info("demo--------list------");retu ResponseBean.buildSuccess(demoEntityService.list());}}

1.4.6、自定义分表算法 CustomShardingTableAlgorithm

@Slf4jpublic class CustomShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {for (String tableName : availableTargetNames) {if (tableName.endsWith(shardingValue.getValue() % 3 + "")) {log.info("表为：{}, 主键为：{}, 最终被分到的表为：{}", availableTargetNames, shardingValue, tableName);retu tableName;}}throw new IllegalArgumentException();}}

1.5、开始测试

1.5.1、UserController测试

1.5.1、插入测试：/hlj/user/insert

1.5.1.1、测试数据

name	city
1	a
2	b
3	c
4	d
5	e
6	f

1.5.1.2、观察数据库ds_0中所有user表的数据

user_0

user_1

user_2

1.5.1.3、归纳总结：

虽然上面的分布不均衡，但是足以证明，分表成功。分布不均衡是因为测试数据太少的缘故

1.5.2、CompanyController测试

说明：和1.5.1、UserController测试结果相同无需测试

1.5.3、DemoController：默认数据源

以上分表只有一个数据库，也就是只有一个数据源。所以测试成功，自行测试吧