pagehelper-spring-boot
pagehelper-spring-boot copied to clipboard
Published
20 hours ago •
pagehelper
pagehelper
pagehelper 集成 sharding jdbc 空指针
sharding 自定义 分片逻辑 需要改造 dosharding 分配逻辑（需要调用bmapper 查询某些数据然后判断分配策略）
代码逻辑如下:执行分页 ->shardingjdbc 修改preparestatment执行 分片策略（查询sql）->按照分片策略找到表执行mapper查询
异常现象:
amapper ->startpage ->pageintercept->a#count ->count(1)from a
->preparestatment
->bmapper ->pageintercept->b#count->count(1)from b
->bmapper ->select * from b LIMIT ? ::: [10] 执行有误
-> clear thread_local
->a afterCount->get thread_local ->null exception
bmapper这边可以修改成jdbc方式规避掉，pagehelper这里可以做到处理吗
查询逻辑
public PageInfo<Course> pagelistCourse(@RequestBody Course course) {
CourseExample courseExample = buildCondition(course);
try (HintManager hintManager = HintManager.getInstance();) {
setHintValue(hintManager);
PageHelper.startPage(1, 10,true);
List<Course> courses = courseMapper.selectByExample(courseExample);
return PageInfo.of(courses);
}
}
sharding分片逻辑
@Slf4j
public class MyHintShardingStrategy implements HintShardingAlgorithm<String> {
/**
* @param availableTargetNames
* @param shardingValue
* @return
* @see ShardingRouteDecorator#getShardingConditions(java.util.List, org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext, org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaData, org.apache.shardingsphere.core.rule.ShardingRule)
* @see
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValue) {
log.info("availableTargetNames:{} ,shardingValue:{}", availableTargetNames, shardingValue);
Collection<String> result = new ArrayList<>();
if (availableTargetNames.size() == 1) {
return availableTargetNames;
}
log.info("查看pagehelper值:{}", PageHelper.getLocalPage());
//test 嵌套查询
log.info("执行一段嵌套查询 ");
UserMapper mapper = SpringContants.beanFactory.getBean(UserMapper.class);
//count 阶段不执行
if (PageHelper.getLocalPage().getTotal() > 0) {
log.info("执行查询路由表sql");
mapper.selectByExample(new UserExample());
}
// DataSource dataSource = SpringContants.beanFactory.getBean(DataSource.class);
// JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
for (String each : availableTargetNames) {
for (Object value : shardingValue.getValues()) {
if (value instanceof Number) {
Number number = (Number) value;
if (each.endsWith(String.valueOf(number.longValue() % 2 + 1))) {
result.add(each);
}
}
}
}
return result;
}
}
日志信息
2020-11-11 15:10:27.691 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.C.selectByExample_COUNT : ==> Preparing: SELECT count(0) FROM course
2020-11-11 15:10:27.692 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.C.selectByExample_COUNT : ==> Parameters:
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : availableTargetNames:[course_1, course_2] ,shardingValue:HintShardingValue(logicTableName=course, columnName=, values=[10])
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : 查看pagehelper值:Page{count=true, pageNum=1, pageSize=10, startRow=0, endRow=10, total=0, pages=0, reasonable=false, pageSizeZero=false}[]
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : 执行一段嵌套查询
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : availableTargetNames:[course] ,shardingValue:HintShardingValue(logicTableName=course, columnName=, values=[10])
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM course
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@7c8fbff3, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3da1bfd8), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3da1bfd8, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=14, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@3366561c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3759d4a8, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@725b5c27, containsSubquery=false)
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT count(0) FROM course_1
2020-11-11 15:10:27.693 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT count(0) FROM course
2020-11-11 15:10:27.701 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.C.selectByExample_COUNT : <== Total: 1
2020-11-11 15:10:27.747 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.CourseMapper.selectByExample : ==> Preparing: select cid, cname, user_id, status, update_time from course order by update_time desc LIMIT ?
2020-11-11 15:10:27.747 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.CourseMapper.selectByExample : ==> Parameters: 10(Integer)
2020-11-11 15:10:27.747 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : availableTargetNames:[course_1, course_2] ,shardingValue:HintShardingValue(logicTableName=course, columnName=, values=[10])
2020-11-11 15:10:27.747 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : 查看pagehelper值:Page{count=true, pageNum=1, pageSize=10, startRow=0, endRow=10, total=27, pages=3, reasonable=false, pageSizeZero=false}[]
2020-11-11 15:10:27.747 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : 执行一段嵌套查询
2020-11-11 15:10:27.747 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : 执行查询路由表sql
2020-11-11 15:10:27.872 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.b.U.selectByExample_COUNT : ==> Preparing: SELECT count(0) FROM user
2020-11-11 15:10:27.872 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.b.U.selectByExample_COUNT : ==> Parameters:
2020-11-11 15:10:27.874 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM user
2020-11-11 15:10:27.874 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@2b7e7491, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1b3cc0e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1b3cc0e, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=14, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@364e1327, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@725d67e6, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@43d81a56, containsSubquery=false)
2020-11-11 15:10:27.874 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: SELECT count(0) FROM user
2020-11-11 15:10:27.878 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.b.U.selectByExample_COUNT : <== Total: 1
2020-11-11 15:10:27.879 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.base.UserMapper.selectByExample : ==> Preparing: select id, name, age from user LIMIT ?
2020-11-11 15:10:27.879 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.base.UserMapper.selectByExample : ==> Parameters: 10(Integer)
2020-11-11 15:10:27.880 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: select
id, name, age
from user
LIMIT ?
2020-11-11 15:10:27.880 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5366ba20, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ffc15f4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ffc15f4, projectionsContext=ProjectionsContext(startIndex=28, stopIndex=40, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7d0127c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@7b57b028, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2b5899a1, containsSubquery=false)
2020-11-11 15:10:27.880 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: select
id, name, age
from user
LIMIT ? ::: [10]
2020-11-11 15:10:27.885 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.base.UserMapper.selectByExample : <== Total: 10
2020-11-11 15:10:28.007 INFO 20428 --- [nio-8080-exec-2] c.m.s.s.MyHintShardingStrategy : availableTargetNames:[course] ,shardingValue:HintShardingValue(logicTableName=course, columnName=, values=[10])
2020-11-11 15:10:28.007 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: select
cid, cname, user_id, status, update_time
from course
order by update_time desc
LIMIT ?
2020-11-11 15:10:28.007 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@13dbc2c6, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3e6b2d0f), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3e6b2d0f, projectionsContext=ProjectionsContext(startIndex=28, stopIndex=67, distinctRow=false, projections=[ColumnProjection(owner=null, name=cid, alias=Optional.empty), ColumnProjection(owner=null, name=cname, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=status, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@6619b9e3, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@c874c80, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@408ae2db, containsSubquery=false)
2020-11-11 15:10:28.008 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: select
cid, cname, user_id, status, update_time
from course_1
order by update_time desc
LIMIT ? ::: [10]
2020-11-11 15:10:28.008 INFO 20428 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m2 ::: select
cid, cname, user_id, status, update_time
from course
order by update_time desc
LIMIT ? ::: [10]
2020-11-11 15:10:28.015 DEBUG 20428 --- [nio-8080-exec-2] c.m.s.m.s.CourseMapper.selectByExample : <== Total: 10
