Sharding-Jdbc分庫分表的導讀

前言

Sharding-JDBC是一個開源的分布式資料庫中間件，它無需額外部署和依賴，完全相容JDBC和各種ORM架構。Sharding-JDBC作為面向開發的微服務雲原生基礎類庫，完整的實作了分庫分表、讀寫分離和分布式主鍵功能，并初步實作了柔性事務。

以2.0.3為例maven包依賴如下

<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>com.dongpeng</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>

    <name>sharding-jdbc</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>2.0.3</version>
        </dependency> 
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId> 
            <version>0.9.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.21</version>
        </dependency> 
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.21</version>
        </dependency>

    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
        <plugins>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

簡單的分庫demo介紹如下

package com.dongpeng.sharding.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import io.shardingjdbc.core.api.ShardingDataSourceFactory;
import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import io.shardingjdbc.core.api.config.TableRuleConfiguration;
import io.shardingjdbc.core.api.config.strategy.InlineShardingStrategyConfiguration;

/**
 * sharding-jdbc分庫的demo
 * @author Admin
 *
 */
public class ShardingDataDemo {
    public static void main(String[] args) throws Exception{

        Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
        ComboPooledDataSource dataSource1 = new ComboPooledDataSource();
        dataSource1.setDriverClass("com.mysql.jdbc.Driver"); // loads the jdbc driver
        dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/db_0?useSSL=false");
        dataSource1.setUser("root");
        dataSource1.setPassword("root");
        dataSourceMap.put("db_0", dataSource1);
        
        ComboPooledDataSource dataSource2 = new ComboPooledDataSource();
        dataSource2.setDriverClass("com.mysql.jdbc.Driver"); // loads the jdbc driver
        dataSource2.setJdbcUrl("jdbc:mysql://localhost:3306/db_1?useSSL=false");
        dataSource2.setUser("root");
        dataSource2.setPassword("root");
        dataSourceMap.put("db_1", dataSource1);
        
        /**
         * 配置分庫規則
         */
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        orderTableRuleConfig.setLogicTable("t_order");
        orderTableRuleConfig.setActualDataNodes("db_${0..1}.t_order_0");
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "db_${user_id % 2}"));
        
        /**
         * 分片規則配置
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
        Properties properties = new Properties();
        properties.put("sql.show", "true"); 
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,new HashMap<String,Object>(),properties);
        Connection connection  = dataSource.getConnection();
        PreparedStatement statement =  connection.prepareStatement("select * from t_order where user_id=?");
        
        statement.setInt(1, 1);
        ResultSet rs = statement.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getString("user_id"));
        }
        rs.close();
        statement.close();
        connection.close();
        
       
    
    }
}

幾個重要的類

ShardingRuleConfiguration

ShardingDataSourceFactory

ShardingDataSource

ShardingConnection

ShardingPreparedStatement

ShardingStatement

源碼分析

分片的規則配置類主要的屬性如下

private String defaultDataSourceName;
    
    private Collection<TableRuleConfiguration> tableRuleConfigs = new LinkedList<>();
    
    private Collection<String> bindingTableGroups = new LinkedList<>();
    
    private ShardingStrategyConfiguration defaultDatabaseShardingStrategyConfig;
    
    private ShardingStrategyConfiguration defaultTableShardingStrategyConfig;
    
    private String defaultKeyGeneratorClass;
    
    private Collection<MasterSlaveRuleConfiguration> masterSlaveRuleConfigs = new LinkedList<>();

defaultDataSourceName指定沒有分片規則庫使用的資料源

tableRuleConfigs 配置指定表的分片規則

TableRuleConfiguration的屬性如下

private String logicTable;
    
    private String actualDataNodes;
    
    private ShardingStrategyConfiguration databaseShardingStrategyConfig;
    
    private ShardingStrategyConfiguration tableShardingStrategyConfig;
    
    private String keyGeneratorColumnName;
    
    private String keyGeneratorClass;
    
    private String logicIndex;

logicTable配置邏輯表比如資料庫中有t_order_0,t_order_1兩張表，邏輯表設定為t_order

actualDataNodes邏輯節點配置,用的類InlineExpressionParser作為解析器，配置如 db_${0..1}.t_order_0

databaseShardingStrategyConfig配置資料庫的分片規則

tableShardingStrategyConfig 表的分片規則

兩個分片規則都實作了ShardingStrategyConfiguration接口，用于建構ShardingStrategy分片類,以下是sharding-jdbc提供的分片配置類，具體實作分片規則可以檢視源碼

keyGeneratorColumnName配置分布式主鍵

keyGeneratorClass配置id生成類

logicIndex配置邏輯分片索引位置

還有properties,map的方式配置一些資訊這個可參考文檔，如sql.show在properties中配置等等

shardingDataSourceFactory是ShardingDataSource的工廠類，用于建立ShardingDataSource生成方式

支援檔案，自定義等等如下

public static DataSource createDataSource(final Map<String, DataSource> dataSourceMap, final ShardingRuleConfiguration shardingRuleConfig, 
                                              final Map<String, Object> configMap, final Properties props) throws SQLException {
        return new ShardingDataSource(shardingRuleConfig.build(dataSourceMap), configMap, props);
    }
    
    /**
     * Create sharding data source.
     *
     * @param yamlFile yaml file for rule configuration of databases and tables sharding with data sources
     * @return sharding data source
     * @throws SQLException SQL exception
     * @throws IOException IO exception
     */
    public static DataSource createDataSource(final File yamlFile) throws SQLException, IOException {
        YamlShardingConfiguration config = unmarshal(yamlFile);
        return new ShardingDataSource(config.getShardingRule(Collections.<String, DataSource>emptyMap()), config.getShardingRule().getConfigMap(), config.getShardingRule().getProps());
    }

主要用于初始化建構dataSource的一些配置資訊封裝成ShardingContext提供給ShardingConnection使用

繼承自AbstractConnectionAdapter同時實作了connection接口，封了preparedStatement和statement的調用方法，ShardingPreparedStatement和ShardingStatement類來實作,主要的方法是擷取connection的方法如下

public Connection getConnection(final String dataSourceName, final SQLType sqlType) throws SQLException {
        if (getCachedConnections().containsKey(dataSourceName)) {
            return getCachedConnections().get(dataSourceName);
        }
        DataSource dataSource = shardingContext.getShardingRule().getDataSourceMap().get(dataSourceName);
        Preconditions.checkState(null != dataSource, "Missing the rule of %s in DataSourceRule", dataSourceName);
        String realDataSourceName;
        if (dataSource instanceof MasterSlaveDataSource) {
            NamedDataSource namedDataSource = ((MasterSlaveDataSource) dataSource).getDataSource(sqlType);
            realDataSourceName = namedDataSource.getName();
            if (getCachedConnections().containsKey(realDataSourceName)) {
                return getCachedConnections().get(realDataSourceName);
            }
            dataSource = namedDataSource.getDataSource();
        } else {
            realDataSourceName = dataSourceName;
        }
        Connection result = dataSource.getConnection();
        getCachedConnections().put(realDataSourceName, result);
        replayMethodsInvocation(result);
        return result;
    }

這個方法的作用是，如果拿到的資料源是一個MasterSalveDataSource的資料源，需要進行讀寫分離的判斷，并最終傳回執行的connection

ShardingPreparedStatement和ShardingStatement

具體的執行類，都有對應的route方法封裝最終都交由SQLRouter來解析sql擷取最終的路由資訊，最後執行相應的sql

Sharding-Jdbc分庫分表的導讀

繼續閱讀

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method