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.

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

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.

<code>@Mapper
public interface OrderMapper extends MPJBaseMapper&lt;Order&gt; {
}
</code>

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.

<code>@Service
@AllArgsConstructor
public class OrderServiceImpl implements OrderService {
    private final OrderMapper orderMapper;
}
</code>

MPJLambdaWrapper

Use

MPJLambdaWrapper

to build a join query 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 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:

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

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

:

<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

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:

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

Then use

selectJoinPage()

to obtain a paged result:

<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, 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.