3、ShardingSphere 之 Sharding-JDBC 实现水平分库

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

1.1.1 创建分库1

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

1.1.2 创建分库2

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

1.1.3 创建分库1中的表1

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

1.1.4 创建分库1中的表2

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

1.1.5 创建分库2中的表1

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

1.1.6 创建分库2中的表2

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

1.2 创建SpringBoot工程

1.2.1 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</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>5.1.6</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>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

1.2.2 创建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;
    }

}

1.2.3 创建mapper 接口

package com.ccb.sharding.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ccb.sharding.po.Course;
import org.springframework.stereotype.Repository;

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

1.2.4 添加到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);
    }

}

1.3 配置水平分库策略

# sharding-JDBC分片策略
# 配置数据源，给数据源命名
# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=ds1,ds2

# 配置ds1数据源具体内容，连接池、驱动、地址、用户名和密码
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/edudb1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=chengwen

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

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 指定数据库分布情况，数据库里面表分布情况
# ds1 ds2 course1 course2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{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

# 指定表分片策略 约定cid的值偶数添加到course1表，奇数添加到course2表
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}

# 指定数据库分片策略 约定user_id 的值是偶数添加到ds1库，奇数添加到ds2库
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column==user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{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=ds$->{user_id % 2 + 1}
# 打印sql输出日志
spring.shardingsphere.props.sql.show=true

1.4 测试水平分库

package com.ccb.sharding;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
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;

@SpringBootTest
class ShardingApplicationTests {

    @Autowired
    CourseMapper courseMapper;

    // =============== 测试水平分库 ===============
    @Test
    public void addCourseDB() {
        Course course = new Course();
        course.setCname("Mysql");
        course.setCstatus("Normal");
        course.setUserId(100L);
        courseMapper.insert(course);
    }

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

    @Test
    public void getCourseDB() {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("cid",482547343917318145L);
        queryWrapper.eq("user_id",100L);
        Course course = courseMapper.selectOne(queryWrapper);
        System.out.println(course);
    }
}

1.5 Test Result

1.5.1 分库1表1结果

1.5.2 分库2表2结果

1.5.3 根据cid和user_id从分库分表中查询结果