Mybatis-Plus中and()和or()的使用與原理詳解

更新時(shí)間：2022年09月07日 15:12:34 作者：勤奮的Mr.Li

最近發(fā)現(xiàn)MyBatisPlus還是挺好用的,下面這篇文章主要給大家介紹了關(guān)于Mybatis-Plus中and()和or()的使用與原理的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一. 簡(jiǎn)單無(wú)優(yōu)先級(jí)連接(即無(wú)括號(hào)的sql語(yǔ)句)

簡(jiǎn)單來(lái)說(shuō),兩個(gè)子條件間默認(rèn)and與連接,若兩個(gè)之間顯式寫出or()則or或連接.

1. 與連接 and()

當(dāng)需要簡(jiǎn)單的將兩個(gè)條件與連接,則最直接的寫法為:

QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key).
eq("catelog_id",catelogId);

當(dāng)然也可以顯式地寫出and()如下,但沒(méi)必要:

QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key);
 
queryWrapper.and(qr -> qr.eq("catelog_id", catelogId));

2. 或連接 or()

當(dāng)需要簡(jiǎn)單的將兩個(gè)條件或連接,則最直接的寫法為:

QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key).
or().
eq("catelog_id",catelogId);

當(dāng)然也可以如下,但不那么直觀:

QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key);
 
queryWrapper.or(qr -> qr.eq("catelog_id", catelogId));

二. 復(fù)雜有優(yōu)先級(jí)的的連接

上面有2個(gè)不推薦的做法,是因?yàn)閟ql語(yǔ)句為A or B , A and B這種簡(jiǎn)單連接.當(dāng)涉及到諸如 A and ( B or C) and D 這類的復(fù)雜有優(yōu)先級(jí)的的連接,直接拼接會(huì)導(dǎo)致成為 A and B or C and D.所以這時(shí)候需要需要or(Consumer consumer),and(Consumer consumer)這兩個(gè)方法.示例如下:

        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語(yǔ)句如下:

select ...
WHERE (attr_type = ? AND ( (attr_id = ? OR attr_name LIKE ?) ) AND ( (catelog_id = ?) ))
...;

由此還可見(jiàn)or(Consumer consumer),and(Consumer consumer)這兩個(gè)方法參數(shù)為Consumer時(shí),會(huì)在連接處生成2對(duì)括號(hào),以此提高優(yōu)先級(jí).

補(bǔ)充：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();