Simplify MyBatis-Plus Join Queries with MPJ: A Hands‑On Guide

MyBatis-Plus greatly simplifies database operations in Java projects, but its native join capabilities have long been criticized. The

mybatis-plus-join

(MPJ) library eliminates the need to write XML for left or right joins, allowing developers to write join queries in a fluent, type‑safe style.

Introduce Dependency

Add the following Maven dependencies to your project. MPJ requires a recent version of MyBatis‑Plus, so using the latest versions is recommended.

<code><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>
</code>

Configure the data source in the usual Spring Boot way after adding the dependencies.

Data Preparation

Create three tables for testing join queries: an order table, a user table (containing user names), and a product table (containing product names and unit prices). The order table references the user and product tables by their IDs.

Modify Mapper

Replace the default

BaseMapper

inheritance with

MPJBaseMapper

for each entity mapper. For example:

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

The same change applies to the mappers of the other two tables. Services can optionally extend

MPJBaseService

and

MPJBaseServiceImpl

, but this is not required.

Query with MPJLambdaWrapper

Inject the

OrderMapper

into a service and use

selectJoinList

to perform a join without writing SQL:

<code>public void getOrder() {
    List&lt;OrderDto&gt; list = orderMapper.selectJoinList(OrderDto.class,
        new MPJLambdaWrapper&lt;Order&gt;()
            .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);
}
</code>

The result shows fields from the related tables correctly:

The

MPJLambdaWrapper

provides several useful methods:

selectAll()

: select all columns of the specified entity.

select()

: select specific columns (only from the same table in a single call).

selectAs()

: alias a column to match a DTO field name.

leftJoin()

: perform a left join, specifying the target entity, the ON field from the target, and the ON field from the source.

MPJQueryWrapper

The

MPJQueryWrapper

offers a string‑based alternative similar to MyBatis‑Plus

QueryWrapper

. The same query can be written as:

<code>public void getOrderSimple() {
    List&lt;OrderDto&gt; list = orderMapper.selectJoinList(OrderDto.class,
        new MPJQueryWrapper&lt;Order&gt;()
            .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);
}
</code>

When using the string version, refer to the generated table aliases (

t

for the main table,

t1

,

t2

, …) instead of the original table names.

Pagination Query

MPJ also supports pagination. First, register the MyBatis‑Plus pagination interceptor:

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

Then use

selectJoinPage

:

<code>public void page() {
    IPage&lt;OrderDto&gt; orderPage = orderMapper.selectJoinPage(
        new Page&lt;OrderDto&gt;(2, 10),
        OrderDto.class,
        new MPJLambdaWrapper&lt;Order&gt;()
            .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);
}
</code>

The generated SQL includes a

LIMIT

clause for pagination:

Conclusion

MPJ provides a practical solution for join queries in MyBatis‑Plus projects, especially when the query logic is not overly complex. It reduces the need for manual XML, improves development efficiency, and integrates smoothly with existing MyBatis‑Plus features such as pagination.