1, Introduction to wapper

1. Wrapper family

In MP, we can use general Mapper (BaseMapper) to implement basic queries, or use custom Mapper (custom XML) to implement more advanced queries. Of course, you can also combine conditional constructors to facilitate more advanced queries.

Wrapper: conditional construction abstract class, topmost parent class

AbstractWrapper: used to encapsulate query conditions and generate sql where conditions

QueryWrapper: query condition encapsulation

UpdateWrapper: Update conditional encapsulation

AbstractLambdaWrapper: use Lambda syntax

LambdaQueryWrapper: a query Wrapper used for Lambda syntax

Lambda updatewrapper: LambdaUpdateWrapper

2. Create test class

@SpringBootTest
public class WrapperTests {
    
    @Resource
    private UserMapper userMapper;
}

2, QueryWrapper

1. Example 1: assembly query criteria

Query the user whose name contains n, age is greater than or equal to 10 and less than or equal to 20, and email is not empty

@Test
public void test1() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .like("name","n")
        .between("age", 10, 20)
        .isNotNull("email");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

2. Example 2: assembly sorting conditions

Query users by age in descending order. If they are the same age, they are sorted by id in ascending order

@Test
public void test2() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .orderByDesc("age")
        .orderByAsc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

3. Example 3: assembly deletion condition

Delete users whose email is empty

@Test
public void test3() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email");
    int result = userMapper.delete(queryWrapper); //The condition constructor can also build conditions for deleting statements
    System.out.println("delete return count = " + result);
}

4. Example 4: priority of condition

Query the users whose name contains n and whose age is less than 18 or whose email is empty, and set their age to 18 and their email to user@atguigu.com

@Test
public void test4() {
    //modify condition
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .like("name", "n")
        .and(i -> i.lt("age", 18).or().isNull("email")); //Logical priority operation in lambda expression
    User user = new User();
    user.setAge(18);
    user.setEmail("user@atguigu.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println(result);
}

5. Example 5: assemble the select clause

Query the user names and ages of all users

@Test
public void test5() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("name", "age");
    
    //selectMaps() returns the Map collection list, which is usually used in conjunction with select() to avoid null column values not found in the User object
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//The return value is the Map list
    maps.forEach(System.out::println);
}

6. Example 6: implement sub query

Query the id list of all users whose id is not greater than 3

@Test
public void test6() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.inSql("id", "select id from user where id <= 3");
    //Usage scenario of selectObjs: only one column is returned
    List<Object> objects = userMapper.selectObjs(queryWrapper);//The return value is the Object list
    objects.forEach(System.out::println);
}

However, the above method is easy to trigger sql injection

 queryWrapper.inSql("id", "select id from user where id <= 3 or true"); // Or insert all user IDs

Instead, use the following query method

queryWrapper.in("id", 1, 2, 3 );
// or
queryWrapper.le("id", 3 );

3, UpdateWrapper

Example 7: the requirements are the same as example 4

Query the users whose name contains n and whose age is less than 18 or whose email is empty, and set their age to 18 and their email to user@hguo.com

@Test
public void test7() {
    
    //Assemble set clause
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper
        .set("age", 18)
        .set("email", "user@atguigu.com")
        .like("name", "n")
        .and(i -> i.lt("age", 18).or().isNull("email")); //Logical priority operation in lambda expression
    //You must create a User object here, or auto fill cannot be applied. If there is no auto fill, it can be set to null
    User user = new User();
    int result = userMapper.update(user, updateWrapper);
    System.out.println(result);
}

4, condition

Example 8: dynamic assembly query criteria

The query name contains n, users older than 10 and less than 20. The query criteria are from user input and are optional

@Test
public void test8() {
    
    //Define query criteria, which may be null (not entered by the user)
    String name = null;
    Integer ageBegin = 10;
    Integer ageEnd = 20;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    if(StringUtils.isNotBlank(name)){
        queryWrapper.like("name","n");
    }
    if(ageBegin != null){
        queryWrapper.ge("age", ageBegin);
    }
    if(ageEnd != null){
        queryWrapper.le("age", ageEnd);
    }
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

The above implementation scheme has no problem, but the code is complex. We can use the overload method with condition parameter to build query conditions and simplify the writing of code

@Test
public void test8Condition() {
    //Define query criteria, which may be null (not entered by the user)
    String name = null;
    Integer ageBegin = 10;
    Integer ageEnd = 20;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .like(StringUtils.isNotBlank(name), "name", "n")
        .ge(ageBegin != null, "age", ageBegin)
        .le(ageEnd != null, "age", ageEnd);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

5, Lambdaxwrapper

1. Example 9: Query - requirements are the same as example 8

@Test
public void test9() {
    //Define query criteria, which may be null (not entered by the user)
    String name = null;
    Integer ageBegin = 10;
    Integer ageEnd = 20;
    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper
        //Avoid using strings to represent fields and prevent runtime errors
        .like(StringUtils.isNotBlank(name), User::getName, "n")
        .ge(ageBegin != null, User::getAge, ageBegin)
        .le(ageEnd != null, User::getAge, ageEnd);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

2. Example 10: Update - requirements are the same as example 4

@Test
public void test10() {
    //Assemble set clause
    LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
    updateWrapper
        .set(User::getAge, 18)
        .set(User::getEmail, "user@atguigu.com")
        .like(User::getName, "n")
        .and(i -> i.lt(User::getAge, 18).or().isNull(User::getEmail)); //Logical priority operation in lambda expression
    
    User user = new User();
    int result = userMapper.update(user, updateWrapper);
    System.out.println(result);
}