QueryWrapper常用方法

MybatisPlus 使用QueryWrapper测试用例
一、ge、gt、le、lt、isNull、isNotNull

@Test
public void testQuery() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.isNull("name").ge("age", 12).isNotNull("email");int result = userMapper.delete(queryWrapper);System.out.println("delete return count = " + result);
}

二、eq、ne

@Test
public void testSelectOne() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.eq("name", "Tom");Useruser = userMapper.selectOne(queryWrapper);//只能返回一条记录，多余一条则抛出异常System.out.println(user);
}

三、between、notBetween

@Test
public void testSelectCount() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.between("age", 20, 30);Integer count = userMapper.selectCount(queryWrapper); //返回数据数量System.out.println(count);
}

四、like、notLike、likeLeft、likeRight

@Test
public void testSelectMaps() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.select("name", "age").like("name", "e").likeRight("email", "5");List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表maps.forEach(System.out::println);
}

五、orderBy、orderByDesc、orderByAsc

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

六、插入insert

@Test
public void save() {User user = new User();user.setAge(23);user.setEmail("344");user.setName("test");// 1.使用service新增userService.save(user);// 2.使用mapper 新增userMapper.insert(user);
}

七、删除remove

@Test
public void delete() {// 根据条件删除userService.remove(Wrappers.<User>query().lambda().eq(User::getAge, 3));
}

八、修改update

@Test
public void update() {User user = new User();user.setAge(23);user.setEmail("344");user.setName("test333");UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();updateWrapper.eq("id","1");// 1.user 中封装修改的属性值， updateWrapper 中封装修改的条件参数值userService.update(user,updateWrapper);// 2.根据条件修改对应的参数属性值userService.update(Wrappers.<User>update().lambda().set(User::getName, "2").eq(User::getAge, 23));　　　　// 3.判断参数是否为空,并进行修改　　　　 String name = "name";　　　　 userService.update(Wrappers.<User>update().lambda().set(StringUtils.isNotBlank(name),User::getName, name).eq(User::getAge, 23));
}

示例：

QueryWrapper<实体类> queryWrapper = new QueryWrapper<>();List<实体类> practice_log = aaScoreMapper.selectList(queryWrapper.eq("xxxx",jsonObject.getString("xxxx")).eq("yyyyy",jsonObject.getString("yyyy")).eq("uuuuu","uuuuu"));

子查询和嵌套查询

//根据id查询User user = userMapper.selectById(1);System.out.println("根据id查询" + user);//普通查询System.out.println("----- 普通查询 ------");List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 2L).orderByAsc("age"));List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));System.out.println("查询默认未删除 deleted=0" + plainUsers);//子查询System.out.println("----- 子查询 ------");List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>().inSql("role_id", "select id from role where id = 2"));System.out.println("子查询"+plainUsers2);List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda().inSql(User::getRoleId, "select id from role where id = 2"));System.out.println("子查询"+lambdaUsers2);//带嵌套查询System.out.println("----- 带嵌套查询 ------");List<User> plainUsers3 = userMapper.selectList(new QueryWrapper<User>().nested(i -> i.eq("role_id", 2L).or().eq("role_id", 3L)).and(i -> i.ge("age", 20)));List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda().nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L)).and(i -> i.ge(User::getAge, 20)));System.out.println("带嵌套查询"+plainUsers3);System.out.println("带嵌套查询"+lambdaUsers3);//自定义(sql注入)System.out.println("----- 自定义(sql注入) ------");List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>().apply("role_id = 2"));

注意：

1. 优先顺序：!>or>and所以and和or在一起，先计算or，在这里有括号也没用。
2. queryWrapper增加eq或者其他标签，会在后面增加，所以需要重新new一下。
3. 子查询略复杂，请酌定使用。