Hint

Background

Apache ShardingSphere uses ThreadLocal to manage sharding key values for mandatory routing. A sharding value can be added by programming to the HintManager that takes effect only within the current thread.

Main application scenarios for Hint:

  • The sharding fields do not exist in the SQL and database table structure but in the external business logic.
  • Certain data operations are forced to be performed in given databases.

Procedure

  1. Call HintManager.getInstance() to obtain an instance of HintManager.
  2. Use HintManager.addDatabaseShardingValue, HintManager.addTableShardingValue to set the sharding key value.
  3. Execute SQL statements to complete routing and execution.
  4. Call HintManager.close to clean up the contents of ThreadLocal.

Sample

Hint Configuration

Hint algorithms require users to implement the interface of org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm. Apache ShardingSphere will acquire sharding values from HintManager to route.

Take the following configurations for reference:

  1. rules:
  2. - !SHARDING
  3.   tables:
  4.     t_order:
  5.       actualDataNodes: demo_ds_${0..1}.t_order_${0..1}
  6.       databaseStrategy:
  7.         hint:
  8.           algorithmClassName: xxx.xxx.xxx.HintXXXAlgorithm
  9.       tableStrategy:
  10.         hint:
  11.           algorithmClassName: xxx.xxx.xxx.HintXXXAlgorithm
  12.   defaultTableStrategy:
  13.     none:
  14.   defaultKeyGenerateStrategy:
  15.     type: SNOWFLAKE
  16.     column: order_id
  18. props:
  19.     sql-show: true

Get HintManager

  1. 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 set sharding value in hint route to some certain sharding database without sharding tables.

Clean Hint 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:

  1. // Sharding database and table with HintManager
  2. String sql = "SELECT * FROM t_order";
  3. try (HintManager hintManager = HintManager.getInstance();
  4.      Connection conn = dataSource.getConnection();
  5.      PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
  6.     hintManager.addDatabaseShardingValue("t_order", 1);
  7.     hintManager.addTableShardingValue("t_order", 2);
  8.     try (ResultSet rs = preparedStatement.executeQuery()) {
  9.         while (rs.next()) {
  10.             // ...
  11.         }
  12.     }
  13. }
  15. // Sharding database and one database route with HintManager
  16. String sql = "SELECT * FROM t_order";
  17. try (HintManager hintManager = HintManager.getInstance();
  18.      Connection conn = dataSource.getConnection();
  19.      PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
  20.     hintManager.setDatabaseShardingValue(3);
  21.     try (ResultSet rs = preparedStatement.executeQuery()) {
  22.         while (rs.next()) {
  23.             // ...
  24.         }
  25.     }
  26. }