Mybatis-Plus implements pagination query

Mybatis-Plus implements paging query

Article directory

  • Mybatis-Plus implements pagination query
    • 1.1 Technical overview
    • 1.2 Technical details
      • 1.2.1 Configure the pagination plugin
      • 1.2.2 Custom pagination
        • 1. If your mapper does not inherit from BaseMapper
        • 2. If your mapper inherits BaseMapper
    • 1.3 Problems and solutions encountered in the use of technology
    • 1.4 Summary

Mybatis-Plus implements paging query

1.1 Technical overview

  • Paging query is a common database query method, and using the Mybatis-Plus paging plug-in can save you more time to write complex database statements. Of course, the premise is that you still need to understand some database query statements and Mybatis -Plus common methods.
  • The reason for learning this technology is also because there is a need for paging queries in software engineering practice. In order to speed up development efficiency, I chose to use paging plug-ins.
  • Difficulties: It is necessary to pay attention to the different requirements of different versions of Mybatis-Plus; the related methods of Mybatis-Plus still need to be learned.

1.2 Technical details

1.2.1 Configure pagination plugin

1. Spring and mybatis-plus integration

<!-- spring xml method -->
<property name="plugins">
    <array>
        <bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor">
            <property name="sqlParser" ref="Custom parsing class, optional"/>
            <property name="dialectClazz" value="Custom dialect class, optional"/>
            <!-- COUNT SQL parsing. Can be without -->
            <property name="countSqlParser" ref="countSqlParser"/>
        </bean>
    </array>
</property>

<bean id="countSqlParser" class="com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize">
    <!-- set to true to optimize some left join sql -->
    <property name="optimizeJoin" value="true"/>
</bean>

2. Integration of spring boot and mybatis-plus

//Spring boot method
@Configuration
@MapperScan("mapper package name")
public class MybatisPlusConfig {<!-- -->

    // old version
    @Bean
    public PaginationInterceptor paginationInterceptor() {<!-- -->
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // Set the operation after the requested page is larger than the maximum page, true to return to the home page, false to continue the request, default false
        // paginationInterceptor. setOverflow(false);
        // Set the maximum number of single page limit, the default is 500, -1 is unlimited
        // paginationInterceptor. setLimit(500);
        // Enable the join optimization of count, only for some left joins
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
    
    // new
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {<!-- -->
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
        return interceptor;
    }
    
}

1.2.2 Custom pagination

1. If your mapper does not inherit from BaseMapper

UserMapper.java method content

public interface UserMapper {<!-- -->//Can inherit or not inherit BaseMapper
    /**
     * <p>
     * Query: Query the user list according to the state, and display it in pages
     *</p>
     *
     * @param page pagination object, value can be obtained from it in xml, and the parameter Page is automatically paginated, which must be placed first (you can inherit Page to implement your own paging object)
     * @param state state
     * @return pagination object
     */
    IPage<User> selectPageVo(Page<?> page, Integer state);
}

UserMapper.xml is equivalent to writing a normal list query, and mybatis-plus will automatically paginate for you

<!-- The following is a simple query example. The <select> tag is to represent the query. The id attribute is a unique identifier. Note that it should correspond to the method name of your mapper class one by one. The resultType is the obtained data. There are many types of encapsulated types, about the syntax and attributes of mapper.xml of mybatis, so I won't go into details here -->
<select id="selectPageVo" resultType="com.baomidou.cloud.entity.UserVo">
    SELECT id,name FROM user WHERE state=#{state}
</select>

UserServiceImpl.java calls the paging method

public IPage<User> selectUserPage(Page<User> page, Integer state) {<!-- -->
    // Do not perform count sql optimization to solve the problem that MP cannot automatically optimize SQL. At this time, you need to query the count part yourself
    // page.setOptimizeCountSql(false);
    // When total is less than 0 or setSearchCount(false) pagination plugin will not perform count query
    // Important!! The object returned by pagination is the same object passed in
    return userMapper. selectPageVo(page, state);
}

Note:
If the return type is IPage, the IPage of the input parameter cannot be null, because the returned IPage == the IPage of the input parameter
If the return type is List, the input IPage can be null (if it is null, there will be no pagination), but you need to manually enter the IPage.setRecords (returned List);
If the xml needs to get the value from the page, it needs to get the page.attribute.

flow chart:

2. If your mapper inherits BaseMapper

In addition to the above methods, you can use the very powerful CRUD method declared in BaseMapper, as shown in the following example.

/**
     * Query unreviewed posts (pagination)
     * @param tag search item
     * @param page page number
     * @return post list of posts
     */
    public BackPage<Post> findBackPostList(String tag, int page){<!-- -->
        BackPage<Post> postBackPage = new BackPage<>();
        // set conditional constructor
        QueryWrapper<Post> wrapper = new QueryWrapper<>();
        wrapper.like("post_title", tag);
        wrapper.eq("is_check", NOT_CHECKED);
        // Construct paging information, where the first parameter of Page<>(page, PAGE_RECORDS_NUM) is the number of pages, and the second parameter is the number of records per page
        Page<Post> postPage = new Page<>(page, PAGE_RECORDS_NUM);
        // page(postPage, wrapper) The first parameter here is the Page object defined above, and the second parameter is the condition constructor object defined above. By calling this method, you can obtain it according to your paging information and query information Paged data
        IPage<Post> postIPage = page(postPage, wrapper);
        // Encapsulate data, where getRecords() is to get the number of records, getCurrent() to get the current page number, getPages() to get the total number of pages, getTotal() to get the total number of records, and more methods, you can check it yourself, I won't go into too much detail here
        postBackPage.setContentList(postIPage.getRecords());
        postBackPage.setCurrentPage(postIPage.getCurrent());
        postBackPage.setTotalPage(postIPage.getPages());
        postBackPage.setTotalNum(postIPage.getTotal());
        return postBackPage;
    }

Among them, BackPage and Post are the entity classes I wrote to encapsulate the query data, and you can choose according to your own needs.

public class BackPage<T> {<!-- -->

    private static final long serialVersionUID=1L;

    /**
     * total pages
     */
    private long totalPage;

    /**
     * current page number
     */
    private long currentPage;

    /**
     * Total
     */
    private long totalNum;

    /**
     * content
     */
    private List<T> contentList;
}

flow chart:

1.3 Problems and solutions encountered in the use of technology

  • Regarding the issue of the mybatis-plus version, if the 3.x version uses paging, the plug-in must be configured, otherwise it will have no effect, unlike 2.x without a plug-in, it is memory paging (when paging, all the data is queried out , and then paging in memory through RowBounds).
 /**
       * <p>
       * According to entity conditions, query all records (and turn pages)
       *</p>
       *
       * @param rowBounds pagination query condition (can be RowBounds.DEFAULT)
       * @param wrapper entity object wrapper operation class (can be null)
       * @return List<T>
       */
      List<T> selectPage(RowBounds rowBounds, Wrapper<T> wrapper);
  • And the relationship between the above-mentioned IPage passing and returning parameters. The solution process is to obtain relevant solutions through online inquiries (blog garden, csdn, etc.).

1.4 Summary

If you use Mybatis-Plus and need to use the paging function, you can consider the Mybatis-Plus paging plug-in, which can be used with simple configuration, which is a good choice.

References
https://www.cnblogs.com/FZU-TKQ/p/14944506.html
I remembered paging query when I was writing a project. I learned it before, but I forgot a bit. This article in the blog garden is really good, and it reminds me of a lot of knowledge points. Highly recommend checking it out.
Come on! Programmer!