spring boot series mybatis paging query

Posted by dicamille on Sun, 14 Jun 2020 09:01:08 +0200

Article catalog


If you use mybatis to write paging query, it's troublesome. You need to write the select statement to get count first, and then write paging query statement. Here we use a powerful plug-in pagehelper to help developers quickly implement paging.


  • and sqlmapper.xml File decoupling is implemented in the form of plug-ins to avoid writing page query sql directly
  • Convenient and fast

Introducing pagehelper dependency

pom.xml Introduce dependency into

<!--        Add paging plug-in pagehelper -->

application.yml Medium configuration

# Paging plug in
  helperDialect: mysql
  reasonable: false
  params: count=countSql
  supportMethodsArguments: true


helperDialect: Specifies the database, and does not specify that the database type will be automatically detected

reasonable: rationalization parameter, false by default,

true: if the page number is less than 1, query the data of the first page. If the page number is greater than the total number, return the data of the last page;
false: if the page number is less than or equal to 1, the first page data will be returned. If the page number is greater than the total number, the first page data will be returned empty

supportMethodsArguments: default false, true: the paging plug-in finds the appropriate value according to the value in params, and then automatically paging

Add interface


    List<UserMoudel> findAllByPage();


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

serviceImpl, focusing on the implementation here, is also the core code of using pagehelper

    public PageInfo<UserMoudel> findAllByPage(int page, int offset) {
        // This sentence is the core
        List<UserMoudel> all = dao.findAllByPage();
       return  new PageInfo<UserMoudel>(all);


    @ApiOperation(value = "Paging query user information")
    public PageInfo<UserMoudel> findAllByPage(@RequestParam(value = "Current page number",required = true) int page,@RequestParam(value = "Quantity per page",required = true) int offset){
        return userSvc.findAllByPage(page,offset);

Test paging query interface

Pass the test

Problem summary

application.yml Configuration of pagehelper in

1. The specified database must use helperDialect instead of dialect. Otherwise, the spring boot program fails to start with the following exception:

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.boot.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

It can be seen from the exception that mysql cannot be found, and then debug. It is found that the problem is the database specified in the configuration

Topics: Database github MySQL xml