preface

There are several ways to realize the paging of the mybatis framework. The simplest is to use the native sql keyword limit. The other is to use the interceptor to splice sql to achieve the same functions as limit. The other is to use the PageHelper. Here, take MySQL as an example to explain these two common implementation methods.

No matter which implementation method is used, we can no longer use List for the returned results. We need a custom object Pager:

package com.macay.entity;

import java.util.List;

/**
 * @ClassName: Pager,Paging object
 * @Description:
 * @Author: Macay
 * @Date: 2021/9/22 11:58 afternoon
 */
public class Pager<T> {
    private int page;//Paging start page
    private int size;//Records per page
    private List<T> rows;//Collection of records returned
    private long total;//Total records

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }

    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    public long getTotal() {
        return total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    @Override
    public String toString() {
        return "Pager{" +
                "page=" + page +
                ", size=" + size +
                ", rows=" + rows +
                ", total=" + total +
                '}';
    }
}

1, Normal paging using limit

The core of using limit paging is SQL, which can be easily implemented without any plug-ins or tools. We splice this SQL in the mapper file:

SELECT * from user limit startIndex,pageSize 

startIndex: the starting position of paging, starting from 0;
pageSize: number of queries;

Interface:

List<Student> getStudentByPage(Map<String, Object> params);
long getCount();

mapper file:

<select id="getStudentByPage" resultType="com.macay.entity.Student">
    select * from student limit #{startIndex}, #{pageSize}
</select>
<select id="getCount" resultType="long">
    select count(*) from student
</select>

Test class:

@Test
public void testGetStudentByPage() {
    Pager<Student> studnentByPage = getStudnentByPage(2, 5);
    System.out.println(studnentByPage);
}
private Pager<Student> getStudnentByPage(int page, int size) {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    Map<String, Object> params = new HashMap<>();
    params.put("startIndex", (page-1)*size);
    params.put("pageSize", size);
    List<Student> students = mapper.getStudentByPage(params);
    Pager<Student> studentPager = new Pager<>();
    studentPager.setPage(page);
    studentPager.setSize(size);
    studentPager.setRows(students);
    studentPager.setTotal(mapper.getCount());
    return studentPager;
}

The test results are as follows:

2, Implementation using Mybatis paging interceptor

Using the Mybatis paging interceptor, we don't need to write paging query statements in the Mapper configuration file. We just need to write non paging query statements. Then, through the paging interceptor, we intercept the ordinary sql that needs paging query, replace the ordinary sql with paging sql, and skillfully realize paging query.

There is no in-depth explanation here. For details, you can search the explanation on the Internet:

https://blog.csdn.net/u014292162/article/details/52089808

https://blog.csdn.net/feinifi/article/details/88769101

3, Using the paging plug-in pagehelper

This plug-in supports many database versions, as follows:

The github address is as follows: https://github.com/pagehelper/Mybatis-PageHelper

In fact, the PageHelper method is also a third-party implementation of the second way to use the Interceptor interceptor. It internally helps us realize the functions of the Interceptor. The basic steps are as follows:

1. Introducing maven dependency

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

2. Add plugin configuration

Configure the PageHelper plug-in in the mybatis core configuration file (note the target directory):

<!--stay<environments>Before joining-->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

Note: since version 4.0.0, you can automatically identify the database. You don't need to specify the database

3. PageHelper object

The PageHelper.startPage static method is called before query.
Call the PageHelper.startPage static method before you need to paging the MyBatis query method.
The first MyBatis query method after this method will be paged.

The interfaces are as follows:

List<Student> getStudentByPageHelper();

mapper file:

<select id="getStudentByPageHelper" resultType="com.macay.entity.Student">
    select * from student
</select>

Test class:

@Test
public void getStudentByPageHelper() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    PageHelper.startPage(2, 5);
    List<Student> students = mapper.getStudentByPageHelper();
    System.out.println(students);
}

As shown in the figure, the query results in the second page of data, with 5 entries per page.

Not only that, we can also get other parameters related to paging:

@Test
public void getStudentByPageHelper() {
    // Integrated paging assistant plug-in to test paging
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    // Set paging related parameters current page + number of items displayed per page
    PageHelper.startPage(2, 5);
    List<Student> students = mapper.getStudentByPageHelper();
    // System.out.println(students);
    //Get paging related parameters
    PageInfo<Student> pageInfo = new PageInfo<>(students);
    System.out.println("Current page:"+pageInfo.getPageNum());
    System.out.println("Number of items displayed per page:"+pageInfo.getPageSize());
    System.out.println("Total number of pieces:"+pageInfo.getTotal());
    System.out.println("Total pages:"+pageInfo.getPages());
    System.out.println("previous page:"+pageInfo.getPrePage());
    System.out.println("Next page:"+pageInfo.getNextPage());
    System.out.println("First page:"+pageInfo.isIsFirstPage());
    System.out.println("Is this the last page:"+pageInfo.isIsLastPage());
}

The results are as follows: