Hint强制路由的JAVA API示例的问题

泠恒谦 · 2021 年12 月 31 日 01:17

在使用 Java API :: ShardingSphere (apache.org) ,里面配置分表策略的语法类似orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "tableShardingAlgorithm"));.

但在强制路由 :: ShardingSphere (apache.org) 一节可以看到Hint并没有shardingColumn的概念,这一处的setTableShardingStrategy应该配置什么呢?

我在ShardingSphere SourceCode的Example没有看到相关的,用JAVA API配置Hint强制路由的案例(而HintShardingStrategyConfiguration这样的用JAVA API配置Hint行表达式的例子似乎也没有?).希望能有朋友解惑.

RaigorJiang · 2021 年12 月 31 日 02:09

你好，hint 算法直接使用 hint value 判断路由，不需要指定 shardingColumn。您可以看下另一个和 hint 有关的帖子：

泠恒谦 · 2021 年12 月 31 日 02:16

@RaigorJiang 我可以理解为logicTable.setTableShardingStrategy(new StandardShardingStrategyConfiguration(null, "tableShardingAlgorithm")); ,然后再shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("hint", tableShardingAlgorithmrProps)); 这样处理?

RaigorJiang · 2021 年12 月 31 日 02:20

可否使用代码块来粘贴代码，这样不利于阅读。

泠恒谦 · 2021 年12 月 31 日 02:32

@RaigorJiang 我说的是类似如下这样的JAVA API配置,但我不确定setTableShardingStrategy()内怎么配置(而schema.sql就是正常初始化h2database的SQL脚本,com.lingh.algorithm.HintXXXAlgorithm就是正常实现HintShardingAlgorithm接口的类)

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

@Configuration
public class LocalShardingDatabasesAndTablesConfiguration {

    // 创建 ShardingSphereDataSource
    @Bean
    public DataSource getDataSource() throws SQLException {
        ModeConfiguration modeConfiguration = createModeConfiguration();
        Map<String, DataSource> dataSourceMap = createDataSourceMap();
        ShardingRuleConfiguration shardingRuleConfiguration = createShardingRuleConfiguration();
        return ShardingSphereDataSourceFactory.createDataSource(
                modeConfiguration,
                dataSourceMap,
                Collections.singleton(shardingRuleConfiguration),
                new Properties());
    }

    // 配置分片规则
    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 配置 tc 表规则
        shardingRuleConfig.getTables().add(this.getTcTableRuleConfiguration());
        shardingRuleConfig.getBindingTableGroups().add("tc");
        // 配置分表算法
        Properties tableShardingAlgorithmrProps = new Properties();
        tableShardingAlgorithmrProps.setProperty("algorithmClassName", "com.lingh.algorithm.HintXXXAlgorithm");
        shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("hint", tableShardingAlgorithmrProps));
        return shardingRuleConfig;
    }

    // 配置 tc 表规则
    private ShardingTableRuleConfiguration getTcTableRuleConfiguration() {
        List<String> collect = Arrays.asList("tc_a", "tc_bb", "tc_ccc");
        ShardingTableRuleConfiguration tc = new ShardingTableRuleConfiguration("tc", "ds0.${" + collect + "}");
        tc.setTableShardingStrategy(new StandardShardingStrategyConfiguration(null, "tableShardingAlgorithm"));
        return tc;
    }

    // 配置真实数据源
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第 1 个数据源
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("org.h2.Driver");
        dataSource1.setJdbcUrl("jdbc:h2:mem:test;MODE=MySQL;DATABASE_TO_LOWER=TRUE;INIT=RUNSCRIPT FROM 'classpath:db/schema.sql'");
        dataSource1.setUsername("root");
        dataSource1.setPassword("123456");
        dataSourceMap.put("ds0", dataSource1);
        return dataSourceMap;
    }

    private static ModeConfiguration createModeConfiguration() {
        return new ModeConfiguration("Memory", null, true);
    }

当然,这么配置是

Caused by: org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI `org.apache.shardingsphere.sharding.spi.ShardingAlgorithm` with type `hint`.
	at org.apache.shardingsphere.spi.typed.TypedSPIRegistry.getRegisteredService(TypedSPIRegistry.java:76) ~[shardingsphere-spi-5.0.0.jar:5.0.0]

的.
ps: 刚刚发现配置的ds_0写错了，才改回来。但依然是spi实现的问题。

RaigorJiang · 2021 年12 月 31 日 02:58

泠恒谦:

// 配置分表算法
        Properties tableShardingAlgorithmrProps = new Properties();
        tableShardingAlgorithmrProps.setProperty("algorithmClassName", "com.lingh.algorithm.HintXXXAlgorithm");
        shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("hint", tableShardingAlgorithmrProps));

要使用自定义算法吗？不是的话，不用指定 algorithmClassName
tableShardingAlgorithmrProps 应该指定算法需要的 props，例如 algorithm-expression: ds_${Integer.valueOf(value) % 2}
ShardingSphereAlgorithmConfiguration 指定的 type 应是 SPI type，内置的 HINT_INLINE 可使用。

RaigorJiang · 2021 年12 月 31 日 03:00

这里要使用 HintShardingStrategyConfiguration ，而非 StandardShardingStrategyConfiguration

泠恒谦 · 2021 年12 月 31 日 05:23

@RaigorJiang 感谢你的帮助,我模仿SourceCode的example的sharding-hint-databases-only.yaml的配置解决了这一问题,在自定义的实现了HintShardingAlgorithm的算法类中重写getType()的返回值解决.

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.HintShardingStrategyConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

@Configuration
public class LocalShardingDatabasesAndTablesConfiguration {

    // 创建 ShardingSphereDataSource
    @Bean
    public DataSource getDataSource() throws SQLException {
        ModeConfiguration modeConfiguration = createModeConfiguration();
        Map<String, DataSource> dataSourceMap = createDataSourceMap();
        ShardingRuleConfiguration shardingRuleConfiguration = createShardingRuleConfiguration();
        return ShardingSphereDataSourceFactory.createDataSource(
                modeConfiguration,
                dataSourceMap,
                Collections.singleton(shardingRuleConfiguration),
                new Properties());
    }

    // 配置分片规则
    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 配置 tc 表规则
        shardingRuleConfig.getTables().add(this.getTcTableRuleConfiguration());
        shardingRuleConfig.getBindingTableGroups().add("tc");
        return shardingRuleConfig;
    }

    // 配置 tc 表规则
    private ShardingTableRuleConfiguration getTcTableRuleConfiguration() {
        List<String> collect = Arrays.asList("tc_a", "tc_bb", "tc_ccc");
        String collect1 = collect.stream()
                .map(s -> "'" + s + "'")
                .collect(Collectors.joining(","));
        ShardingTableRuleConfiguration tc = new ShardingTableRuleConfiguration("tc", "ds0.${[" + collect1 + "]}");
        tc.setTableShardingStrategy(new HintShardingStrategyConfiguration("linghHint"));
        return tc;
    }

    // 配置真实数据源
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第 1 个数据源
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("org.h2.Driver");
        dataSource1.setJdbcUrl("jdbc:h2:mem:test;MODE=MySQL;DATABASE_TO_LOWER=TRUE;INIT=RUNSCRIPT FROM 'classpath:db/schema.sql'");
        dataSource1.setUsername("root");
        dataSource1.setPassword("123456");
        dataSourceMap.put("ds0", dataSource1);
        return dataSourceMap;
    }

    private static ModeConfiguration createModeConfiguration() {
        return new ModeConfiguration("Memory", null, true);
    }
}

但我还是有点疑惑,ShardingSphere Doc的强制路由 :: ShardingSphere (apache.org) , tables.t_order.hint.algorithmClassName: xxx.xxx.xxx.HintXXXAlgorithm这样的配置又能怎么在JAVA API中配置?
org.apache.shardingsphere.sharding.api.config.strategy.sharding.HintShardingStrategyConfiguration只能传入shardingAlgorithmName的String,不能传入Properties对象.

RaigorJiang · 2021 年12 月 31 日 05:27

这个示例可能有问题，5.0 中要使用自定义算法类，需要自己实现接口，并使用 CLASS_BASED 方式。

RaigorJiang · 2021 年12 月 31 日 05:28

文档也许需要更新，您可以先验证代码，再尝试修复文档。

泠恒谦 · 2021 年12 月 31 日 05:44

@RaigorJiang 老实说这个问题挺让我困惑的, 不知从哪开始排查. HINT强制路由的SPI Type似乎不是hint?我尝试更改为如下代码后(加入Properties tableShardingAlgorithmrProps来配置algorithmClassName),抛出的异常居然是

No implementation class load from SPI `org.apache.shardingsphere.sharding.spi.ShardingAlgorithm` with type `hint`.

更改的代码是如下的样式

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.HintShardingStrategyConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

@Configuration
public class LocalShardingDatabasesAndTablesConfiguration {

    // 创建 ShardingSphereDataSource
    @Bean
    public DataSource getDataSource() throws SQLException {
        ModeConfiguration modeConfiguration = createModeConfiguration();
        Map<String, DataSource> dataSourceMap = createDataSourceMap();
        ShardingRuleConfiguration shardingRuleConfiguration = createShardingRuleConfiguration();
        return ShardingSphereDataSourceFactory.createDataSource(
                modeConfiguration,
                dataSourceMap,
                Collections.singleton(shardingRuleConfiguration),
                new Properties());
    }

    // 配置分片规则
    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 配置 tc 表规则
        shardingRuleConfig.getTables().add(this.getTcTableRuleConfiguration());
        shardingRuleConfig.getBindingTableGroups().add("tc");
        // 配置分表算法
        Properties tableShardingAlgorithmrProps = new Properties();
        tableShardingAlgorithmrProps.setProperty("algorithmClassName", "com.lingh.algorithm.HintXXXAlgorithm");
        shardingRuleConfig.getShardingAlgorithms().put("linghAlgorithm", new ShardingSphereAlgorithmConfiguration("hint", tableShardingAlgorithmrProps));
        return shardingRuleConfig;
    }

    // 配置 tc 表规则
    private ShardingTableRuleConfiguration getTcTableRuleConfiguration() {
        List<String> collect = Arrays.asList("tc_a", "tc_bb", "tc_ccc");
        String collect1 = collect.stream()
                .map(s -> "'" + s + "'")
                .collect(Collectors.joining(","));
        ShardingTableRuleConfiguration tc = new ShardingTableRuleConfiguration("tc", "ds0.${[" + collect1 + "]}");
        tc.setTableShardingStrategy(new HintShardingStrategyConfiguration("linghAlgorithm"));
        return tc;
    }

    // 配置真实数据源
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第 1 个数据源
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("org.h2.Driver");
        dataSource1.setJdbcUrl("jdbc:h2:mem:test;MODE=MySQL;DATABASE_TO_LOWER=TRUE;INIT=RUNSCRIPT FROM 'classpath:db/schema.sql'");
        dataSource1.setUsername("root");
        dataSource1.setPassword("123456");
        dataSourceMap.put("ds0", dataSource1);
        return dataSourceMap;
    }

    private static ModeConfiguration createModeConfiguration() {
        return new ModeConfiguration("Memory", null, true);
    }
}

抛出的异常是

Caused by: org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI `org.apache.shardingsphere.sharding.spi.ShardingAlgorithm` with type `hint`.
	at org.apache.shardingsphere.spi.typed.TypedSPIRegistry.getRegisteredService(TypedSPIRegistry.java:76) ~[shardingsphere-spi-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmFactory.createAlgorithm(ShardingSphereAlgorithmFactory.java:41) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.sharding.rule.ShardingRule.lambda$new$0(ShardingRule.java:91) ~[shardingsphere-sharding-core-5.0.0.jar:5.0.0]
	at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684) ~[na:1.8.0_312]
	at org.apache.shardingsphere.sharding.rule.ShardingRule.<init>(ShardingRule.java:91) ~[shardingsphere-sharding-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.sharding.rule.builder.ShardingRuleBuilder.build(ShardingRuleBuilder.java:41) ~[shardingsphere-sharding-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.sharding.rule.builder.ShardingRuleBuilder.build(ShardingRuleBuilder.java:35) ~[shardingsphere-sharding-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.infra.rule.builder.schema.SchemaRulesBuilder.buildRules(SchemaRulesBuilder.java:85) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.infra.rule.builder.schema.SchemaRulesBuilder.buildRules(SchemaRulesBuilder.java:70) ~[shardingsphere-infra-common-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.mode.manager.memory.MemoryContextManagerBuilder.build(MemoryContextManagerBuilder.java:53) ~[shardingsphere-memory-mode-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:75) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:61) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:79) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
	at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:94) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
	at com.sg8000.config.LocalShardingDatabasesAndTablesConfiguration.getDataSource(LocalShardingDatabasesAndTablesConfiguration.java:27) ~[classes/:na]
	at com.sg8000.config.LocalShardingDatabasesAndTablesConfiguration$$EnhancerBySpringCGLIB$$3edabb14.CGLIB$getDataSource$0(<generated>) ~[classes/:na]
	at com.sg8000.config.LocalShardingDatabasesAndTablesConfiguration$$EnhancerBySpringCGLIB$$3edabb14$$FastClassBySpringCGLIB$$a0d400d1.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.3.14.jar:5.3.14]
	at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331) ~[spring-context-5.3.14.jar:5.3.14]
	at com.sg8000.config.LocalShardingDatabasesAndTablesConfiguration$$EnhancerBySpringCGLIB$$3edabb14.getDataSource(<generated>) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_312]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_312]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_312]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_312]
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.3.14.jar:5.3.14]
	... 83 common frames omitted


进程已结束,退出代码0

RaigorJiang · 2021 年12 月 31 日 05:47

hint 不是算法的 type，是分片策略的 type。算法需要使用具体的实现 SPI，如之前提到的 HINT_INLINE。

泠恒谦 · 2021 年12 月 31 日 05:54

@RaigorJiang emmm可能我没表达清楚,以使用 Java API :: ShardingSphere (apache.org) 为例,shardingRuleConfig.getShardingAlgorithms().put的是算法名字和一个org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration吧?

构造ShardingSphereAlgorithmConfiguration需要type和props.

但我似乎没在ShardingSphere Doc看到强制路由的Type是?还是说它的Type也是 HINT_INLINE?

RaigorJiang · 2021 年12 月 31 日 09:51

ShardingSphereAlgorithmConfiguration 配置具体算法，使用 SPI，如 HINT_INLINE
内置 SPI 列表

示例有问题的话还可以看看这个：

github.com

apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/config/ShardingTablesConfigurationPrecise.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.apache.shardingsphere.example.sharding.raw.jdbc.config;

import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;

This file has been truncated. show original

泠恒谦 · 2021 年12 月 31 日 11:48

@RaigorJiang 文档和这个example类我都看过。所以我应该把HINT强制路由归类到CLASS_BASE上？也许文档应该说的清楚一点emmm

RaigorJiang · 2021 年12 月 31 日 16:31

你好，不是将 HiNT 归类到 CLASS_BASED，而是说要配置自定义的算法实现，需要用 SPI 或者 CLASS_BASED 方式，其中 CLASS_BASED 的配置要容易理解一些。