MyBatis利用插件PageHelper分页

      在Spring Boot整合MyBatis，对查询结果分页。本示例用的数据库是PostgreSQL，数据库是"testdb"，public模式。

1.Linux平台，创建数据库"testdb"，和表"student"：

postgres@shanks:~$ psql
psql (9.6.7)
输入 "help" 来获取帮助信息.

postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb;
您现在已经连接到数据库 "testdb",用户 "postgres".
testdb=#create table student(id serial primary key, name varchar, sex varchar default '男');
CREATE TABLE
testdb=# \d public.student;
                         数据表 "public.student"
 栏位 |       类型        |                    修饰词
------+-------------------+-----------------------------------------------
 id   | integer           | 非空 默认 nextval('student_id_seq'::regclass)
 name | character varying |
 sex  | character varying | 默认 '男'::character varying
索引：
    "student_pkey" PRIMARY KEY, btree (id)

testdb=# insert into public.student(name,sex) values('刘备','男'),('曹操','男'),('孙权','男'),('貂蝉','女'),('诸葛亮','男'),('张辽','男'),('陈寿','男');
INSERT 0 7
testdb=# select * from student;
 id |  name  | sex 
----+--------+-----
  1 | 刘备   | 男
  2 | 曹操   | 男
  3 | 孙权   | 男
  4 | 貂蝉   | 女
  5 | 诸葛亮 | 男
  6 | 张辽   | 男
  7 | 陈寿   | 男
(7 行记录)

2.创建Spring Boot项目

• 结构:

      

• 所有maven依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<!--postgresql数据库-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<!--分页插件-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.2.1</version>
</dependency>

• application.properties配置：

# 服务端口
server.port = 8848
# 数据库链接
spring.datasource.driverClassName = org.postgresql.Driver
spring.datasource.url = jdbc:postgresql://localhost:5432/testdb?currentSchema=public,sys,app
spring.datasource.username = postgres
spring.datasource.password = aaa
# mybatis配置文件
mybatis.config-location = classpath:mybatis-config.xml
# 指定实体类所在包，就不用在所有实体类上加其他注解，就可以被spring容器管理
mybatis.type-aliases-package = com.example.demo.entity

• mybatis-config.xml配置，需要添加interceptor：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 配置分页插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- 设置数据库类型 PostgreSQL-->
            <property name="dialect" value="postgresql"/>
        </plugin>
    </plugins>
</configuration>

• DemoApplication.java，注解”@MapperScan("com.example.demo.mapper")“可以扫描mapper类，本例采用的是注解的方式写sql：

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication()
@MapperScan("com.example.demo.mapper")
public class DemoApplication {
	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}
}

• StudentMapper.java，启动类上有@MapperScan，就不用再添加@Mapper注解：

package com.example.demo.mapper;

import com.example.demo.entity.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;

public interface StudentMapper {

    @Select("SELECT * FROM student")
    List<Student> queryAll();

    @Select("SELECT * FROM student WHERE sex=${sex}")
    List<Student> queryBySex(@Param("sex") String sex);

}

• 实体类 Student.java：

package com.example.demo.entity;
public class Student {

    Integer id;

    String name;

    String sex;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

•   服务类 StudentService.java：

package com.example.demo.service;

import com.example.demo.entity.Student;
import com.example.demo.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentService {

    @Autowired
    private StudentMapper studentMapper;

    /**
     * 查询所有
     * @return
     */
    public List<Student> queryAll() {
        return studentMapper.queryAll();
    }

    /**
     * 按性别查询
     * @return
     */
    public List<Student> queryBySex(String sex) {
        return studentMapper.queryBySex(sex);
    }
}

• StudentController.java，分页查询：

package com.example.demo.contoller;

import com.example.demo.entity.Student;
import com.example.demo.service.StudentService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @RequestMapping(value = {""}, method = {RequestMethod.GET, RequestMethod.POST})
    public PageInfo queryAll(
            @RequestParam(value = "pageNumber", defaultValue = "1") Integer pageNumber,
            @RequestParam(value = "pageSize",defaultValue = "3") Integer pageSize
    ) {
        // 分页查询。只对后一个查询有效
        PageHelper.startPage(pageNumber,pageSize);
        // 结果中已经分页
        List<Student> lists = studentService.queryAll();
        // 返回分页相关信息
        return new PageInfo(lists);
    }

    @GetMapping("/sex")
    public PageInfo queryBySex(
            @RequestParam(value = "pageNumber", defaultValue = "1") Integer pageNumber,
            @RequestParam(value = "pageSize",defaultValue = "3") Integer pageSize,
            @RequestParam("sex") String sex
    ) {
        // 分页查询。只对后一个查询有效
        PageHelper.startPage(pageNumber,pageSize);
        // 结果中已经分页
        List<Student> lists = studentService.queryBySex(sex);
        // 返回分页相关信息
        return new PageInfo(lists);
    }
}

3.启动项目，在浏览器输入http://localhost:8848/student?pageNumber=1&pageSize=2，返回如下：

{
"pageNum": 1,
"pageSize": 2,
"size": 2,
"orderBy": null,
"startRow": 1,
"endRow": 2,
"total": 7,
"pages": 4,
"list": [
{
"id": 1,
"name": "刘备",
"sex": "男"
},
{
"id": 2,
"name": "曹操",
"sex": "男"
}
],
"prePage": 0,
"nextPage": 2,
"isFirstPage": true,
"isLastPage": false,
"hasPreviousPage": false,
"hasNextPage": true,
"navigatePages": 8,
"navigatepageNums": [
1,
2,
3,
4
],
"navigateFirstPage": 1,
"navigateLastPage": 4,
"firstPage": 1,
"lastPage": 4
}

      可以设置分页信息，返回其他结果。