You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/01/02 05:39:06 UTC
[GitHub] [incubator-shardingsphere] LuciferZK opened a new issue #3845:
mybatis-plus or pagehelper and sharding-jdbc implement paging,
but eventually lead to a full table query
LuciferZK opened a new issue #3845: mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query
URL: https://github.com/apache/incubator-shardingsphere/issues/3845
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：
```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:
```yaml
#服务端口
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：
```java
@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：
````java
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.
````java
@Test
public void findByPage() {
QueryWrapper<Dict> queryWrapper=new QueryWrapper<>();
IPage<Dict> dictIPage=new Page<>(1,3);
IPage<Dict> selectPage = dictDao.selectPage(dictIPage, queryWrapper);
}
````
```java
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 * FROM `t_dict` LIMIT 1,2;")
List<Dict> findByPage();`
```java
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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] RaigorJiang commented on issue #3845:
mybatis-plus or pagehelper and sharding-jdbc implement paging,
but eventually lead to a full table query
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #3845: mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query
URL: https://github.com/apache/incubator-shardingsphere/issues/3845#issuecomment-570127188
@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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3845:
mybatis-plus or pagehelper and sharding-jdbc implement paging,
but eventually lead to a full table query
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3845: mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query
URL: https://github.com/apache/incubator-shardingsphere/issues/3845#issuecomment-570126675
Hi, your logic SQL is `SELECT * FROM `t_dict` LIMIT 1,2;` which do not include any sharding key, it should be full table query.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] RaigorJiang commented on issue #3845:
mybatis-plus or pagehelper and sharding-jdbc implement paging,
but eventually lead to a full table query
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #3845: mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query
URL: https://github.com/apache/incubator-shardingsphere/issues/3845#issuecomment-570125316
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #3845:
mybatis-plus or pagehelper and sharding-jdbc implement paging,
but eventually lead to a full table query
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #3845: mybatis-plus or pagehelper and sharding-jdbc implement paging, but eventually lead to a full table query
URL: https://github.com/apache/incubator-shardingsphere/issues/3845
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services