Logo
Hint

Hint #

Data Fragmentation Forced Routing #

Background Information #

SphereEx-DBPlusEngine uses ThreadLocal to manage slice key values for forced routing. You can programmatically add a slice value to the HintManager that takes effect only within the current thread. SphereEx-DBPlusEngine can also force routing by adding comments to SQL.SphereEx-DBPlusEngine

Main usage scenarios for Hint:

  • Slice fields do not exist in the SQL and database table structure but in the external business logic.

  • Forcing certain data operations to be performed in the specified database.

Operation steps #

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

Example of configuration using Hint sharding #

Rule Configuration #

The Hint sharding algorithm requires the user to implement the org.apache.shardingsphere.sharding.api.sharding.hint.HintShardingAlgorithm interface. Apache ShardingSphere will get the sharding values from the HintManager for routing operations.

The reference 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();

Adding a sharding key value #

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

In the case of splitting a database without splitting a table, you can use hintManager.setDatabaseShardingValue to set the sharding value when forcing a route to a particular shard.

Clearing a sharding key #

Sharding keys are stored in ThreadLocal, so you need to call hintManager.close() at the end of the operation to clear the contents of ThreadLocal.

The hintManager implements the AutoCloseable interface, which is recommended for automatic closure using a try with the resource.

Full 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()) {
            // ...
        }
    }
}

Example of configuration using SQL comments #

Using the specification #

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

  • {table}.SHARDING_DATABASE_VALUE: used to add the key value of the data source slice corresponding to the {table} table, with multiple attributes separated by commas;
  • {table}.SHARDING_TABLE_VALUE: Used to add the key value of the table slice corresponding to the {table} table, with multiple attributes separated by commas.

In the case of splitting a database without a table, you can use SHARDING_DATABASE_VALUE to set a slice without specifying {table} when forcing a route to a particular database.

Full Example #

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

Read/Write Splitting Forced Routing #

Background Information #

SphereEx-DBPlusEngine uses ThreadLocal to manage master library routing tags for forced routing. You can programmatically add a master library routing tag to HintManager with a value that takes effect only within the current thread. SphereEx-DBPlusEngine can also do master routing by adding annotations to SQL.

Hint is mainly used in read-write separation scenarios to force certain data operations to be performed at the primary library.

Operation Steps #

  1. Call HintManager.getInstance() to obtain an instance of HintManager;
  2. Call HintManager.setWriteRouteOnly() method to set the primary library route tag;
  3. Execute the SQL statement to complete the routing and execution;
  4. Call HintManager.close() to clean up the contents of ThreadLocal.

Example of Configuration for Forced Master Routing with Hint #

Get HintManager #

Same as for Hint-based data sharding.

Set the primary master route #

Use hintManager.setWriteRouteOnly to set the master database route.

Clear the sharding key value #

Same as Hint-based data sharding.

Full code example #

String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
     Connection conn = dataSource.getConnection();
     PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
    hintManager.setWriteRouteOnly();
    try (ResultSet rs = preparedStatement.executeQuery()) {
        while (rs.next()) {
            // ...        }
    }
}

Example of a forced master database routing configuration using SQL comments #

Usage specifications #

The comment format of SQL Hint function is only supported /* */for now, the content needs to start with SHARDINGSPHERE_HINT: and the attribute name is WRITE_ROUTE_ONLY.

Full example #

/* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */
SELECT * FROM t_order;

Example of using Hint to force a route to specify a database configuration #

Get HintManager #

Same as for Hint-based data sharding.

Set the route to the specified database #

  • Use hintManager.setDataSourceName to set the database name.

Full code example #

String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
     Connection conn = dataSource.getConnection();
     PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
    hintManager.setDataSourceName("ds_0");
    try (ResultSet rs = preparedStatement.executeQuery()) {
        while (rs.next()) {
            // ...
        }
    }
}

Example of forced routing of a specified database configuration using SQL comments #

Usage Specification #

The SQL Hint feature currently only supports routing to one data source. The comment format supports only /* */ for now, and the content needs to start with SHARDINGSPHERE_HINT:. The attribute name is DATA_SOURCE_NAME.
If using MySQL client connection, you need to add -c option to keep the comments. The client default is --skip-comments to filter the comments.

Full Example #

/* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=ds_0 */
SELECT * FROM t_order;

Use SQL comments to skip the rewriting logic of the parsing layer to force transparent transmission #

Skip the rewriting logic of the parsing layer and transparently transmit SQL directly to the storage_unit

Usage Specification #

By configuring SQL Hint /* SHARDINGSPHERE_HINT: SKIP_SQL_REWRITE=false */, skip all rewriting logic. SQL hints that skip rewriting can cooperate with data source transparent transmission SQL hints to achieve flexible control from routing to rewriting

Full Example #

/* SHARDINGSPHERE_HINT: SKIP_SQL_REWRITE=false */
SELECT * FROM t_order;