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("操作完成...");
    }

}

日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

Java，ShardingSphere，Sharding-JDBC，分庫分表的入門程序案例

Sharding-JDBC

入門程序案例

數獨大挑戰2018-06-03

答題星2018-06-03

全階人生考試2018-06-03

運動步數有氧達人2018-06-03

每日養生app2018-06-03

體育訓練成績評定2018-06-03