Sharding-JDBC 实现水平分库分表

1、需求分析

​

CREATE TABLE `edu_db_1`.`course_1`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NULL,
  `user_id` bigint(20) NULL,
  `cstatus` varchar(10) NULL,
  PRIMARY KEY (`cid`)
);

CREATE TABLE `edu_db_1`.`course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NULL,
  `user_id` bigint(20) NULL,
  `cstatus` varchar(10) NULL,
  PRIMARY KEY (`cid`)
);

CREATE TABLE `edu_db_2`.`course_1`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NULL,
  `user_id` bigint(20) NULL,
  `cstatus` varchar(10) NULL,
  PRIMARY KEY (`cid`)
);

CREATE TABLE `edu_db_2`.`course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NULL,
  `user_id` bigint(20) NULL,
  `cstatus` varchar(10) NULL,
  PRIMARY KEY (`cid`)
);

3、在 SpringBoot 配置文件配置数据库分片规则

## 配置数据源，给数据源起名称
# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=m1,m2
## 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
##配置第一个数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

##配置第二个数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

#指定数据库分布情况，数据库里面表分布情况
# m1 m2 course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# 指定 course 表里面主键 cid 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定数据库分片策略 约定 user_id 是偶数添加 m1，是奇数添加 m2
#spring.shardingsphere.sharding.default-database-strategy.inline.shardingcolumn=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithmexpression=m$->{user_id % 2 + 1}
spring.shardingsphere.sharding.tables.course.database-strategy.inline..sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 指定表分片策略 约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到 course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

 4、编写测试方法

 

    //添加操作
    @Test
    public void addCourseDb() {
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCname("javademo" + i);
            //分库根据 user_id
            course.setUserId(100L + i);
            course.setCstatus("Normal1");
            courseMapper.insert(course);
        }
    }
    //查询操作
    @Test
    public void findCourseDb() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        //设置 userid 值
        wrapper.eq("user_id",101L);
        //设置 cid 值
        wrapper.eq("cid",581615031192387584L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }