MybatisPlus中and和or的使用

CBeann

发布于 2023-12-25 18:55:29

4540

发布于 2023-12-25 18:55:29

文章被收录于专栏：CBeann的博客CBeann的博客

需求

最近自己玩发现MyBatisPlus还是挺好用的，但是忽然发现对于一个持久层框架来说支持拼接复杂的SQL也是一个优势，对一个持久层框架拼接SQL来说，or比and更难拼，所以此处用案例来实现MybatisPlus中or和and的简单使用。

代码下载（内含数据库）

ChaiRongD/Demooo - Gitee.com

and和or的使用

案例1：AandB

@GetMapping("/AandB")
    public Object AandB(){
        //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();
        return list;
    }

案例2：AorB

 @GetMapping("/AorB")
    public Object AorB(){
        //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();
        return list;
    }

案例3：A or（C and D)

@GetMapping("/A_or_CandD")
    public Object A_or_CandD() {
        //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();
      return list;

案例4：(AandB)or(CandD)

@GetMapping("/AandB_or_CandD")
  public Object AandB_or_CandD() {
    // 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();
    return list;
  }

案例5：A or (B and ( C or D))

@GetMapping("/complex")
  public Object complex() {
    // 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();
    return list;
  }