Advanced Query Techniques in MyBatis‑Plus: Using apply, inSql, and JOIN for Complex Conditions

The article introduces a series of MyBatis‑Plus tutorials titled “Play with MyBatis‑Plus in 3 Minutes a Day”, focusing on advanced query capabilities. It first prepares a sample MySQL table user with DDL statements, inserts several rows, and shows the resulting table structure.

Case 1 – Query users created on 2020‑01‑15 whose direct manager's name starts with "J"

Three SQL approaches are presented:

Plain sub‑query using IN : SELECT * FROM demo.user WHERE date_format(create_time, '%Y-%m-%d') = '2020-01-15' AND manager_id IN (SELECT id FROM demo.user WHERE name LIKE 'J%');

INNER JOIN version: SELECT user1.* FROM demo.user AS user1 INNER JOIN demo.user AS user2 ON user1.manager_id = user2.id WHERE date_format(user1.create_time, '%Y-%m-%d') = '2020-01-15' AND user2.name LIKE 'J%';

Dynamic MyBatis‑Plus wrapper using apply and inSql : queryWrapper.apply("date_format(create_time, '%Y-%m-%d')={0}", "2020-01-15") .inSql("manager_id", "select id from user where name like 'J%'");

The article explains the two overloads of apply and warns that the first overload (direct SQL string) can lead to injection if user input is not sanitized.

It then shows how to use inSql for sub‑queries, providing syntax examples and a concrete usage inside the wrapper.

A complete Java test method is given, illustrating the wrapper configuration, execution, and result printing:

@Test
public void testSelectByQueryWrapper4() {
    System.out.println("----- 查询创建日期为2020年1月15日并且直属上级的名字为“J”开头的 ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    // queryWrapper.apply("date_format(create_time, '%Y-%m-%d')='2020-01-15' or true or true"); // vulnerable
    queryWrapper.apply("date_format(create_time, '%Y-%m-%d')={0}", "2020-01-15")
        .inSql("manager_id", "select id from user where name like 'J%'");
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Execution logs and result screenshots are included to verify the query output.

The article also demonstrates how a careless use of apply (e.g., injecting "'2020-01-15' or true or true") can produce a SQL statement that returns all rows, illustrating a classic SQL‑injection scenario.

Finally, the tutorial repeats the series titles for other MyBatis‑Plus topics, promotes the author’s public account, and provides download links to the source code repository.