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

Sharding-JDBC

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

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

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

4.x文档地址：

入门程序案例

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

ds1、ds2库中分别执行：

 CREATE TABLE `t_user` (
  `id` BIGINT PRIMARY KEY ,
  `name` VARCHAR() NOT NULL,
  `status` VARCHAR() NOT NULL
); 
 create table `t_order`(
  `id` BIGINT PRIMARY KEY ,
  `user_id` BIGINT NOT NULL,
  `code`  VARCHAR() NOT NULL
);
create table `t_order`(
  `id` BIGINT PRIMARY KEY ,
  `user_id` BIGINT NOT NULL,
  `code`  VARCHAR() 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>.4.1</version>
    </dependency>
    <!-- pagehelper分页 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>.2.13</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>.1.22</version>
    </dependency>
    <dependency>
        <groupId>my SQL </groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>.0.21</version>
    </dependency>
    <!-- shardingsphere -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId> Sharding -jdbc-spring-boot-starter</artifactId>
        <version>.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
        <version>.1.1</version>
    </dependency>
    <!-- 使用 XA 事务时，需要引入此模块 -->
    <!--
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-transaction-xa-core</artifactId>
        <version>.1.1</version>
    </dependency>
    -->
    <!-- 使用BASE事务时，需要引入此模块 -->
    <!--
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-transaction-base-seata-at</artifactId>
        <version>.1.1</version>
    </dependency>
    -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId> fastjson </artifactId>
        <version>.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 ShardingJDBCDemo {

    public static void main(String[] args) {
        // =====================================================================//
        // 配置真实数据源
        Map<String, DataSource>  DataSource Map = new HashMap<>();
        // 配置第一个数据源
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.1.11:3306/ds1?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root1234");
        dataSourceMap.put("ds", dataSource1);
        // 配置第二个数据源
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://192.168.1.11:3306/ds2?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root1234");
        dataSourceMap.put("ds", dataSource2);
        // =====================================================================//
        // 配置User表规则
        TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration("t_user");
        // 配置分库
        userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds$->{id%+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 =; i <= 60; i++) {
            try {
                String sql = "insert into t_user(`id`,`name`,`status`) values(" + 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("操作完成...");
    }

}

代码案例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 ShardingJDBCDemo {

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

        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 =; i <= 10; i++) {
            try {
                String sql = "insert into t_order(`id`,`user_id`,`code`) values(" + i + "," + i + ",'')";
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                System.out.println("插入：" + i);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("操作完成...");
    }

}