Java，ShardingSphere，Sharding-JDBC，分库分表的入门程序案例

2021-05-14 IT小奋斗

Sharding-JDBC

背景：出于工作的需要，非常需要详细了解数据库分表分库的内容，这样便于选择版本，了解原理及性能，做出更好的选择。

Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。它定位为轻量级JAVA 框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

Apache ShardingSphere，一套开源的分布式数据库解决方案组成的生态圈。

4.x文档地址：
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

入门程序案例

准备MySQL数据库，分别建立库：ds1和ds2。

ds1、ds2库中分别执行：

CREATE TABLE `t_user` (
  `id` BIGINT PRIMARY KEY ,
  `name` VARCHAR(45) NOT NULL,
  `status` VARCHAR(45) NOT NULL
);

create table `t_order0`(
  `id` BIGINT PRIMARY KEY ,
  `user_id` BIGINT NOT NULL,
  `code`  VARCHAR(45) NOT NULL
);
create table `t_order1`(
  `id` BIGINT PRIMARY KEY ,
  `user_id` BIGINT NOT NULL,
  `code`  VARCHAR(45) NOT NULL
);

pom.xml：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.1</version>
    </dependency>
    <!-- pagehelper分页 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.13</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.22</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>
    <!-- shardingsphere -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
        <version>4.1.1</version>
    </dependency>
    <!-- 使用XA事务时，需要引入此模块 -->
    <!--
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-transaction-xa-core</artifactId>
        <version>4.1.1</version>
    </dependency>
    -->
    <!-- 使用BASE事务时，需要引入此模块 -->
    <!--
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-transaction-base-seata-at</artifactId>
        <version>4.1.1</version>
    </dependency>
    -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.1.22</version>
    </dependency>
</dependencies>

代码案例1：

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class ShardingJDBCDemo01 {

    public static void main(String[] args) {
        // =====================================================================//
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第一个数据源
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://192.168.1.11:3306/ds1?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root1234");
        dataSourceMap.put("ds1", dataSource1);
        // 配置第二个数据源
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://192.168.1.11:3306/ds2?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource2.setUsername("root");
        dataSource2.setPassword("root1234");
        dataSourceMap.put("ds2", dataSource2);
        // =====================================================================//
        // 配置User表规则
        TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_user");
        // 配置分库
        userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds$->{id%2+1}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
        // =====================================================================//
        // 获取数据源对象
        // =====================================================================//
        DataSource dataSource = null;
        try {
            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // =====================================================================//
        // 插入数据
        // =====================================================================//
        for (int i = 41; i <= 60; i++) {
            try {
                String sql = "insert into t_user(`id`,`name`,`status`) values(" + i + ",'1','1')";
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                System.out.println("插入：" + i);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("操作完成...");
    }

}

代码案例2：

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class ShardingJDBCDemo02 {

    public static void main(String[] args) {
        // =====================================================================//
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第一个数据源
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://192.168.1.11:3306/ds1?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root1234");
        dataSourceMap.put("ds1", dataSource1);
        // 配置第二个数据源
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://192.168.1.11:3306/ds2?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource2.setUsername("root");
        dataSource2.setPassword("root1234");
        dataSourceMap.put("ds2", dataSource2);
        // =====================================================================//
        // 配置User表规则
        // 配置User表规则
        TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_user");
        // 配置分库
        userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds$->{id%2+1}"));
        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${1..2}.t_order${0..1}");
        // 配置分库 + 分表策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id%2+1}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_order${id%2}"));

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
        // =====================================================================//
        // 获取数据源对象
        // =====================================================================//
        DataSource dataSource = null;
        try {
            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // =====================================================================//
        // 插入数据
        // =====================================================================//
        for (int i = 1; i <= 10; i++) {
            try {
                String sql = "insert into t_order(`id`,`user_id`,`code`) values(" + i + "," + i + ",'1')";
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                System.out.println("插入：" + i);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("操作完成...");
    }

}