PageHelper

Speaking of PageHelper, Mybatis friends may not be very strange, as a Chinese developed paging plug-in, it basically meets our daily needs. However, I went to the official documentation to look at this thing for use with Spring Boot and found this:

So I spent a night researching the proper way to play this.

Quick start

If you want to do a quick paging operation in a Spring Boot project, there are only two steps:

Import the Maven

Here I import 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>
Copy the code

use

 // Only the first Mybatis query (Select) method immediately after the pagehelper. startPage method is paginated!!!!
 PageHelper.startPage(1.10);
 return PageInfo.of(userService.findAll());
Copy the code

Yes, it only takes two lines of code to get the paging effect we want

Advanced play

If you want to simply use paging, then this article is over for you, but as a programmer, are you content with just playing around? No! You don’t! So, move on

From the document, we can see that the author provides us with a lot of parameters for us to configure:

HelperDialect, offsetAsPageNum, rowBoundsWithCount, pageSizeZero, Reasonable, Params, Support Method Guments, AutoRuntimeDialect, closeConn, etc. We can configure these properties for more powerful effects

If you are using SpringBoot, you can configure it directly in the application. Yml configuration file:

pagehelper:
  # the dialect: (1)
  The pagination plugin automatically detects the current database link and automatically selects the appropriate pagination method (optional).
  helper-dialect: mysql 
  Setting the following to true does not change the result of the above database setting (default: true)
  auto-dialect: true 
  page-size-zero: false # 2.
  reasonable: true # 3.
  The default value is false, which is valid when using RowBounds as a paging parameter. (Not usually necessary)
  offset-as-page-num: false 
  # default value is false, RowBounds count query (not required)
  row-bounds-with-count: false 
  # params: (4)
  #support-methods-arguments: Use with Params, see the explanation below
  The default value is false. When set to true, allows automatic identification of pages in dialects based on multiple data sources at run time
  auto-runtime-dialect: false # 5.
  # Work with auto-run-time dialect
  close-conn: true 
  Set this parameter to true and the total value will be -1
  default-count: false 
  # the dialect - alias: 6
Copy the code

(1) : by default paging will use PageHelper way, if you want to achieve their own paging logic, can realize the Dialect (com) making. PageHelper. The Dialect) interface, and then configure the properties to achieve the fully qualified name of the class. (It’s not recommended, since you’re using someone else’s plugin, so why bother?)

② : The default value is false. When this parameter is set to true, pageSize=0 or rowbounds. limit =0 will query all results (equivalent to not performing a paging query but still returning results of Page type).

It should be noted that the count action was performed even though all results were returned, and the developer indicated in the issue that a later version would fix this

③ : validity, that is, error correction mechanism, with reasonable set to true, if pageNum <= 0, the first page will be queried, if pageNum > pages, the last page will be queried.

(4) : 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. The default value is false. The pagination plug-in will automatically search for an appropriate value from the parameter value of the query method according to the params configuration field above.

A little 🌰 :

	@GetMapping("/page1")
    public PageInfo<UserDO> findPage(HttpServletRequest request) {
    	// Pass the parameters contained in the request directly
        PageHelper.startPage(request);
        return PageInfo.of(userService.findAll());
    }
 	/** * query all information *@returnPersonnel list */
    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "userName",  column = "user_name"),
            @Result(property = "password", column = "password")})List<UserDO> findAll(a);
	/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
	
	// The difference is that the conditional request is passed directly to the Mapper interface
 	@GetMapping("/page2")
    public PageInfo<UserDO> findPage2(HttpServletRequest request) {
        return PageInfo.of(userService.findPage(request));
    }
    
    // The Mapper interface is different from the Mapper interface
	@Select("SELECT * FROM user")
    @Results({
            @Result(property = "userName",  column = "user_name"),
            @Result(property = "password", column = "password")})List<UserDO> findPage(HttpServletRequest request);
Copy the code

⑤ : The default value is false. When set to true, allows automatic identification of pages in dialects at run time based on multiple data sources,

CloseConn: The default value is true. If the helperDialect property is not set to false, the system will automatically obtain a database connection from the system if the database type is automatically obtained using a runtime dynamic data source or if the helperDialect property is not set to true. The setting of this parameter depends on the data source you choose.

The dialects-alias parameter allows you to configure a dialects-alias for a custom implementation. The dialects-alias parameter can be used to automatically obtain an implementation based on JDBCURL. Separated) :

pagehelper.dialect-alias=oracle=com.github.pagehelper.dialect.helper.OracleDialect
Copy the code

Several different ways to play it

Lambda is really nice!

	 //1. offsetPage
        PageHelper.offsetPage(1.10);
        return PageInfo.of(userService.findAll());
        //2. Lambda
        return PageHelper.startPage(1.10).doSelectPageInfo(() -> userService.findAll());
Copy the code

Source code address:

Github Gitee

Remember to click a star, you are sure to be the biggest motivation for my writing!

The resources

The official documentation

The public,

Original article, writing is limited, talent and learning shallow, if the article is not straight, hope to inform.