引入pom文件
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
开启扫描tkmapper
@MapperScan("com.carenmg.shangpu.mapper")
@SpringBootApplication
public class ShangpuApplication {
public static void main(String[] args) {
SpringApplication.run(ShangpuApplication.class, args);
}
}
service层代码
public PageInfo<Info> findByPage(Integer page, Integer limit, Integer types_pid, Integer city_pid, Integer city_id, Integer types_id, Integer mark_id, Integer mark, String keyword) {
PageHelper.startPage(page, limit);//分页代码
Example example = new Example(Info.class);
if (!ObjectUtils.isEmpty(keyword)) {
example.and().andLike("title", "%" + keyword + "%");
}
if (mark.equals(1)) {
//已处理
example.and().andNotEqualTo("mark_id", 1);
if(mark_id.equals(0)){
List<Integer> allMark = markService.findAllId();
example.and().andIn("mark_id",allMark);
}else{
example.and().andEqualTo("mark_id",mark_id);
}
} else {
//未处理
example.and().andEqualTo("mark_id", 1);
}
if (city_id.equals(0)) {
//全部
List<Integer> allCitiesIdByPid = cityService.findAllCitiesIdByPid(city_pid);
example.and().andIn("city_id", allCitiesIdByPid);
} else {
//区域
example.and().andEqualTo("city_id", city_id);
}
if (types_id.equals(0)) {
//全部
List<Integer> allTypesIdByPid = typesService.findAllTypesIdByPid(types_pid);
example.and().andIn("types_id",allTypesIdByPid);
} else {
//部分
example.and().andEqualTo("types_id",types_id);
}
List<Info> infos = mapper.selectByExample(example);
return new PageInfo<>(infos);
}
分页失效,返回全部数据
{
"code": 0,
"msg": "OK",
"data": {
"total": 26,
"list": [
...//这里省略了
],
"pageNum": 1,
"pageSize": 26,
"size": 26,
"startRow": 0,
"endRow": 25,
"pages": 1,
"prePage": 0,
"nextPage": 0,
"isFirstPage": true,
"isLastPage": true,
"hasPreviousPage": false,
"hasNextPage": false,
"navigatePages": 8,
"navigatepageNums": [
1
],
"navigateFirstPage": 1,
"navigateLastPage": 1
}
}
发现返回数据一共有26条,但是我设置的是10条;分析日志如下:
2021-04-05 15:40:40.560 INFO 2437 --- [nio-8000-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-04-05 15:40:40.560 INFO 2437 --- [nio-8000-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2021-04-05 15:40:40.564 INFO 2437 --- [nio-8000-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 4 ms
2021-04-05 15:40:40.571 INFO 2437 --- [nio-8000-exec-2] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Starting...
2021-04-05 15:40:40.582 INFO 2437 --- [nio-8000-exec-2] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Start completed.
2021-04-05 15:40:40.583 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.M.selectByExample_COUNT : ==> Preparing: SELECT count(0) FROM fa_mark
2021-04-05 15:40:40.583 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.M.selectByExample_COUNT : ==> Parameters:
2021-04-05 15:40:40.584 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.M.selectByExample_COUNT : <== Total: 1
2021-04-05 15:40:40.585 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : ==> Preparing: SELECT id,title,remark FROM fa_mark order by id ASC LIMIT ?
2021-04-05 15:40:40.585 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : ==> Parameters: 10(Integer)
2021-04-05 15:40:40.586 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : <== Total: 5
2021-04-05 15:40:40.589 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : ==> Preparing: SELECT id,url,title,contact,phone,qrcode,city_id,types_id,mark,mark_id,created_time,updated_time,house_id FROM fa_info WHERE ( ( mark_id <> ? ) and ( mark_id in ( ? , ? , ? , ? , ? ) ) and ( city_id = ? ) and ( types_id = ? ) )
2021-04-05 15:40:40.589 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : ==> Parameters: 1(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 10(Integer), 10(Integer)
2021-04-05 15:40:40.595 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : <== Total: 26
发现sql不对劲,怎么给另一个sevice的查询分页了?将分页代码放到需要分页的查询之前:
//前面的省略了,删除方法第一行的分页代码,放到这里就可以了
PageHelper.startPage(page, limit);
List<Info> infos = mapper.selectByExample(example);
return new PageInfo<>(infos);
返回数据
{
"code": 0,
"msg": "OK",
"data": {
"total": 26,
"list": [...],//这里省略了
"pageNum": 1,
"pageSize": 10,
"size": 10,
"startRow": 1,
"endRow": 10,
"pages": 3,
"prePage": 0,
"nextPage": 2,
"isFirstPage": true,
"isLastPage": false,
"hasPreviousPage": false,
"hasNextPage": true,
"navigatePages": 8,
"navigatepageNums": [
1,
2,
3
],
"navigateFirstPage": 1,
"navigateLastPage": 3
}
}
查看log日志
2021-04-05 15:45:11.384 INFO 2437 --- [nio-8000-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-04-05 15:45:11.385 INFO 2437 --- [nio-8000-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2021-04-05 15:45:11.388 INFO 2437 --- [nio-8000-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 3 ms
2021-04-05 15:45:11.403 INFO 2437 --- [nio-8000-exec-2] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Starting...
2021-04-05 15:45:11.414 INFO 2437 --- [nio-8000-exec-2] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Start completed.
2021-04-05 15:45:11.414 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : ==> Preparing: SELECT id,title,remark FROM fa_mark order by id ASC
2021-04-05 15:45:11.414 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : ==> Parameters:
2021-04-05 15:45:11.415 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.MarkMapper.selectByExample : <== Total: 5
2021-04-05 15:45:11.424 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.I.selectByExample_COUNT : ==> Preparing: SELECT count(0) FROM fa_info WHERE ((mark_id <> ?) AND (mark_id IN (?, ?, ?, ?, ?)) AND (city_id = ?) AND (types_id = ?))
2021-04-05 15:45:11.424 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.I.selectByExample_COUNT : ==> Parameters: 1(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 10(Integer), 10(Integer)
2021-04-05 15:45:11.425 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.m.I.selectByExample_COUNT : <== Total: 1
2021-04-05 15:45:11.425 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : ==> Preparing: SELECT id,url,title,contact,phone,qrcode,city_id,types_id,mark,mark_id,created_time,updated_time,house_id FROM fa_info WHERE ( ( mark_id <> ? ) and ( mark_id in ( ? , ? , ? , ? , ? ) ) and ( city_id = ? ) and ( types_id = ? ) ) LIMIT ?
2021-04-05 15:45:11.426 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : ==> Parameters: 1(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 10(Integer), 10(Integer), 10(Integer)
2021-04-05 15:45:11.428 DEBUG 2437 --- [nio-8000-exec-2] c.c.s.mapper.InfoMapper.selectByExample : <== Total: 10
limit应用在了fa_info这个表,正确