[MyBatis Plus] Use MyBatis Plus to complete the paging function and implement the universal paging entity

Article directory

  • 1. MyBatis Plus paging plug-in
    • 1.1 Understand the plug-in functions of MyBatis Plus
    • 1.2 Configure paging plug-in
    • 1.3 Test paging function
  • 2. Implement universal paging entities
    • 2.1 Paging query requirements
    • 2.2 Pagination entity class
    • 2.3 Paging query interface
    • 2.4 Conversion of general entities
      • 2.4.1 Convert PageQuery to MP Page
      • 2.4.2 Paging results PO conversion VO

1. MyBatis Plus paging plug-in

1.1 Understand the plug-in functions of MyBatis Plus

When using the plug-in function of MyBatis Plus, you can configure different plug-ins to meet the needs of the project. These plug-ins can enhance the functionality of MyBatis Plus and improve the efficiency and security of database operations. The following is an introduction to the main plugins:

Plug-in Function
PaginationInnerInterceptor Auto paging
TenantLineInnerInterceptor Multi-tenant
DynamicTableNameInnerInterceptor Dynamic table name
OptimisticLockerInnerInterceptor Optimistic Locking
IllegalSQLInnerInterceptor SQL Performance Specifications
BlockAttackInnerInterceptor Prevent full table updates and deletions

  1. PaginationInnerInterceptor (automatic paging): PaginationInnerInterceptor is used to implement automatic paging function. By configuring this plugin, you can easily enable pagination in query operations without having to manually write SQL for paginated queries. The plug-in automatically calculates the starting and ending rows to obtain the specified range of data.

  2. TenantLineInnerInterceptor (Multi-tenant): The TenantLineInnerInterceptor plugin is used to support multi-tenant architectures. In a multi-tenant environment, different tenants share the same database, but the data needs to be separated. This plugin can automatically add tenant conditions in SQL queries to ensure that each tenant can only access its own data.

  3. DynamicTableNameInnerInterceptor (Dynamic Table Name): DynamicTableNameInnerInterceptor allows dynamically changing table names in SQL queries at runtime. This is useful for selecting different tables based on different criteria, for example, based on user identity or other factors.

  4. OptimisticLockerInnerInterceptor (optimistic locking): OptimisticLockerInnerInterceptor is used to support the optimistic locking mechanism. Optimistic locking is a concurrency control method that prevents multiple users from modifying the same record at the same time. This plugin automatically adds an optimistic locking version field to entities and checks the version number during update operations to ensure data consistency.

  5. IllegalSQLInnerInterceptor (SQL Performance Specification): IllegalSQLInnerInterceptor is used to check the performance specifications of SQL query statements. It can help developers optimize SQL queries and reduce potential performance issues. You can improve the efficiency of your queries by checking SQL’s performance specifications.

  6. BlockAttackInnerInterceptor (preventing full table updates and deletions): BlockAttackInnerInterceptor is used to prevent full table update and delete operations. This helps reduce potentially dangerous operations to protect database security. Plug-ins can intercept SQL queries containing specific conditions, preventing those operations from being performed.

By properly configuring these plug-ins, the functionality and performance of MyBatis Plus can be improved to meet the needs of different projects, and these plug-ins make database operations more efficient and secure. The following will demonstrate how to use the PaginationInnerInterceptor automatic paging plug-in to implement the paging function.

1.2 Configure paging plug-in

Without the introduction of the paging plug-in, Mybatis Plus does not support the paging function, and the paging methods in IService and BaseMapper cannot take effect normally. Therefore, we must configure the paging plug-in. Simply put, we create a configuration class and register a Bean object:

@Configuration
public class MyBatisConfig {<!-- -->
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){<!-- -->
        
        // Initialize MybatisPlusInterceptor core plug-in
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //Add automatic paging plug-in PaginationInnerInterceptor
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        // return
        return interceptor;
    }
}

Explanation of the code above:

  1. Create a Java configuration class MyBatisConfig and mark it with the @Configuration annotation, which will make it a Spring Boot configuration class.

  2. Through the @Bean annotation, a Bean object named mybatisPlusInterceptor is created. This Bean is the Mybatis Plus core plug-in MybatisPlusInterceptor instance.

  3. In the Bean of mybatisPlusInterceptor, initialize the core plug-in of MybatisPlusInterceptor and add the paging plug-in PaginationInnerInterceptor to it.

  4. Through the interceptor.addInnerInterceptor(new PaginationInnerInterceptor()) statement, the paging plug-in is added to the core interceptor, thereby enabling the paging function.

With this configuration, MyBatis Plus will be able to support the paging function normally, and the paging methods in IService and BaseMapper will take effect. When executing a query with paging parameters, the paging plug-in will automatically calculate the starting row and ending row, and modify the SQL query statement to obtain the specified range of data.

1.3 Test paging function

Next, write a test class for paging query to query the data on the first page. Each page can have up to two records:

@Test
void testPageQuery() {<!-- -->
    // 1. Prepare paging query conditions
    Page<User> page = new Page<>();
    // 1.1. Set paging parameters
    page.setCurrent(0).setSize(2);
    // 2. Paging query, the final returned results are stored in the page object
    page = userService.page(new Page<>(0, 2));
    // 3. Total number of items
    long total = page.getTotal();
    // 4. Total number of pages
    long pages = page.getPages();

    System.out.println("Total number of items: " + total + " Total number of pages: " + pages);
    // 5. Current page data
    List<User> users = page.getRecords();
    users.forEach(System.out::println);
}

Explanation of the above test code:

  1. In the testPageQuery method, a paging query condition is first prepared. This is accomplished by creating a Page object, where User is the entity class for which the database table is to be queried.

  2. In the paging query conditions, the paging parameters are set, including the current page and the number of records per page. In the example, the current page is 0 (the first page), and each page displays up to 2 records.

  3. Next, use userService.page(new Page<>(0, 2)) to perform a paged query. The page() method is the paging query method provided by MyBatis Plus. The query results will be stored in the page object.

  4. Get the total number of records through page.getTotal(), which is the number of all records that meet the query conditions.

  5. Get the total number of pages via page.getPages(), which is calculated based on the paging parameters.

  6. Finally, obtain the data list of the current page through page.getRecords(), and traverse and print each record through the forEach method.

This test code is used to verify that the paging function is working properly and that the correct paginated results are returned.

Final running result:


In addition, the paging parameter Page used here can support both paging parameters and sorting parameters. For example, sort in descending order according to balance:

// 1. Prepare paging query conditions
Page<User> page = new Page<>();
// 1.1. Set paging parameters
page.setCurrent(0).setSize(2);
// 1.2. Set sorting rules
page.addOrder(new OrderItem("balance", false));

The OrderItem class is used to set the sorting rules. The first parameter is used to specify the sorting field, and the second parameter specifies whether it is sorted in ascending order. Set false to Descending order.

2. Implement universal paging entities

2.1 Paging query requirements

Now we need to implement an interface for user paging query. The interface specification is as follows:

  1. Request method: GET

  2. Request path: /users/page

  3. Request parameters:

    {<!-- -->
    "pageNo": 1,
    "pageSize": 5,
    "sortBy": "balance",
    "isAsc": false,
    "name": "o",
    "status": 1
}

  4. Response data format:

    {<!-- -->
    "total": 100006,
    "pages": 50003,
    "list": [
        {<!-- -->
            "id": 1685100878975279298,
            "username": "user_9****",
            "info": {<!-- -->
                "age": 24,
                "intro": "English teacher",
                "gender": "female"
            },
            "status": "normal",
            "balance": 2000
        }
    ]
}

  5. Special Instructions:

    • If the sort field is empty, it will be sorted by update time by default;
    • If the sort field is not empty, the sorting field will be sorted.

To implement the above interface, you first need to define 3 entity classes:

  • UserQuery: The entity of paging query conditions, including paging, sorting parameters, and filtering conditions
  • PageDTO: paging result entity, including total number of items, total number of pages, and current page data
  • UserVO: User page view entity

2.2 Pagination entity class

  1. UserQuery has been defined before and contains filter conditions. The specific code is as follows:

    @Data
@ApiModel(description = "User query condition entity")
public class UserQuery {<!-- -->
    @ApiModelProperty("username keyword")
    private String name;
    @ApiModelProperty("User status: 1-normal, 2-frozen")
    private Integer status;
    @ApiModelProperty("Minimum balance")
    private Integer minBalance;
    @ApiModelProperty("Maximum balance")
    private Integer maxBalance;
}

  2. What is missing is just the paging conditions, which are not only needed by users for paging queries, but also by other businesses in the future. Therefore, it is best to define the paging query condition separately as a PageQuery entity.

    PageQuery is a paging query parameter submitted by the front end, which generally contains the following four attributes:

    • pageNo: page number
    • pageSize: page size
    • sortBy: sort field
    • isAsc: Whether to ascend

    The following is the implementation code of the PageQuery entity class:

    @Data
@ApiModel(description = "Paging query entity class")
public class PageQuery {<!-- -->
    @ApiModelProperty("page number")
    private Integer pageNo;
    @ApiModelProperty("page size")
    private Integer pageSize;
    @ApiModelProperty("Sort field")
    private String sortBy;
    @ApiModelProperty("Whether to ascend")
    private Boolean isAsc;
}

  3. Then, let UserQuery inherit this entity:
    The annotation @EqualsAndHashCode< provided by Lombok is used here. /code> to override the equals and hashCode methods.

  4. The user entity of the return value here follows the previously implemented UserVO entity:

  5. Finally, we need to implement the PageDTO entity to return the results of paging queries. Since paging queries may be performed on multiple tables later, we implement it as a generic class here. The specific implementation code is as follows :

    @Data
@ApiModel("Paging result entity")
public class PageDTO<T> {<!-- -->
    @ApiModelProperty("Total number of items")
    private Long total;
    @ApiModelProperty("Total number of pages")
    private Long pages;
    @ApiModelProperty("Current page data collection")
    private List<T> list;
}

2.3 Paging query interface

After completing the preparation of all the above entity classes, you can define the interface for paging query.

  1. First use the Controller method to add a queryUsersPage interface in UserController:

    @Api(tags = "User Management Interface")
@RequestMapping("/user")
@RestController
public class UserController {<!-- -->
    @Autowired
    private IUserService userService;
\t\t
// ...
\t\t
    @GetMapping("/page")
    @ApiOperation("Paging query user interface based on complex conditions")
    public PageDTO<UserVO> queryUsersPage(UserQuery query){<!-- -->
        return userService.queryUsersPage(query);
    }
}

  2. Implement the Service interface

    • First define the queryUsersPage method in IUserService 
    public interface IUserService extends IService<User> {<!-- -->
// ...
    PageDTO<UserVO> queryUsersPage(UserQuery query);
}
    • Then implement the queryUsersPage method in UserServiceImpl
    @Override
public PageDTO<UserVO> queryUsersPage(UserQuery query) {<!-- -->
    // 1. Prepare paging query conditions
    // 1.1. Paging conditions
    Page<User> page = Page.of(query.getPageNo(), query.getPageSize());
    // 1.2. Sorting conditions
    if(query.getSortBy().isEmpty()){<!-- -->
        // If the sort field is empty, sort by update time
        page.addOrder(new OrderItem("update_time", false));
    } else {<!-- -->
        page.addOrder(new OrderItem(query.getSortBy(), query.getIsAsc()));
    }
    // 2. Query, the query interface will be encapsulated into the page object.
    this.page(page);
    // 3. Data non-null verification
    List<User> users = page.getRecords();
    if(users == null || users.isEmpty()){<!-- -->
        return new PageDTO<>(page.getTotal(), page.getPages(), Collections.emptyList());
    }
    // 4. Data exists, perform entity class conversion
    List<UserVO> list = BeanUtil.copyToList(users, UserVO.class);

    // 5. Encapsulate the return result
    return new PageDTO<>(page.getTotal(), page.getPages(), list);
}

After completing the above code, send the following request:
Return results:

Although the universal paging entity has been implemented through the above operations, we found that there is a lot of code in the implemented queryUsersPage method, such as the preparation of paging conditions, the conversion of final results, etc. They are all common codes. If you want to implement the paging query function of other tables, these codes need to be written again. Therefore we can consider encapsulating this part of the code.

2.4 Conversion of general entities

2.4.1 Convert PageQuery to MP Page

Let’s first transform the code that prepares paging conditions. In the code just now, the process of converting from PageQuery to Mybatis Plus’s Page is quite troublesome. Therefore, we can directly encapsulate this code into the PageQuery entity as a tool method to simplify development.

For example:

@ApiModel(description = "Paging query entity class")
public class PageQuery {<!-- -->
    @ApiModelProperty("page number")
    private Integer pageNo;
    @ApiModelProperty("page size")
    private Integer pageSize;
    @ApiModelProperty("Sort field")
    private String sortBy;
    @ApiModelProperty("Whether to ascend")
    private Boolean isAsc;


    /**
     * Convert PageQuery to Mybatis Plus Page
     *
     * @param orderItems Manually set sorting conditions
     * @param <T> generic
     * @return Mybatis Plus Page
     */
    public <T> Page<T> toMpPage(OrderItem... orderItems) {<!-- -->
        // 1. Paging conditions
        Page<T> p = Page.of(pageNo, pageSize);
        // 2. Sort submission
        if (sortBy != null) {<!-- -->
            p.addOrder(new OrderItem(sortBy, isAsc));
            return p;
        }

        if (orderItems != null) {<!-- -->
            p.addOrder(orderItems);
        }
        return p;
    }

    // Manually pass in the sorting method
    public <T> Page<T> toMpPage(String defaultSortBy, boolean isAsc) {<!-- -->
        return this.toMpPage(new OrderItem(defaultSortBy, isAsc));
    }

    //Default sort by CreateTime in descending order
    public <T> Page<T> toMpPageDefaultSortByCreateTimeDesc() {<!-- -->
        return this.toMpPage("create_time", false);
    }

    //Default sort by UpdateTime in descending order
    public <T> Page<T> toMpPageDefaultSortByUpdateTimeDesc() {<!-- -->
        return this.toMpPage("update_time", false);
    }
}

In this way, we can save the need to convert from PageQuery to Page when writing Service code:

// 1. Construction conditions
Page<User> page = query.toMpPageDefaultSortByCreateTimeDesc();

2.4.2 Paging results PO conversion VO

After querying the paging results, the non-null verification of the data and the VO conversion of the data are also template codes, which are very troublesome to write. Therefore, we can also encapsulate it into the tool method of PageDTO to simplify the whole process:

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("Paging result entity")
public class PageDTO<T> {<!-- -->
    @ApiModelProperty("Total number of items")
    private Long total;
    @ApiModelProperty("Total number of pages")
    private Long pages;
    @ApiModelProperty("Current page data collection")
    private List<T> list;


    /**
     * Return empty paginated results
     * @param p Paginated results of MyBatis Plus
     * @param <V> target VO type
     * @param <p> Original PO type
     * @return VO's paging results
     */
    public static <V, P> PageDTO<V> empty(Page<p> p) {<!-- -->
        return new PageDTO<>(p.getTotal(), p.getPages(), Collections.emptyList());
    }

    /**
     * Convert MyBatis Plus paging results to VO paging results
     * @param p Paginated results of MyBatis Plus
     * @param voClass bytecode of the target VO type
     * @param <V> target VO type
     * @param <p> Original PO type
     * @return VO's paging results
     */
    public static <V, P>PageDTO<V> of(Page<p> p, Class<V> voClass){<!-- -->
        // 1. Non-null verification
        List<p> records = p.getRecords();
        if(records == null || records.isEmpty()){<!-- -->
            // No data, return empty result
            return empty(p);
        }
        // 2. Data conversion
        List<V> vos = BeanUtil.copyToList(records, voClass);
        // 3. Encapsulate return
        return new PageDTO<>(p.getTotal(), p.getPages(), vos);
    }


    /**
     * Convert Mybatis Plus paging results to VO paging results, allowing users to customize the conversion method from PO to VO
     * @param p Mybatis Plus paging results
     * @param convertor PO to VO conversion function
     * @param <V> target VO type
     * @param <p> Original PO type
     * @return VO's paging results
     */
    public static <V, P>PageDTO<V> of(Page<p> p, Function<P, V> convertor){<!-- -->
        // 1. Non-null verification
        List<p> records = p.getRecords();
        if(records == null || records.isEmpty()){<!-- -->
            // No data, return empty result
            return empty(p);
        }
        // 2. Data conversion
        List<V> vos = records.stream().map(convertor).collect(Collectors.toList());
        // 3. Encapsulate return
        return new PageDTO<>(p.getTotal(), p.getPages(), vos);
    }
}

After completing all the above functions, the final implementation method of queryUsersPage becomes:

@Override
public PageDTO<UserVO> queryUsersPage(UserQuery query) {<!-- -->
    // 1. Prepare paging conditions
    Page<User> p = query.toMpPageDefaultSortByCreateTimeDesc();
    // 2. Paging query
    this.page(p);
    // 3. Encapsulate return
    return PageDTO.of(p, UserVO.class);
}

At this point, the three lines of code are completed, and if you want to perform paging queries on other database tables, you can generally reuse these codes.

In addition, if you want to customize the PO to VO conversion process, you can do this:

@Override
public PageDTO<UserVO> queryUsersPage(UserQuery query) {<!-- -->
    // 1. Prepare paging conditions
    Page<User> p = query.toMpPageDefaultSortByCreateTimeDesc();
    // 2. Paging query
    this.page(p);
    // 3. Encapsulate return
    return PageDTO.of(p, user -> {<!-- -->
        //Copy attributes to VO
        UserVO userVO = BeanUtil.copyProperties(user, UserVO.class);
        //Username desensitization processing
        String username = userVO.getUsername();
        userVO.setUsername(username.substring(0, username.length() - 2) + "**");
        // Return userVO object
        return userVO;
    });
}

Final query return results:

It was found that the username was successfully desensitized:

syntaxbug.com © 2021 All Rights Reserved.