一、引言
今天来说下MP中强大的条件查询功能。
本章是基于上个案例来讲的:MyBaits-Plus 快速入门示例
二、具体操作
1、首先来说说基本的查询吧,根据主键或者简单的查询条件进行查询。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | /** * 通过单个ID主键进行查询 */ @Test public void selectById() { User user = userMapper.selectById(1094592041087729666L); System.out.println(user); } /** * 通过多个ID主键查询 */ @Test public void selectByList() { List<Long> longs = Arrays.asList(1094592041087729666L, 1094590409767661570L); List<User> users = userMapper.selectBatchIds(longs); users.forEach(System.out::println); } /** * 通过Map参数进行查询 */ @Test public void selectByMap() { Map<String, Object> params = new HashMap<>(); params.put( "name" , "张雨琪" ); List<User> users = userMapper.selectByMap(params); users.forEach(System.out::println); } |
2、MyBatis-Plus还提供了Wrapper条件构造器,具体使用看如下代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | /** * 名字包含雨并且年龄小于40 * <p> * WHERE name LIKE '%雨%' AND age < 40 */ @Test public void selectByWrapper1() { QueryWrapper<User> wrapper = new QueryWrapper(); wrapper.like( "name" , "雨" ).lt( "age" , 40 ); List<User> 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 */ @Test public void selectByWrapper2() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.like( "name" , "雨" ).between( "age" , 20 , 40 ).isNotNull( "email" ); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 名字为王性 * 或者年龄大于等于25 * 按照年龄降序排序,年龄相同按照id升序排序 * <p> * WHERE name LIKE '王%' OR age >= 25 ORDER BY age DESC , id ASC */ @Test public void selectByWrapper3() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.likeRight( "name" , "王" ).or() .ge( "age" , 25 ).orderByDesc( "age" ).orderByAsc( "id" ); List<User> 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 '王%') */ @Test public void selectByWrapper4() { QueryWrapper<User> 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 '王%'" ); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 查询王姓 * 并且年龄小于40或者邮箱不为空 * <p> * WHERE name LIKE '王%' AND ( age < 40 OR email IS NOT NULL ) */ @Test public void selectByWrapper5() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.likeRight( "name" , "王" ).and(qw -> qw.lt( "age" , 40 ).or().isNotNull( "email" )); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 查询王姓 * 并且年龄大于20 、年龄小于40、邮箱不能为空 * <p> * WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL ) */ @Test public void selectByWrapper6() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.likeRight( "name" , "王" ).or( qw -> qw.between( "age" , 20 , 40 ).isNotNull( "email" ) ); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * (年龄小于40或者邮箱不为空) 并且名字姓王 * WHERE ( age < 40 OR email IS NOT NULL ) AND name LIKE '王%' */ @Test public void selectByWrapper7() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.nested(qw -> qw.lt( "age" , 40 ).or().isNotNull( "email" )) .likeRight( "name" , "王" ); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 查询年龄为30、31、32 * WHERE age IN (?,?,?) */ @Test public void selectByWrapper8() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.in( "age" , Arrays.asList( 30 , 31 , 32 )); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } /** * 查询一条数据 * limit 1 */ @Test public void selectByWrapper9() { QueryWrapper<User> wrapper = Wrappers.query(); wrapper.in( "age" , Arrays.asList( 30 , 31 , 32 )).last( "limit 1" ); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } |
工作中的一个示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | @Test void test_01() { List<EmailTask> emailTaskList = this .emailTaskService .list(Wrappers.<EmailTask>lambdaQuery() .or(qw -> qw .eq(EmailTask::getMakeQueueStatus, 1 ) .eq(EmailTask::getStartType, 2 ) .lt(EmailTask::getStartTime, LocalDateTime.now()) ) .or(qw -> qw .eq(EmailTask::getMakeQueueStatus, 1 ) .eq(EmailTask::getStartType, 3 ) ) ); System.out.println(emailTaskList); } |
输出:
1 2 3 4 5 6 7 8 9 | SELECT id,user_id, name ,template_ids,receiver_ids,add_time,update_time FROM email_task WHERE ( (make_queue_status = 1 AND start_type = 2 AND start_time < '2022-07-15T17:00:37.320896400' ) OR (make_queue_status = 1 AND start_type = 3) ) |