springboot+mybatis+pagehelper实现分页查询

雪____

于 2019-07-01 17:52:56 发布

阅读量1.8k

点赞数 2

文章标签： springboot mybatis pagehelper

本文链接：https://blog.csdn.net/qq_37231511/article/details/94398601

版权

案例代码下载地址：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属性，但是这两种方式都没有用，最后更换版本解决了问题