Force Routing #

Introduction #

DBPlusEngine uses ThreadLocal to manage sharding key values for forced routing. The sharding value can be added to HintManager programmatically, and this sharding value only takes effect in the current thread. DBPlusEngine can also enforce routing by adding comments in SQL.

The main usage scenarios of Hint:

• Sharding fields do not exist in SQL and database table structures, but in external business logic.
• Force certain data operations to be performed on the master database.

Operation Steps #

1. Call HintManager.getInstance() to get the HintManager instance;

2. Call the HintManager.addDatabaseShardingValue, HintManager.addTableShardingValue methods to set the sharding key value;

3. Execute SQL statements to complete routing and execution;

4. Call HintManager.close to clean up the content of ThreadLocal.

Operation Example #

Using Hint sharding #

Rule Configuration #

The Hint sharding algorithm requires users to implement org.apache.shardingsphere.sharding.api.sharding.hint.HintShardingAlgorithm interface. When DBPlusEngine performs routing, it will obtain the fragment value from HintManager for routing operations.

The referencing configuration is as follows:

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.

In the case of sharding without table sharding, when forcing routing to a sharding database, The hintManager.setDatabaseShardingValue method can be used to set the sharding value.

Clear Sharding Key Value #

The shard key value is saved in ThreadLocal, so the hintManager.close() needs to be called at the end of the operation to clear the contents in ThreadLocal.

hintManager implements the AutoCloseable interface, it is recommended to use try with resource to automatically close.

Complete code example #

// Sharding database and table with 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 with 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 Comments Method #

Terms of Use #

The SQL Hint function requires the user to enable the configuration of parsing comments in advance, and set sqlCommentParseEnabled to true. The comment format currently only supports /* */, and the content needs to start with ShardingSphere hint:. Optional properties include:

• {table}.SHARDING_DATABASE_VALUE: used to add the data source sharding key value corresponding to the {table} table, multiple attributes need to separated by commas;
• {table}.SHARDING_TABLE_VALUE: It is used to add the table sharding key value corresponding to the {table} table, multiple attributes need to separated by commas.

In the case of sharding without sharding tables, when forcing routing to a certain shard, the SHARDING_DATABASE_VALUE can be used to set sharding without specifying {table}.

Complete Example #

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