本文共 6849 字，大约阅读时间需要 22 分钟。

1 Sharding-JDBC简介

是轻量级的java框架，是增强版的JDBC驱动，

2 Sharding-JDBC

Sharding-JDBC 不是做分库分表
主要目的是简化对分库分表之后相关数据操作
主要做两个功能 数据分片和读写分离

3 Sharding-JDBC实现水平分表

3.1 搭建环境

3.1.1 总体概览

SpringBoot + MybaitsPlus + Sharding-JDBC + Druid 连接池

3.1.2 创建SpringBoot工程

pom.xml 如下：

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">    <modelVersion>4.0.0</modelVersion>    <parent>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-parent</artifactId>        <version>2.3.1.RELEASE</version>        <relativePath/> <!-- lookup parent from repository -->    </parent>    <groupId>com.ccb</groupId>    <artifactId>shardingsphere</artifactId>    <version>0.0.1-SNAPSHOT</version>    <name>shardingsphere</name>    <description>Sharding sphere project for Spring Boot</description>    <properties>        <java.version>1.8.251</java.version>    </properties>    <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>            <version>8.0.15</version>        </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>    <build>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>            </plugin>        </plugins>    </build></project>

3.1.3 按照水平分表的方式，创建数据库和数据库表

创建数据库 coursedb

CREATE SCHEMA `coursedb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

创建数据库表 course1 course2

CREATE TABLE `coursedb`.`course1` (  `cid` BIGINT NOT NULL,  `cname` VARCHAR(45) NOT NULL,  `user_id` BIGINT NOT NULL,  `cstatus` VARCHAR(45) NOT NULL,  PRIMARY KEY (`cid`));

CREATE TABLE `coursedb`.`course2` (  `cid` BIGINT NOT NULL,  `cname` VARCHAR(45) NOT NULL,  `user_id` BIGINT NOT NULL,  `cstatus` VARCHAR(45) NOT NULL,  PRIMARY KEY (`cid`));

约定规则 如果添加课程id为奇数把数据添加course2，如果添加课程id为偶数把数据添加到course1

3.2 代码实现

3.2.1 创建po实体类

package com.ccb.sharding.po;public class Course {       private Long cid;    private String cname;    private Long userId;    private String cstatus;    public Long getCid() {           return cid;    }    public void setCid(Long cid) {           this.cid = cid;    }    public String getCname() {           return cname;    }    public void setCname(String cname) {           this.cname = cname;    }    public Long getUserId() {           return userId;    }    public void setUserId(Long userId) {           this.userId = userId;    }    public String getCstatus() {           return cstatus;    }    public void setCstatus(String cstatus) {           this.cstatus = cstatus;    }        @Override    public String toString() {           return "Course{" +                "cid=" + cid +                ", cname='" + cname + '\'' +                ", userId=" + userId +                ", cstatus='" + cstatus + '\'' +                '}';    }}

3.2.2 创建mapper 接口

package com.ccb.sharding.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.ccb.sharding.po.Course;import org.springframework.stereotype.Repository;@Repositorypublic interface CourseMapper extends BaseMapper<Course> {   }

3.2.3 添加到MapperScan扫描

package com.ccb.sharding;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication@MapperScan("com.ccb.sharding.mapper")public class ShardingApplication {       public static void main(String[] args) {           SpringApplication.run(ShardingApplication.class, args);    }}

3.3 配置分片策略

在application.properties文件中增加如下配置

# sharding-JDBC分片策略# 配置数据源，给数据源命名spring.shardingsphere.datasource.names=ds1# 配置数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/coursedb?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=chengwen# 一个实体类对应两张表，覆盖spring.main.allow-bean-definition-overriding=true# 指定数据表course分布情况，配置表在哪个数据库里面，表名称都是什么spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds1.course$->{1..2}# 指定course 表里面主键cid 生成策略 SNOWFLAKE 雪花算法spring.shardingsphere.sharding.tables.course.key-generator.column=cidspring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE#指定分片策略 约定cid的值偶数添加到course1表，奇数添加到course2表spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cidspring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course$->{cid % 2 + 1}# 打印sql输出日志spring.shardingsphere.properties.sql.show=true

3.4 Test

package com.ccb.sharding;import com.ccb.sharding.mapper.CourseMapper;import com.ccb.sharding.po.Course;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;@SpringBootTestclass ShardingApplicationTests {       @Autowired    CourseMapper courseMapper;    @Test    public void addCourse() {           for (int i = 1 ; i <= 10 ; i ++){               Course course = new Course();            course.setCname("Java" + i);            course.setCstatus("Normal" + i);            course.setUserId(100L);            courseMapper.insert(course);        }    }        @Test    public void getCourse(){           QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.eq("cid",481545888309706752L);        Course course = courseMapper.selectOne(queryWrapper);        System.out.println(course);    }}

3.5 Test result

分表1

分表2

getCourse