Mybatis-Plus高级查询LambdaQueryWrapper&QueryWrapper

傷城~ 2024-03-30 09:04 79阅读 0赞

**目录**

前言

Wrapper

查询构造器

查询条件

前期准备

查询条件

allEq

between,notBetween

like,notLike

likeLeft

likeRight

isNull 空值查询

isNotNull 非空值查询

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 ##

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

## 查询构造器 ##

### 查询条件 ###

### 前期准备 ###

*  创建一个数据库 **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** 坐标

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.5.3</version>
            <relativePath/> 
        </parent>
        <groupId>com.cmy</groupId>
        <artifactId>mybatis_plus</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>mybatis_plus</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.0</version>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

*   *  **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<User> {
    }

### 查询条件 ###

#### allEq ####

条件用 Map 进行封装

“name” -> “张三”

“age” -> 20

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

#### eq ####

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

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

#### ne ####

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

public List<Dict> listByDictCode(DictCode dictCode) {
            LambdaQueryWrapper<Dict> 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<User> userList() {
            LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
            wrapper.gt(User::getAge, 20);            
            return this.baseMapper.selectList(wrapper);
        }

#### ge ####

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

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

#### lt ####

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

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

#### le ####

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

public List<User> userList() {
            LambdaQueryWrapper<User> 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<User> userList() {
            LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
            wrapper.between(User::getAge, 18,25);                
            return this.baseMapper.selectList(wrapper);
        }

#### like,notLike ####

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

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

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

#### likeLeft ####

`likeLeft 匹配值 -> "%值"`

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

#### likeRight ####

`likeRight 匹配值 -> "值%"`

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

#### isNull 空值查询 ####

#### isNotNull 非空值查询 ####

public List<User> userList() {
            LambdaQueryWrapper<User> 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<User> userList() {
            LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
            wrapper.in(User::getName, "张三","李四");
            //wrapper.in(User::getName, "张三","李四");           
            return this.baseMapper.selectList(wrapper);
        }

#### inSql、notInSql ####

public List<User> userList() {
        LambdaQueryWrapper<User> 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<User> userList() {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.groupBy(User::getName);
        return this.baseMapper.selectList(wrapper);
    }

#### orderBy、orderByAsc、orderByDesc ####

public List<User> userList() {
        LambdaQueryWrapper<User> 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<User> userList() {
            LambdaQueryWrapper<User> 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<User> userList() {
    
        LambdaQueryWrapper<User> 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<User> userList() {
    
        LambdaQueryWrapper<User> 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<User> userList() {
        LambdaQueryWrapper<User> 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<User> userList() {
        LambdaQueryWrapper<User> 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