ShardingSphere 4.x User Manual-Sharding-JDBC-Hint/Orchestration

Hint

Introduction

ShardingSphere uses ThreadLocal to manage sharding key value or Hint route. Users can program to add sharding values to HintManager, and those values only take effect within the current thread. Main applications of Hint:

1. Sharding fields are not in SQL or table structure, but in external business logic.
2. Some operations forced to do in the master database.

Sharding Based on Hint

Hint Configuration

Hint algorithms require users to implement the interface of org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm. If ShardingSphere finds TableRule in LogicTable has used Hint, it will acquire sharding values from HintManager to route.

Take the following configurations for reference:

shardingRule:
  tables:
   t_order:
        actualDataNodes: demo_ds_${0..1}.t_order_${0..1}
        databaseStrategy:
          hint:
            algorithmClassName: org.apache.shardingsphere.userAlgo.HintAlgorithm
        tableStrategy:
          hint:
            algorithmClassName: org.apache.shardingsphere.userAlgo.HintAlgorithm
  defaultTableStrategy:
    none:
  defaultKeyGenerator:
    type: SNOWFLAKE
    column: order_id
props:
    sql.show: true

Get HintManager

HintManager hintManager = HintManager.getInstance();

Add Sharding Value

• Use hintManager.addDatabaseShardingValue to add sharding key value of data source.
• Use hintManager.addTableShardingValue to add sharding key value of table.

Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables. After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced.

Clean Sharding Values

Sharding values are saved in ThreadLocal, so it is necessary to use hintManager.close() to clean ThreadLocal.

HintManager has implemented AutoCloseable. We recommend to close it automatically with try with resource.

Codes:

// Sharding database and table with hintManager.
        String sql = "SELECT * FROM t_order";
        try (HintManager hintManager = HintManager.getInstance();
             Connection conn = dataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            hintManager.addDatabaseShardingValue("t_order", 1);
            hintManager.addTableShardingValue("t_order", 2);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                while (rs.next()) {
                    // ...
                }
            }
        }
// Sharding database and one database route with hintManger.
        String sql = "SELECT * FROM t_order";
        try (HintManager hintManager = HintManager.getInstance();
             Connection conn = dataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            hintManager.setDatabaseShardingValue(3);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                while (rs.next()) {
                    // ...
                }
            }
        }

Forcible Master Database Route Based on Hint

Get HintManager

Be the same as sharding based on hint.

Configure Master Database Route

• Use hintManager.setMasterRouteOnly to configure master database route.

Clean Sharding Value

Be the same as data sharding based on hint.

Codes:

String sql = "SELECT * FROM t_order";
try (
        HintManager hintManager = HintManager.getInstance();
        Connection conn = dataSource.getConnection();
        PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
    hintManager.setMasterRouteOnly();
    try (ResultSet rs = preparedStatement.executeQuery()) {
        while (rs.next()) {
            // ...
        }
    }
}

Example

www.github.com/apache/shardingsphere-example/tree/4.0.0-RC2/sharding-jdbc-example/other-feature-example/hint-example

Orchestration

Using orchestration requires designating a registry center, in which all the configurations are saved. Users can either use local configurations to cover registry center configurations or read configurations from registry center.

Not Use Spring

Introduce Maven Dependency

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-orchestration</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<!--If you want to use zookeeper, please use the artifactId below.-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

Rule Configuration Based on Java

// Configure dataSourceMap and shardingRuleConfig
    // ...
    // Configure registry center
    RegistryCenterConfiguration regConfig = new RegistryCenterConfiguration("zookeeper");
    regConfig.setServerLists("localhost:2181");
    regConfig.setNamespace("sharding-sphere-orchestration");
    // Configure orchestration
    OrchestrationConfiguration orchConfig = new OrchestrationConfiguration("orchestration-sharding-data-source", regConfig, false);
    // Get data source
    DataSource dataSource = OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties(), orchConfig);

Rule Configuration Based on Yaml

Or use Yaml to configure, similar as above configurations:

orchestration:
  name: orchestration-sharding-data-source
  overwrite: false
  registry:
    type: zookeeper
    serverLists: localhost:2181
    namespace: sharding-sphere-orchestration
DataSource dataSource = YamlOrchestrationShardingDataSourceFactory.createDataSource(yamlFile);

Using Spring

Introduce Maven Dependency

<!-- for spring boot -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<!--If you want to use zookeeper, please add the Maven below.-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<!--If you want to use zookeeper, please add the Maven below.-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

Rule Configuration Based on Spring Boot

spring.shardingsphere.orchestration.name=orchestration-sharding-data-source
spring.shardingsphere.orchestration.overwrite=false
spring.shardingsphere.orchestration.registry.type=zookeeper
spring.shardingsphere.orchestration.registry.server-lists=localhost:2181
spring.shardingsphere.orchestration.registry.namespace=sharding-jdbc-orchestration

Rule Configuration Based on Spring Name Space

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:orchestraion="http://shardingsphere.apache.org/schema/shardingsphere/orchestration"
       xmlns="http://www.springframework.org/schema/beans"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://shardingsphere.apache.org/schema/shardingsphere/orchestration
                           http://shardingsphere.apache.org/schema/shardingsphere/orchestration/orchestration.xsd">
     <import resource="namespace/shardingDataSourceNamespace.xml" />
     <orchestraion:registry-center id="regCenter" type="zookeeper" server-lists="localhost:3181" namespace="orchestration-spring-namespace-test" operation-timeout-milliseconds="1000" max-retries="3" />
     <orchestraion:sharding-data-source id="simpleShardingOrchestration" data-source-ref="simpleShardingDataSource" registry-center-ref="regCenter" />
</beans>

For detailed configurations, please refer to Configuration Manual.