Mybatis-Plus中and()和or()的使用與原理詳解
一. 簡單無優先級連接(即無括號的sql語句)
簡單來說,兩個子條件間默認and與連接,若兩個之間顯式寫出or()則or或連接.
1. 與連接 and()
當需要簡單的將兩個條件與連接,則最直接的寫法為:
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>(). eq("attr_id",key). eq("catelog_id",catelogId);
當然也可以顯式地寫出and()如下,但沒必要:
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>(). eq("attr_id",key); queryWrapper.and(qr -> qr.eq("catelog_id", catelogId));
2. 或連接 or()
當需要簡單的將兩個條件或連接,則最直接的寫法為:
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>(). eq("attr_id",key). or(). eq("catelog_id",catelogId);
當然也可以如下,但不那麼直觀:
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>(). eq("attr_id",key); queryWrapper.or(qr -> qr.eq("catelog_id", catelogId));
二. 復雜有優先級的的連接
上面有2個不推薦的做法,是因為sql語句為A or B , A and B這種簡單連接.當涉及到諸如 A and ( B or C) and D 這類的復雜有優先級的的連接,直接拼接會導致成為 A and B or C and D.所以這時候需要需要or(Consumer consumer),and(Consumer consumer)這兩個方法.示例如下:
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().eq("attr_type", "base".equalsIgnoreCase(type) ? 1 : 0); queryWrapper.and(qr -> qr.eq("attr_id", key). or(). like("attr_name", key) ); queryWrapper.and(qr -> qr.eq("catelog_id", catelogId));
生成的sql語句如下:
select ... WHERE (attr_type = ? AND ( (attr_id = ? OR attr_name LIKE ?) ) AND ( (catelog_id = ?) )) ...;
由此還可見or(Consumer consumer),and(Consumer consumer)這兩個方法參數為Consumer時,會在連接處生成2對括號,以此提高優先級.
補充:MybatisPlus中and和or的組合使用
案例1:where A=? and B=?
//SELECT id,name,age,sex FROM student WHERE (name = ? AND age = ?) List<Student> list = studentService.lambdaQuery().eq(Student::getName, "1").eq(Student::getAge, 1).list();
案例2:where A=? or B=?
//SELECT id,name,age,sex FROM student WHERE (name = ? OR age = ?) List<Student> list = studentService.lambdaQuery().eq(Student::getName, "1").or().eq(Student::getAge, 12).list();
案例3:where A=? or(C=? and D=?)
//SELECT id,name,age,sex FROM student WHERE (name = ? OR (name = ? AND age = ?)) List<Student> list = studentService .lambdaQuery() .eq(Student::getName, "1") .or(wp -> wp.eq(Student::getName, "1").eq(Student::getAge, 12)) .list();
案例4:where (A=?andB=?)or(C=?andD=?)
// SELECT id,name,age,sex FROM student WHERE ((name = ? AND age = ?) OR (name = ? AND age = ?)) List<Student> list = studentService .lambdaQuery() .and(wp -> wp.eq(Student::getName, "1").eq(Student::getAge, 12)) .or(wp -> wp.eq(Student::getName, "1").eq(Student::getAge, 12)) .list();
案例5:whert A =? or (B=? and ( C=? or D=?))
// SELECT * FROM student WHERE ((name <> 1) OR (name = 1 AND (age IS NULL OR age >= 11))) List<Student> list = studentService .lambdaQuery() .and(wp -> wp.ne(Student::getName, "1")) .or( wp -> wp.eq(Student::getName, "1") .and(wpp -> wpp.isNull(Student::getAge).or().ge(Student::getAge, 11))) .list();
總結
到此這篇關於Mybatis-Plus中and()和or()使用與原理的文章就介紹到這瞭,更多相關Mybatis-Plus中and()和or()使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Mybatis Plus QueryWrapper復合用法詳解
- MybatisPlus使用聚合函數的示例代碼
- MyBatisPlus超詳細分析條件查詢
- mybatis-plus QueryWrapper 添加limit方式
- SpringBoot 整合mybatis+mybatis-plus的詳細步驟