目录

前言

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

查询构造器

查询条件

前期准备

• 创建一个数据库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.0org.springframework.bootspring-boot-starter-parent2.5.3 com.cmymybatis_plus0.0.1-SNAPSHOTmybatis_plusDemo project for Spring Boot1.8org.springframework.bootspring-boot-startercom.baomidoumybatis-plus-boot-starter3.4.0mysqlmysql-connector-javaruntimeorg.springframework.bootspring-boot-starter-testtestorg.springframework.bootspring-boot-maven-plugin

• • mysql数据库相关配置

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1/mybatisplus?useUnicode=true&characterEncoding=utf-8username: rootpassword: 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;}@Overridepublic 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 DESCwrapper.orderBy(true, true, User::getName).orderBy(true, false, User::getAge);// SELECT id,name,email,age FROM user ORDER BY name ASC,age ASCwrapper.orderByAsc(User::getName, User::getAge);// SELECT id,name,email,age FROM user ORDER BY name DESC,age DESCwrapper.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 1wrapper.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