ShardingSphere5.1适配达梦数据库

简介：

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强

处理方案

目前官方并不支持达梦数据库，可以使用一些方法进行适配

ShardingSphere在项目启动的时候通过ServiceLoader.load去获取DatabaseType和OptimizerSQLDialectBuilder的实现类从而找到实现类， 可以在META-INF/services中放入对应的自定义的实现类

引入依赖

<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>DmJdbcDriver18</artifactId>
    <version>8.1.1.193</version>
 </dependency>
     
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.1.1</version>
</dependency>
     
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
 

扩展实现类

public class DM8DatabaseType implements BranchDatabaseType {

    public String getName() {
        return "dm";
    }

    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:", getName().toLowerCase()));
    }

    //达梦数据库的数据源元数据
    public DM8DataSourceMetaData getDataSourceMetaData(String url, String username) {
        return new DM8DataSourceMetaData(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 DatabaseTypeRegistry.getActualDatabaseType("MySQL");

    }

@Getter
public class DM8DataSourceMetaData implements DataSourceMetaData {
    private static final int DEFAULT_PORT = 5236;

    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 DM8DataSourceMetaData(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");
    }

public class DM8OptimizerBuilder 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 "dm";
    }

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

路径：src/main/resources/META-INF/services

利用SPI机制 在/META-INF/services 创建两个shardingsphere全路径名的文件

在 org.apache.shardingsphere.infra.database.type.DatabaseType 文件中添加下面的内容

com.jovision.mix.union.spi.DM8DatabaseType
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.jovision.mix.union.spi.DM8OptimizerBuilder
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

YML配置

  shardingsphere:
    datasource:
      names: demo
      demo:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: dm.jdbc.driver.DmDriver
        url: jdbc:dm://localhost:5236?schema=DEMO&compatibleMode=oracle&characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: SYSDBA
        password: SYSDBA
    props:
      sql-show: true
    # 分表规则
    rules:
      sharding:
        # 表的分片策略
        tables:
           # 逻辑表的名称
          tabel_demo:
            # 数据节点配置，采用Groovy表达式
            actual-data-nodes: demo.TABEL_DEMO$->{0..4}
            # 配置策略
            table-strategy:
              # 用于单分片键的标准分片场景
              standard:
                sharding-column: id
                # 分片算法名字
                sharding-algorithm-name: hash_mod_type_count_5
        # 策略算法名称(推荐使用雪花算法)
        key-generators:
          snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          hash_mod_type_count_5:
            type: HASH_MOD
            props:
              sharding-count: 5
        # 绑定表
        bindingTables:
                - 省略