QueryWrapper

组装查询条件

查询名字包含a ,年龄在20 到30 之间 ，邮箱不为null 的用户信息

@Resource
    UserServiceImpl userService;
    @Test
    public void testQueryWrapper(){
        /**
         * 查询名字包含a ,年龄在20 到30 之间 ，邮箱不为null 的用户信息
         */
        QueryWrapper<User> queryWrapper= new QueryWrapper<>();
        queryWrapper.like("name","a")
                .between("age",20,30)
                .isNotNull("email");
        List<User> list = userService.list(queryWrapper);
        list.forEach(lists-> System.out.println(lists));
    }
    

结果

组装排序条件

用户按年龄降序排列，若年龄相同则按id 升序排列

  @Test
    public void testQueryWrapper02(){
        /**
         * 用户按年龄降序排列，若年龄相同则按id 升序排列
         */
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper
                .orderByDesc("age")
                .orderByAsc("id");
        List<User> lists = userService.list(queryWrapper);
        lists.forEach(list-> System.out.println(list));
    }

组装条件删除

删除email 为空的用户

 @Test
    public void testQueryWrapper03(){
        /**
         * 把邮箱为空的用户删除
         */
        QueryWrapper<User> queryWrapper =new QueryWrapper<>();
        queryWrapper.isNull("email");
        log.info("没有删除之前的结果");
        List<User> lists1 = userService.list(null);
        lists1.forEach(list-> System.out.println(list));
        boolean remove = userService.remove(queryWrapper);
        log.info("删除结果:"+ remove);
        log.info("删除后的结果");
        List<User> lists = userService.list(null);
        lists.forEach(list-> System.out.println(list));

    }

条件的优先级

将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改

@Test
    public void testQueryWrapper04() {
        //将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age", 20)
                .like("name", "a")
                .or()
                .isNull("email");
        User user = new User();
        user.setName("test_1");
        user.setEmail("test_1@qq.com");
        userService.update(user,queryWrapper);
    }

将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改

  @Test
    public void test05() {
        //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .and(i -> i.gt("age", 20).or().isNull("email"));
        userService.update();
    }

组装select 语句

查询用户信息的name和age字段

 @Test
    public void test06(){
        //查询用户信息的name和age字段
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.select("name","email");
        List<Map<String, Object>> mapList = userService.listMaps(queryWrapper);
        mapList.forEach(mapLists-> System.out.println(mapLists));
    }

实现子查询

@Test
public void test06() {
//查询id小于等于3的用户信息
//SELECT id,username AS name,age,email,is_deleted 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> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}

UpdateWrapper