Hint

Introduction

Apache ShardingSphere uses ThreadLocal to manage sharding key value or hint route. Users can add sharding values to HintManager, and those values only take effect within the current thread.
Apache ShardingSphere is able to add special comments in SQL to hint route too.

Usage of hint:

  • Sharding columns are not in SQL and table definition, but in external business logic.
  • Some operations forced to do in the primary database.
  • Some operations forced to do in the database chosen by yourself.

Usage

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

Primary Route with Hint

Use manual programming

Get HintManager

Be the same as sharding based on hint.

Configure Primary Database Route
  • Use hintManager.setWriteRouteOnly to configure primary database route.
Clean Hint Value

Be the same as data sharding based on hint.

Codes:
  1. String sql = "SELECT * FROM t_order";
  2. try (HintManager hintManager = HintManager.getInstance();
  3.      Connection conn = dataSource.getConnection();
  4.      PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
  5.     hintManager.setWriteRouteOnly();
  6.     try (ResultSet rs = preparedStatement.executeQuery()) {
  7.         while (rs.next()) {
  8.             // ...
  9.         }
  10.     }
  11. }

Use special SQL comments

Terms of Use

To use SQL Hint function, users need to set sql-comment-parse-enabled to true. The comment format only supports /* */ for now. The content needs to start with ShardingSphere hint:, and the attribute name needs to be writeRouteOnly.

Codes:
  1. /* ShardingSphere hint: writeRouteOnly=true */
  2. SELECT * FROM t_order;

Route to the specified database with Hint

Use manual programming

Get HintManager

Be the same as sharding based on hint.

Configure Database Route
  • Use hintManager.setDataSourceName to configure database route.
Codes:
  1. String sql = "SELECT * FROM t_order";
  2. try (HintManager hintManager = HintManager.getInstance();
  3.      Connection conn = dataSource.getConnection();
  4.      PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
  5.     hintManager.setDataSourceName("ds_0");
  6.     try (ResultSet rs = preparedStatement.executeQuery()) {
  7.         while (rs.next()) {
  8.             // ...
  9.         }
  10.     }
  11. }

Use special SQL comments

Terms of Use

To use SQL Hint function, users need to set sql-comment-parse-enabled to true. Currently, only support routing to one data source. The comment format only supports /* */ for now. The content needs to start with ShardingSphere hint:, and the attribute name needs to be dataSourceName.

Codes:
  1. /* ShardingSphere hint: dataSourceName=ds_0 */
  2. SELECT * FROM t_order;