Mandatory Routing #

Introduction #

DBPlusEngine uses ThreadLocal to manage primary database routing marks for mandatory routing. A primary database routing mark can be added to HintManager through programming, and the value is only valid in the current thread.

Moreover, DBPlusEngine can also perform mandatory routing in the primary database by adding annotations to SQL.

Hint is mainly used to perform mandatory data operations in the primary database under the read/write splitting scenarios.

Procedure #

1. Call HintManager.getInstance() to obtain HintManager instance.

2. Call HintManager.setWriteRouteOnly() method to set the primary database routing marks.

3. Execute SQL statements to complete routing and execution.

4. Call HintManager.close to clear the content of ThreadLocal.

Sample #

Mandatory Primary Database Routing with Hint #

Use Manual Programming #

Obtain HintManager #

The same as Hint-based data sharding.

Configure Primary Database Routing #

Use hintManager.setWriteRouteOnly to configure primary database routing.

Clear Sharding Key Value #

The same as Hint-based data sharding.

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

Use SQL Annotations #

Term of Use #

Before using SQL Hint, users should enable configurations of parsing annotations in advance and set sqlCommentParseEnabled to true.

The annotation format only supports /* */ and content has to start with SHARDINGSPHERE_HINT. The property name is WRITE_ROUTE_ONLY.

Complete Sample #

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

Route to the specified database with Hint #

Use manual programming #

Get HintManager #

The same as sharding based on hint.

Configure Database Route #

• Use hintManager.setDataSourceName to configure database route.

Code: #

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

Use special SQL comments #

Terms of Use: #

Currently, the SQL Hint function only supports 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 DATA_SOURCE_NAME. Client connections using MySQL need to add the -c option to preserve comments, because the client defaults to --skip-comments to filter comments.

Code: #

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