ShardingSphere之SpingBoot整合Sharding-JDBC分库分表代码展示：垂直拆分、水平拆分

一、垂直拆分

针对职位表进行拆分：职位表、职位详情表
拆分两个库

1、表结构

# 职位表
CREATE TABLE `position`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `salary` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

# 职位详情
CREATE TABLE `position_detail`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `pid` bigint(11) NOT NULL DEFAULT 0,
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、引入依赖

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

3、配置文件

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/shardingsphere1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=000000
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/shardingsphere2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=000000
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver

#sharding-database  垂直拆分 针对职位表 拆分成 职位表与职位详情表 分库策略
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds${id % 2}
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds${pid % 2}
#id
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
#自定义主键生成算法
spring.shardingsphere.sharding.tables.position.key-generator.type=CKWKEYID
spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
#spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=CKWKEYID

4、代码

1. 实体类

@Data
@Entity
@Table(name = "position")
@ToString
public class PositionEntity implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "name")
    private String name;
    
    @Column(name = "salary")
    private String salary;
    
    @Column(name = "city")
    private String city;
    
}

@Data
@ToString
@Entity
@Table(name = "position_detail")
public class PositionDetailEntity implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "pid")
    private long pid;

    @Column(name = "description")
    private String description;
}

2. dao层使用Jpa

public interface PositionRepository extends JpaRepository<PositionEntity,Long> {

    /**
     * @Author: ckw
     * @description: 根据职位id查询 职位表与职位详情表
     * @date: 2022/3/1 16:43
     * @params: id
     * @return 职位信息
     */
    @Query(nativeQuery = true,value = "select position.id,position.name,position.salary,position.city,position_detail.description  " +
            "from position join position_detail on position.id = position_detail.pid where position.id = :id")
    Map<String,Object> findPositionById(@Param("id") Long id);

}

public interface PositionDetailReposition extends JpaRepository<PositionDetailEntity,Long> {
}

3. Sharding-JDBC自定义生成主键策略

public class MyKeyGeneratorId implements ShardingKeyGenerator {

    private final SnowflakeShardingKeyGenerator snowflakeShardingKeyGenerator = new SnowflakeShardingKeyGenerator();

    @Override
    public Comparable<?> generateKey() {
        return snowflakeShardingKeyGenerator.generateKey();
    }

    @Override
    public String getType() {
        return "CKWKEYID";
    }

    @Override
    public Properties getProperties() {
        return null;
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

在resources建META-INF/services文件夹
创建org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator文件
内容为 自定义主键类路径

com.ckw.shardingjdbctest.keyGenerator.MyKeyGeneratorId

5、测试

1. 插入数据

@Test
    public void contextLoads() {
        for (int i = 1; i < 20; i++) {
            PositionEntity position = new PositionEntity();
//            position.setId(i);
            position.setName("ckw");
            position.setSalary("100");
            position.setCity("北京");
            positionRepository.save(position);

            PositionDetailEntity positionDetail = new PositionDetailEntity();
            positionDetail.setPid(position.getId());
            positionDetail.setDescription("------------->>>>>>>>>>>>>");
            positionDetailReposition.save(positionDetail);
        }

    }

2. 查询数据

@Test
    public void test1(){
        Map<String, Object> position = positionRepository.findPositionById(705372604415868928L);
        position.entrySet().forEach(e -> {
            System.out.println(e.getKey() + "--->" +e.getValue());
        });
    }

二、水平拆分：分库分表
针对 投递简历表做分库分表 ds0、ds1,b_order0,b_order1

1、表结构

CREATE TABLE `b_order0`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `company_id` int(10) NOT NULL COMMENT '公司id',
  `position_id` int(10) NOT NULL COMMENT '职位id',
  `user_id` int(10) NOT NULL COMMENT '用户id',
  `publish_user_id` int(10) NOT NULL COMMENT '职位发布者id',
  `resume_type` int(2) NOT NULL DEFAULT 0 COMMENT '简历状态 0附件1在线',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '投递状态',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `operator_time` datetime(0) NOT NULL COMMENT '操作时间',
  `work_year` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作年限',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '投递简历名字',
  `position_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职位名称',
  `resume_id` int(11) NULL DEFAULT NULL COMMENT '投递建立的id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_create_time`(`create_time`) USING BTREE,
  INDEX `index_company_status`(`company_id`, `status`, `is_del`) USING BTREE,
  INDEX `index_cpmId_pub_ctime`(`company_id`, `publish_user_id`, `create_time`) USING BTREE,
  INDEX `index_companyId_positionId`(`company_id`, `position_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、配置文件

上面都一样

# 水平拆分 分库分表 分两个库ds0、ds1  针对订单表进行拆分b_order0、b_order1
# 分库策略
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds${company_id % 2}
# 分表策略
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
# 真实的数据库表
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
# 主键生成策略
spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
spring.shardingsphere.sharding.tables.b_order.key-generator.type=CKWKEYID

3、代码

1. 实体类

@Data
@Entity
@Table(name = "b_order")
@ToString
public class BOrderEntity implements Serializable {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del")
    private Boolean isDel;


    @Column(name = "company_id")
    private Integer companyId;

    @Column(name = "position_id")
    private long positionId;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "publish_user_id")
    private Integer publishUserId;

    @Column(name = "resume_type")
    private Integer resumeType;

    @Column(name = "status")
    private String status;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "operator_time")
    private Date operatorTime;

    @Column(name = "work_year")
    private String workYear;

    @Column(name = "name")
    private String name;

    @Column(name = "position_name")
    private String positionName;

    @Column(name = "resume_id")
    private Integer resumeId;
}

2. 数据访问层

public interface BOrderRepository extends JpaRepository<BOrderEntity,Long> {
}

3. 测试

@Test
    @Repeat(100)
    public void test3(){
            Random random = new Random();
            int companyId = random.nextInt(10);
            BOrderEntity bOrder = new BOrderEntity();
            bOrder.setCompanyId(companyId);
            bOrder.setName("ckw");
            bOrder.setCreateTime(new Date());
            bOrder.setIsDel(false);
            bOrder.setOperatorTime(new Date());
            bOrder.setPositionId(123123);
            bOrder.setPositionName("java");
            bOrder.setPublishUserId(12312);
            bOrder.setStatus("AUTO");
            bOrder.setResumeType(1);
            bOrder.setWorkYear("2");
            bOrder.setUserId(12312);
            bOrder.setResumeId(12312312);
            bOrderRepository.save(bOrder);
    }

三、广播表示例

广播表：所有分库中表数据都一样

CREATE TABLE `city`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `province` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

只需添加配置

#指定广播表
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=CKWKEYID