Simplify MyBatis Join Queries with MyBatis-Plus-Join (MPJ) – A Hands‑On Guide

Introducing MPJ Dependency

First add the required Maven coordinates. MPJ depends on a higher version of MyBatis-Plus, so it is recommended to use the latest versions.

<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join</artifactId>
    <version>1.2.4</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

After adding the dependencies, configure the data source in the Spring Boot project as usual.

Data Preparation

Create three tables for testing join queries: orders, users, and products.

Order table:

User table (contains user name):

Product table (contains product name and unit price):

The order table links to the user and product tables via foreign keys.

Modify Mapper Interfaces

When using MyBatis-Plus, mapper interfaces usually extend BaseMapper. After adding MPJ, change them to extend MPJBaseMapper instead.

@Mapper
public interface OrderMapper extends MPJBaseMapper<Order> {
}

Apply the same change to the mappers of the other two tables. Services can optionally extend MPJBaseService and MPJBaseServiceImpl, but this is not mandatory.

Query Implementation

Inject the modified mapper into a service. Since Order is the primary table, only OrderMapper needs to be injected.

@Service
@AllArgsConstructor
public class OrderServiceImpl implements OrderService {
    private final OrderMapper orderMapper;
}

MPJLambdaWrapper

Use MPJLambdaWrapper to build a join query without writing SQL:

public void getOrder() {
    List<OrderDto> list = orderMapper.selectJoinList(OrderDto.class,
        new MPJLambdaWrapper<Order>()
            .selectAll(Order.class)
            .select(Product::getUnitPrice)
            .selectAs(User::getName, OrderDto::getUserName)
            .selectAs(Product::getName, OrderDto::getProductName)
            .leftJoin(User.class, User::getId, Order::getUserId)
            .leftJoin(Product.class, Product::getId, Order::getProductId)
            .eq(Order::getStatus, 3));
    list.forEach(System.out::println);
}

The method selectJoinList returns multiple rows, and the first argument ( OrderDto.class) specifies the DTO that receives the result, similar to resultType in MyBatis XML.

Define the DTO by extending the entity and adding fields for the joined columns:

@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class OrderDto extends Order {
    String userName;
    String productName;
    Double unitPrice;
}

Key methods of MPJLambdaWrapper: selectAll(): query all fields of the specified entity. select(): query specific fields (only from the same table in a single call). selectAs(): alias a field when the DTO property name differs. leftJoin(): perform a left join; the first argument is the joined entity class, the second is the ON field from that entity, and the third is the corresponding ON field from the primary entity.

MPJQueryWrapper

For a more SQL‑like style, use MPJQueryWrapper:

public void getOrderSimple() {
    List<OrderDto> list = orderMapper.selectJoinList(OrderDto.class,
        new MPJQueryWrapper<Order>()
            .selectAll(Order.class)
            .select("t2.unit_price", "t2.name as product_name")
            .select("t1.name as user_name")
            .leftJoin("t_user t1 on t1.id = t.user_id")
            .leftJoin("t_product t2 on t2.id = t.product_id")
            .eq("t.status", "3"));
    list.forEach(System.out::println);
}

When using MPJQueryWrapper, refer to tables by the aliases generated by MPJ (the primary table is t, subsequent tables are t1, t2, etc.).

Pagination Query

Add a pagination interceptor to the project:

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
    return interceptor;
}

Then use selectJoinPage() to obtain a paged result:

public void page() {
    IPage<OrderDto> orderPage = orderMapper.selectJoinPage(
        new Page<OrderDto>(2, 10),
        OrderDto.class,
        new MPJLambdaWrapper<Order>()
            .selectAll(Order.class)
            .select(Product::getUnitPrice)
            .selectAs(User::getName, OrderDto::getUserName)
            .selectAs(Product::getName, OrderDto::getProductName)
            .leftJoin(User.class, User::getId, Order::getUserId)
            .leftJoin(Product.class, Product::getId, Order::getProductId)
            .orderByAsc(Order::getId));
    orderPage.getRecords().forEach(System.out::println);
}

The generated SQL includes a LIMIT clause for pagination, and the same approach works with MPJQueryWrapper.

Conclusion

After testing, MPJ proves to be a practical tool for join queries in projects that do not require extremely complex SQL, significantly improving development efficiency. Some issues remain in the current version, and future releases are expected to address them.