MybatisPlus条件构造器Wrapper和常用接口

一、wrapper介绍

wrapper：条件构造抽象类，最顶端父类

        AbstractWrapper：用于查询条件封装，生成sql的where条件

                QuerryWrapper：查询条件封装

                Updatewarpper：Update条件封装

                AbstractLambdaWrapper：使用Lambda语法

                        LambdaQueryWrapper：用于Lambda语法使用的查询Wrapper

                        LambdaUpdateWrapper：Lambda语法更新封装Wrapper

二、QueryWrapper

1.组装查询条件

    /**
     * 组装查询条件
     * 查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
     * */
    @Test
    public void test01(){
        //创建User类型的queryWrapper对象
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //组装查询条件
        //("columnName","value")
        queryWrapper.like("name","a")
                .between("age",20,30)
                .isNotNull("email");
        //UserMapper extends BaseMapper<User>
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

2.组装排序条件

    /**
     * 组装排序
     * 按年龄降序查询用户，如果年龄相同则按id升序排序
     * */
    @Test
    public void test02(){
        //SELECT id,name,age,email FROM t_user ORDER BY age DESC,id ASC
        //创建User类型的queryWrapper对象
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("id");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

3.组装删除条件

    /**
     * 组装删除
     * 删除email为空的用户
     * */
    @Test
    public void test03(){
        //DELETE FROM t_user WHERE (email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        userMapper.delete(queryWrapper);
    }

4.条件优先级

    /**
     * 条件的优先级
     * */
    @Test
    public void test04(){
        //将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
        //UPDATE t_user SET age=?, email=? WHERE (name LIKE ? AND age > ? OR email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .gt("age","20")
                .or()
                .isNull("email");
        User user = new User();
        user.setAge(18);
        user.setEmail("123@163.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println(result);
    }

    /**
     * 条件的优先级
     * */
    @Test
    public void test05(){
        //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
        //UPDATE t_user SET age=?, email=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .and(i -> i.gt("age", 20).or().isNull("email"));
        User user = new User();
        user.setAge(18);
        user.setEmail("123@163.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println(result);
    }

5.组装select子句

    /**
     * 组装select子句
     * */
    @Test
    public void test06(){
        //SELECT name,age FROM t_user
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name", "age");
        List<Map<String, Object>> mapList = userMapper.selectMaps(queryWrapper);
        mapList.forEach(System.out::println);
    }

6.实现子查询

    /**
     * 实现子查询
     * */
    @Test
    public void test07(){
        //SELECT id,name,age,email FROM t_user WHERE (id IN (select id from t_user where id <= 3))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id", "select id from t_user where id <= 3");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

7.updatewrapper

    /**
     * updatewrapper
     */
    @Test
    public void test08() {
        //UPDATE t_user SET age=?,email=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.set("age", 18)
                .set("email", "123@123.com")
                .like("name", "a")
                .and(i -> i.gt("age", 20).or().isNull("email"));
        int result = userMapper.update(null, updateWrapper);
        System.out.println(result);
    }

8.condition

    /**
     * condition
     * 组装条件来源于用户输入，因此在组装条件时，必须先判断用户是否选择了这些条件，
     * 若选择则组装
     * 可以使用condition参数
     */
    @Test
    public void test09() {
        String name = null;
        Integer ageBegin = 10;
        Integer ageEnd = 24;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 构造条件的方法里有一个 布尔类型的condition参数
        queryWrapper.like(StringUtils.isNotBlank(name), "name", name)
                .ge(ageBegin != null, "age", ageBegin)
                .le(ageEnd != null, "age", ageEnd);
        //SELECT id,name,age,email FROM t_user WHERE (age >= ? AND age <= ?)
        //可以看到 name值为null 则不会作为sql语句的查询条件
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

9.LambdaQueryWrapper

    /**
     * LambdaQueryWrapper
     */
    @Test
    public void test10() {
        //SELECT id,name,age,email FROM t_user WHERE (name LIKE ? AND age >= ? AND age <= ?)
        String name = "a";
        Integer ageBegin = 10;
        Integer ageEnd = 24;
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        //使用User::getAge代替字符串
        lambdaQueryWrapper.like(StringUtils.isNotBlank(name), User::getName, name)
                .ge(ageBegin != null, User::getAge, ageBegin)
                .le(ageEnd != null, User::getAge, ageEnd);
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        userList.forEach(System.out::println);
    }

10.LambdaUpdateWrapper

    /**
     * LambdaUpdateWrapper
     */
    @Test
    public void test11() {
        //UPDATE t_user SET age=?,email=? WHERE (name LIKE ? AND (age < ? OR email IS NULL))
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.set(User::getAge, 18)
                .set(User::getEmail, "126@223.com")
                .like(User::getName, "a")
                .and(i -> i.lt(User::getAge, 24).or().isNull(User::getEmail));
        User user = new User();
        int result = userMapper.update(user, lambdaUpdateWrapper);
        System.out.println(result);
    }

三、mybatisplus配置文件

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      #配置MP操作表的默认前缀，或者也可以在实体类上面用@TableName("[表名]")
      table-prefix: t_
      #设置主键策略
#      id-type: auto