mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query

[LuciferZK]

Hello, the problem is that I use the jar package of mybati-plus or pagehelper to integrate with sharding-jdbc to implement the paging function, resulting in a full table query. pom.xml：

<dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.0.0-RC1</version>
</dependency>

 <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.2.0</version>
</dependency>

 <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.10</version>
 </dependency>

application.yml:

#服务端口
server:
  port: 56081
#服务名
spring:
  application:
    name: sharding-jdbc-examples
  main:
    allow-bean-definition-overriding: true
  #shardingsphere相关配置
  shardingsphere:
    datasource:
      names: m1,m2  
      m1:  
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.87.133:3306/user_db?useUnicode=true
        username: root
        password: 123456
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.87.134:3306/user_db?useUnicode=true
        username: root
        password: 123456
    sharding:
      broadcast‐tables: t_dict  #公共表
      tables:
        t_dict:
          key-generator:
            column: dict_id
            type: SNOWFLAKE
    props:
      sql:
        show: true   #打印sql

Test code snippet：

  @Test
    public void findByPage() {
        QueryWrapper<Dict> queryWrapper=new QueryWrapper<>();
        PageHelper.startPage(1,2);
        List<Dict> dicts = dictDao.selectList(queryWrapper);
        PageInfo<Dict> pageInfo = new PageInfo<>(dicts);
        System.out.println("pageInfo:"+pageInfo);
    }

Console print：

2020-01-02 13:21:13.753 DEBUG 14988 --- [           main] c.l.sharding.dao.DictDao.selectList      : ==>  Preparing: SELECT dict_id,code,type,value FROM t_dict 
2020-01-02 13:21:13.779 DEBUG 14988 --- [           main] c.l.sharding.dao.DictDao.selectList      : ==> Parameters: 
2020-01-02 13:21:14.255  INFO 14988 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2020-01-02 13:21:14.256  INFO 14988 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  dict_id,code,type,value  FROM t_dict
2020-01-02 13:21:14.256  INFO 14988 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=SELECT  dict_id,code,type,value  FROM t_dict)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=30, groupByLastIndex=0, items=[CommonSelectItem(expression=dict_id, alias=Optional.absent()), CommonSelectItem(expression=code, alias=Optional.absent()), CommonSelectItem(expression=type, alias=Optional.absent()), CommonSelectItem(expression=value, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-02 13:21:14.256  INFO 14988 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  dict_id,code,type,value  FROM t_dict
2020-01-02 13:21:14.295 DEBUG 14988 --- [           main] c.l.sharding.dao.DictDao.selectList      : <==      Total: 6
pageInfo:PageInfo{pageNum=1, pageSize=6, size=6, startRow=0, endRow=5, total=6, pages=1, list=[Dict(dictId=1, type=1, code=1, value=1), Dict(dictId=2, type=2, code=2, value=2), Dict(dictId=3, type=3, code=3, value=3), Dict(dictId=4, type=4, code=4, value=4), Dict(dictId=5, type=5, code=5, value=5), Dict(dictId=6, type=6, code=6, value=6)], prePage=0, nextPage=0, isFirstPage=true, isLastPage=true, hasPreviousPage=false, hasNextPage=false, navigatePages=8, navigateFirstPage=1, navigateLastPage=1, navigatepageNums=[1]}

I use pagehelper for pagination，The result is not what I expected. The paging effect was not achieved, but all the queries came out.

Later, I switched to using the pagination api of mybatis-plus.

 @Test
    public void findByPage() {
        QueryWrapper<Dict> queryWrapper=new QueryWrapper<>();
        IPage<Dict> dictIPage=new Page<>(1,3);
        IPage<Dict> selectPage = dictDao.selectPage(dictIPage, queryWrapper);
    }

2020-01-02 13:26:13.309 DEBUG 3480 --- [           main] c.l.sharding.dao.DictDao.selectPage      : ==>  Preparing: SELECT dict_id,code,type,value FROM t_dict 
2020-01-02 13:26:13.331 DEBUG 3480 --- [           main] c.l.sharding.dao.DictDao.selectPage      : ==> Parameters: 
2020-01-02 13:26:13.778  INFO 3480 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2020-01-02 13:26:13.779  INFO 3480 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  dict_id,code,type,value  FROM t_dict
2020-01-02 13:26:13.780  INFO 3480 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=SELECT  dict_id,code,type,value  FROM t_dict)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=30, groupByLastIndex=0, items=[CommonSelectItem(expression=dict_id, alias=Optional.absent()), CommonSelectItem(expression=code, alias=Optional.absent()), CommonSelectItem(expression=type, alias=Optional.absent()), CommonSelectItem(expression=value, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-02 13:26:13.780  INFO 3480 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  dict_id,code,type,value  FROM t_dict
2020-01-02 13:26:13.819 DEBUG 3480 --- [           main] c.l.sharding.dao.DictDao.selectPage      : <==      Total: 6

But when I write sql myself, without using the jar package provided by a third party to integrate with sharding-jdbc, the paging function can be implemented。

@Select("SELECT * FROMt_dictLIMIT 1,2;") List<Dict> findByPage();

2020-01-02 13:33:51.200 DEBUG 14840 --- [           main] c.l.sharding.dao.DictDao.findByPage      : ==>  Preparing: SELECT * FROM `t_dict` LIMIT 1,2; 
2020-01-02 13:33:51.222 DEBUG 14840 --- [           main] c.l.sharding.dao.DictDao.findByPage      : ==> Parameters: 
2020-01-02 13:33:51.663  INFO 14840 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2020-01-02 13:33:51.664  INFO 14840 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT * FROM `t_dict` LIMIT 1,2;
2020-01-02 13:33:51.665  INFO 14840 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_dict, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_dict, quoteCharacter=BACK_QUOTE, schemaNameLength=0), SQLToken(startIndex=29), SQLToken(startIndex=31)], parametersIndex=0, logicSQL=SELECT * FROM `t_dict` LIMIT 1,2;)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=Limit(offset=LimitValue(value=1, index=-1, boundOpened=false), rowCount=LimitValue(value=2, index=-1, boundOpened=false)), subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-02 13:33:51.665  INFO 14840 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: SELECT * FROM `t_dict` LIMIT 1,2;
2020-01-02 13:33:51.705 DEBUG 14840 --- [           main] c.l.sharding.dao.DictDao.findByPage      : <==      Total: 2

Is sharding-jdbc not compatible with mybatis-plus or pagehelper to achieve paging effect? Or is there something wrong with my code?

[RaigorJiang]

Hi, LuciferZK Please check your usage of mybatis-plus and pagehelper to make sure logic SQL is correct. If your logic SQL doesn't have limit statement, SS can not understand your intentions either.

[terrymanu]

Hi, your logic SQL is SELECT * FROMt_dictLIMIT 1,2; which do not include any sharding key, it should be full table query.

[RaigorJiang]

@terrymanu LuciferZK's doubt is that there is no pagination with pagehelper, when the logic SQL is SELECT dict_id,code,type,value FROM t_dict.

[wentaotang0]

I ran into the same problem, mybatis plus and pagehelper paging were fine when not using shardingsphere-jdbc, but logicl sql did not have a limit when using it, causing problems with the actual sql