Sharding-JDBC 实现水平分表

1、搭建环

（1） 技术： SpringBoot2.2.1+ MyBatisPlus + Sharding-JDBC + Druid 连接池
（2）创建 SpringBoot 工程 

​

 （3）修改工程 SpringBoot 版本 2.2.1

​

引入依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

2、 按照水平分表的方式， 创建数据库和数据库表
（1）创建数据库 course_db
（2）在数据库创建两张表 course_1 和 course_2
（3）约定规则：如果添加课程 id 是偶数把数据添加 course_1，如果奇数添加到 course_2

CREATE TABLE `course_db`.`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 `course_db`.`course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NULL,
  `user_id` bigint(20) NULL,
  `cstatus` varchar(10) NULL,
  PRIMARY KEY (`cid`)
);

3.创建实体类和mapper

Course.java

package com.weianlai.shardingjdbc.entity;

import lombok.Data;


@Data
public class Course{

    private static final long serialVersionUID = 1L;

    private Long cid;

    private String cname;

    private Long userId;

    private String cstatus;

}

CourseMapper.java

package com.weianlai.shardingjdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.weianlai.shardingjdbc.entity.Course;
import org.springframework.stereotype.Repository;

@Repository
public interface CourseMapper extends BaseMapper<Course> {
}

启动类扫描mapper

@MapperScan("com.weianlai.shardingjdbc.mapper")

4、配置 Sharding-JDBC 分片策略
（1）在项目 application.properties 配置文件中进行配置

​

## 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1
## 一个实体类对应两张表，覆盖
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/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

#指定 course 表分布情况，配置表在哪个数据库里面，表名称都是什么 m1.course_1 ,m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

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

# 指定分片策略 约定 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

5、编写测试代码

package com.weianlai.shardingjdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.weianlai.shardingjdbc.entity.Course;
import com.weianlai.shardingjdbc.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
class ShardingJdbcDemoApplicationTests {

    @Autowired
    CourseMapper courseMapper;

    @Test
    public void addCourse() {
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCname("java");
            course.setUserId(100L);
            course.setCstatus("Normal");
            courseMapper.insert(course);
        }
    }

    @Test
    public void findCourse() {
        QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("cid", 581259549126688769L);
        Course course = courseMapper.selectOne(queryWrapper);
        System.out.println("coursr=" + course);
    }

}