This is the 10th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

The current company does not use MyBatis-Plus plug-in development, using native MyBatis, is not good at doing pagination, which uses PageHelper interceptor to handle pagination

Reference documents: blog.csdn.net/xiaojin21ce…

In the actual project development, it is often necessary to use paging, which is divided into two types: front-end paging and back-end paging

  • Front end paging (fake paging)

    An Ajax request for all records of the data is then cached in the front end and the count and paging logic is computed, as a normal front end component (such as the dataTable) provides paging action. Features: simple and suitable for small-scale Web platforms, when the amount of data will produce performance problems, in the query and network transmission time will be very long.

  • Back-end paging (true paging)

    Specify page number pageNum and pageSize pageSize in the ajax request, and the back end queries out when the page data is returned, and the front end is only responsible for rendering. Characteristics are: some complex; The performance bottleneck is in MySQL query performance, which can of course be addressed by tuning.

    In general, development uses this approach.

1. Paging operations without using paging plug-ins

If you do not use the paging plugin, you need to write a select statement to query count, and then write a true paging statement. MySQL supports paging through the limit clause

  • limitThe use of the keyword is:LIMIT [offset,] rows
    • offsetIs the offset relative to the first row (the first row is 0)
    • rowsYes Number of returns (number of returns per page)

Such as:

  • There are five records per page. Take the first page and return the first five records
select * from tableA limit 0.5;
Copy the code
  • Five records per page, take the second page, return the sixth record, to the tenth record
select * from tableA limit 5.5;
Copy the code

However, as the offset increases, the query speed may slow down and performance may deteriorate (SQL statements can be optimized to improve efficiency).

2. Configure PageHelper 4.x

PageHelper is a free, open source plugin for Mybatis third-party physical pagination. Official address: pageHelper.github. IO /

Example: Using PageHelper in SpringBoot

  • In the firstpom.xmlIn the configurationPageHelperRely onYou can find ‘pageh…
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>2</version>
</dependency>
Copy the code

Configure PageHelper plugin in Mybatis configuration file, if not configured in the later use of the PageInfo class, there will be a problem, the output result of the PageInfo attribute value is basically wrong, configure as follows

<plugins>
    <! -- com.github. Pagehelper specifies the package name of pageHelper -->
    <plugin interceptor="com.github.pagehelper.PageHelper">
        <property name="dialect" value="mysql"/>
        <! -- This parameter defaults to false -->
        <! -- When set to true, the first RowBounds argument offset is used as pageNum page number -->
        <! PageNum in startPage -->
        <property name="offsetAsPageNum" value="false"/>
        <! -- This parameter defaults to false -->
        <! -- When set to true, count queries are performed using RowBounds paging -->
        <property name="rowBoundsWithCount" value="false"/>
        <! When set to true, pageSize=0 or rowbounds. limit =0 will query all results -->
        <! -->
        <property name="pageSizeZero" value="true"/>
        <! -- Version 3.3.0 available -- paging parameters rationalized, default false disabled -->
        <! If pageNum<1, select * from pageNum; if pageNum>pages, select * from pageNum;
        <! -- If pageNum<1 or pageNum>pages returns null data -->
        <property name="reasonable" value="true"/>
        <! -- Version 3.5.0 available -- to support the startPage(Object Params) method
        <! -- Added a 'params' parameter to configure parameter mapping for values from Map or ServletRequest -->
        <! - you can configure the pageNum, pageSize, count, pageSizeZero, reasonable, not configure mapping with default values -- -- >
        <! --<property name="params" value="pageNum=start; pageSize=limit; pageSizeZero=zero; reasonable=heli; count=contsql"/>-->
    </plugin>
</plugins>
Copy the code

Above is the official PageHelper configuration and comments, although written a lot, but does describe very clear.

  • dialect: Identify which database is required by design.
  • offsetAsPageNumWill:RowBoundsThe first parameteroffsetAs apageNumThe page number to use
  • rowBoundsWithCount: set totrueWhen usingRowBoundsPaging will happencountThe query
  • reasonable:value=trueWhen,pageNumLess than 1 will query the first page ifpageNumIs greater thanpageSizeThe last page will be queried

Note: The above configuration is for PageHelper4.x only, if you are using PageHelper5.x you will need to configure this

3. PageHelper 5.x is officially recommended

(1) Configuration method 1: Configure interceptor plug-in in MyBatis configuration XML (personally recommended)

<! -- The location of the plugins in the configuration file must be correct, otherwise an error will be reported, in the following order: properties? , settings? , typeAliases? , typeHandlers? , objectFactory? ,objectWrapperFactory? , plugins? , environments? , databaseIdProvider? , mappers? -->
<plugins>
    <! -- com.github. Pagehelper specifies the package name of pageHelper -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <! Use the following method to configure the parameters:
        <property name="param1" value="value1"/>
    </plugin>
</plugins>
Copy the code

The sample code

<! -- Configure paging plug-in -->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <! Type, set up the database Oracle, Mysql and MariaDB, SQLite, Hsqldb, PostgreSQL six database - >
        <property name="helperDialect" value="mysql"/>
    </plugin>
</plugins>
Copy the code

Configuration method 2: Configure the interceptor plug-in in the Spring configuration file

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <! -- Notice other configuration -->
  <property name="plugins">
    <array>
      <bean class="com.github.pagehelper.PageInterceptor">
        <property name="properties">
          <! -- Configure the parameters one in a row as follows:
          <value>
            params=value1
          </value>
        </property>
      </bean>
    </array>
  </property>
</bean>
Copy the code

4. Application of MyBatis project

After configuring Mybatis, I briefly say the service usage of PageHelper, take paging query user list as an example, query all users mapper interface (no paging SQL)

List<UserVo> listUser(a);// SELECT * FROM sys_user
Copy the code

Then in service, turn on paging and put the query result set into PageInfo

public PageInfo listUserByPage(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);// 1. Enable paging
    List<UserVo> userVoList=userMapper.listUser();
    PageInfo pageInfo=new PageInfo(userVoList);// 2. Add the query result set to PageInfo
    return pageInfo;
}
Copy the code

Code description:

  • PageHelper.startPage(pageNum, pageSize);This is very important. This code indicates the beginning of paging, meaning from number onepageNumPage start, each page is displayedpageSizeRecords.
  • PageInfoThis class is the plug-in class whose properties are displayed in the outputPageInfoThe class you need to query out oflistPut it in.

PageHelper (); PageHelper (); PageHelper (); PageHelper (); PageHelper (); pageNum (); pageSize ()

{
    "msg": "Succeeded in obtaining user information on page 1"."code": 200."data": {
        "pageNum": 1./ / the current page
        "pageSize": 5.// Number of pages per page
        "size": 5.// The number of current pages
        "orderBy": null./ / sorting
        "startRow": 1.// The database line number of the first element of the current page
        "endRow": 5.// The database line number of the last element of the current page
        "total": 11.// Total records (in this case, the total number of users queried)
        "pages": 3.// Total number of pages (this is also easy to calculate, 5 pages per page, there are 11 pages in total, it takes 3 pages to display)
        "list": [						/ / the result set
            {
                "userId": "a24d0c3b-2786-11e8-9835-e4f89cdc0d1f"."username": "2015081040"
            },
            {
                "userId": "b0bc9e45-2786-11e8-9835-e4f89cdc0d1f"."username": "2015081041"
            },
            {
                "userId": "b44fd6ac-2786-11e8-9835-e4f89cdc0d1f"."username": "2015081042"
            },
            {
                "userId": "b7ac58f7-2786-11e8-9835-e4f89cdc0d1f"."username": "2015081043"
            },
            {
                "userId": "bbdeb5d8-2786-11e8-9835-e4f89cdc0d1f"."username": "2015081044"}]."prePage": 0./ / the previous page
        "nextPage": 2./ / the next page
        "isFirstPage": true.// If it is the first page
        "isLastPage": false.// If it is the last page
        "hasPreviousPage": false.// Is there a previous page
        "hasNextPage": true.// Is there a next page
        "navigatePages": 8.// Navigation page number
        "navigatepageNums": [				// All navigation page numbers
            1.2.3]."navigateFirstPage": 1.// Navigate to the first page
        "navigateLastPage": 3.// Navigate to the last page
        "firstPage": 1./ / the first page
        "lastPage": 3						// Last page
    },
    "success": true."error": null
}
Copy the code

5. Configure SpringBoot

  • Introduction of depend on
<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
  • Simple to use
//[pageNum, pageSize] Number of pages displayed per page
PageHelper.startPage(pageNum,pageSize);
PageInfo<UserInfo> pageInfo = new PageInfo<>(UserInfoService.selectUserList());
Copy the code
  • Advanced instructions

The official documentation provides a number of parameters for us to configure: HelperDialect, offsetAsPageNum, rowBoundsWithCount, pageSizeZero, Reasonable, Params, Support Method Guments, AutoRuntimeDialect, closeConn, etc.

In the Spring Boot project, go to application.yml to configure it

pagehelper:
  # 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 
  #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?)

(2) 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 executing a paging query but still returning a result of Page type).

(3) Validity, that is, error correction mechanism, with reasonable set to true, if pageNum <= 0, the first page will be queried, and if pageNum > Pages, the last page will be queried.

(4) 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.

(5) Default value is false. When set to true, allows automatic identification of dialect pages at runtime 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.

Dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias: dialectus-alias Separated) :

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

Lambda expressions can be used

//1.offsetPage
PageHelper.offsetPage(1.10);
return PageInfo.of(userService.findAll());
//2.Lambda
return PageHelper.startPage(1.10).doSelectPageInfo(() -> userService.findAll());
Copy the code
// Some attributes
System.out.println("Total quantity"+pageInfo.getTotal());
System.out.println("Current page Query Record"+pageInfo.getList().size());
System.out.println("Current page number"+pageInfo.getPageNum());
System.out.println("Number of displays per page"+pageInfo.getPageSize());
System.out.println("Total pages"+pageInfo.getPages());
Copy the code

Controler in the way

public PageInfo<T> getPageById(@RequestParam("sectionId") String sectionId,@RequestParam("pageNum" Integer pageNum,@RequestParam("pageSize" Integer pageSize)) {
	PageInfo<T> info= PageHelper
        	.orderBy(xxx ASC/DESC)
        	.startPage(pageNum, pageSize).doSelectPage(() -> {
        		this.xxxService.getPagedBySectionId(sectionId);
    		});
   returninfo; }Copy the code
// The first, RowBounds call
List<Country> list = sqlSession.selectList("x.y.selectIf".null.new RowBounds(0.10));
 
// The second way, Mapper interface call, this way of use is recommended.
PageHelper.startPage(1.10);
List<Country> list = countryMapper.selectIf(1);
 
// The third way, Mapper interface call, this way of use is recommended.
PageHelper.offsetPage(1.10);
List<Country> list = countryMapper.selectIf(1);
 
// The fourth type, parameter method call
// There are the following Mapper interface methods, you don't need two arguments after XML processing
public interface CountryMapper {
    List<Country> selectByPageNumSize(
            @Param("user") User user,
            @Param("pageNum") int pageNum, 
            @Param("pageSize") int pageSize);
}
 
/ / configuration supportMethodsArguments = true
// Call directly from code:
List<Country> list = countryMapper.selectByPageNumSize(user, 1.10);
 
// Type 5, parameter object
// If pageNum and pageSize exist in the User object, they will also be paginated as long as the parameters have values
// There are the following User objects
public class User {
    / / the other fields
    // The following two parameters have the same name as the params configuration
    private Integer pageNum;
    private Integer pageSize;
}
 
// There are the following Mapper interface methods, you don't need two arguments after XML processing
public interface CountryMapper {
    List<Country> selectByPageNumSize(User user);
}
 
// When user is pageNum! = null && pageSize! = null, pages are automatically paginated
List<Country> list = countryMapper.selectByPageNumSize(user);
 
// ISelect
//jdk6,7, create interface
Page<Country> page = PageHelper.startPage(1.10).doSelectPage(new ISelect() {
    @Override
    public void doSelect(a) { countryMapper.selectGroupBy(); }});/ / jdk8 lambda usage
Page<Country> page = PageHelper.startPage(1.10).doSelectPage(()-> countryMapper.selectGroupBy());
 
// You can also return PageInfo directly. Note the doSelectPageInfo method and doSelectPage
pageInfo = PageHelper.startPage(1.10).doSelectPageInfo(new ISelect() {
    @Override
    public void doSelect(a) { countryMapper.selectGroupBy(); }});// Corresponding lambda usage
pageInfo = PageHelper.startPage(1.10).doSelectPageInfo(() -> countryMapper.selectGroupBy());
 
//count query returns the count of a query statement
long total = PageHelper.count(new ISelect() {
    @Override
    public void doSelect(a) { countryMapper.selectLike(country); }});//lambda
total = PageHelper.count(()->countryMapper.selectLike(country));
Copy the code

6. PageHelper security call

  • It is extremely safe to use the RowBounds and PageRowBounds arguments

  • Using parameters is extremely safe

  • Calling using the ISelect interface is extremely secure

    ISelect interface method in addition to ensure security, but also in particular to achieve the query into a simple count query method, this method can be any query method, into a select count(*) query method.

  • When does it cause unsafe paging? The PageHelper method uses the static ThreadLocal parameter, and the paging parameter is bound to the thread. ** As long as you can ensure that the PageHelper method is followed by the MyBatis query method, this is safe. ** Because PageHelper automatically clears objects stored in ThreadLocal in the finally snippet. If an exception occurs before the code enters Executor, the thread becomes unavailable. This is an artificial Bug (such as when the MappedStatement cannot be found due to a mismatch between the interface method and the XML). In this case, the thread is unavailable. It does not cause the ThreadLocal parameter to be used incorrectly. \

  • But if you write code like this, it is not safe to use:

PageHelper.startPage(1.10);
List<Country> list;
if(param1 ! =null){
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<Country>();
}
Copy the code

In this case, because param1 is null, PageHelper produces a paging parameter, but it is not consumed, and the parameter remains on the thread. When the thread is used again, it can cause methods that shouldn’t be paged to consume the paging parameter, resulting in unexplained paging.

The code above should be written like this: This is safe.

List<Country> list;
if(param1 ! =null){
    PageHelper.startPage(1.10);
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<Country>();
}
Copy the code

The appendix

(1) If the 4.x version uses the 5.x version, springBoot will report an error when starting the project. There are many error messages, mainly because

Caused by: org.apache.ibatis.builder.BuilderException: 
Error resolving class. Cause: org.apache.ibatis.type.TypeException: 
Could not resolve type alias 'com.github.pagehelper.PageInterceptor'.
Caused by: org.apache.ibatis.type.TypeException: 
Could not resolve type alias 'com.github.pagehelper.PageInterceptor'.
Caused by: java.lang.ClassNotFoundException: 
Cannot find class: com.github.pagehelper.PageInterceptor
Copy the code

As a whole is lack of the com. Making. Pagehelper. PageInterceptor, this is the new interceptors, 5. X version to use, so in 4. X version configuration is not so.

(2) Does the configuration of 5.x version take effect on PageHelper4.x? The answer is no

Caused by: org.apache.ibatis.builder.BuilderException: 
Error parsing SQL Mapper Configuration. Cause: 
java.lang.ClassCastException: com.github.pagehelper.PageHelper 
cannot be cast to org.apache.ibatis.plugin.Interceptor
Caused by: java.lang.ClassCastException: 
com.github.pagehelper.PageHelper 
cannot be cast to org.apache.ibatis.plugin.Interceptor
Copy the code

The new PageInterceptor interceptor doesn’t convert from the old interceptor, so it won’t work.

Pagehelper4.x = 4.x, pageHelper5.x = 5.x