Advanced Query Techniques in MyBatis-Plus: Date Filtering, Subqueries, and Dynamic SQL with apply and inSql

The tutorial walks through advanced querying capabilities of MyBatis-Plus, starting with data preparation by creating and populating a user table.

It then presents a case study: retrieve records created on 2020‑01‑15 whose direct manager's name starts with "J". Three approaches are shown:

1. Plain SQL with a sub‑query:

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%');

2. Using INNER JOIN :

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%';

3. Leveraging MyBatis‑Plus dynamic wrappers with apply and inSql :

QueryWrapper
queryWrapper = new QueryWrapper<>();
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);

The article explains the two overloads of apply —the conditional version and the simple version—warning that the simple version can introduce SQL‑injection risks if user input is concatenated directly.

It also details the inSql helper, showing how to embed raw sub‑queries or comma‑separated values into a IN clause.

A full annotated example combines these techniques, includes author and source information, and demonstrates the resulting SQL log and query output screenshots.

Finally, the tutorial illustrates how an insecure use of apply (e.g., passing "'2020-01-15' or true or true") can produce a vulnerable query that may expose all user data, emphasizing the importance of parameterized statements.