分库分表之_分库分表+读写分离

雨点打透心脏的1/2处 2023-07-20 12:30 23阅读 0赞

**前言**

Github：[https://github.com/HealerJean][https_github.com_HealerJean]

博客：[http://blog.healerjean.com][http_blog.healerjean.com]

# 1、开始Demo #

## 1.1、`hlj-07-sharding_db_table-read_write.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 ds0slave;
    create database ds0slave character set 'utf8' collate 'utf8_general_ci';
    use ds0slave;
    
    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;
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    drop database if exists ds1slave;
    create database ds1slave character set 'utf8' collate 'utf8_general_ci';
    use ds1slave;
    
    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、数据库图文 ###

![1585560832811][]

## 1.2、依赖 ##

<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=master0,master0slave,master1,master1slave
    ## 默认数据源指定（不分库的表）
    spring.shardingsphere.sharding.default-data-source-name=ds0
    
    
    # 数据源
    spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3306/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.master0.username=root
    spring.shardingsphere.datasource.master0.password=123456
    
    spring.shardingsphere.datasource.master0slave.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.master0slave.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master0slave.url=jdbc:mysql://localhost:3306/ds0slave?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.master0slave.username=root
    spring.shardingsphere.datasource.master0slave.password=123456
    
    spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master1.url=jdbc:mysql://localhost:3306/ds_1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.master1.username=root
    spring.shardingsphere.datasource.master1.password=123456
    
    spring.shardingsphere.datasource.master1slave.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.master1slave.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.master1slave.url=jdbc:mysql://localhost:3306/ds1slave?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    spring.shardingsphere.datasource.master1slave.username=root
    spring.shardingsphere.datasource.master1slave.password=123456
    
    # 读写分离
    spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
    spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave
    
    spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
    spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave
    
    
    # 分库配置
    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、开始测试 ##

> 测试成功

![ContactAuthor][]

[https_github.com_HealerJean]: https://github.com/HealerJean
[http_blog.healerjean.com]: http://HealerJean.github.io
[1585560832811]: /images/20230528/646e4cae138541d6bb3f2ebe612f2385.png
[ContactAuthor]: /images/20230528/5044b2d826954ca8a93b33c6592bac54.png