Mandatory Routing #

Introduction #

DBPlusEngine uses ThreadLocal to manage sharding values for mandatory routing. A sharding value can be added to HintManager through programming, and the value is only valid in the current thread. Additionally, DBPlusEngine can also perform mandatory routing by adding annotations to SQL.

Hint can be used in the following scenarios:

• Fields used for sharding exist in external business logic rather than in SQL, database, and table structures.
• Some data operations are forcibly carried out in the primary database.

Procedure #

1. Call HintManager.getInstance() to obtain HintManager instance.

2. Call HintManager.addDatabaseShardingValue，HintManager.addTableShardingValue method to set the sharding key value.

3. Execute SQL statements to complete routing and execution.

4. Call HintManager.close to clear the content of ThreadLocal.

Sample #

Sharding with Hint #

Rules Configuration #

Hint sharding algorithm requires users to implement org.apache.shardingsphere.sharding.api.sharding.hint.HintShardingAlgorithm interface. DBPlusEngine will obtain sharding values from HintManager for routing.

Configuration sample:

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: demo_ds_${0..1}.t_order_${0..1}
      databaseStrategy:
        hint:
          algorithmClassName: xxx.xxx.xxx.HintXXXAlgorithm
      tableStrategy:
        hint:
          algorithmClassName: xxx.xxx.xxx.HintXXXAlgorithm
  defaultTableStrategy:
    none:
  defaultKeyGenerateStrategy:
    type: SNOWFLAKE
    column: order_id

props:
    sql-show: true

Obtain HintManager #

HintManager hintManager = HintManager.getInstance();

Add Sharding Key Value #

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

When database sharding is required and table sharding is not, while mandatory routing is carried out to a sub-database, you can use hintManager.setDatabaseShardingValue to set sharding values.

Clear Sharding Key Value #

The sharding key value is stored in ThreadLocal, so you need to call hintManager.close() at the end of the operation to clear the content of ThreadLocal.

__hintManager has implemented the AutoCloseable interface, and you are advised to use try with resource to close it automatically.

Complete Code Example #

// Sharding database and table using 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 without sharding table and routing to only one database using HintManager
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()) {
            // ...
        }
    }
}

Use SQL Annotations #

Terms of Use #

Before using the SQL Hint, users should enable configurations of parsing annotations in advance and set sqlCommentParseEnabled to true.

The annotation format only supports /* */ and content has to start with ShardingSphere hint:. Optional properties include:

• {table}.SHARDING_DATABASE_VALUE: used to add data source sharding key value corresponding to {table}. Multiple properties are separated by a comma.
• {table}.SHARDING_TABLE_VALUE: used to add table sharding key value corresponding to {table}. Multiple properties are separated by a comma.

When database sharding is required and table sharding is not, while mandatory routing is carried out to a sub-database, you can use SHARDING_DATABASE_VALUE to add shards without specifying {table}.

Complete Sample #

/* SHARDINGSPHERE_HINT: t_order.SHARDING_DATABASE_VALUE=1, t_order.SHARDING_TABLE_VALUE=1 */
SELECT * FROM t_order;