目录

前言

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语句执行顺序

---

前言

我刚刚毕业开始进入项目组的时候，从未使用过mybatis-Plus，只用过Mybatis，Mybatis还是老套的xml配置化，已经快转正了，所以做个总结，现在SpringBoot里面的JPa可以注解实现sql的增删改查，针对单表查询现在实际项目开发过程中根本不使用原生Hibernate或者Mybatis了，目前使用的是Mybatis-Plus，使用轻便友好，开发代码段少且完美的实现，在这里写一个文档针对我进入工作时是如何开发使用的！

优势：单表查询的话，可以直接的使用对象操作，其实实现起来极其方便而且简单！

如果多表联查的话，一般四张表以上关联或者是很复杂的sql，那就建议使用xml格式的配置化进行关联！

目前在重构一个项目的时候要针对原有的SQL进行重写，所以针对一个好用的Mybatis的插件使用。在这里做一些总结，然后通过我们组内人员使用，统一的改用LambdaQueryWrapper&QueryWrapper

简单对象查询方式用起来极为方便！涉及到单表查询的是该该对象查询继承；

推荐使用：LambdaQueryWrapper

LambdaQueryWrapper使用lambda表达式可以直接通过实体类get()属性，而QueryWrapper必须要与数据库的中表名一致，由于表名可能会很复杂，这时候相较而言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;public class User {        @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;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语句执行顺序

from
2、where (or 最后执行)
3、group by
4、having
5、DISTINCT
6、order by
7、limit

来源地址：https://blog.csdn.net/weixin_52317961/article/details/128317591