SpringBoot 整合 ShardingSphere JDBC、MySQL分表实例

清疚 2023-01-18 14:30 18阅读 0赞

### 1. 概述 ###

ShardingSphere 分为ShardingSphere-JDBC、ShardingSphere-Proxy、ShardingSphere-Sidecar（TODO）。

ShardingSphere 官方手册：[传送门][Link 1] ；这里使用的是 ShardingSphere-JDBC ，ShardingSphere-JDBC为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

实例工程目录如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70]

### 2. 代码实例 ###

#### 2.1 创建数据库 ####

数据库版本：MySQL5.7 ，创建了两个表 customer\_1、customer\_2 。  
![在这里插入图片描述][20210506225056919.png]  
建表语句：

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for customer_1
    -- ----------------------------
    DROP TABLE IF EXISTS `customer_1`;
    CREATE TABLE `customer_1` (
      `id` bigint(20) NOT NULL COMMENT '主键id，自增',
      `name` varchar(31) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
      `create_time` datetime NOT NULL COMMENT '创建时间',
      `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for customer_2
    -- ----------------------------
    DROP TABLE IF EXISTS `customer_2`;
    CREATE TABLE `customer_2` (
      `id` bigint(20) NOT NULL COMMENT '主键id，自增',
      `name` varchar(31) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
      `create_time` datetime NOT NULL COMMENT '创建时间',
      `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    SET FOREIGN_KEY_CHECKS = 1;

#### 2.2 源代码 ####

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.4.4</version>
            <relativePath/> 
        </parent>
        <groupId>com.syrdbt</groupId>
        <artifactId>shardingsphere-study</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>shardingsphere-study</name>
        <description>Demo 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>
            </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.3.2</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
    
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.xml</include>
                    </includes>
                    <filtering>true</filtering>
                </resource>
            </resources>
        </build>
    
    </project>

实体 Customer.java：

package com.syrdbt.shardingspherestudy.domain;
    
    import java.util.Date;
    
    /**
     * @author syrdbt
     * @date 2021-05-05
     */
    public class Customer {
        
        /**
         * 主键id，自增
         */
        private Long id;
    
        /**
         * 名称
         */
        private String name;
    
        /**
         * 创建时间
         */
        private Date createTime;
    
        /**
         * 修改时间
         */
        private Date updateTime;
    
        public Long getId() {
        
            return id;
        }
    
        public void setId(Long id) {
        
            this.id = id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public Date getCreateTime() {
        
            return createTime;
        }
    
        public void setCreateTime(Date createTime) {
        
            this.createTime = createTime;
        }
    
        public Date getUpdateTime() {
        
            return updateTime;
        }
    
        public void setUpdateTime(Date updateTime) {
        
            this.updateTime = updateTime;
        }
    }

CustomerMapper.java 如下所示：

package com.syrdbt.shardingspherestudy.mapper;
    
    import com.syrdbt.shardingspherestudy.domain.Customer;
    
    /**
     * @author syrdbt
     * @date 2021-05-05
     */
    public interface CustomerMapper {
        
        int insert(Customer record);
    }

启动类 ShardingsphereStudyApplication.java 如下所示：

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

CustomerMapper.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.syrdbt.shardingspherestudy.mapper.CustomerMapper">
        <resultMap id="BaseResultMap" type="com.syrdbt.shardingspherestudy.domain.Customer">
            
            <id column="id" jdbcType="BIGINT" property="id"/>
            <result column="name" jdbcType="VARCHAR" property="name"/>
            <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
            <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        </resultMap>
    
        <insert id="insert" parameterType="com.syrdbt.shardingspherestudy.domain.Customer">
            
            insert into customer (`name`, create_time, update_time)
            values (#{name,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP})
        </insert>
    </mapper>

application.properties，数据库的账号、密码需要修改成读者自己本地的账号、密码:

# shardingjdbc 分片策略
    
    # 配置数据源，给数据源起名称
    spring.shardingsphere.datasource.names=db01
    
    # 一个实体类对应两张表，覆盖
    spring.main.allow-bean-definition-overriding=true
    
    mybatis-plus.mapper-locations = classpath:/mapper/*.xml
    
    # 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
    spring.shardingsphere.datasource.db01.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.db01.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.db01.url=jdbc:mysql://localhost:3306/sharding-sphere?serverTimezone=GMT%2B8
    spring.shardingsphere.datasource.db01.username=root
    spring.shardingsphere.datasource.db01.password=syrdbt
    
    # 指定 customer 表分布情况，配置表在哪个数据库里面，表名称都是什么 db01.customer1, db01.customer2
    spring.shardingsphere.sharding.tables.customer.actual-data-nodes=db01.customer_$->{1..2}
    
    # 指定 customer 表里面主键 id 生成策略 SNOWFLAKE
    spring.shardingsphere.sharding.tables.customer.key-generator.column=id
    spring.shardingsphere.sharding.tables.customer.key-generator.type=SNOWFLAKE
    
    # 指定分片策略 约定 id 值奇数添加到 customer1 表，如果 id 是偶数添加到 customer2
    spring.shardingsphere.sharding.tables.customer.table-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.customer.table-strategy.inline.algorithm-expression=customer_$->{2-id%2}
    
    # 打开 sql 输出日志
    spring.shardingsphere.props.sql.show=true

### 3. 测试 ###

测试类：

package com.syrdbt.shardingspherestudy.mapper;
    
    import com.syrdbt.shardingspherestudy.ShardingsphereStudyApplication;
    import com.syrdbt.shardingspherestudy.domain.Customer;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import javax.annotation.Resource;
    import java.util.Date;
    
    /**
     * @author syrdbt
     * @date 2021-05-05
     */
    @RunWith(SpringRunner.class)
    @SpringBootTest(classes = ShardingsphereStudyApplication.class)
    public class CustomerMapperTest {
        
    
        @Autowired
        private CustomerMapper customerMapper;
    
        @Test
        public void testInsert() {
        
            Customer customer = new Customer();
            customer.setName("syrdbt");
            customer.setCreateTime(new Date());
            customer.setUpdateTime(new Date());
    
            for (long i=1002; i<1100; i++) {
        
                customerMapper.insert(customer);
            }
        }
    
    }

运行testInsert方法，customer\_1中的数据如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70 1]  
customer\_2中的数据如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70 2]

[Link 1]: https://shardingsphere.apache.org/document/current/cn/overview/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70]: /images/20221021/8a4d53325a994d7eb9026d1ae9282303.png
[20210506225056919.png]: /images/20221021/11cca8e0e75c4e6ebcb031a5db4b47d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70 1]: /images/20221021/da2dfbebf9854bc48d04a913a8ec0e9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzM3OTky_size_16_color_FFFFFF_t_70 2]: /images/20221021/44ecbb08541543b7ac64d724b59b3b4b.png