preface

If you use Mybatis to write paging query, more trouble, need to first write the select statement to obtain count, and then write paging query statement. A powerful plugin == PageHelper == is used to help developers quickly implement pagination.

Advantages:

  • Decoupled from SQLmapper. XML file, implemented as a plug-in to avoid directly writing paging query SQL
  • Convenient and fast

Introduce the PageHelper dependency

Introduce related dependencies in POM.xml

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

In the application. The yml configuration

# pagination plugin
pagehelper:
  helperDialect: mysql
  reasonable: false
  params: count=countSql
  supportMethodsArguments: true
  
Copy the code

Description:

==helperDialect== : Specifies the database. Do not specify the type of the database that will be automatically detected

==reasonable== : reasonable parameter, default false,

True: If the page number is less than 1, query the data on the first page. If the page number is greater than the total number, return the data on the last page. False: Returns the first page data if the page number is less than or equal to 1. Returns null if the page number is greater than the total numberCopy the code

==supportMethodsArguments== : Default false, true: Pagination automatically when the pagination plugin finds the appropriate value in Params

Increase the interface

dao

    List<UserMoudel> findAllByPage(a);
Copy the code

service

    PageInfo<UserMoudel> findAllByPage(int page, int offset);
Copy the code

ServiceImpl, the main implementation here, is also the core code that uses == PageHelper ==

    @Override
    public PageInfo<UserMoudel> findAllByPage(int page, int offset) {
        // This sentence is the core
        PageHelper.startPage(page,offset);
        List<UserMoudel> all = dao.findAllByPage();
       return  new PageInfo<UserMoudel>(all);
    }
Copy the code

controller

    @ApiOperation(value = "Paging query user information")
    @GetMapping("/findAllByPage")
    @ResponseBody
    public PageInfo<UserMoudel> findAllByPage(@RequestParam(value = "Current page number",required = true) int page,@RequestParam(value = "Number per page",required = true) int offset){
        return userSvc.findAllByPage(page,offset);
    }
Copy the code

Test the paging query interface

The test pass

The problem summary

Pagehelper configuration problem in application.yml

1 Ensure that ==helperDialect== is used for the database. Do not use ==dialect==. Otherwise, the Spring Boot program fails to start and the following exceptions occur:

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug'Enabled. 2020-06-14 13:56:42. 839 ERROR 2104 - [restartedMain] O.S.B oot. SpringApplication: Application run failed org.springframework.beans.factory.BeanCreationException: Error creating bean with name'com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration': Invocation of init method failed; nested exception is com.github.pagehelper.PageException: java.lang.ClassNotFoundException: mysql

Copy the code

The mysql database cannot be found because of the error