Mybatis-Plus高级查询LambdaQueryWrapper&QueryWrapper_百度小程序

Wrapper

条件	说明
allEq	基于 map 的比较
eq	等于 =
ne	不等于 <> 或者 !=
gt	大于 >
ge	大于等于 >=
lt	小于 <
le	小于等于 <
between	BETWEEN 值1 AND 值2
notBetween	NOT BETWEEN 值1 AND 值2
like	LIKE ‘%值%’
notLike	NOT LIKE ‘%值%’
likeLeft	LIKE ‘%值’
likeRight	LIKE ‘值%’
isNull	字段 IS NULL
isNotNull	字段 IS NOT NULL
in	字段 IN (value1, value2, …)
notIn	字段 NOT IN (value1, value2, …)
inSql	字段 IN (sql 语句) inSql(“age”, “1,2,3”) -> age in (1,2,3) inSql(“id”, “select id from student where id < 3”) -> id in (select id from student where id < 3)
notInSql	字段 NOT IN (sql 语句)
groupBy	GROUP BY 字段
orderByAsc	升序 ORDER BY 字段, … ASC
orderByDesc	降序 ORDER BY 字段, … DESC
orderBy	自定义字段排序 orderBy(true, true, “id”, “name”) -> order by id ASC, name ASC
having	条件分组
or	OR 语句，拼接 + OR 字段=值
and	AND 语句，拼接 + AND 字段=值
apply	拼接 sql
last	在 sql 语句后拼接自定义条件
exists	拼接 EXISTS(sql语句) exists(“selece id from student where age = 1”) -> exists(selece id from student where age = 1)
notExists	拼接 NOT EXISTS(sql语句)
nested	正常嵌套不带 AND 或者 OR

查询构造器

查询条件

前期准备

创建一个数据库 mybatisplus

创建 user 表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

创建 springboot 工程

导入对应 maven 坐标



    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.3
         
    
    com.cmy
    mybatis_plus
    0.0.1-SNAPSHOT
    mybatis_plus
    Demo project for Spring Boot
    
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter
        
        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.0
        
        
        
            mysql
            mysql-connector-java
            runtime
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    
    
        
            
                org.springframework.boot
                spring-boot-maven-plugin

mysql数据库相关配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1/mybatisplus?useUnicode=true&characterEncoding=utf-8
    username: root
    password: root

mybatis-plus 日志信息配置

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

创建实体类 User

package com.cmy.mybatis_plus.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
/**
 * 实体类 user
 */
public class User {
    /**
     * 指定主键id生成的方式
     * value 是主键字段的名称，如果是id，可以不用写
     * type 指定主键的类型，主键的值如何生成。idType.AUTO 自动增长
     */
    @TableId(
            value = "id",
            type = IdType.AUTO
    )
    private Long id;
    private String name;
    private String email;
    private Integer age;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

自定义 User 的 Mapper 接口

package com.cmy.mybatis_plus.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cmy.mybatis_plus.entity.User;
/**
 * 自定义 Mapper 接口，就是 dao 接口
 * 1. 实现BaseMapper
 * 2. 指定实体类（泛型）
 *
 * BaseMapper 是 MP 框架中的对象，定义了 17 个操作方法（CRUD）
 */
public interface UserMapper extends BaseMapper {
}

查询条件

allEq

条件用 Map 进行封装

“name” -> “张三”

“age” -> 20

public void testAllEq() {
    QueryWrapper queryWrapper = new QueryWrapper<>();
    // 封装条件
    Map hashMap = new HashMap<>();
    hashMap.put("name", "张三");
    hashMap.put("age", 20);
    queryWrapper.allEq(hashMap);
    List userList = userMapper.selectList(queryWrapper);
    userList.forEach(user -> {
        System.out.println(user);
    });
}

eq

eq("列名", 值) -> 列名 = 值

    public List listByDictCode(DictCode dictCode) {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.eq(Dict::getDictCode, dictCode.getCode())
               .eq(Dict::getEnabled, DictEnableEnum.VALID.getType());
        return this.baseMapper.selectList(wrapper);
    }

ne

ne("列名", 值) -> 列名 != 值

    public List listByDictCode(DictCode dictCode) {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.ne(Dict::getDictCode, dictCode.getCode())
               .ne(Dict::getEnabled, DictEnableEnum.VALID.getType());
        return this.baseMapper.selectList(wrapper);
    }

gt

gt("age", 20) -> age > 20

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.gt(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

ge

ge("age", 20) -> age >= 20

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.ge(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

lt

lt("age", 20) -> age < 20

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.lt(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

le

le("age", 21) -> age <= 21

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.le(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

between,notBetween

between("age", 18, 25) -> age BETWEEN 18 AND 25 ，年龄在18到25之间

notBetween就是不在18到25之间

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.between(User::getAge, 18,25);                
        return this.baseMapper.selectList(wrapper);
    }

like,notLike

like 匹配值 -> "%值%" 模糊查询

notLike 模糊查询不匹配"%值%"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
         wrapper.like(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

likeLeft

likeLeft 匹配值 -> "%值"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
         wrapper.likeLeft(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

likeRight

likeRight 匹配值 -> "值%"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.likeRight(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

isNull 空值查询

isNotNull 非空值查询

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.isNull(User::getName);
        //wrapper.isNotNull(User::getName);              
        return this.baseMapper.selectList(wrapper);
    }

in

in("name", "张三", "李四") -> name in ("张三", "李四") 姓名是张三或李四的用户

notIn

notIn("name", "张三", "李四") -> name not in ("张三", "李四") 姓名不是张三或李四的用户

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.in(User::getName, "张三","李四");
        //wrapper.in(User::getName, "张三","李四");           
        return this.baseMapper.selectList(wrapper);
    }

inSql、notInSql

public List userList() {
    LambdaQueryWrapper wrapper= new LambdaQueryWrapper<>();
    // SELECT id,name,email,age FROM user WHERE (age IN (select age from user where id = 1))
    wrapper.inSql(User::getAge, "select age from user where id = 1");
    return this.baseMapper.selectList(wrapper);
}

groupBy

分组

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    wrapper.groupBy(User::getName);
    return this.baseMapper.selectList(wrapper);
}

orderBy、orderByAsc、orderByDesc

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    // SELECT id,name,email,age FROM user ORDER BY name ASC,age DESC
    wrapper.orderBy(true, true, User::getName).orderBy(true, false, User::getAge);
    
    // SELECT id,name,email,age FROM user ORDER BY name ASC,age ASC
    wrapper.orderByAsc(User::getName, User::getAge);
    // SELECT id,name,email,age FROM user ORDER BY name DESC,age DESC
    wrapper.orderByDesc(User::getName, User::getAge);
    return this.baseMapper.selectList(wrapper);
}

or、and

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        // SELECT id,name,email,age FROM user WHERE (name = ? AND id = ?)
        wrapper.eq(User::getName, "张三").and().eq(User::getId,1);
        // SELECT id,name,email,age FROM user WHERE (name = ? OR id = ?)       
        wrapper.eq(User::getName, "张三").or().eq(User::getId,1);     
        return this.baseMapper.selectList(wrapper);
    }

这里说明一下or和and的问题

错误代码

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getId,1);
    wrapper.like(User::getName,"张")
           .or()
           .like(User::getEmail,"163")
           .or()
           .like(User::getAge,1);
}

根据上面的写法写出的sql语句如下：

WHERE id = '1' 
	AND name LIKE '%张%'
	OR email LIKE '%163%'
    OR age LIKE '%1%'

这样明显是不对的，根据mysql语句执行顺序or最后执行，这会导致一旦[name like '%张%']条件成立后面的or条件就会失效，所以第一个条件并没有起到and的作用。

解决方法

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getId,1);
    wrapper.and(wrapper->wrapper.like(User::getName,"张")
                                .or()
                                .like(User::getEmail,"163")
                                .or()
                                .like(User::getAge,1)
               );
}

这样得到的sql语句如下

WHERE id = '1' 
	AND (name LIKE '%张%'
	OR email LIKE '%163%'
    OR age LIKE '%1%')

这样就解决了，这个问题在我的公司中新人(包括我在内)貌似都遇到这个问题，在此说明一下

last

在末尾拼接sql语句

注：last()有sql注入的风险，请谨慎使用！

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    // SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?) limit 1
    wrapper.eq(User::getName, "张三").or().eq(User::getAge, 20).last("limit 1");
    return this.baseMapper.selectList(wrapper);
}

exists、notExists

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    // SELECT id,name,email,age FROM user WHERE (EXISTS (select name from user where age > ?))
    wrapper.exists("select name from user where age > 21");
    // SELECT id,name,email,age FROM user WHERE (NOT EXISTS (select name from user where age > ?))
    wrapper.notExists("select name from user where age > 21");
    return this.baseMapper.selectList(wrapper);
}

总结

略

附加MySQL语句执行顺序

1、from

2、where (or 最后执行)

3、group by

4、having

5、DISTINCT

6、order by

7、limit

上一篇：springBoot整合讯飞星火认知大模型

下一篇：javaweb、spring、springmvc和springboot有什么区别，都是做什么用的？

前言

Wrapper

查询构造器

查询条件

前期准备

查询条件

allEq

eq

ne

gt

ge

lt

le

between,notBetween

like,notLike

likeLeft

likeRight

isNull 空值查询

isNotNull 非空值查询

in

notIn

inSql、notInSql

groupBy

orderBy、orderByAsc、orderByDesc

or、and

解决方法

last

exists、notExists

总结

附加MySQL语句执行顺序