First, the paging plug-inPagehelper

PageHelper is a pagination plugin for Mybatis, very useful!

1.1 Spring BootRely on

<! -- PageHelper pagination plugin -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
</dependency>
Copy the code

Or you could introduce it this way

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>latest version</version>
</dependency>
Copy the code

1.2 PageHelperconfiguration

The configuration file adds the configuration of PageHelper, which mainly sets the paging dialect and supports interface parameters to pass paging parameters, as follows:

Pagehelper: # specify database helper-dialect: mysql # Default is false. Enable rationalization if pageNum<1 will query the first page if pageNum>Pages queries the last page. Disable rationalization if pageNum<1 or pageNum>/ / pages returns blank data for reasonable: false True # in order to support the startPage (Object params) method, increased the parameters to configure the mapping, for according to the attribute names and values from the objects can be configured pageNum, pageSize, count, pageSizeZero, reasonable, If no mapping is configured, the default value is pageNum=pageNum. pageSize=pageSize; count=countSql; reasonable=reasonable; pageSizeZero=pageSizeZero params: count=countSql row-bounds-with-count: trueCopy the code

See mybatis- PageHelper for the complete configuration file.

1.3 How to Page

Only the first Mybatis query (Select) method immediately after the pageHelper. startPage method is automatically paginated!!!!

@Test
public void selectForPage(a) {
    / / what page
    int currentPage = 2;
    // Number per page
    int pageSize = 5;
    / / sorting
    String orderBy = "id desc";
    PageHelper.startPage(currentPage, pageSize, orderBy);
    List<UserInfoPagehelperDO> users = userInfoPagehelperMapper.selectList();
    PageInfo<UserInfoPagehelperDO> userPageInfo = new PageInfo<>(users);
    log.info("userPageInfo:{}", userPageInfo);
}
Copy the code
. : userPageInfo:PageInfo{pageNum=2, pageSize=5, size=1, startRow=6, endRow=6, total=6, pages=2, list=Page{count=true, pageNum=2, pageSize=5, startRow=5, endRow=10, total=6, pages=2, reasonable=false, pageSizeZero=false}[UserInfoPagehelperDO{id=1, userName='null', age=22, createTime=null}], prePage=1, nextPage=0, isFirstPage=false, isLastPage=true, hasPreviousPage=true, hasNextPage=false, navigatePages=8, navigateFirstPage=1, navigateLastPage=2, navigatepageNums=[1, 2]}Copy the code

The returned results include data, whether it is the first/last page, total number of pages, total number of records, see Mybatis-PageHelper

Pagehelper complete example of paging

Second,MybatisThe interceptor implements paging

2.1 MybatisThe interceptor

Mybatis official website [plugin] section has the following description:

  1. throughMyBatisProvides powerful mechanisms for using plug-ins that are very simple to implementInterceptorInterface, and specify the method signature you want to intercept.
  2. MyBatisAllows you to intercept calls at some point during the execution of mapped statements. By default,MyBatisMethod calls that can be intercepted using plug-ins include:
Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
ParameterHandler (getParameterObject, setParameters)
ResultSetHandler (handleResultSets, handleOutputParameters)
StatementHandler (prepare, parameterize, batch, update, query)
Copy the code

We can implement MyBatis plug-in (data paging) by way of interceptor.

I’ll focus on demonstrating how I implemented paging using an interceptor.

2.2 Call Form

Before looking at how to implement it, let’s look at how to use it:

Copy PageHelper’s design and call a static method that intercepts the SQL statement of the first method below and automatically handles it when new a paging object.

@Test
public void selectForPage(a) {
    // The query is paginated, specifying the number of pages and the number of pages per page
    PageInterceptor.startPage(1.2);
    List<UserInfoDO> all = dao.findAll();
    PageResult<UserInfoDO> result = new PageResult<>(all);
    // Paging results to print
    System.out.println("Total records:" + result.getTotal());
    System.out.println(result.getData().toString());
}
Copy the code

Then we’ll focus on the implementation steps.

2.3 Database Dialects

Define a dialect interface, different data using a different dialect implementation

  • Dialect.java
public interface Dialect {
    /** * get the count SQL statement **@param targetSql
     * @return* /
    default String getCountSql(String targetSql) {
        return String.format("select count(1) from (%s) tmp_count", targetSql);
    }

    /** * get the limit SQL statement *@param targetSql
     * @param offset
     * @param limit
     * @return* /
    String getLimitSql(String targetSql, int offset, int limit);
}
Copy the code
  • MysqlPaging dialect
@Component
public class MysqlDialect implements Dialect{

    private static final String PATTERN = "%s limit %s, %s";

    private static final String PATTERN_FIRST = "%s limit %s";

    @Override
    public String getLimitSql(String targetSql, int offset, int limit) {
        if (offset == 0) {
            return String.format(PATTERN_FIRST, targetSql, limit);
        }

        returnString.format(PATTERN, targetSql, offset, limit); }}Copy the code

2.4 Interceptor core logic

See pageInterceptor.java for the complete code for this section

  • Paging assist parameter inner classPageParam.java
public static class PageParam {
    / / the current page
    int pageNum;

    // Where pages start
    int offset;

    // Number of pages
    int limit;

    / / the total number of
    public int totalSize;

    / / the total number of pages
    public int totalPage;
}
Copy the code
  • Query the total number of records
private long queryTotal(MappedStatement mappedStatement, BoundSql boundSql) throws SQLException {

    Connection connection = null;
    PreparedStatement countStmt = null;
    ResultSet rs = null;
    try {

        connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();

        String countSql = this.dialect.getCountSql(boundSql.getSql());

        countStmt = connection.prepareStatement(countSql);
        BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql,
                boundSql.getParameterMappings(), boundSql.getParameterObject());

        setParameters(countStmt, mappedStatement, countBoundSql, boundSql.getParameterObject());

        rs = countStmt.executeQuery();
        long totalCount = 0;
        if (rs.next()) {
            totalCount = rs.getLong(1);
        }

        return totalCount;
    } catch (SQLException e) {
        log.error("Error querying total records", e);
        throw e;
    } finally {
        if(rs ! =null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("exception happens when doing: ResultSet.close()", e); }}if(countStmt ! =null) {
            try {
                countStmt.close();
            } catch (SQLException e) {
                log.error("exception happens when doing: PreparedStatement.close()", e); }}if(connection ! =null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("exception happens when doing: Connection.close()", e); }}}}Copy the code
  • On the pageSQLparameter?Set the value
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
    ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
    parameterHandler.setParameters(ps);
}
Copy the code
  • Replace the original with a dialect interfaceSQLstatements
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
    MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());

    builder.resource(ms.getResource());
    builder.fetchSize(ms.getFetchSize());
    builder.statementType(ms.getStatementType());
    builder.keyGenerator(ms.getKeyGenerator());
    if(ms.getKeyProperties() ! =null&& ms.getKeyProperties().length ! =0) {
        StringBuffer keyProperties = new StringBuffer();
        for (String keyProperty : ms.getKeyProperties()) {
            keyProperties.append(keyProperty).append(",");
        }
        keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
        builder.keyProperty(keyProperties.toString());
    }

    //setStatementTimeout()
    builder.timeout(ms.getTimeout());

    //setStatementResultMap()
    builder.parameterMap(ms.getParameterMap());

    //setStatementResultMap()
    builder.resultMaps(ms.getResultMaps());
    builder.resultSetType(ms.getResultSetType());

    //setStatementCache()
    builder.cache(ms.getCache());
    builder.flushCacheRequired(ms.isFlushCacheRequired());
    builder.useCache(ms.isUseCache());

    return builder.build();
}
Copy the code
  • Count the total number of pages
public int countPage(int totalSize, int offset) {
    int totalPageTemp = totalSize / offset;
    int plus = (totalSize % offset) == 0 ? 0 : 1;
    totalPageTemp = totalPageTemp + plus;
    if (totalPageTemp <= 0) {
        totalPageTemp = 1;
    }
    return totalPageTemp;
}
Copy the code
  • A static paging method to call

I’ve designed it to start with a page number of 1, but you can change it if you want to start with a page number of 0.

public static void startPage(int pageNum, int pageSize) {
    int offset = (pageNum-1) * pageSize;
    int limit = pageSize;
    PageInterceptor.PageParam pageParam = new PageInterceptor.PageParam();
    pageParam.offset = offset;
    pageParam.limit = limit;
    pageParam.pageNum = pageNum;
    PARAM_THREAD_LOCAL.set(pageParam);
}
Copy the code

2.5 Paging result sets

In order to make it easier to wrap results, I have wrapped a fairly complete paging result set myself, which contains too many things. Take a look at the following properties.

public class PageResult<T> implements Serializable {
    /** * is the first page */
    private Boolean isFirstPage = false;
    /** * is the last page */
    private Boolean isLastPage = false;
    /** * the current page */
    private Integer pageNum;
    /**
     * 每页的数量
     */
    private Integer pageSize;
    /** * total number of records */
    private Integer totalSize;
    /** * Total pages */
    private Integer totalPage;
    /** * result set */
    private List<T> data;

    public PageResult(a) {}public PageResult(List<T> data) {
        this.data = data;
        PageInterceptor.PageParam pageParam = PageInterceptor.PARAM_THREAD_LOCAL.get();
        if(pageParam ! =null) {
            pageNum = pageParam.pageNum;
            pageSize = pageParam.limit;
            totalSize = pageParam.totalSize;
            totalPage = pageParam.totalPage;
            isFirstPage = (pageNum == 1); isLastPage = (pageNum == totalPage); PageInterceptor.PARAM_THREAD_LOCAL.remove(); }}public Integer getPageNum(a) {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize(a) {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalSize(a) {
        return totalSize;
    }

    public void setTotalSize(Integer totalSize) {
        this.totalSize = totalSize;
    }

    public Integer getTotalPage(a) {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getData(a) {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public Boolean getFirstPage(a) {
        return isFirstPage;
    }

    public void setFirstPage(Boolean firstPage) {
        isFirstPage = firstPage;
    }

    public Boolean getLastPage(a) {
        return isLastPage;
    }

    public void setLastPage(Boolean lastPage) {
        isLastPage = lastPage;
    }

    @Override
    public String toString(a) {
        return "PageResult{" +
                "isFirstPage=" + isFirstPage +
                ", isLastPage=" + isLastPage +
                ", pageNum=" + pageNum +
                ", pageSize=" + pageSize +
                ", totalSize=" + totalSize +
                ", totalPage=" + totalPage +
                ", data=" + data +
                '} '; }}Copy the code

2.6 Simple test

@Test
public void selectForPage(a) {
    // The query is paginated, specifying the number of pages and the number of pages per page
    PageInterceptor.startPage(1.4);
    List<UserInfoDO> all = userMapper.findAll();
    PageResult<UserInfoDO> result = new PageResult<>(all);
    // Paging results to print
    log.info("Total records: {}", result.getTotalSize());
    log.info("The list: {}", result.getData());
    log.info("result:{}", result);
}
Copy the code

The usage is basically the same as 1.3, except that it’s wrapped into my own paging result set.

  • The log is as follows:
....: ==>  Preparing: SELECT id, user_name, age, create_time FROM user_info_pageable limit 4 
....: ==> Parameters: 
....: <= =      Total: 4
. :Total records:6
. : list: [UserInfoDO(id=1, userName=Zhang SAN, age=22, createTime=2019-10-08T20:52:46), UserInfoDO(id=2, userName=Li si, age=21, createTime=2019-12-23T20:22:54), UserInfoDO(id=3, userName=The king two, age=22, createTime=2019-12-23T20:23:15), UserInfoDO(id=4, userName=The horse five, age=20. createTime=2019-12-23T20:23:15)]
. : result:PageResult{isFirstPage=true, isLastPage=false, pageNum=1, pageSize=4, totalSize=6. totalPage=2, data=[UserInfoDO(id=1, userName=Zhang SAN, age=22, createTime=2019-10-08T20:52:46), UserInfoDO(id=2, userName=Li si, age=21, createTime=2019-12-23T20:22:54), UserInfoDO(id=3, userName=The king two, age=22, createTime=2019-12-23T20:23:15), UserInfoDO(id=4, userName=The horse five, age=20. createTime=2019-12-23T20:23:15)]}
Copy the code

Log analysis shows that the common SELECT * FROM user_info_pageable limit 4 is reassembled into SELECT * FROM user_info_pageable limit 4, indicating successful paging implemented by the interceptor.

Third, summary

There are two methods: Pagination and self-implementation of Pagehelper, depending on the actual situation choose your own.

  1. PagehelperPaging sample code
  2. MybatisInterceptor implements paging sample code

3.1 Ask for praise daily

  1. Ancestral secretSpring Boot SunflowerOpen source, welcome to come to ridicule, provide clues!
  2. Sun alkaloids【Java Knowledge notebook 】Open source, welcome to come to ridicule, provide clues!

3.2 Cultural Exchange

  1. Over the blog
  2. Dust blog – Gold digging
  3. Travelogue Blog – Blog Garden
  4. The Dust Blog -CSDN
  5. Github

The latest article, welcome to pay attention to: public number – dust blog; Exchange views, welcome to add: personal wechat