Mybatis-plus<三> MybatisPlus条件构造器
阅读原文时间:2023年07月08日阅读:1

Mybatis-plus<三> MybatisPlus条件构造器

Demo GitHub下载地址:https://github.com/RJvon/Mybatis_plus_demo

Mybatis-plus条件构造器: https://mp.baomidou.com/guide/wrapper.html#链式调用-lambda-式

*wapper介绍 :*

  • Wrapper : 条件构造抽象类,最顶端父类

---AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件

-------QueryWrapper : Entity 对象封装操作类,不是用lambda语法

-------UpdateWrapper : Update 条件封装,用于Entity对象更新操作

---AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column

-------LambdaQueryWrapper :用于Lambda语法使用的查询Wrapper

-------LambdaUpdateWrapper : Lambda 更新封装Wrapper

以demo中的数据表为例介绍条件构造器使用:

allEq

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
  • 全部eq(或个别isNull)

    个别参数说明:

    params : key为数据库字段名,value为字段值

    null2IsNull : 为true则在mapvaluenull时调用 [isNull]方法,为false时则忽略valuenull

  • 例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null

  • 例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'

    @Test
    public void testAllEq() {
    Map params = new HashMap<>();
    params.put("sex", "f");
    params.put("age", "19");
    params.put("address", null);
    QueryWrapper wrapper = new QueryWrapper<>();
    //1,2,3,4仅保留一项,其余注释掉
    //1.全部匹配params条件
    wrapper.allEq(params);
    //2.全部匹配params条件(为null的不作为条件)
    wrapper.allEq(params, false);
    //3.只有params中k.equals("sex")或者k.equals("id")才作为条件
    wrapper.allEq((k, v) -> (k.equals("sex") || k.equals("id")), params);
    //4.只有params中k.equals("sex")或者k.equals("id")或者k.equals("name")才作为条件
    wrapper.allEq((k, v) -> (k.equals("age") || k.equals("id") || k.equals("name")), params);
    List students = this.studentMapper.selectList(wrapper);
    for (Student student : students) {
    System.out.println(student);
    }
    }

eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)
  • 等于 =

  • 例: eq("name", "老王")--->`name = '老王'

    @Test
    public void testEq() {
        //department==english && age==16 sex==f
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.eq("department", "english")
                .ge("age", "16")
                .in("sex", "f");

like/notlike

like(R column, Object val)
like(boolean condition, R column, Object val)
  • LIKE '%值%'

  • 例: like("name", "王")--->name like '%王%'

    @Test
    public void testLike() {
    QueryWrapper wrapper = new QueryWrapper<>();
    //姓名叫l某某
    wrapper.likeRight("name", "l");
    List students = this.studentMapper.selectList(wrapper);
    for (Student student : students) {
    System.out.println(student);
    }
    }

gt/lt/le/ne/ge……

eq

等于 =

gt

大于 >

lt

小于 <

le

小于等于 <=

ne

不等于 <>

ge

大于等于 >=

in

字段 IN (value.get(0), value.get(1), …)

notIn

字段 NOT IN (v0, v1, …)

between

BETWEEN 值1 AND 值2

notBetween

NOT BETWEEN 值1 AND 值2

@Test
public void selectWrapper04() {
    //姓名叫l某某,并且(年龄小于24或者地址不为空)
    QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "l");
    queryWrapper.and(qw -> qw.lt("age", 24).or().isNotNull("address"));
    List list = studentMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

@Test
    public void selectWrapper05() {
        //年龄等于21,22,28
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(21, 22, 28));
        List<Student> users = studentMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 筛选年龄=21, 22, 28的人,返回一条数据
     */
    @Test
    public void selectWrapper08() {
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(21, 26, 28)).last("limit 1");
        List<Student> users = studentMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

or

@Test
public void testOr() {
    //姓名叫zhujiao,或者年龄等于21
    QueryWrapper<Student> wrapper = new QueryWrapper<>();
    wrapper.eq("name", "zhujiao").or().eq("age", 21);
    List<Student> students = this.studentMapper.selectList(wrapper);
    for (Student student : students) {
        System.out.println(student);
    }
}

orderByDesc/orderByAsc

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
  • 排序:ORDER BY 字段, … ASC

  • 例: orderByAsc("id", "name")--->order by id ASC,name ASC

    @Test
    public void selectWrapper03() {
    //姓名叫l某某,或者年龄小于23,按年龄降序,id升序
    QueryWrapper queryWrapper = new QueryWrapper();
    queryWrapper.likeRight("name", "l");
    queryWrapper.or();
    queryWrapper.lt("age", 23);
    queryWrapper.orderByDesc("age");
    queryWrapper.orderByAsc("id");
    List list = studentMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
    }