使用springBoot+ShardingSphere5.1.2+mybatisPlus+人大金仓出现多表查询报错 （修改人大金仓在ShardingSphere中的策略）

报错原因

人大金仓在ShardingSphere中使用默认sql92,因为通过数据的url开头的jdbc:kingbase8:找不到对应的解决方案。

ShardingSphere在程序启动的时候通过ServiceLoader.load去获取DatabaseType和OptimizerSQLDialectBuilder的实现类从而找到数据的处理方案， 就可以在META-INF/services中放入对应的路经。

因为使用方式和mysql类似，就仿照ShardingSphere中的mysql的方式，创建一个KingBase8DatabaseType和KingBaseOptimizerBuilder

pom

            <!-- MyBatis Plus-->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.3.1</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.30</version>
            </dependency>
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
                <version>5.1.2</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
                <version>${spring.boot.version}</version>
            </dependency>
            <!-- yaml配置读取-->
            <dependency>
                <groupId>org.yaml</groupId>
                <artifactId>snakeyaml</artifactId>
                <version>1.33</version>
            </dependency>

        <dependency>
            <groupId>com.kingbase</groupId>
            <artifactId>kingbase8</artifactId>
            <version>8.6.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/kingbase8-8.6.0.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>org.hibernate.dialect</groupId>
            <artifactId>Kingbase8Dialect</artifactId>
            <version>4</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/hibernate-4.dialect.jar</systemPath>
        </dependency>

配置

spring:
  #shardingjdbc主要配置 使用HikariDataSource连接池
  shardingsphere:
    # 是否启用sharding
    #enabled: true
    props:
      #　是否显示sql
      sql-show: true
    datasource:
      names: iot #数据源名称，多个以逗号隔开
      iot:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.kingbase8.Driver
        url: jdbc:kingbase8://localhost:54321/test?currentSchema=test&characterEncoding=UTF-8
        username: root
        password: 123123
    rules:
      sharding:
        tables:
          wssl_terminal_data_b: # 分表，逻辑表名
            # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
            actual-data-nodes: iot.wssl_terminal_data_b
            table-strategy: # 配置分表策略
              complex: # 用于单分片键的标准分片场景
                sharding-columns: terminal_type,create_time
                sharding-algorithm-name: real-data-inline
        # 分片算法配置
        sharding-algorithms:
          real-data-inline: # 分片算法名称
            type: CLASS_BASED #自定义策略
            props:
              strategy: complex
              # 包名+类名
              algorithmClassName: com.qzsoft.common.config.shardingsphere.DataShardingAlgorithm
  jpa:
    properties:
      hibernate:
        dialect: com.qzsoft.common.config.KingbaseDialect
        hbm2ddl:
          auto: update
        format_sql: true
    show-sql: true
    generate-ddl: true
    hibernate:
      ddl-auto: update

创建一个KingBase8DatabaseType

public class KingBase8DatabaseType implements BranchDatabaseType {
    //需要和驱动url中的jdbc:kingbase8 保持一致,不区分大小写,这里用kingbase8
    public String getName() {
        return "kingbase8";
    }

    @Override
    public String getType() {
        return "kingbase8";
    }

    public Collection getJdbcUrlPrefixAlias() {

        return Collections.emptyList();

    }

    @Override
    public QuoteCharacter getQuoteCharacter() {
        return QuoteCharacter.QUOTE;
    }

    @Override
    public Collection<String> getJdbcUrlPrefixes() {
        return Collections.singleton(String.format("jdbc:%s:", getType().toLowerCase()));
    }

    //达梦数据库的数据源元数据
    public KingBase8DataSourceMetaData getDataSourceMetaData(String url, String username) {
        return new KingBase8DataSourceMetaData(url);
    }

    @Override
    public Optional<String> getDataSourceClassName() {
        return Optional.empty();
    }

    @Override
    public Map<String, Collection<String>> getSystemDatabaseSchemaMap() {
        return Collections.emptyMap();
    }

    @Override
    public Collection<String> getSystemSchemas() {
        return Collections.emptyList();
    }

    //作为MySQL的子集,sql解析等操作使用MySQL的实现
    @Override
    public DatabaseType getTrunkDatabaseType() {
        return DatabaseTypeFactory.getInstance("MySQL");

    }


}

创建一个KingBase8DataSourceMetaData

@Getter
public class KingBase8DataSourceMetaData implements DataSourceMetaData {
    private static final int DEFAULT_PORT = 54321;

    private final String hostname;

    private final int port;

    private final String catalog;

    private final String schema;

    private final Properties queryProperties;

    private final Properties defaultQueryProperties = new Properties();

    public KingBase8DataSourceMetaData(final String url) {
        JdbcUrl jdbcUrl = new StandardJdbcUrlParser().parse(url);
        hostname = jdbcUrl.getHostname();
        port = -1 == jdbcUrl.getPort() ? DEFAULT_PORT : jdbcUrl.getPort();
        catalog = jdbcUrl.getDatabase();
        schema = null;
        queryProperties = jdbcUrl.getQueryProperties();
    }

    @Override
    public Properties getDefaultQueryProperties() {
        return new Properties();
    }


    private void buildDefaultQueryProperties() {
        defaultQueryProperties.setProperty("useServerPrepStmts", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("cachePrepStmts", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("prepStmtCacheSize", "200000");
        defaultQueryProperties.setProperty("prepStmtCacheSqlLimit", "2048");
        defaultQueryProperties.setProperty("useLocalSessionState", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("rewriteBatchedStatements", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("cacheResultSetMetadata", Boolean.FALSE.toString());
        defaultQueryProperties.setProperty("cacheServerConfiguration", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("elideSetAutoCommits", Boolean.TRUE.toString());
        defaultQueryProperties.setProperty("maintainTimeStats", Boolean.FALSE.toString());
        defaultQueryProperties.setProperty("netTimeoutForStreamingResults", "0");
        defaultQueryProperties.setProperty("tinyInt1isBit", Boolean.FALSE.toString());
        defaultQueryProperties.setProperty("useSSL", Boolean.FALSE.toString());
        defaultQueryProperties.setProperty("serverTimezone", "UTC");
        defaultQueryProperties.setProperty("zeroDateTimeBehavior", "round");
    }
}

创建一个KingBaseOptimizerBuilder

public class KingBaseOptimizerBuilder implements OptimizerSQLDialectBuilder {
    @Override
    public Properties build() {
        Properties result = new Properties();
        result.setProperty(CalciteConnectionProperty.LEX.camelName(), Lex.JAVA.name());
        result.setProperty(CalciteConnectionProperty.CONFORMANCE.camelName(), SqlConformanceEnum.BABEL.name());
        result.setProperty(CalciteConnectionProperty.FUN.camelName(), SqlLibrary.POSTGRESQL.fun);
        return result;
    }

    @Override
    public String getType() {
        return "kingbase8";
    }

    @Override
    public boolean isDefault() {
        return true;
    }
}

最后把添加的KingBase8DatabaseType和KingBaseOptimizerBuilder 导入进程序中

在META-INF/services路径下

添加文件

文件分别为

org.apache.shardingsphere.infra.database.type.DatabaseType

com.qzsoft.common.config.shardingsphere.KingBase8DatabaseType
org.apache.shardingsphere.infra.database.type.dialect.H2DatabaseType
org.apache.shardingsphere.infra.database.type.dialect.MariaDBDatabaseType
org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType
org.apache.shardingsphere.infra.database.type.dialect.OpenGaussDatabaseType
org.apache.shardingsphere.infra.database.type.dialect.OracleDatabaseType
org.apache.shardingsphere.infra.database.type.dialect.PostgreSQLDatabaseType
org.apache.shardingsphere.infra.database.type.dialect.SQL92DatabaseType
org.apache.shardingsphere.infra.database.type.dialect.SQLServerDatabaseType

org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.OptimizerSQLDialectBuilder

内容：

com.qzsoft.common.config.shardingsphere.KingBaseOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.H2OptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.MariaDBOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.MySQLOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.OpenGaussOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.OracleOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.PostgreSQLOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.SQLServerOptimizerBuilder
org.apache.shardingsphere.infra.federation.optimizer.context.parser.dialect.impl.SQL92OptimizerBuilder