Advanced MyBatis-Plus Query Cases (Third Wave)

This article presents a collection of advanced MyBatis-Plus query examples (third wave), offering both a case summary and step‑by‑step explanations.

Case Summary (Third Wave)

1.1 Query names starting with "J" and (age < 20 OR email is not null)

Difficulty ★★★

Focus: AND nesting

/*
 * Description: Example 1.5 – Query names starting with "J" and (age < 20 OR email is not null)
 * SQL: SELECT id, name, age, email, manager_id, create_time FROM user WHERE (name LIKE 'J%' AND ((age < 20 OR email IS NOT NULL)))
 */
@Test
public void testSelectByQueryWrapper5() {
    System.out.println("----- Name starts with 'J' and (age < 20 OR email not null) ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "J").and(qw -> qw.lt("age", 20).or().isNotNull("email"));
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Query log and result screenshots are shown in the original article.

1.2 Query (age < 20 OR email is not null) and then name starts with "J"

Difficulty ★★★

Focus: nested usage

/*
 * Description: Example 1.6 – Query (age < 20 OR email is not null) and name starts with "J"
 * SQL: SELECT id, name, age, email, manager_id, create_time FROM user WHERE ((age < 20 OR email IS NOT NULL) AND name LIKE 'J%')
 */
@Test
public void testSelectByQueryWrapper6() {
    System.out.println("----- Name starts with 'J' and (age < 20 OR email not null) ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    queryWrapper.nested(qw -> qw.lt("age", 20).or().isNotNull("email"))
                .likeRight("name", "J");
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Query log and result screenshots are included.

1.3 Query users whose age is in (20, 21, 25, 26)

Difficulty ★

Focus: IN clause

/*
 * Description: Example 1.7 – Query users with age IN (20,21,25,26)
 * SQL: SELECT id, name, age, email, manager_id, create_time FROM user WHERE age IN (20,21,25,26)
 */
@Test
public void testSelectByQueryWrapper7() {
    System.out.println("----- Query ages 20,21,25,26 ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age", Arrays.asList(20, 21, 25, 26));
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Execution screenshots are provided.

1.4 Same IN query but return only the first record

Difficulty ★★

Focus: last and limit usage

/*
 * Description: Example 1.8 – Query ages IN (20,21,25,26) and limit to one record
 * SQL: SELECT id, name, age, email, manager_id, create_time FROM user WHERE age IN (20,21,25,26) LIMIT 1
 */
@Test
public void testSelectByQueryWrapper8() {
    System.out.println("----- Query ages 20,21,25,26, first record only ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age", Arrays.asList(20, 21, 25, 26)).last("limit 1");
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Query log and result screenshots are shown.

Data Preparation

# Create user table
CREATE TABLE user (
    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT 'Primary key',
    name VARCHAR(30) DEFAULT NULL COMMENT 'Name',
    age INT(11) DEFAULT NULL COMMENT 'Age',
    email VARCHAR(50) DEFAULT NULL COMMENT 'Email',
    manager_id BIGINT(20) DEFAULT NULL COMMENT 'Direct manager id',
    create_time DATETIME DEFAULT NULL COMMENT 'Creation time',
    CONSTRAINT manager_fk FOREIGN KEY (manager_id) REFERENCES user (id)
) ENGINE=INNODB CHARSET=UTF8;

# Initialize data
INSERT INTO user (id, name, age, email, manager_id, create_time) VALUES
    (1087982257332887553, 'Big Boss', 40, '[email protected]', NULL, '2019-01-11 14:20:20'),
    (1088248166370832385, 'Wang Tianfeng', 25, '[email protected]', 1087982257332887553, '2019-02-05 11:12:22'),
    (1088250446457389058, 'Li Yiwei', 28, '[email protected]', 1088248166370832385, '2019-02-14 08:31:16'),
    (1094590409767661570, 'Zhang Yuqi', 31, '[email protected]', 1088248166370832385, '2019-01-14 09:15:15'),
    (1094592041087729666, 'Liu Hongyu', 32, '[email protected]', 1088248166370832385, '2019-01-14 09:48:16');

The source code repository is https://github.com/Jackson0714/study-mybatis-plus.git .

At the end of the article the author promotes the WeChat public account “悟空聊架构” for additional learning resources.