MyBatis-Plus Advanced Query Examples and Explanations

Case Summary (First Wave)

1.1 Query users whose name contains "Ja" and age is less than 30

Difficulty: ★☆

1.2 Query users whose name contains "a", age between 15 and 25, and email is not null

Difficulty: ★☆

1.3 Query users whose name starts with "J", age >= 25, ordered by age descending and id ascending

Difficulty: ★★★

Case Explanation

1.1 Query users whose name contains "Ja" and age is less than 30

Focus: like and lt usage.

SQL:

name LIKE '%Ja%' AND age < 30

/*
 * Description: Example 2.1 – Query users whose name contains "Ja" and age is less than 30
 * Author: Blog - Wukong Architecture
 * Date: 2019-01-20
 */
@Test
public void testSelectByQueryWrapper() {
    System.out.println("----- Query users whose name contains \"Ja\" and age < 30 -----");
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "ja").lt("age", 30);
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

1.2 Query users whose name contains "a", age between 15 and 25, and email is not null

Focus: between and isNotNull usage.

SQL:

name LIKE '%a%' AND age BETWEEN 15 AND 25 AND email IS NOT NULL

/*
 * Description: Example 1.2 – Query users whose name contains "a", age between 15 and 25, email not null
 * Author: Blog - Wukong Architecture
 * Date: 2019-01-20
 */
@Test
public void testSelectByQueryWrapper2() {
    System.out.println("----- Query users whose name contains \"a\" and age 15-25, email not null -----");
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "a").between("age", 15, 25).isNotNull("email");
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

1.3 Query users whose name starts with "J", age >= 25, ordered by age descending then id ascending

Focus: likeRight, orderByDesc, orderByAsc usage.

SQL:

name LIKE 'J%' AND age >= 25 ORDER BY age DESC, id ASC

/*
 * Description: Example 1.3 – Query users whose name starts with "J", age > 26, ordered by age desc, id asc
 * Author: Blog - Wukong Architecture
 * Date: 2019-01-20
 */
@Test
public void testSelectByQueryWrapper3() {
    System.out.println("----- Query users whose name starts with \"J\" and age > 26, ordered by age desc, id asc -----");
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "J").or().gt("age", 26).orderByDesc("age").orderByAsc("id");
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

Data Preparation

The User table structure:

id

name

age

email

1

Jone

18

[email protected]

2

Jack

20

[email protected]

3

Tom

28

[email protected]

4

Sandy

21

[email protected]

5

Billie

24

[email protected]

Code repository: https://github.com/Jackson0714/study-mybatis-plus.git

Follow the public account "Wukong Architecture" for more daily technical tips and resources.