背景:
最近使用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
网友评论