国外设计搜索网站,青岛网站建设seo,北京室内设计公司排名,泰安网站营销推广一、条件构造器关系 条件构造器关系介绍#xff1a;
绿色框#xff1a;抽象类 abstract 蓝色框#xff1a;正常 class 类#xff0c;可 new 对象 黄色箭头#xff1a;父子类关系#xff0c;箭头指向为父类
wrapper介绍#xff1a;
Wrapper #xff1a;条件构造抽象类…一、条件构造器关系 条件构造器关系介绍
绿色框抽象类 abstract 蓝色框正常 class 类可 new 对象 黄色箭头父子类关系箭头指向为父类
wrapper介绍
Wrapper 条件构造抽象类最顶端父类
AbstractWrapper 用于查询条件封装生成 sql 的 where 条件
QueryWrapper Entity 对象封装操作类不是用lambda语法
UpdateWrapper Update 条件封装用于Entity对象更新操作
AbstractLambdaWrapper Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
LambdaQueryWrapper 看名称也能明白就是用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper Lambda 更新封装Wrapper
二、项目实例
函数名说明举例eq等于 例eq ( name , 老叶 ) — name ’ 老叶 ’ne不等于 例ne ( name , 老叶 ) — name ’ 老叶 ’gt大于 例gt ( age , 18 ) — name 18ge大于等于 例ge ( age , 18 ) — name 18lt小于 例lt ( age , 18 ) — name 18le小于等于 例le ( age , 18 ) — name 18betweenBetween 值1 and 值2例between ( age , 18, 30 ) — age between 18 and 30notBetweenNOT Between 值1 and 值2例notBetween ( age , 18, 30 ) — age not between 18 and 30likeLIKE ’ %值% ’例like ( name , 王 ) — name like ’ %王% ’notLikeNOT LIKE ’ %值% ’例notLike ( name , 王 ) — name not like ’ %王% ’likeLeftLIKE ’ %值 ’例likeLeft ( name , 王 ) — name like ’ %王 ’likeRightLIKE ’ 值% ’例likeRight ( name , 王 ) — name like ’ 王% ’isNull字段 IS NULL例isNull ( name ) — name is nullisNotNull字段 IS NOT NULL例isNotNull ( name ) — name is not nullin字段 IN (v0, v1, …)例in ( age , {1, 2, …} ) — age in (1, 2, …)notIn字段 NOT IN (v0, v1, …)例notIn ( age , {1, 2, …} ) — age not in (1, 2, …)inSql字段 IN ( sql语句 )inSql ( id , select id from table where id 3 ) — id in ( select id from table where id 3 )notInSql字段 NOT IN ( sql语句 )notInSql ( id , select id from table where id 3 ) — id not in ( select id from table where id 3 )groupBy分组GROUP BY 字段, …例groupBy ( id , name ) — group by id, nameorderBy排序ORDER BY 字段, …例orderBy ( id , name ) — order by id ASC, name DESCorderByAsc排序ORDER BY 字段, … ASC例orderByAsc ( id , name ) — order by id ASC, name ASCorderByDesc排序ORDER BY 字段, … DESC例orderByDesc ( id , name ) — order by id DESC, name DESChavingHAVING ( sql语句 )例having ( sum ( age ) { 0 } , 11 ) —having sum ( age ) 11or拼接 OR注意事项主动调用 or 表示紧接着下一个方法不是用 and 连接(不调用 or 则默认为使用 and 连接)例eq ( id , 1 ).or ().eq ( name , 老王 ) — id 1 or name ’ 老王 ’and嵌套 AND例and ( i - i.eq ( name , 李白 ).ne ( status , 活着 ) ) — and ( name ’ 李白 ’ and status ’ 活着 ’ )nested正常嵌套不带 AND 或者 OR例nested ( i - i.eq ( name , 李白 ).ne ( status , 活着 ) ) — ( name ’ 李白 ’ and status ’ 活着 ’ )apply拼接 sql注意事项该方法可用于数据库函数 动态入参的 params 对应前面 sqlHaving 内部的 { index } 部分这样是不会有 sql 注入风险的反之会有例apply ( date_format ( dateColum, ’ %Y-%m-%d ’ ) { 0 } , 2023-10-30 ) — date_format ( dateColumn, ’ %Y-%m-%d ’ ) ’ 2023-10-30 ’last无视优化规则直接拼接到 sql 的最后注意事项只能调用一次多次调用以最后一次为准。有 sql 注入的风险请谨慎使用例last ( limit 1 )exists拼接 EXISTS ( sql语句 )例exists ( select id from table where age 1 ) — exists ( select id from table where age 1 )notExists拼接 NOT EXISTS ( sql语句 )例notExists ( select id from table where age 1 ) — not exists ( select id from table where age 1 )
1、根据简单条件查询 /*** 通过单个ID主键进行查询*/Testpublic void selectById() {User user userMapper.selectById(1094592041087729666L);System.out.println(user);}/*** 通过多个ID主键查询*/Testpublic void selectByList() {ListLong longs Arrays.asList(1094592041087729666L, 1094590409767661570L);ListUser users userMapper.selectBatchIds(longs);users.forEach(System.out::println);}/*** 通过Map参数进行查询*/Testpublic void selectByMap() {MapString, Object params new HashMap();params.put(name, 郑梓妍);params.put(age, 18);ListUser users userMapper.selectByMap(params);users.forEach(System.out::println);}2、Wrapper 条件构造器
MyBatis Plus 还提供了 Wrapper 条件构造器具体使用请看如下代码 /*** 名字包含雨* 且年龄小于40* p* WHERE name LIKE %雨% AND age 40*/Testpublic void selectByWrapperOne() {QueryWrapperUser wrapper new QueryWrapper();wrapper.like(name, 雨).lt(age, 40);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 名字包含雨* 且年龄大于20小于40* 且邮箱不能为空* p* WHERE name LIKE %雨% AND age BETWEEN 20 AND 40 AND email IS NOT NULL*/Testpublic void selectByWrapperTwo() {QueryWrapperUser wrapper Wrappers.query();wrapper.like(name, 雨).between(age, 20, 40).isNotNull(email);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 名字为王姓* 或者年龄大于等于25* 按照年龄降序排序年龄相同按照id升序排序* p* WHERE name LIKE 王% OR age 25 ORDER BY age DESC , id ASC*/Testpublic void selectByWrapperThree() {QueryWrapperUser wrapper Wrappers.query();wrapper.likeRight(name, 王).or().ge(age, 25).orderByDesc(age).orderByAsc(id);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 查询创建时间为2019年2月14* 且上级领导姓王* p* WHERE date_format(create_time,%Y-%m-%d) 2019-02-14 AND manager_id IN (select id from user where name like 王%)*/Testpublic void selectByWrapperFour() {QueryWrapperUser wrapper Wrappers.query();wrapper.apply(date_format(create_time,%Y-%m-%d) {0}, 2019-02-14).inSql(manager_id, select id from user where name like 王%);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 查询王姓* 且年龄小于40或者邮箱不为空* p* WHERE name LIKE 王% AND ( age 40 OR email IS NOT NULL )*/Testpublic void selectByWrapperFive() {QueryWrapperUser wrapper Wrappers.query();wrapper.likeRight(name, 王).and(qw - qw.lt(age, 40).or().isNotNull(email));ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 查询王姓* 且年龄大于20 、年龄小于40、邮箱不能为空* p* WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL )*/Testpublic void selectByWrapperSix() {QueryWrapperUser wrapper Wrappers.query();wrapper.likeRight(name, 王).or(qw - qw.between(age, 20, 40).isNotNull(email));ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** (年龄小于40或者邮箱不为空) 并且名字姓王* WHERE ( age 40 OR email IS NOT NULL ) AND name LIKE 王%*/Testpublic void selectByWrapperSeven() {QueryWrapperUser wrapper Wrappers.query();wrapper.nested(qw - qw.lt(age, 40).or().isNotNull(email)).likeRight(name, 王);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 查询年龄为30、31、32* WHERE age IN (?,?,?)*/Testpublic void selectByWrapperEight() {QueryWrapperUser wrapper Wrappers.query();wrapper.in(age, Arrays.asList(30, 31, 32));ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}/*** 年龄为30、31、32* 查询一条数据* limit 1*/Testpublic void selectByWrapperNine() {QueryWrapperUser wrapper Wrappers.query();wrapper.in(age, Arrays.asList(30, 31, 32)).last(limit 1);ListUser users userMapper.selectList(wrapper);users.forEach(System.out::println);}三、具体使用操作
注意以下条件构造器的方法入参中的 column 均表示数据库字段
1、ge、gt、le、lt、isNull、isNotNull
Test
public void testDelete() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.isNull(name).ge(age, 12).isNotNull(email);int result userMapper.delete(queryWrapper);System.out.println(delete return count result);
}update user set deleted 1 where deleted 0 and name is null and age ? and email is not null2、eq、ne
selectOne 返回的是一条实体记录当出现多条时会报错
Test
public void testSelectOne() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.eq(name, Tom);User user userMapper.selectOne(queryWrapper);System.out.println(user);
}3、between、notBetween
包含大小边界
Test
public void testSelectCount() {QueryWrapperUser 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 20 and 304、allEq
包含大小边界
Test
public void testSelectList() {QueryWrapperUser queryWrapper new QueryWrapper();MapString, Object map new HashMap();map.put(id, 2);map.put(name, Jack);map.put(age, 20);queryWrapper.allEq(map);ListUser 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 Jack and id 2 and age 205、like、notLike、likeLeft、likeRight
selectMaps 返回 Map 集合列表
Test
public void testSelectMaps() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.notLike(name, e).likeRight(email, t);ListMapString, 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 %e% and email like t%6、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) --- age in (1,2,3)inSql(id, select id from table where id 3) --- id in (select id from table where id 3)Test
public void testSelectObjs() {QueryWrapperUser queryWrapper new QueryWrapper();//queryWrapper.in(id, 1, 2, 3);queryWrapper.inSql(id, select id from user where id 3);ListObject objects userMapper.selectObjs(queryWrapper);//返回值是Object列表objects.forEach(System.out::println);
}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)7、or、and
这里使用的是 UpdateWrapper 不调用 or 则默认为使用 and 连接
Test
public void testUpdate() {//修改值User user new User();user.setAge(99);user.setName(Andy);//修改条件UpdateWrapperUser 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 Andy, age 99, update_time ? where deleted 0 and ((name like %h%) or (age between 20 and 30))-- 名称包含’h‘或名年龄在20到30岁之间8、嵌套or、嵌套and
这里使用 lambda 表达式or 中的表达式最后翻译成 sql 时会被加上圆括号
Test
public void testUpdate2() {//修改值User user new User();user.setAge(99);user.setName(Andy);//修改条件UpdateWrapperUser 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);
}update user set name Andy, age 99, update_time ? where deleted 0 and name like %h% or (name %李白% and age 20)-- 名称包含’h‘或名称包含‘李白’且年龄不等于20岁9、orderBy、orderByDesc、orderByAsc
Test
public void testSelectListOrderBy() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.orderByDesc(id);ListUser 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 order by id desc10、last
直接拼接到 sql 的最后 注意只能调用一次多次调用以最后一次为准有 sql 注入的风险请谨慎使用
Test
public void testSelectListLast() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.last(limit 1);ListUser 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 111、指定要查询的列
Test
public void testSelectListColumn() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.select(id, name, age);ListUser users userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}select id, name, age from user where deleted 012、set、setSql
最终的 sql 会合并 user.setAge()以及 userUpdateWrapper.set() 和 setSql() 中的字段
Test
public void testUpdateSet() {//修改值User user new User();user.setAge(99);//修改条件UpdateWrapperUser userUpdateWrapper new UpdateWrapper();userUpdateWrapper.like(name, h).set(name, 老李头) //除了可以查询还可以使用set设置修改的字段.setSql( email 123qq.com); //可以有子查询int result userMapper.update(user, userUpdateWrapper);
}update user set age 99, update_time ?, name 老李头, email 123qq.com where deleted 0 and name like %h%四、项目中的实际应用
实例1-- 包含 eq 相等的比较方法 实例2-- 包含 ge le 等比较方法及分页查询 好事定律每件事最后都会是好事如果不是好事说明还没到最后。
