PageHelper

Speaking of PageHelper, friends who have used Mybatis may not be unfamiliar. As a paging plugin developed by a Chinese, it basically meets our daily needs.But when I wanted to go to the official documentation and see how it worked with Spring Boot, I found this:

So I spent an evening exploring how to play this sensibly.

quick get start

If you want a quick paging operation in a Spring Boot project, it only takes two steps:

Import Maven

Here I'm importing the official latest:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.10</version>
</dependency>

Use

 // Only the first Mybatis Query (Select) method that follows the PageHelper.startPage method will be paginated!!!!
 PageHelper.startPage(1, 10);
 return PageInfo.of(userService.findAll());

Yes, it only takes two sentences of code to achieve the desired paging effect

Advanced Play

If you simply want to use paging, this article is over for you at this point, but as a programmer, will you be content with just the first level of play?NoYou can't?So, look down ~

From the documentation, we can see that the author has provided us with many parameters to configure:

helperDialect, offsetAsPageNum, rowBoundsWithCount, pageSizeZero, reasonable, params, supportMethodsArguments, autoRuntimeDialect, closeConn, and so on, we can configure these properties to achieve more powerful results~

It should be noted here that most of the online tutorials are configurable in xml or code. In fact, if you are using springboot, why not go further? We can configure it directly in the application.yml configuration file of Springboot:

pagehelper:
  # dialect: ①
  # The Paging Plugin automatically detects the current database links and automatically chooses the appropriate paging method (it may not be set)
  helper-dialect: mysql 
  # With the database settings above, setting the following to true does not change the results above (default is true)
  auto-dialect: true 
  page-size-zero: false # ②
  reasonable: true # ③
  # The default value is false, which is valid when RowBounds is used as a paging parameter.(Normally not needed)
  offset-as-page-num: false 
  # The default value is false, and whether RowBounds queries count (not typically required)
  row-bounds-with-count: false 
  #params: ④
  #support-methods-arguments: Used with params, as explained below
  # The default value is false.Set to true to allow automatic dialect paging at run time based on multiple data sources
  auto-runtime-dialect: false # ⑤
  # Use with auto-runtime-dialect
  close-conn: true 
  # In the method used to control whether a count query is executed by default without a count query, when set to true here, total is -1
  default-count: false 
  #dialect-alias: ⑥

1: PageHelper is used by default for paging. If you want to implement your own paging logic, you can implement the Dialect(com.github.pagehelper.Dialect) interface and configure this property to implement the fully qualified name of the class.(This is not recommended here, after all, you used other people's plug-ins. Why do you want to do more?)

(2) The default value is false, and when this parameter is set to true, if pageSize=0 or RowBounds.limit=0, all results will be queried (equivalent to not performing a paging query, but the returned results are still Page type).

It is important to note that although all the results are returned, count s have been performed and that later versions of issue will fix this problem

After my tests, there is no solution yet_

(3) Legality, i.e. error correction mechanism, configures reasonable to true, in which case pageNum <= 0 queries the first page and pageNum > pages queries the last page.

(4) In order to support the startPage(Object params) method, this parameter is added to configure parameter mapping, which is used to take values from objects based on attribute names. pageNum,pageSize,count,pageSizeZero,reasonable can be configured, default value for mapping is not configured, default value is
pageNum=pageNum;
pageSize=pageSize;
count=countSql;
reasonable=reasonable;
pageSizeZero=pageSizeZero.
support-methods-arguments supports passing paging parameters through Mapper interface parameters. The default value is false, and the paging plug-in automatically takes values from the parameter values of the query method based on the fields configured by the params above. Paging automatically occurs when the appropriate values are found.

Let's have a little:

    @GetMapping("/page1")
    public PageInfo<UserDO> findPage(HttpServletRequest request) {
        // Pass directly the parameters contained in the request to
        PageHelper.startPage(request);
        return PageInfo.of(userService.findAll());
    }
     /**
     * Query all information
     * @return Personnel List
     */
    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "userName",  column = "user_name"),
            @Result(property = "password", column = "password")
    })
    List<UserDO> findAll();
    /*****************************************************************/
    
    //The difference between this method and the above is that conditional requests are passed directly to the Mapper interface
     @GetMapping("/page2")
    public PageInfo<UserDO> findPage2(HttpServletRequest request) {
        return PageInfo.of(userService.findPage(request));
    }
    
    //The last difference the Mapper interface encounters is that it has one more parameter and the SQL is exactly the same
    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "userName",  column = "user_name"),
            @Result(property = "password", column = "password")
    })
    List<UserDO> findPage(HttpServletRequest request);

_: The default value is false.Set to true to allow automatic dialect paging at run time based on multiple data sources.

closeConn: The default is true.When a database type is automatically acquired using a runtime dynamic data source or when the helperDialect property is not set, a database connection is automatically acquired, and the property is used to set whether the acquired connection is closed or not. The default true is closed, and when set to false, the acquired connection is not closed. This parameter is set toDepending on the data source you choose.

_: The dialect-alias parameter, which allows configuration of aliases for custom implementations, can be used to automatically obtain corresponding implementations based on JDBCURL, allowing overwriting existing implementations in this way, for example (multiple configurations separated by semicolons):

pagehelper.dialect-alias=oracle=com.github.pagehelper.dialect.helper.OracleDialect

Several different ways of playing

(whisper BB) Lambda is really beautiful!

     //1. offsetPage
        PageHelper.offsetPage(1, 10);
        return PageInfo.of(userService.findAll());
        //2. Lambda
        return PageHelper.startPage(1, 10).doSelectPageInfo(() -> userService.findAll());

Source address:

Github
Gitee

Remember to hit the star, you must be the greatest motivation for me to write!

Reference material

Official Documents

Public Number

The original article, limited in writing, is light in learning. If there are any inaccuracies in the article, we hope to inform you.