Using MyBatis-Plus Join Plugin for Multi-Table Queries in Java

MyBatis‑Plus does not support join operations out of the box, so this article introduces the mybatis-plus-join library (version 1.2.4) that adds join capabilities without writing raw SQL.

Installation

For Maven, add the following dependency:

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

For Gradle, use:

implementation 'com.github.yulichang:mybatis-plus-join:1.2.4'

Or clone the project and run mvn install before adding the dependency.

Usage

Mapper should extend MPJBaseMapper (required).

Service can extend MPJBaseService (optional).

Service implementation can extend MPJBaseServiceImpl (optional).

Core Classes – MPJLambdaWrapper

Example of a three‑table query using lambda syntax:

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class,
            new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select(UserAddressDO::getTel)
                .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress)
                .select(AreaDO::getProvince, AreaDO::getCity)
                .leftJoin(UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId)
                .eq(UserDO::getId, 1)
                .like(UserAddressDO::getTel, "1")
                .gt(UserDO::getId, 5));
    }
}

Corresponding SQL:

SELECT
    t.id,
    t.name,
    t.sex,
    t.head_img,
    t1.tel,
    t1.address AS userAddress,
    t2.province,
    t2.city
FROM
    user t
    LEFT JOIN user_address t1 ON t1.user_id = t.id
    LEFT JOIN area t2 ON t2.id = t1.area_id
WHERE (
    t.id = ?
    AND t1.tel LIKE ?
    AND t.id > ?
)

Key points: UserDTO.class specifies the result type. selectAll() selects all fields of the main entity. select() selects specific fields; can be called multiple times. selectAs() provides aliasing for fields. leftJoin() takes the target entity class, the ON field of the target, and the ON field of the main table.

Table aliases default to t, t1, t2 …

All conditions are built with MyBatis‑Plus methods, avoiding SQL‑injection risks.

Core Classes – MPJQueryWrapper

Similar functionality using raw SQL fragments for joins and selections.

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class,
            new MPJQueryWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select("addr.tel", "addr.address", "a.province")
                .leftJoin("user_address addr on t.id = addr.user_id")
                .rightJoin("area a on addr.area_id = a.id")
                .like("addr.tel", "1")
                .le("a.province", "1"));
    }
}

Corresponding SQL:

SELECT
    t.id,
    t.name,
    t.sex,
    t.head_img,
    addr.tel,
    addr.address,
    a.province
FROM
    user t
    LEFT JOIN user_address addr on t.id = addr.user_id
    RIGHT JOIN area a on addr.area_id = a.id
WHERE (
    addr.tel LIKE ?
    AND a.province <= ?
)

Pagination Queries

Both wrappers support pagination via selectJoinPage (or selectJoinPage with a Page object).

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        IPage<UserDTO> iPage = userMapper.selectJoinPage(new Page<>(2, 10), UserDTO.class,
            new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select(UserAddressDO::getTel)
                .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress)
                .select(AreaDO::getProvince, AreaDO::getCity)
                .leftJoin(UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId));
    }
}

Corresponding SQL includes LIMIT ?,? for pagination.

Advanced Usage (Not Recommended)

Shows how to embed CASE statements, aggregate functions, and sub‑queries inside the wrapper.

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class,
            new MPJQueryWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select("addr.tel", "addr.address")
                .select("CASE t.sex WHEN '男' THEN '1' ELSE '0' END AS sex")
                .select("sum(a.province) AS province")
                .leftJoin("(select * from user_address) addr on t.id = addr.user_id")
                .rightJoin("area a on addr.area_id = a.id")
                .like("addr.tel", "1")
                .le("a.province", "1")
                .orderByDesc("addr.id"));
    }
}

Corresponding SQL demonstrates CASE, SUM, sub‑query joins, and ordering.

Source: juejin.cn