Re:How to use sharding jdbc with hint(如何在sharding jdbc中使用hint)

[Sion Yang <sc...@163.com>]

Hi,


It looks like you don't add shardingValue to `HintManager`. 
As the document show, you should add your shardingValue to `HintManager` 
before your execute your SQL. 


These codes come from document:
```
// 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 hintManger.
        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()) {
                    // ...
                }
            }
        }
```


> HintManager hintManager = HintManager.getInstance();
this code should be used to get `HintManager` in your SQL execute Thread,
and add your shardingValue before you execute SQLs.


--

Yi Yang(Sion)
Apache ShardingSphere



在 2019-08-30 15:55:20，"悟饭" <zh...@skio.cn> 写道：
>hi，English is not my first language，if you don’t  understand which I talk about, please mail me. 
>
>my config：
>
>Shardjdbc version: 		4.0.0-RC2
>Springboot version:		 2.1.7
>DB: 					mysql5.7
>Jdbc driver:			com.mysql.jdbc.Driver
>Db connection pool:	com.alibaba.druid.pool.DruidDataSource
>
>When I use hint to sharing my table, it looks like not work.please show me which one I was mistake. 
>
>When I insert one object to mysql, it will insert 12 records in every sharding table, and, when query by createdAt it also query all tables don’t use the sharing algorithm.
>
>The official demo link was failure.（https://github.com/apache/incubator-shardingsphere-example/tree/dev/sharding-jdbc-example/hint-example <https://github.com/apache/incubator-shardingsphere-example/tree/dev/sharding-jdbc-example/hint-example>）
>
>Thanks all.
>
>
>
>Config in application.yml:
>
>spring:
>  application.name: demo
>  shardingsphere:
>    datasource:
>      ds0:
>        driver-class-name: com.mysql.jdbc.Driver
>        type: com.alibaba.druid.pool.DruidDataSource
>        url: jdbc:mysql://$ <mysql://$>{DB_URL}:3306/demo
>        username: ${DB_USERNAME}
>        password: ${DB_PASS}
>      names: ds0
>    sharding:
>      binding-tables: t_order
>      default-database-strategy:
>        hint:
>          algorithmClassName: com.demo.config.MyShardingAlgorithm
>      tables:
>        illegal_result:
>          actual-data-nodes: ds0.t_order$->{1..9}, ds0.t_order$->{10..12}
>          key-generator:
>            column: id
>            type: SNOWFLAKE
>          table-strategy:
>            hint:
>              algorithmClassName: com.demo.config.MyShardingAlgorithm
>    props:
>      sql:
>        show: true
>
>MyShardAlgorithm.java
>public class MyShardingAlgorithm implements HintShardingAlgorithm<String> {
>  private static final String ORDER_TABLE = "illegal_result";
>
>  @Override
>  public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> shardingValue) {
>
>    List<String> list = Lists.newArrayList(shardingValue.getValues());
>    List<String> actualTable = Lists.newArrayList();
>
>    // 页面上的查询条件会以json的方式传到shardingValue变量中
>    String json = list.get(0);
>    QueryCondition condition = JSON.parseObject(json, IllegalResultQueryCondition.class);
>    // if no createdat, quarry all
>    if (StringUtils.isEmpty(condition.getCreatedAt())) {
>      // 所有的分表
>      for (int i = 1; i < 13; i++) {
>        String tabIndex = i < 10 ? "0".concat(String.valueOf(i)) : String.valueOf(i);
>        actualTable.add(ORDER_TABLE.concat(tabIndex));
>      }
>    } else {
>      // if has createdAt, only query month table which createAt in. ShardingUtils.getDateIndex will get month from createdAt
>      String tableSuffix = ShardingUtils.getDateIndex(condition.getCreatedAt());
>      actualTable.add(ORDER_TABLE.concat(tableSuffix));
>    }
>
>    return actualTable;
>  }
>
>}
>
>Hi,英文水平有限，抱歉。
>我在使用shardinng jdbc进行分表实践的时候，使用hint策略，并根据文档进行了配置，发现并没有生效。官方给出的demo链接已经失效（404）。
>根据官方文档（https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/hint/ <https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/hint/>）我实现了自己的路由算法，但debug时，并没有执行该方法
>另外对于文档中提到的
>获取HintManager
>
>
>HintManager hintManager = HintManager.getInstance();
>
>不太理解应该放在那里。
>