sharding-jdbc配置之后,提示table does n

背景:

最近使用springboot2.x + sharding-jdbc + mybatis 测试分库分表方案,但是通过官方网站上的配置之后,运行时提示table does not exist错误.

项目代码主要配置如下:

依赖的sharding包版本:

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>4.0.0</version>
 </dependency>

sharding数据源 + 分库分表配置:

/**
 * @author ying.pan
 * @date 2020/6/7 2:42 PM.
 * sharding-jdbc分片配置类
 */
@Configuration
public class ShardingDataSourceConfig {
    private static final Logger LOGGER = LoggerFactory.getLogger(ShardingDataSourceConfig.class);

    @Resource
    private MasterDataBaseConfig masterDbConfig;
    @Resource
    private Slave0DataBaseConfig slave0DataBaseConfig;

    @Bean
    public DataSource getDataSouce() throws SQLException {
        LOGGER.info("初始化默认sharding-jdbc-sharding数据源");
        DataSource dataSource = this.buildDataSource();
        return dataSource;
    }

    private DataSource buildDataSource() throws SQLException {
        //设置从库数据源集合
        Map<String, DataSource> dataSourceMap = Maps.newConcurrentMap();
        dataSourceMap.put("ds0", masterDbConfig.createDataSouce());
        dataSourceMap.put("ds1", slave0DataBaseConfig.createDataSouce());


        // 配置Order表规则 - 哪些库的哪些表参与规则,使用行表达式
        TableRuleConfiguration entityDetailTableRuleConfig = new TableRuleConfiguration("entity_detail_", "ds${0..1}.entity_detail_${0..1}");

        // 配置分库 + 分表策略
        entityDetailTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("entity_id", "ds${entity_id % 2}"));

        entityDetailTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("entity_id", "entity_detail_${entity_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.setTableRuleConfigs(Lists.newArrayList(entityDetailTableRuleConfig));

        // 获取数据源对象
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, this.getProperties());
    }


    private  Properties getProperties() {
        Properties properties = new Properties();
        properties.setProperty("sql.show","true");
        return properties;
    }
}

mybatis的mapper文件:

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO entity_detail
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id!=null">
                id,
            </if>
            <if test="entityId!=null">
                entity_id,
            </if>
            <if test="data!=null">
                data,
            </if>
            <if test="createDate!=null">
                create_date,
            </if>
            <if test="updateTime!=null">
                update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="id!=null">
                        #{id,jdbcType=BIGINT},
                </if>
                <if test="entityId!=null">
                        #{entityId,jdbcType=BIGINT},
                </if>
                <if test="data!=null">
                        #{data,jdbcType=LONGVARCHAR},
                </if>
                <if test="createDate!=null">
                        #{createDate,jdbcType=TIMESTAMP},
                </if>
                <if test="updateTime!=null">
                        #{updateTime,jdbcType=TIMESTAMP}
                </if>
        </trim>
    </insert>

运行时提示:

### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: Table 'entity_storage_0.entity_detail' doesn't exist
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO entity_detail          ( id,             entity_id,             version,             section,             data,             create_date,             update_time )           values ( ?,                 ?,                 ?,                 ?,                 ?,                 ?,                 ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'entity_storage_0.entity_detail' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'entity_storage_0.entity_detail' doesn't exist] with root cause

java.sql.SQLSyntaxErrorException: Table 'entity_storage_0.entity_detail' doesn't exist

排查原因:

看到这个提示的第一想法就是sql解析有问题,因为数据库以及对应的表都是自己亲手创建的,并且也check了一遍,不是这块儿导致的.

然后就看看是否别人也遇到了类似问题,上程序员聚集地sharding俱乐部默默注视了一波:https://github.com/apache/shardingsphere/issues ,没有发现啥有价值的东西.没办法,只能查看源码,最终在sharding重写sql的时候发现了猫腻..

 /**
     * Route SQL.
     *
     * @param logicSQL logic SQL
     * @param parameters SQL parameters
     * @param sqlStatement SQL statement
     * @return parse result
     */
    @SuppressWarnings("unchecked")
    public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
        Optional<ShardingStatementValidator> shardingStatementValidator = ShardingStatementValidatorFactory.newInstance(sqlStatement);
        if (shardingStatementValidator.isPresent()) {
            shardingStatementValidator.get().validate(shardingRule, sqlStatement, parameters);
        }
        SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(metaData.getRelationMetas(), logicSQL, parameters, sqlStatement);
        Optional<GeneratedKey> generatedKey = sqlStatement instanceof InsertStatement
                ? GeneratedKey.getGenerateKey(shardingRule, metaData.getTables(), parameters, (InsertStatement) sqlStatement) : Optional.<GeneratedKey>absent();
        ShardingConditions shardingConditions = getShardingConditions(parameters, sqlStatementContext, generatedKey.orNull(), metaData.getRelationMetas());
        boolean needMergeShardingValues = isNeedMergeShardingValues(sqlStatementContext);
        if (sqlStatementContext.getSqlStatement() instanceof DMLStatement && needMergeShardingValues) {
            checkSubqueryShardingValues(sqlStatementContext, shardingConditions);
            mergeShardingConditions(shardingConditions);
        }
        RoutingEngine routingEngine = RoutingEngineFactory.newInstance(shardingRule, metaData, sqlStatementContext, shardingConditions);
        RoutingResult routingResult = routingEngine.route();
        if (needMergeShardingValues) {
            Preconditions.checkState(1 == routingResult.getRoutingUnits().size(), "Must have one sharding with subquery.");
        }
        SQLRouteResult result = new SQLRouteResult(sqlStatementContext, shardingConditions, generatedKey.orNull());
        result.setRoutingResult(routingResult);
        if (sqlStatementContext instanceof InsertSQLStatementContext) {
            setGeneratedValues(result);
        }
        return result;
    }

sharding在重写sql的时候会拿原sql语句中的table name与metaData中的数据做逻辑处理,sql中的表明需要与

entityDetailTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("entity_id", "entity_detail_${entity_id % 2}"));

entity_detail_${entity_id % 2}中的entity_detail_保持一致.否则会导致sql解析失败,以原sql表中的数据为准...

因此,使用sharding的时候需要保证sql中的表名、分表规则的表名前缀保持一致.

附上springboot2.x + mybatis + sharding-jdbc分库分表demo:
https://github.com/panyingself/springboot-sharding-jdbc-mybatis/tree/master/sharding-jdbc-mybatis-sharding