Advanced MyBatis-Plus Query Examples: AND, Nested, IN, and LIMIT

This article presents a series of advanced query examples using MyBatis-Plus, demonstrating how to combine AND, nested conditions, IN clauses, and LIMIT with the QueryWrapper API.

First, a user table is created and sample data inserted via standard SQL statements.

#创建用户表
CREATE TABLE user (
    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',
    name VARCHAR(30) DEFAULT NULL COMMENT '姓名',
    age INT(11) DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',
    create_time DATETIME DEFAULT NULL COMMENT '创建时间',
    CONSTRAINT manager_fk FOREIGN KEY (manager_id) REFERENCES user (id)
) ENGINE=INNODB CHARSET=UTF8;

#初始化数据：
INSERT INTO user (id, name, age, email, manager_id, create_time) VALUES
(1087982257332887553, '大boss', 40, '[email protected]', NULL, '2019-01-11 14:20:20'),
(1088248166370832385, '王天风', 25, '[email protected]', 1087982257332887553, '2019-02-05 11:12:22'),
...

The article then lists four query cases, each with a difficulty rating.

1.1 Query names starting with “J” and (age < 20 or email not null)

@Test
public void testSelectByQueryWrapper5() {
    System.out.println("----- 名字为'J'开头并且（年龄小于20或邮箱不为空） ------");
    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);
}

Execution logs and result screenshots are shown.

1.2 Query (age < 20 or email not null) and name starts with “J” (nested)

@Test
public void testSelectByQueryWrapper6() {
    System.out.println("----- 名字为'J'开头并且（年龄小于20或邮箱不为空） ------");
    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);
}

Logs and results follow.

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

@Test
public void testSelectByQueryWrapper7() {
    System.out.println("----- 名字为'J'开头并且（年龄小于20或邮箱不为空） ------");
    QueryWrapper
queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age", Arrays.asList(20,21,25,26));
    List
userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Result screenshots are included.

1.4 Same IN query but return only the first record (limit 1)

@Test
public void testSelectByQueryWrapper8() {
    System.out.println("----- 查询年龄为20、21、25、26的用户，且只需返回第一条记录 ------");
    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);
}

Execution logs and result screenshots are displayed.

At the end, the article provides links to earlier MyBatis-Plus tutorials and a call‑to‑action to follow the “悟空聊架构” public account for more architecture resources.