分库分表之_分库分表

原创

HealerJean 2022-08-31 19:56:47 博主文章分类：Database ©著作权

文章标签 spring 修改时间主键 文章分类 运维

©著作权归作者所有：来自51CTO博客作者HealerJean的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

Github：https://github.com/HealerJean

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

1、开始Demo

1.1、`hlj-06-sharding_db_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,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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 '企业英文名称',
    status               int(10)             not null default '0' comment '状态',
    create_time          datetime            not null default current_timestamp comment '创建时间',
    update_time          datetime            not 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 '企业英文名称',
    status               int(10)             not null default '0' comment '状态',
    create_time          datetime            not null default current_timestamp comment '创建时间',
    update_time          datetime            not 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` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_user` bigint(16) unsigned          DEFAULT NULL COMMENT '更新人',
    `update_name` varchar(64)                  DEFAULT '' COMMENT '更新人名称',
    `update_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;








drop database if exists ds_1;
create database ds_1 character set 'utf8' collate 'utf8_general_ci';
use ds_1;

drop table if exists user_0;
create table `user_0`
(
    `id`        bigint(20) unsigned not null,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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,
    city        varchar(20)         not null default '',
    name        varchar(20)         not null default '',
    status      int(10)             not null default '0' comment '状态',
    create_time datetime            not null default current_timestamp comment '创建时间',
    update_time datetime            not 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 '企业英文名称',
    status               int(10)             not null default '0' comment '状态',
    create_time          datetime            not null default current_timestamp comment '创建时间',
    update_time          datetime            not 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 '企业英文名称',
    status               int(10)             not null default '0' comment '状态',
    create_time          datetime            not null default current_timestamp comment '创建时间',
    update_time          datetime            not 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的一些配置，也可以在 application.properties 中配置，如果配置了就不需要了mybatis.xml
#mybatis-plus.config-location=classpath:mybatis.xml
#Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）
mybatis-plus.mapper-locations=classpath*:mapper/*.xml
mybatis-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: true
mybatis-plus.refresh-mapper: true

##############################
## 分库分表
#############################
# #当遇到同样名字的时候，是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true
# 显示SQL
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
## 默认数据源指定（不分库的表在这个里面）
spring.shardingsphere.sharding.default-data-source-name=ds0


# 数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds_1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456


# 分库配置
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
#spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.healerjean.proj.config.datasource.CustomShardingDBAlgorithm


# user  company 分表
# user_0,user_1,user_2（自定义分表算法）
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..2}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.healerjean.proj.config.datasource.CustomShardingTableAlgorithm
# company_0,company_1 （inline分表策略 表达式 id%2）
spring.shardingsphere.sharding.tables.company.actual-data-nodes=ds$->{0..1}.company_$->{0..1}
spring.shardingsphere.sharding.tables.company.table-strategy.inline.sharding-column=id
spring.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`

@Data
public 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(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
    private Date createTime;

    @ApiModelProperty(value = "修改时间", hidden = true)
    @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
    private Date updateTime;

}

1.4.2.2、`CompanyDTO.java`

@Data
public class CompanyDTO {

  @JsonSerialize(using = JsonLongSerializer.class )
  private Long id;
  private String name;
  private String companyNameEnglish;
  private String status;


  @ApiModelProperty(value = "创建时间", hidden = true)
  @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
  private Date createTime;

  @ApiModelProperty(value = "修改时间", hidden = true)
  @JsonFormat(pattern = 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
@Slf4j
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;


    @Override
    public UserDTO insert(UserDTO userDTO) {
        User user = BeanUtils.dtoToUserDTO(userDTO);
        user.setStatus(StatusEnum.生效.code);
        userMapper.insert(user);
        userDTO.setId(user.getId());
        return userDTO;
    }

    @Override
    public UserDTO findById(Long id) {
        User user = userMapper.selectById(id);
        return user == null ? null : BeanUtils.userToDTO(user);
    }

    @Override
    public 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());
        }
        return list;
    }

}

1.4.5.2、`CompanyServiceImpl.java`

@Service
public class CompanyServiceImpl implements CompanyService {

    @Resource
    private CompanyMapper companyMapper;

    @Override
    public CompanyDTO insert(CompanyDTO companyDTO) {
        Company company = BeanUtils.dtoToCompany(companyDTO);
        company.setStatus(StatusEnum.生效.code);
        companyMapper.insert(company);
        companyDTO.setId(company.getId());
        return companyDTO;
    }

    @Override
    public CompanyDTO findById(Long id) {
        Company company = companyMapper.selectById(id);
        return company == null ? null : BeanUtils.companyToDTO(company);
    }

    @Override
    public 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());
        }
        return list;
    }
}

1.4.5.3、`DemoEntityServiceImpl.java`

@Service
@Slf4j
public class DemoEntityServiceImpl implements DemoEntityService {


    @Resource
    private DemoEntityMapper demoEntityMapper;

    @Resource
    private CompanyService companyService;
    @Resource
    private UserService userService;

    @Override
    public DemoDTO insert(DemoDTO demoDTO) {
        DemoEntity demoEntity = BeanUtils.dtoToDemo(demoDTO);
        demoEntity.setStatus(StatusEnum.生效.code);
        demoEntityMapper.insert(demoEntity);
        demoDTO.setId(demoEntity.getId());
        return demoDTO;
    }


    @Override
    public DemoDTO findById(Long id) {
        DemoEntity demoEntity = demoEntityMapper.selectById(id);
        return demoEntity == null ? null : BeanUtils.demoToDTO(demoEntity);
    }

    @Override
    public 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());
        }
        return 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")
@Slf4j
public class UserController {



    @Autowired
    private 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")
    @ResponseBody
    public 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);
        }
        return 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}")
    @ResponseBody
    public ResponseBean findById(@PathVariable Long id) {
        log.info("样例--------findById------数据：id：{}", id);
        return 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")
    @ResponseBody
    public ResponseBean list() {
        log.info("样例--------list------");
        return 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")
@Slf4j
public class CompanyController {


    @Autowired
    private 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")
    @ResponseBody
    public ResponseBean insert(CompanyDTO companyDTO) {
        log.info("user--------insert------请求参数：{}", companyDTO);
        return 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}")
    @ResponseBody
    public ResponseBean findById(@PathVariable Long id) {
        log.info("company--------findById------id：{}", id);
        return 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")
    @ResponseBody
    public ResponseBean list() {
        log.info("company--------list------");
        return 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")
@Slf4j
public class DemoController {

    @Autowired
    private 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")
    @ResponseBody
    public ResponseBean insert(DemoDTO demoDTO) {
        log.info("demo--------insert------请求参数：{}", demoDTO);
        return 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}")
    @ResponseBody
    public ResponseBean findById(@PathVariable Long id) {
        log.info("demo--------findById------id：{}", id);
        return 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")
    @ResponseBody
    public ResponseBean list() {
        log.info("demo--------list------");
        return ResponseBean.buildSuccess(demoEntityService.list());
    }

}

1.4.6、自定义分表算法 `CustomShardingTableAlgorithm`

@Slf4j
public class CustomShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {

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

1.4.7、自定义分库算法：`CustomShardingDBAlgorithm`

@Slf4j
public class CustomShardingDBAlgorithm implements PreciseShardingAlgorithm<Long> {


    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String dbName : availableTargetNames) {
            if (dbName.endsWith(shardingValue.getValue() % 2 + "")) {
                log.info("库为：{}, 主键为：{}, 最终被分到的库为：【{}】", availableTargetNames, shardingValue, dbName);
                return dbName;
            }
        }
        throw new IllegalArgumentException();
    }
}

1.5、开始测试

测试成功

分库分表之_分库分表_主键_02

上一篇：分库分表之_只分表不分库

下一篇：分库分表之_垂直拆分（不同的表在不同的库中）+ 读写分离

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

分库分表之_分库分表

分库分表之_分库分表

1、开始Demo

1.1、​​hlj-06-sharding_db_table.sql​​

1.1.1、数据库图文

1.2、依赖

1.3、配置文件：​​application.properties​​

1.4、具体测试方法和类

1.4.1、实体类

1.4.1.1、​​User.java​​

1.4.1.2、​​Company.java​​

1.4.1.3、​​DemoEntity.java​​

1.4.2、DTO数据

1.4.2.1、​​UserDTO.java​​

1.4.2.2、​​CompanyDTO.java​​

1.4.2.3、​​DemoDTO.java​​

1.4.3、Mapper

1.4.3.1、​​UserMapper.java​​

1.4.3.1、​​CompanyMapper.java​​

1.4.3.1、​​DemoEntityMapper.java​​

1.4.4、​​Service​​

1.4.4.1、 ​​UserService.java​​

1.4.4.2、 ​​CompanyService.java​​

1.4.4.3、 ​​DemoEntityService.java​​

1.4.5、​​ServiceImpl.java​​

1.4.5.1、​​UserServiceImpl.java​​

1.4.5.2、​​CompanyServiceImpl.java​​

1.4.5.3、​​DemoEntityServiceImpl.java​​

1.4.6、​​Controller​​

1.4.6.1、​​UserController.java​​

1.4.6.2、​​CompanyController.java​​

1.4.6.3、​​DemoController.java​​

1.4.6、自定义分表算法 ​​CustomShardingTableAlgorithm​​

1.4.7、自定义分库算法：​​CustomShardingDBAlgorithm​​

1.5、开始测试

51CTO博客

1.1、`hlj-06-sharding_db_table.sql`

1.3、配置文件：`application.properties`

1.4.1.1、`User.java`

1.4.1.2、`Company.java`

1.4.1.3、`DemoEntity.java`

1.4.2.1、`UserDTO.java`

1.4.2.2、`CompanyDTO.java`

1.4.2.3、`DemoDTO.java`

1.4.3.1、`UserMapper.java`

1.4.3.1、`CompanyMapper.java`

1.4.3.1、`DemoEntityMapper.java`

1.4.4、`Service`

1.4.4.1、 `UserService.java`

1.4.4.2、 `CompanyService.java`

1.4.4.3、 `DemoEntityService.java`

1.4.5、`ServiceImpl.java`

1.4.5.1、`UserServiceImpl.java`

1.4.5.2、`CompanyServiceImpl.java`

1.4.5.3、`DemoEntityServiceImpl.java`

1.4.6、`Controller`

1.4.6.1、`UserController.java`

1.4.6.2、`CompanyController.java`

1.4.6.3、`DemoController.java`

1.4.6、自定义分表算法 `CustomShardingTableAlgorithm`

1.4.7、自定义分库算法：`CustomShardingDBAlgorithm`