用到了wrapper,整理资料记录一下,以备后续复习。
上图绿色框为抽象类abstract
蓝色框为正常class类,可new对象
黄色箭头指向为父子类关系,箭头指向为父类
Wrapper : 条件构造抽象类,最顶端父类
AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
QueryWrapper : Entity 对象封装操作类,不是用lambda语法
UpdateWrapper : Update 条件封装,用于Entity对象更新操作
AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper : Lambda 更新封装Wrapper
/** * 通过单个ID主键进行查询 */ @Test public void selectById() { User user = userMapper.selectById(1094592041087729666L); System.out.println(user); } /** * 通过多个ID主键查询 public void selectByList() { List<Long> longs = Arrays.asList(1094592041087729666L, 1094590409767661570L); List<User> users = userMapper.selectBatchIds(longs); users.forEach(System.out::println); * 通过Map参数进行查询 public void selectByMap() { Map<String, Object> params = new HashMap<>(); params.put("name", "张雨琪"); List<User> users = userMapper.selectByMap(params);
/** * 名字包含雨并且年龄小于40 * <p> * WHERE name LIKE '%雨%' AND age < 40 */ @Test public void selectByWrapperOne() { QueryWrapper<User> wrapper = new QueryWrapper(); wrapper.like("name", "雨").lt("age", 40); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 名字包含雨 * 年龄大于20小于40 * 邮箱不能为空 * WHERE name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL public void selectByWrapperTwo() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.like("name", "雨").between("age", 20, 40).isNotNull("email"); * 名字为王性 * 或者年龄大于等于25 * 按照年龄降序排序,年龄相同按照id升序排序 * WHERE name LIKE '王%' OR age >= 25 ORDER BY age DESC , id ASC public void selectByWrapperThree() { wrapper.likeRight("name", "王").or() .ge("age", 25).orderByDesc("age").orderByAsc("id"); * 查询创建时间为2019年2月14 * 并且上级领导姓王 * WHERE date_format(create_time,'%Y-%m-%d') = '2019-02-14' AND manager_id IN (select id from user where name like '王%') public void selectByWrapperFour() { wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14") .inSql("manager_id", "select id from user where name like '王%'"); * 查询王姓 * 并且年龄小于40或者邮箱不为空 * WHERE name LIKE '王%' AND ( age < 40 OR email IS NOT NULL ) public void selectByWrapperFive() { wrapper.likeRight("name", "王").and(qw -> qw.lt("age", 40).or().isNotNull("email")); * 并且年龄大于20 、年龄小于40、邮箱不能为空 * WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL ) public void selectByWrapperSix() { wrapper.likeRight("name", "王").or( qw -> qw.between("age", 20, 40).isNotNull("email") ); * (年龄小于40或者邮箱不为空) 并且名字姓王 * WHERE ( age < 40 OR email IS NOT NULL ) AND name LIKE '王%' public void selectByWrapperSeven() { wrapper.nested(qw -> qw.lt("age", 40).or().isNotNull("email")) .likeRight("name", "王"); * 查询年龄为30、31、32 * WHERE age IN (?,?,?) public void selectByWrapperEight() { wrapper.in("age", Arrays.asList(30, 31, 32)); * 查询一条数据 * limit 1 public void selectByWrapperNine() { wrapper.in("age", Arrays.asList(30, 31, 32)).last("limit 1");
注意:以下条件构造器的方法入参中的 column
均表示数据库字段
@Test public void testDelete() { 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); }
SQL:UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
注意:seletOne返回的是一条实体记录,当出现多条时会报错
@Test public void testSelectOne() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("name", "Tom"); User user = userMapper.selectOne(queryWrapper); System.out.println(user); }
包含大小边界
@Test public void testSelectCount() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.between("age", 20, 30); Integer count = userMapper.selectCount(queryWrapper); System.out.println(count); }
SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?
@Test public void testSelectList() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); Map<String, Object> map = new HashMap<>(); map.put("id", 2); map.put("name", "Jack"); map.put("age", 20);9 queryWrapper.allEq(map); List<User> users = userMapper.selectList(queryWrapper); users.forEach(System.out::println); }
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?
selectMaps返回Map集合列表
@Test public void testSelectMaps() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper .notLike("name", "e") .likeRight("email", "t"); List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表 maps.forEach(System.out::println); }
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name NOT LIKE ? AND email LIKE ?
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)
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)
注意:这里使用的是 UpdateWrapper 不调用or则默认为使用 and 连
@Test public void testSelectObjs() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); //queryWrapper.in("id", 1, 2, 3); queryWrapper.inSql("id", "select id from user where id < 3"); List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表 objects.forEach(System.out::println); }
UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND name LIKE ? OR age BETWEEN ? AND ?
这里使用了lambda表达式,or中的表达式最后翻译成sql时会被加上圆括号
@Test public void testUpdate1() { //修改值 User user = new User(); user.setAge(99); user.setName("Andy"); //修改条件 UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>(); userUpdateWrapper .like("name", "h") .or() .between("age", 20, 30); int result = userMapper.update(user, userUpdateWrapper); System.out.println(result); }
UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND name LIKE ? OR ( name = ? AND age <> ? )
@Test public void testUpdate2() { //修改值 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); System.out.println(result); }
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 ORDER BY id DESC
直接拼接到 sql 的最后
注意:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
@Test public void testSelectListLast() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.last("limit 1"); List<User> users = userMapper.selectList(queryWrapper); users.forEach(System.out::println); }
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 limit 1
@Test public void testSelectListColumn() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.select("id", "name", "age"); List<User> users = userMapper.selectList(queryWrapper); users.forEach(System.out::println); }
SELECT id,name,age FROM user WHERE deleted=0
最终的sql会合并 user.setAge(),以及 userUpdateWrapper.set() 和 setSql() 中 的字段
@Test public void testUpdateSet() { //修改值 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); }
UPDATE user SET age=?, update_time=?, name=?, email = '123@qq.com' WHERE deleted=0 AND name LIKE ?
参考文档
1、https://blog.csdn.net/m0_37034294/article/details/82917234
2、https://blog.csdn.net/kepengs/article/details/112345870
3、https://blog.csdn.net/weixin_39615889/article/details/107086931
4、https://blog.csdn.net/weixin_38111957/article/details/91447509