ShardingSphere-JDBC操作

官方文档：Apache ShardingSphere

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

Apache ShardingSphere 由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。它们均提供标准化的基于数据库作为存储节点的增量功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

结合官方文档，对ShardingSphere-JDBC做一个学习记录。

ShardingSphere-JDBC 内部做好了分装，我们只需要写好我们需要的数据配置，操作如同单库单表一样丝滑。

先创建相关的数据库/表

/*
    先创建四个数据库：course_db、edu_db0、edu_db1、user_db0
 */

CREATE DATABASE course_db;

CREATE DATABASE edu_db1;

CREATE DATABASE edu_db0;

CREATE DATABASE user_db0;


/**
    1、course_db、edu_db0、edu_db1 三个库分别各自都创建 course_0、course_1、common_code
    2、user_db0 中创建user_0、common_code

    说明：common_code 为字典表,每个表都有一模一样的一份

 */


/**
       course_0、course_1
 */

-- ----------------------------
-- Table structure for course_0
-- ----------------------------
DROP TABLE IF EXISTS `course_0`;
CREATE TABLE `course_0`
(
    `id`          bigint(20) NOT NULL,
    `course_name` varchar(255) DEFAULT NULL,
    `user_id`     bigint(20) DEFAULT NULL,
    `status`      int(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Table structure for course_1
-- ----------------------------
DROP TABLE IF EXISTS `course_1`;
CREATE TABLE `course_1`
(
    `id`          bigint(20) NOT NULL,
    `course_name` varchar(255) DEFAULT NULL,
    `user_id`     bigint(20) DEFAULT NULL,
    `status`      int(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET
FOREIGN_KEY_CHECKS = 1;


/**
     common_code
 */

-- ----------------------------
-- Table structure for common_code
-- ----------------------------
DROP TABLE IF EXISTS `common_code`;
CREATE TABLE `common_code` (
                               `id` bigint(11) NOT NULL,
                               `code` varchar(32) DEFAULT NULL,
                               `msg` varchar(255) DEFAULT NULL,
                               PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;


/**
    user_0
 */

-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
                          `user_id` bigint(20) NOT NULL,
                          `user_name` varchar(255) DEFAULT NULL,
                          `status` int(2) DEFAULT NULL,
                          PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

引入maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${latest.release.version}</version>
</dependency>

先配置一个数据源试试效果

application.properties配置文件

# 配置真实数据源
#给每个数据源取别名（ds0）
spring.shardingsphere.datasource.names=ds0
#实体类对应多表覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://rm-xxxxxxxxxl.rds.aliyuncs.com/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=xxxxx
spring.shardingsphere.datasource.ds0.password=xxxxxxx

#库中表的分布情况
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds0.course_$->{0..1}

#指定主键和主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

#数据表的分片策略(id奇数存在course_1,偶数存在course_0)
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2}

#打印sql
spring.shardingsphere.props.sql.show=true

创建相关必要的类

Course.java

@Data
public class Course {
    private Long id;
    private String courseName;
    private Long userId;
    private Integer status;
}

CourseMapper.java

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

ShardingSphere01Test.java

@SpringBootTest
public class ShardingSphere01Test {
    @Autowired
    private CourseMapper courseMapper;

    @Test
    void save() {
        //创建10 个课程。观察数据在数据库中的分布情况（期望：id 为奇数的在course_1表中，id 为偶数的在course_0表中）
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCourseName("java"+i);
            course.setUserId(10001L+i);
            course.setStatus(1);
            courseMapper.insert(course);
            System.out.println("");

        }

    }

    @Test
    void select() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("status",1);
        List<Course> courses = courseMapper.selectList(wrapper);
        System.out.println(courses);
    }


    @Test
    void delete() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("course_name","java1");
        int delete = courseMapper.delete(wrapper);
        System.out.println(delete);
    }
}

对比结果

course_0

course_1

上面只配置了一个数据源，将一个表course 分成了course_0 和 course_1 进行简单的操作。完成了一个库水平分表的过程，现在我们在多增加一些数据源试试效果。

添加多个数据源试试效果

application.properties配置文件

# 配置真实数据源
#给每个数据源取别名,水平分库配置多个数据源
spring.shardingsphere.datasource.names=ds0,ds1
#实体类对应多表覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://rm-bp1esj9lvb99c4j1y5o.mysql.rds.aliyuncs.com/edu_db0?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=face
spring.shardingsphere.datasource.ds0.password=Root@123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://rm-bp1esj9lvb99c4j1y5o.mysql.rds.aliyuncs.com/edu_db1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.username=face
spring.shardingsphere.datasource.ds1.password=Root@123456

#指定库表情况
#ds0 ->(course_0,course_1)
#ds1 ->(course_0,course_1)
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{0..1}.course_$->{0..1}

#指定主键和主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE


#针对特定表(course) 库分片策略（user_id 奇偶性对应数据库的奇偶性）
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}


#数据表的分片策略（id 奇偶性对应表的奇偶性）
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2}


#打印sql
spring.shardingsphere.props.sql.show=true

现在我们一起有两个数据源ds0、ds1，ds0和ds1下面分别都存在course_0/1两个表。

我们设置的策略是 user_id 的奇偶性对应数据库ds_0/1的奇偶性。主键id对应数据表course_0/1的奇偶性。

预期数据分布：user_id为偶数的数据都在ds_0中，user_id为奇数的数据都在ds_1中，且主键id为偶数的数据都在course_0中，主键id为奇数的数据都在course_1中。

我们任然执行save()方法。

@Test
void save() {
    for (int i = 0; i < 10; i++) {
        Course course = new Course();
        course.setCourseName("java"+i);
        course.setUserId(10001L+i);
        course.setStatus(1);
        courseMapper.insert(course);
        System.out.println("");
    }

}

数据分布如下：

ds_0:

ds_1:

根据观察数据分布等于预期效果。

接下来继续扩展数据源

application.properties配置文件

# 配置真实数据源
#给每个数据源取别名,水平分库配置多个数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2
#实体类对应多表覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://rm-xxxxxx.mysql.rds.aliyuncs.com/edu_db0?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=xxx
spring.shardingsphere.datasource.ds0.password=xxxxx

# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://xxxxxx.mysql.rds.aliyuncs.com/edu_db1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.username=xxxx
spring.shardingsphere.datasource.ds1.password=xxxxxx

# 配置第 3 个数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://xxxxxxxx.mysql.rds.aliyuncs.com/user_db0?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds2.username=xxxx
spring.shardingsphere.datasource.ds2.password=xxxxx


#公共表设置（每个库都有的）
spring.shardingsphere.sharding.broadcast-tables=common_code
spring.shardingsphere.sharding.tables.common_code.key-generator.column=id
spring.shardingsphere.sharding.tables.common_code.key-generator.type=SNOWFLAKE

#指定库表情况
#ds0 ->(course_0,course_1)
#ds1 ->(course_0,course_1)
#ds2 ->(user_0)
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{0..1}.course_$->{0..1}
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{2}.user_$->{0}

#指定主键和主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

#数据库的分片策略（user_id 奇偶性对应库的奇偶性）

#针对特定表(course) 库分片策略
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}

#用户表对应ds_2
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds$->{2}

#数据表的分片策略（user_id 奇偶性对应表的奇偶性）
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2}

# user表没有分表，分表策略可不指定
#spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_0


#打印sql
spring.shardingsphere.props.sql.show=true

创建必要的类

User.java

@Data

public class User {
    private Long userId;
    private String userName;
    private Integer status;
}

UserMapper.java

@Repository
public interface UserMapper extends BaseMapper<User> {
}

测试类中添加几个方法如下：

    @Test
    void saveUser() {
        User user = new User();
        user.setUserName("张三2");
        user.setStatus(1);
        userMapper.insert(user);

    }

    @Test
    void selectUser() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("status",1);
        List<User> users = userMapper.selectList(wrapper);
        System.out.println(users);

    }

    /**
     * 字典表操作测试
     */

    @Test
    void saveCommonCoe() {
        CommonCode commonCode = new CommonCode();
        commonCode.setCode("error-user");
        commonCode.setMsg("用户error");
        commonCodeMapper.insert(commonCode);
    }

    @Test
    void seelctCommonCoe() {
        QueryWrapper<CommonCode> wrapper = new QueryWrapper<>();
        commonCodeMapper.selectList(wrapper);
    }

    @Test
    void deleteCommonCoe() {
        QueryWrapper<CommonCode> wrapper = new QueryWrapper<>();
        wrapper.eq("code","error-user");
        commonCodeMapper.delete(wrapper);
    }

  
//保存用户，用按照分库策略保存到ds_2
@Test
void saveUser() {
    User user = new User();
    user.setUserName("张三2");
    user.setStatus(1);
    userMapper.insert(user);

}

@Test
void selectUser() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("status",1);
    List<User> users = userMapper.selectList(wrapper);
    System.out.println(users);

}

/**
 * 字典表操作测试:字典表操作，会同步操作配置了字典表的数据库，一改都改，查询也是查询全部。
 */

@Test
void saveCommonCoe() {
    CommonCode commonCode = new CommonCode();
    commonCode.setCode("error-user");
    commonCode.setMsg("用户error");
    commonCodeMapper.insert(commonCode);
}

@Test
void seelctCommonCoe() {
    QueryWrapper<CommonCode> wrapper = new QueryWrapper<>();
    commonCodeMapper.selectList(wrapper);
}

@Test
void deleteCommonCoe() {
    QueryWrapper<CommonCode> wrapper = new QueryWrapper<>();
    wrapper.eq("code","error-user");
    commonCodeMapper.delete(wrapper);
}

数据分布如同预期，展示。

我们也可以继续扩展数据源。

application.properties配置文件

# 配置真实数据源
#给每个数据源取别名,水平分库配置多个数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2,m0,s0
#实体类对应多表覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://xxxxxxxxxxx.mysql.rds.aliyuncs.com/edu_db0?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=xxxxx
spring.shardingsphere.datasource.ds0.password=xxxxx

# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://xxxxxxxxxxxo.mysql.rds.aliyuncs.com/edu_db1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.username=xxxxxxxxxxx
spring.shardingsphere.datasource.ds1.password=xxxxxxxxxxx

# 配置第 3 个数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://xxxxxxxxxxx.mysql.rds.aliyuncs.com/user_db0?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds2.username=xxxxxxxxxxx
spring.shardingsphere.datasource.ds2.password=xxxxxxxxxxx

# 配置第 4 个数据源  course_db 主服务器
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://xxxxxxxxxxx.mysql.rds.aliyuncs.com/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=xxxxxxxxxxx
spring.shardingsphere.datasource.m0.password=Rxxxxxxxxxxx

# 配置第 5 个数据源  course_db（course_db_read） 从服务器
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://xxxxxxxxxxx.mysql.rds.aliyuncs.com/course_db_read?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=xxxxxxxxxxx
spring.shardingsphere.datasource.s0.password=xxxxxxxxxxx

#说明数据库主从库(读写分离)
spring.shardingsphere.sharding.master-slave-rules.ds.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds.slave-data-source-names=s0



#公共表设置（每个库都有的）
spring.shardingsphere.sharding.broadcast-tables=common_code
spring.shardingsphere.sharding.tables.common_code.key-generator.column=id
spring.shardingsphere.sharding.tables.common_code.key-generator.type=SNOWFLAKE

#指定库表情况
#ds0 ->(course_0,course_1)
#ds1 ->(course_0,course_1)
#ds2 ->(user_0)
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{0..1}.course_$->{0..1}
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{2}.user_$->{0}
spring.shardingsphere.sharding.tables.lesson.actual-data-nodes=ds.lesson

#指定主键和主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.lesson.key-generator.column=id
spring.shardingsphere.sharding.tables.lesson.key-generator.type=SNOWFLAKE

#数据库的分片策略（user_id 奇偶性对应库的奇偶性）

#针对特定表(course) 库分片策略
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}

spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds$->{2}

#数据表的分片策略（user_id 奇偶性对应表的奇偶性）
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2}


#打印sql
spring.shardingsphere.props.sql.show=true

其实操作与前面类似，只是多添加数据配置。不再赘述。

接下来看看shardingsphere-jdbc的操作sql日志：

当我们做正删改查操作的时候，其实是按照配置对应正确的数据库表分别执行sql.

如保存课程：实际代码是分别执行多条sql的。


Logic SQL: INSERT INTO course  ( id,
course_name,
user_id,
status )  VALUES  ( ?,
?,
?,
? )


Actual SQL: ds1 ::: INSERT INTO course_0   (id, course_name, user_id, status) VALUES (?, ?, ?, ?) ::: [1496398327883304962, 测试课程0, 10001, 1]
Actual SQL: ds1 ::: INSERT INTO course_0   (id, course_name, user_id, status) VALUES (?, ?, ?, ?) ::: [1496398331922419714, 测试课程2, 10003, 1]
Actual SQL: ds0 ::: INSERT INTO course_0   (id, course_name, user_id, status) VALUES (?, ?, ?, ?) ::: [1496398332010500098, 测试课程3, 10004, 1]
Actual SQL: ds0 ::: INSERT INTO course_1   (id, course_name, user_id, status) VALUES (?, ?, ?, ?) ::: [1496398332211826689, 测试课程5, 10006, 1]
Actual SQL: ds0 ::: INSERT INTO course_1   (id, course_name, user_id, status) VALUES (?, ?, ?, ?) ::: [1496398332400570369, 测试课程7, 10008, 1]

查询课程也是类似：

Logic SQL: SELECT  id,course_name,user_id,status  FROM course  
 WHERE  status = ?

Actual SQL: ds0 ::: SELECT  id,course_name,user_id,status  FROM course_0  WHERE  status = ? ::: [1]
Actual SQL: ds0 ::: SELECT  id,course_name,user_id,status  FROM course_1  WHERE  status = ? ::: [1]
Actual SQL: ds1 ::: SELECT  id,course_name,user_id,status  FROM course_0  WHERE  status = ? ::: [1]
Actual SQL: ds1 ::: SELECT  id,course_name,user_id,status  FROM course_1  WHERE  status = ? ::: [1]

posted @ 2022-02-23 16:10 iyandongsheng 阅读(504) 评论(0) 收藏举报

刷新页面返回顶部

thomas

ShardingSphere-JDBC操作

公告