Implementing Pagination in MyBatis Without the PageHelper Plugin

When developing backend features, pagination queries are inevitable; MyBatis offers the pagehelper plugin, but this guide demonstrates how to implement pagination without that plugin by analyzing the original logic.

Ignoring code for a moment, the SQL approach for MySQL is shown below: select * from user limit(0,5); The statement retrieves the first page, where 0 is the offset for the first page and 5 is the page size. To fetch subsequent pages, two parameters are defined: pageNum and pageSize.

The offset for any page is calculated as (pageNum-1) * pageSize. When pageNum equals 1, the SQL above returns the first page; increasing pageNum yields later pages.

In real scenarios, before paginating you often need to know the total number of records to display the total page count on the UI.

Below are the code changes required.

UserMapper.java – add the following queries:

// Implement pagination query
@Select("select * from tt_user_user limit #{pageNum},#{pageSize}")
List<User> selectPage(Integer pageNum, Integer pageSize);

// Get total record count
@Select("select count(*) from tt_user_user")
Integer selectTotal();

UserController.java – add a new endpoint for paginated data:

// Pagination query, endpoint: /user/page
// limit first argument = (pageNum-1) * pageSize
@GetMapping("/page")
public Map<String,Object> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize) {
    pageNum = (pageNum - 1) * pageSize;
    List<User> data = userMapper.selectPage(pageNum, pageSize);
    Integer total = userMapper.selectTotal();
    Map<String,Object> res = new HashMap<>();
    res.put("data", data);
    res.put("total", total);
    return res;
}

Example request screenshot:

Follow the public account to stay updated and keep learning together!