[Java知识库]Sharding-JDBC 实现水平分表

一、搭建环境

技术：SpringBoot2.5.6?+ MybatisPlus3.4.1 + shardingsphere5.0.0 + Druid1.2.1连接池

1、在项目的pom.xml中引入maven依赖：

<?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 http://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.5.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sharding.demo</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
        <commons.lang3.version>3.12.0</commons.lang3.version>
        <lombok.version>1.18.16</lombok.version>
        <guava.version>20.0</guava.version>
        <fastjson.version>1.2.75</fastjson.version>
        <hutool.version>5.5.1</hutool.version>
        <poi-ooxml.version>4.1.2</poi-ooxml.version>
        <!--升级log4j2版本,避免漏洞问题-->
        <log4j2.version>2.16.0</log4j2.version>
        <commons-pool2.version>2.11.0</commons-pool2.version>
        <shardingsphere.version>5.0.0</shardingsphere.version>
        <mysql-connector-java.version>8.0.28</mysql-connector-java.version>
        <druid.version>1.2.1</druid.version>
        <mybatis-plus.version>3.4.1</mybatis-plus.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <!--不能使用这个druid-spring-boot-starter，否则回合sharding-jdbc冲突-->
        <!--<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>
        <!--引入shardingsphere-jdbc-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions> <!-- 去除springboot默认的logback配置-->
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
<!--            <optional>true</optional>-->
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons.lang3.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>
        <!--工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
        <!--引入log4j2依赖-->
        <!-- 加入log4j2配置 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>3.1.0</version>
            </plugin>
        </plugins>
    </build>
</project>

2、按照水平分表的方式创建数据库、数据表

• 创建数据库 course_db。
• 在数据库中创建两张表 course_0?和 course_1。
• 数据存放约定规则：添加的数据id为偶数放 course_0?表中，id为奇数放 course_1?表中。

create database course_db;

use course_db;

create table course_0 (
	`cid` bigint(20) primary key,
	`cname` varchar(50) not null,
	`user_id` bigint(20) not null,
	`cstatus` varchar(10) not null
);

create table course_1 (
	`cid` bigint(20) primary key,
	`cname` varchar(50) not null,
	`user_id` bigint(20) not null,
	`cstatus` varchar(10) not null
);

?

3、在application.yml中定义公共的数据源，以及mybatis-plus，并且引入sharding-shphere文件

spring:
  application:
    name: kgf-java-learning2021
  profiles:
    active: sharding
  datasource:
    url: jdbc:mysql://192.168.56.20:3306/course_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
    username: root
    password: xxxxxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    initial-size: 5 #初始连接数
    min-idle: 10 #最小连接池数量
    max-active: 20 #最大连接池数量
    max-wait: 60000 #配置获取连接等待超时的时间
    time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    min-evictable-idle-time-millis: 300000 #配置一个连接在池中最小生存的时间，单位是毫秒
    max-evictable-idle-time-millis: 900000 #配置一个连接在池中最大生存的时间，单位是毫秒
    validation-query: SELECT 1 FROM DUAL #配置检测连接是否有效
    test-while-idle: true #建议配置为true，不影响性能，并且保证安全性申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
    test-on-borrow: false #这里建议配置为TRUE，防止取到的连接不可用
    test-on-return: false #归还连接时执行validationQuery检测连接是否有效
    filters: stat,wall,slf4j #设置使用哪些插件 stat是统计，wall是SQL防火墙，防SQL注入的，log4j是用来输出统计数据的
    pool-prepared-statements: false #是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
    max-pool-prepared-statement-per-connection-size: 0
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 #通过connectProperties属性来打开mergeSql功能；慢SQL记录

server:
  port: 8080

logging:
  config: classpath:log4j2.xml

mybatis-plus: #注意：延迟加载不能在开启debug后，不能点开list信息，甚至不能把鼠标移到上面。否则在debug下会默认执行关联查询。
  mapper-locations: classpath:/mappers/**/*.xml
  type-aliases-package: com.sharding.demo
  configuration:
    aggressive-lazy-loading: false
    lazy-loading-enabled: true

4、定义上面引入的application-sharding.yml文件

spring:
  shardingsphere:
    # 数据源配置
    datasource:
      # 数据源名称，多数据源以逗号分隔，名称可以随意起名
      names: db01
      db01:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        password: xxxxxx
        username: root
        url: jdbc:mysql://192.168.56.20:3306/course_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    # 规则配置
    rules:
      sharding:
        # 分片算法配置
        sharding-algorithms:
          table-inline:
            type: INLINE # 分片算法类型
            props:
              # 分片算法的行表达式
              algorithm-expression: course_$->{cid % 2}
        # 分布式序列算法配置
        key-generators:
          snowflake:
            type: snowflake #分布式序列算法类型,雪花算法：SNOWFLAKE； UUID：UUID）
            column: cid #分布式主键列
        tables:
          # 逻辑表名称
          course:
            # 行表达式标识符可以使用 ${...} 或 $->{...}，但前者与 Spring 本身的属性文件占位符冲突，因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
            actual-data-nodes: db01.course_${0..1}
            # 分表策略
            table-strategy:
              standard:
                # 分片列名称
                sharding-column: cid
                # 分片算法名称
                sharding-algorithm-name: table-inline
            key-generate-strategy:
              column: cid #分布式序列列名称
              key-generator-name: snowflake #分布式序列算法名称
    # 属性配置
    props:
      # 展示修改以后的sql语句
      sql-show: true

二、编写代码实现对分表数据的操作?

1、创建对应course的实体类

2、创建启动类扫描

?3、创建controller类

package com.sharding.demo.web;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sharding.demo.model.Course;
import com.sharding.demo.service.CourseService;
import org.apache.commons.lang.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

@RestController
@RequestMapping(value = "course")
public class CourseController {

    @Resource
    private CourseService courseService;

    @RequestMapping(value = "addCourse")
    public void addCourse(){
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            //cid由我们设置的策略，雪花算法进行生成（至少70年内生成的id不会重复）
            course.setCname("java"+i);
            course.setUserId(Long.valueOf(i));
            course.setCstatus(i%2==0?String.valueOf(1):String.valueOf(0));
            courseService.addCourse(course);
        }
    }

    @RequestMapping(value = "updateCourseByParam")
    public String updateCourseByParam(Course course){
        return courseService.updateCourseByParam(course);
    }

    @RequestMapping(value = "delCourseByParam")
    public String delCourseByParam(Course course){
        return courseService.delCourseByParam(course);
    }

    @RequestMapping(value = "queryCourseByParams")
    public List<Course> queryCourseByParams(Course course){
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(course.getCname())){
            wrapper.eq("cname", course.getCname());
        }
        if (StringUtils.isNotBlank(course.getCstatus())){
            wrapper.eq("cstatus", course.getCstatus());
        }
        if (null!=course.getCid()){
            wrapper.eq("cid", course.getCid());
        }
        if (null!=course.getUserId()){
            wrapper.eq("user_id", course.getUserId());
        }
        return courseService.findCourse(wrapper);
    }

}

4、创建service接口类

package com.sharding.demo.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sharding.demo.model.Course;

import java.util.List;

public interface CourseService {
    void addCourse(Course course);

    List<Course> findCourse(QueryWrapper<Course> wrapper);

    String updateCourseByParam(Course course);

    String delCourseByParam(Course course);
}

5、创建service的实现类

package com.sharding.demo.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sharding.demo.mapper.CourseMapper;
import com.sharding.demo.model.Course;
import com.sharding.demo.service.CourseService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class CourseServiceImpl implements CourseService {


    @Resource
    private CourseMapper courseMapper;

    @Override
    public void addCourse(Course course) {
        courseMapper.insert(course);
    }

    @Override
    public List<Course> findCourse(QueryWrapper<Course> wrapper) {
        List<Course> courses = courseMapper.selectList(wrapper);
        return courses;
    }

    @Override
    public String updateCourseByParam(Course course) {
        courseMapper.updateCourseByParam(course);
        return "更新成功!";
    }

    @Override
    public String delCourseByParam(Course course) {
        courseMapper.delCourseByParam(course);
        return "删除成功!";
    }
}

6、创建CourseMapper接口类

package com.sharding.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sharding.demo.model.Course;

public interface CourseMapper extends BaseMapper<Course> {
    void updateCourseByParam(Course course);

    void delCourseByParam(Course course);
}

7、创建xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.sharding.demo.mapper.CourseMapper">

    <update id="updateCourseByParam" parameterType="com.sharding.demo.model.Course">
        update course
        <set>
            <if test="cname!=null and cname != ''">
                 cname = #{cname},
            </if>
            <if test="cstatus!=null and cstatus != ''">
                cstatus = #{cstatus},
            </if>
            <if test="userId!=null and userId != ''">
                user_id = #{userId},
            </if>
        </set>
        <where>
            <if test="cid!=null and cid != ''">
                cid = #{cid}
            </if>
            <if test="cstatus!=null and cstatus != ''">
                and cstatus = #{cstatus}
            </if>
        </where>
    </update>
    <delete id="delCourseByParam">
        delete from course
        <where>
            <if test="cid!=null and cid != ''">
                cid = #{cid}
            </if>
            <if test="cstatus!=null and cstatus != ''">
                and cstatus = #{cstatus}
            </if>
        </where>
    </delete>
</mapper>

三、使用接口调用，进行测试

1、首先调用新增接口，向course_0和course_1中插入数据

? ? ? ? 路径：http://localhost:8080/course/addCourse

? ? ? ? 效果：

? ? ? ? course_0表：

? ???course_1表：

??

?2、根据条件查询数据

? ? ? ? 路径:?http://localhost:8080/course/queryCourseByParams

?加上条件：

?3、删除数据接口调用

? ? ? ? 路径：http://localhost:8080/course/delCourseByParam?userId=3

?4、调用修改接口

? ? ? ? 路径：http://localhost:8080/course/updateCourseByParam?userid=1&cname=test666