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. Apache ShardingSphere can also do mandatory routing by adding comments to SQL.

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

Sharding with Hint

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. }

Use special SQL comments

Terms of Use

For the SQL Hint function, the comment format only supports /* */ for now. The content needs to start with SHARDINGSPHERE_HINT:, and optional attributes include:

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

Users can use SHARDING_DATABASE_VALUE to set sharding value in hint route to some certain sharding database without sharding tables.

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