ShardingSphere4.1.1 | Sharding-JDBC实现水平分表.md

前言

前文已经介绍了ShardingSphere及相关组件，Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。本文主要通过代码实例介绍如何使用Sharding-JDBC 。

第一篇文章介绍是官网对于sharding-jdbc的解释和介绍，其实说的直白一点，就是包含了分库分表功能的JDBC，因此我们可以直接把sharding-jdbc当做普通的jdbc来进行使用。

本系列文章项目基于SpringBoot构建，采用ShardingSphere v4.1.1，由于5.X版本还在公测，可能存在诸多坑。

构建父项目

引入如下依赖：

<?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>

    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-4.1.1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>pom</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <modules>
        <!-- 数据库分库示例 -->
        <module>sharding-sphere-demo-1</module>
    </modules>
    <!-- 依赖版本管理 -->
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
        <druid.version>1.1.16</druid.version>
        <mybatis.version>2.1.3</mybatis.version>
        <lombok.version>1.18.12</lombok.version>
        <mysql.version>8.0.23</mysql.version>
        <reg-zookeeper-curator.version>4.0.1</reg-zookeeper-curator.version>
    </properties>

    <dependencyManagement>
        <dependencies>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
                <version>${spring-boot.version}</version>
            </dependency>

            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>${mybatis.version}</version>
            </dependency>

            <!-- lombok -->
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>${lombok.version}</version>
                <scope>provided</scope>
            </dependency>

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>

            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.version}</version>
            </dependency>

            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${sharding-sphere.version}</version>
            </dependency>

            <!-- 如果使用sp-distributed 服务治理环境，需引入该依赖，并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
                <version>${sharding-sphere.version}</version>
            </dependency>

            <!-- 如果使用sp-distributed 服务治理环境，且使用zookeeper作为配置或注册中心，需引入该依赖，并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
                <version>${sharding-sphere.version}</version>
            </dependency>

            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
                <version>${reg-zookeeper-curator.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <version>${spring-boot.version}</version>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>

        </dependencies>
    </dependencyManagement>

</project>

Sharding-JDBC 实现水平分表

1、环境构建

创建一个SpringBoot项目，引入如下依赖：

<?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">
    <parent>
        <groupId>cn.zwqh</groupId>
        <artifactId>sharding-sphere-4.1.1</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-demo-1</artifactId>
    <version>${parent.version}</version>

    <packaging>jar</packaging>

    <name>sharding-sphere-demo-1</name>
    <description>Demo project for Spring Boot</description>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.zwqh.shardingspheredemo1.ShardingSphereDemo1Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

2、创建数据库ds0，创建数据表t_orders_0、t_orders_1

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_orders_0
-- ----------------------------
DROP TABLE IF EXISTS `t_orders_0`;
CREATE TABLE `t_orders_0` (
	`order_id` int NOT NULL COMMENT '订单id',
	`order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
	`user_id` int DEFAULT NULL COMMENT '用户id',
	`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
	PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table structure for t_orders_1
-- ----------------------------
DROP TABLE IF EXISTS `t_orders_1`;
CREATE TABLE `t_orders_1` (
	`order_id` int NOT NULL COMMENT '订单id',
	`order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
	`user_id` int DEFAULT NULL COMMENT '用户id',
	`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
	PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SET FOREIGN_KEY_CHECKS = 1;

3、分片规则

如果order_id是偶数添加到t_orders_0，如果是奇数添加到t_orders_1。

4、创建实体类

@Data
public class Orders {
    private Integer orderId;
    private Integer orderType;
    private Integer userId;
    private Double orderAmount;
}

5、创建Mapper类

@Mapper
public interface OrdersMapper {

    @Insert("insert into t_orders(order_id,order_type,user_id,order_amount) values(#{orderId},#{orderType},#{userId},#{orderAmount})")
    void insert(Orders orders);

    @Select("select * from t_orders where order_id = #{orderId}")
    @Results({
            @Result(property = "orderId", column = "order_id"),
            @Result(property = "orderType", column = "order_type"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "orderAmount", column = "order_amount"	)
    })
    Orders selectOne(Integer orderId);
}

6、创建配置文件

serser.port=8080

spring.application.name=spring-boot-shardingsphere

#配置数据源的名称
spring.shardingsphere.datasource.names=ds0

#配置数据源的具体内容，
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://127.0.0.1:3306/ds0?serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#指定orders表的分布情况，配置表在哪个数据库中，表名称是什么
spring.shardingsphere.sharding.tables.t_orders.actual-data-nodes=ds0.t_orders_$->{0..1}
#指定orders表里主键id生成策略
spring.shardingsphere.sharding.tables.t_orders.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_orders.key-generator.type=SNOWFLAKE

#指定分片策略。根据id的奇偶性来判断插入到哪个表
spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.algorithm-expression=t_orders_${order_id%2}

#打开sql输出日志
spring.shardingsphere.props.sql.show=true

7、创建测试类

@SpringBootTest
public class OrderTests {

    @Resource
    private OrdersMapper ordersMapper;

    @Test
    public void addOrders() {
        for (int i = 1; i <= 10; i++) {
            Orders orders = new Orders();
            orders.setOrderId(i);
            orders.setUserId(i % 3);
            orders.setOrderType(i % 2);
            orders.setOrderAmount(1000.0 * i);
            ordersMapper.insert(orders);
        }
    }

    @Test
    public void queryOrders() {
        Orders orders = ordersMapper.selectOne(1);
        System.out.println(orders);
    }

}

8、执行结果

可以看到预期的结果

9、SNOWFLAKE

把新增操作中的主键order_id去除，新增时会自动生成唯一ID插入。

源码地址

github

码云

目录CONTENT