Advanced Query Techniques in MyBatis-Plus: Using apply, inSql, and Subqueries

The article provides a step‑by‑step tutorial on using MyBatis‑Plus for advanced query operations, focusing on scenarios where users need to retrieve records created on a specific date whose direct manager's name starts with a particular letter.

Data preparation : It begins with SQL statements to drop the existing user table, create a new one with fields such as id , name , age , email , manager_id , and create_time , and insert five sample rows.

Case 1 – Query example : The goal is to find users whose create_time is 2020‑01‑15 and whose manager's name begins with "J". The difficulty is marked as ★★★★ and the case explores the use of apply and inSql methods.

SQL approaches :

Subquery method: 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 method: 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 apply usage (two signatures): apply(String applySql, Object... params) apply(boolean condition, String applySql, Object... params)

Using inSql for subqueries: inSql(R column, String inValue) inSql(boolean condition, R column, String inValue)

Full example shows a JUnit test that builds a QueryWrapper<User> with a safe apply call and an inSql call to filter by manager name:

@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"); // unsafe
    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);
}

SQL injection risk : The article warns that using the first apply signature with raw strings (e.g., "date_format(create_time, '%Y-%m-%d')='2020-01-15' or true or true" ) can lead to injection, producing a query that returns all users and potentially leaks private data.

Additional resources such as the GitHub repository https://github.com/Jackson0714/study-mybatis-plus.git and the author's blog are provided for further reading.