SpringBoot uses PageHelper for data paging

preface

When we do page query, because the page cannot display all the data at one time, so we adopt a segmented display strategy — paging query. There are many ways to achieve paging query, such as limit in SQL, paging plug-in with Mybatisplus and so on. Here we show you how to implement paging queries in SpringBoot using the PageHelper plug-in.

The body of the

PageHelper

PageHelper is the most convenient pagination plugin for MyBatis PageHelper supports any complex single-table, multi-table queries. It has the following characteristics:

  • PageHelperNested result mappings are not supported.
  • PageHelperThere are essentially two queries, the first on the total number of records and the second on the records.
  • The query of the record is utilizedmybatisProvided by interceptor, obtainedThreadLocalthepageSizeandpageNo, reassemble the pagessqlTo complete paging. In fact, in thesqlAfter the splice limit to achieve.

Limit paged query

  • whenlimit offset rowsIn theoffsetIf the data size is large, there will be efficiency problems, so it is not recommended to use large data scalePageHelperTo implement paging.
  • In the actual development process, we use our own handwriting for two queries. In the second query for records, we use sub-query to optimize the performance.
select * from test where val=4 limit 300000.5
Copy the code

Optimized:

select * from test a inner join (select id from test where val=4 limit 300000.5) b on a.id=b.id
Copy the code

So, if you can use PageHelper when there is a small amount of data, it’s not recommended otherwise.

SpringBoot uses PageHelper for data paging

Maven rely on

<! --pagehelper--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2. 5</version>
</dependency>
Copy the code

The properties, application of the configuration class

Pagehelper. helper-dialect=mysql # paghelper. reasonable=true# pageHelper.support-methods-arguments = # pagehelper.support-methods-arguments= # pagehelper.support-methods-arguments=true# If the countSql attribute is found in a POJO or Map, Pagehelper. params=count=countSql #mybatis log is used as the count parameter mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImplCopy the code

PageRequestPam: Abstract class for the request

/** * Encapsulates the paging fuzzy query request */
@Setter
@Getter
@ToString
public abstract class PageRequestPam implements Serializable {
    private static final long serialVersionUID = 1L;
    private int pageNum; // Page number
    private int pageSize;  // Page size
}
Copy the code

UserInfoForSearchRes: Request class

/** * this is the wrapper class for user queries */
@Setter
@Getter
@ToString
@apiModel (" User query request class ")
public class UserInfoForSearchRes extends PageRequestPam {
    private static final long serialVersionUID = -5625703056478578435L;


    @apiModelProperty (notes = "username ", example = "xiyuan666")
    private String userName;


    @APIModelProperty (notes = "query start time ")
    private Date searchStartTime;


    @APIModelProperty (notes = "query end time ")
    private Date searchEndTime;


}
Copy the code

PageUtils: The resulting utility class for paging

public class PageUtils {
    /** * encapsulates paging information into a unified interface *@param pam
     * @param pageInfo
     * @return* /
    public static Page getPageResult(PageRequestPam pam, PageInfo
        pageInfo) {
        Page page = new Page();
        page.setPageNo(pageInfo.getPageNum());
        page.setPageSize(pageInfo.getPageSize());
        page.setTotalSize(pageInfo.getTotal());
        page.setTotalPages(pageInfo.getPages());
        page.setValue(pageInfo.getList());
        returnpage; }}Copy the code

Page: Wrapper class for paging results

/** * The result of paging query encapsulates the class */
@Data
public class Page implements Serializable {
    private static final long serialVersionUID = 1L;
    private int pageNo;/ / the current page
    private int pageSize;// Current page size
    private long totalSize;// Record total
    private int totalPages;// Total number of pages
    private Object value;
}
Copy the code

validation

Paging query interface

@PostMapping("/queryUserListPage")
@apiOperation (value = "ApiOperation ")
@ControllerMethodLog
public ResponseResult queryUserListPage(@RequestBody UserInfoForSearchRes userInfoForSearchRes) {
    long startTime = System.currentTimeMillis();   // Get the start time
    int pageNum = userInfoForSearchRes.getPageNum();
    int pageSize = userInfoForSearchRes.getPageSize();
    PageHelper.startPage(pageNum, pageSize);
    List<UserInfoPojo> list = userService.getUserInfoByPage(userInfoForSearchRes);
    Page page= PageUtils.getPageResult(userInfoForSearchRes, new PageInfo<UserInfoPojo>(list));
    long endTime = System.currentTimeMillis(); // Get the end time
    log.info("User module paging query - Total entries:" + list.size() + "Time:" + (endTime - startTime) + "ms");
    return ResponseResult.success(page, ConstantsUtil.QUERY_SUCCESS);
}
Copy the code

throughknife4jAccess the paging query interface



Console Print information

The source code

The project source code is available from my Github: github source address