Mybatis-Plus中QueryWrapper的使用

条件构造器Wapper

1. Wrapper：条件构造抽象类，最顶端父类
2. 做条件查询最常用QueryWrapper来构造条件，继承自Wapper，功能更强大

QueryWrapper的使用

1. 创建QueryWrapper对象

   QueryWrapper<User> queryWrapper = new QueryWrapper<>();
   1

2. 调用方法实现各种条件查询

   ge、gt、le、lt、isNull、isNotNull

       queryWrapper
           .isNull("name")
           .ge("age", 12)
           .isNotNull("email");
       int result = userMapper.delete(queryWrapper);
       SQL: update user set deleted=1
            where deleted=0 and name is null and age >= ? and email is not null
   1
   2
   3
   4
   5
   6
   7

   eq、ne

       queryWrapper.eq("name", "Tom");
       User user = userMapper.selectOne(queryWrapper); // 返回的是一条实体记录，当出现多条时会报错
       SQL: select * from user where deleted=0 and name = ?
   1
   2
   3

   between、notBetween

       queryWrapper.between("age", 20, 30); // 包含大小边界
       Integer count = userMapper.selectCount(queryWrapper);
       SQL: select count(1) from user where deleted=0 and age between ? and ?
   1
   2
   3

   allEq

       Map<String, Object> map = new HashMap<>();
       map.put("id", 2);
       map.put("name", "Jack");
       map.put("age", 20);
       queryWrapper.allEq(map);
       List<User> users = userMapper.selectList(queryWrapper); // 返回值是User列表
       SQL: select * from user where deleted=0 and name = ? and id = ? and age = ?
   1
   2
   3
   4
   5
   6
   7

   like、notLike、likeLeft、likeRight

       queryWrapper
           .notLike("name", "e")
           .likeRight("email", "t");
       List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper); // 返回值是Map列表
       SQL: select * from user where deleted=0 and name not like ? and email like ?
   1
   2
   3
   4
   5

   in、notIn、inSql、notinSql、exists、notExists

       in、notIn：
       例: notIn("age",{1,2,3})--->age not in (1,2,3)
       例: notIn("age", 1, 2, 3)--->age not in (1,2,3)
       inSql、notinSql：可以实现子查询
       例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
       例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
       代码：
       queryWrapper.inSql("id", "select id from user where id < 3");
       List<Object> objects = userMapper.selectObjs(queryWrapper); // 返回值是Object列表
       SQL: select * from user where deleted=0 and id in (select id from user where id < 3)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

   or、and

       User user = new User();
       user.setAge(99);
       user.setName("Andy");
       UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>(); // 这里使用的是 UpdateWrapper
       userUpdateWrapper // 不调用or则默认为使用and连
           .like("name", "h")
           .or()
           .between("age", 20, 30);
       int result = userMapper.update(user, userUpdateWrapper);
       SQL: update user set name=?, age=?, update_time=?
            where deleted=0 and name like ? or age between ? and ?
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

   嵌套or、嵌套and

       User user = new User();
       user.setAge(99);
       user.setName("Andy");
       UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
       userUpdateWrapper
           .like("name", "h")
           .or(i -> i.eq("name", "李白").ne("age", 20));
       int result = userMapper.update(user, userUpdateWrapper);
       SQL: update user set name=?, age=?, update_time=?
            where deleted=0 and name like ?or ( name = ? and age <> ? )
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

   orderBy、orderByDesc、orderByAsc

       queryWrapper.orderByDesc("id");
       SQL: select * from user where deleted=0 order by id desc
   1
   2

   last - 只能调用一次，多次调用以最后一次为准，有sql注入的风险

       queryWrapper.last("limit 1"); // 直接拼接到sql的最后
       SQL: select * from user where deleted=0 limit 1
   1
   2

   指定要查询的列

       queryWrapper.select("id", "name", "age"); // select()默认是查全部字段
       SQL: select id,name,age from user where deleted=0
   1
   2

   set、setSql

       User user = new User();
       user.setAge(99);
       UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
       userUpdateWrapper
           .like("name", "h")
           .set("name", "老李头") // 除了可以查询还可以使用set设置修改的字段
           .setSql(" email = '123@qq.com'"); // 可以有子查询
       int result = userMapper.update(user, userUpdateWrapper);
       SQL: update user set age=?, update_time=?, name=?, email = '123@qq.com'
            where deleted=0 and name like ?
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10