springboot+mybatis+pagehelper实现分页查询_mybatis pagereq

案例代码下载地址：https://github.com/snowlavenderlove/springbootPagehelper.git

1.数据库springimport_excel,表user,表结构如下：

2. 创建项目springbootPagehelper,创建springboot项目教程博文：https://blog.csdn.net/qq_37231511/article/details/90669242

3.通过mybatis-generator自动生成代码，mybatis-generator教程博文：https://blog.csdn.net/qq_37231511/article/details/90692784，将生成的代码放到项目中，如图

4. 编辑pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.12.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.xue</groupId>
	<artifactId>springbootPagehelper</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springbootPagehelper</name>
	<description>Demo project for Spring Boot</description>
 
	<properties>
		<java.version>1.8</java.version>
		<maven-jar-plugin.version>3.0.0</maven-jar-plugin.version>
	</properties>
 
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
 
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
		    <groupId>commons-logging</groupId>
		    <artifactId>commons-logging</artifactId>
		    <version>1.2</version>
		</dependency>
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid</artifactId>
		    <version>1.1.17</version>
		</dependency>	
		<dependency>
		 <groupId>com.github.pagehelper</groupId>
		 <artifactId>pagehelper-spring-boot-starter</artifactId>
		 <version>1.1.0-beta</version>
		</dependency>
 
		</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
 
</project>

5 .编辑application.properties

 
#mysql
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springimport_excel?&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456
#druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#dao
mybatis.type-aliases-package==com.xue.repository.dao
mybatis.mapper-locations=classpath*:com/xue/repository/mapper/*.xml
 
#thymeleaf
#spring.thymeleaf.prefix=classpath:/templates/
#spring.thymeleaf.suffix=.jsp
spring.thymeleaf.encoding=UTF-8
 
#pagehelper
#配置helperDialect属性来指定分页插件使用哪种数据库
pagehelper.helperDialect=MySQL
#分页合理化参数，默认值为false。当该参数设置为 true 时，pageNum<=0 时会查询第一页， pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询
pagehelper.reasonable=false
#支持通过 Mapper 接口参数来传递分页参数，默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页
pagehelper.supportMethodsArguments=false
#
pagehelper.params=count=countSql
#默认值为 false，该参数对使用 RowBounds 作为分页参数时有效。 当该参数设置为 true 时，会将 RowBounds 中的 offset 参数当成 pageNum 使用，可以用页码和页面大小两个参数进行分页
pagehelper.offsetAsPageNum=true
#默认值为 false，当该参数设置为 true 时，如果 pageSize=0 或者 RowBounds.limit = 0 就会查询出全部的结果（相当于没有执行分页查询，但是返回结果仍然是 Page 类型）
pagehelper.pageSizeZero=true
 
 

6.在entity包下创建http包，并创建类UserReq、BasePageReq、UserListRes、BasePageRes

 UserReq

package com.xue.entity.http;
 
public class UserReq extends BasePageReq{
    private Integer id;
 
    private String username;
 
    private String password;
 
	public Integer getId() {
		return id;
	}
 
	public void setId(Integer id) {
		this.id = id;
	}
 
	public String getUsername() {
		return username;
	}
 
	public void setUsername(String username) {
		this.username = username;
	}
 
	public String getPassword() {
		return password;
	}
 
	public void setPassword(String password) {
		this.password = password;
	}
    
    
}

BasePageReq:

 

package com.xue.entity.http;
 
public class BasePageReq {
	
	private int pageSize;
	
	private int pageIndex;
 
	public int getPageSize() {
		return pageSize;
	}
 
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
 
	public int getPageIndex() {
		return pageIndex;
	}
 
	public void setPageIndex(int pageIndex) {
		this.pageIndex = pageIndex;
	}
	
	
 
}

 UserListRes:

package com.xue.entity.http;
 
import java.util.List;
 
import com.xue.entity.model.User;
 
public class UserListRes extends BasePageRes{
	
	private List<User> dataList;
 
	public List<User> getDataList() {
		return dataList;
	}
 
	public void setDataList(List<User> dataList) {
		this.dataList = dataList;
	}
	
	
	
 
}

BasePageRes:

package com.xue.entity.http;
 
public class BasePageRes {
	
	private int allCount;
 
	public int getAllCount() {
		return allCount;
	}
 
	public void setAllCount(int allCount) {
		this.allCount = allCount;
	}
	
	
 
}

 7.创建controller,创建类UserController

package com.xue.controller;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
 
import com.github.pagehelper.PageInfo;
import com.xue.entity.http.UserListRes;
import com.xue.entity.http.UserReq;
import com.xue.entity.model.User;
import com.xue.service.UserService;
 
@Controller
public class UserController {
	
	@Autowired
	private UserService userService;
	
	@RequestMapping("/select")
	@ResponseBody
	public UserListRes indexSelect(HttpSession session,HttpServletRequest request,@RequestBody UserReq inparam){
		UserListRes res = null;
		
 
		try {
			res = userService.selectAllUser(inparam);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(null == res){
			System.out.println("未获得数据");
		}
		
		return res;
		
	}
	
 
	
 
}

8.创建service层，创建接口UserService,并创建接口实现类UserServiceImpl

 UserService

package com.xue.service;
 
import com.github.pagehelper.PageInfo;
import com.xue.entity.http.UserListRes;
import com.xue.entity.http.UserReq;
import com.xue.entity.model.User;
 
public interface UserService {
	
	public UserListRes selectAllUser(UserReq req);
 
}

UserServiceImpl

package com.xue.service.impl;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.xue.entity.http.UserListRes;
import com.xue.entity.http.UserReq;
import com.xue.entity.model.User;
import com.xue.repository.dao.UserMapper;
import com.xue.service.UserService;
@Service
public class UserServiceImpl implements UserService {
	
	@Autowired
	private UserMapper userMapper;
 
	@Override
	public UserListRes selectAllUser(UserReq req) {
		
 
//		pageIndex为当前页码;pageSize每页显示记录数;count=true则进行count查询总记录数
		PageHelper.startPage(req.getPageIndex(),req.getPageSize(),true);
 
		List<User> datas = userMapper.selectAllUser();
		
		PageInfo<User> page = new PageInfo<User>(datas);
		
		UserListRes res = new UserListRes();
		
		res.setAllCount((int)page.getTotal());
		
		res.setDataList(datas);
		
		return res;
	}
 
	
	
	
	
	
 
}

9.dao层编辑UserMpper.java与UserMapper.xml

UserMapper.java最后添加：

    List<User> selectAllUser();
 UserMapper.xml最后添加：

  <select id="selectAllUser" resultMap="BaseResultMap">
      select * from user 
  </select>
 

 10.主程序类SpringbootPagehelperApplication

package com.xue;
 
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
@MapperScan("com.xue.repository.dao")
public class SpringbootPagehelperApplication {
 
	public static void main(String[] args) {
		SpringApplication.run(SpringbootPagehelperApplication.class, args);
	}
}

11.至此代码完成，用postman进行测试，效果如图：

第一页，展示五条数据：

第二页，展示五条数据： 

 

注：如果遇到sql报错limit ?语句错误，或者是代码运行无问题，但是查出来的数据是全部数据，则是pagehelper版本问题导致，此案例springboot版本为1.5.2,pagehelper版本未1.1.0-beta，在这两个错误上卡了很久，网上有些解决方式为pom.xml中要配合其他依赖，有的为application.properties不用配置pagehelper属性，但是这两种方式都没有用，最后更换版本解决了问题