Mybatisplus multi-table related pagination query multiple implementation methods

In MyBatis-Plus, although there is no built-in method that directly supports multi-table association pagination query, multi-table association pagination query can be implemented in the following ways:

1. Use MyBatis-Plus to work with XML custom SQL to implement multi-table associated pagination query

This is the most commonly used method. Custom SQL queries can realize more complex association queries, combined with MyBatis-Plus'sIPageInterface, can achieve pagination effect.

Implementation steps

• Define query method: Define the pagination query method in the Mapper interface.
• Writing XML query statements: Write SQL queries in Mapper XML files, including paging logic.
• Calling the paging plugin: Call the pagination query method at the service layer.

Sample code

Entity class definition

Suppose there are two entity classes:UserandOrder, We want to query the user and their corresponding order list.

@Data
public class User {
    private Long id;
    private String name;
}

@Data
public class Order {
    private Long id;
    private Long userId;
    private String productName;
}

Mapper interface definition

existUserMapperThe paging query method is defined in:

import ;
import ;
import ;

public interface UserMapper extends BaseMapper<User> {
    IPage<User> selectUserOrders(Page<?> page, @Param("userId") Long userId);
}

Mapper XML to write multi-table association queries

existDefine multi-table association SQL query in  :

<select  resultType="User">
    SELECT u.*, o.product_name
    FROM user u
    LEFT JOIN order o ON  = o.user_id
    WHERE  = #{userId}
</select>

Service calls pagination query

@Autowired
private UserMapper userMapper;

public IPage<User> getUserOrders(Page<User> page, Long userId) {
    return (page, userId);
}

Passed in when calling this methodPageObject, MyBatis-Plus will automatically process paging parameters.

Page<User> page = new Page<>(1, 10); //Pagination parameters: Page 1, 10 items per pageIPage<User> result = (page, 1L);

2. Use MyBatis-Plus' Wrapper with custom SQL to implement pagination query

Can be passedWrapperMatchCustom SQLImplement associated query and pagination in a way. This method is flexible, but requires writing SQL statements yourself.

Sample code

existUserMapperDefine the query method in  :

@Select("SELECT u.*, o.product_name " +
        "FROM user u LEFT JOIN order o ON  = o.user_id " +
        "WHERE  = #{userId}")
IPage<User> selectUserOrdersCustom(Page<?> page, @Param("userId") Long userId);

Invoke pagination query directly at the Service layer:

Page<User> page = new Page<>(1, 10); //Pagination parametersIPage<User> result = (page, 1L);

3. Use MyBatis-Plus to implement paging query with Wrapper and associated query DTO

Use DTO (Data Transfer Object) as the query result to map the fields queryed into the DTO, reducing the coupling between database fields and entity classes.

Sample code

Define the DTO class for query results

@Data
public class UserOrderDTO {
    private Long userId;
    private String userName;
    private String productName;
}

Define the query method of UserMapper

@Select("SELECT  AS userId,  AS userName, o.product_name AS productName " +
        "FROM user u LEFT JOIN order o ON  = o.user_id " +
        "WHERE  = #{userId}")
IPage<UserOrderDTO> selectUserOrderDTO(Page<?> page, @Param("userId") Long userId);

Service calls pagination query

Page<UserOrderDTO> page = new Page<>(1, 10); //Pagination parametersIPage<UserOrderDTO> result = (page, 1L);

Through DTO, returning paging data, avoiding the direct use of entity classes as result objects, which can increase query flexibility and reusability.

4. Use MyBatis-Plus and PageHelper to implement multi-table related pagination query

Although MyBatis-Plus comes with a paging plug-in, it can also be combined in complex multi-table queries.PageHelperUse the pagination function.

Implementation steps

Add PageHelper dependencies:

<dependency>
    <groupId></groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.13</version>
</dependency>

Called in query method()Set paging parameters.

Sample code

import ;
import ;

public PageInfo<UserOrderDTO> getUserOrdersPage(int pageNum, int pageSize, Long userId) {
    (pageNum, pageSize);
    List<UserOrderDTO> list = (userId);
    return new PageInfo<>(list);
}

pass(pageNum, pageSize);Configure the paging and then usePageInfoEncapsulate and return the result and obtain the paging data.

Summarize

Through the above methods, you can select the appropriate multi-table related paging query method according to the project's needs and complexity.

This is the article about Mybatisplus multi-table related pagination query and multiple implementation methods. For more related Mybatisplus multi-table related pagination query content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!