三，MyBatis-Plus 的各种查询的“超详细说明”，比如(等值查询，范围查询，模糊查询...)

@

目录

• 三，MyBatis-Plus 的各种查询的“超详细说明”，比如(等值查询，范围查询，模糊查询...)
• 1. 条件构造器介绍
• 2. 准备工作：
• 3. 等值查询
  • 3.1 eq (条件筛选属性 = ?)
  • 3.2 allEq(满足多个条件字段的值的筛选)
  • 3.3 ne (不等于)
• 4. 范围查询
  • 4.1 gt( > 大于的范围)
  • 4.2 ge(>= 大于等于的范围)
  • 4.3 lt(< 小于的范围)
  • 4.4 le(小于等于的范围)
  • 4.5 between(在该区间的范围)
  • 4.6 notBetween(不在该区间的范围)
• 5. 模糊查询
  • 5.1 like( %值%)
  • 5.2 notLike(不满足 %值%)
  • 5.3 likeLeft（%值）
  • 5.4 likeRight(值%)
• 6. 判空查询
  • 6.1 isNUll(判断是否为 Null )
  • 6.2 isNotNull
• 7. 包含查询
  • 7.1 in（包含该内容的字段）
  • 7.2 notIn(不包含该内容的字段)
  • 7.3 inSql(包含该内容的字段)
  • 7.4 notInSql(不包含该内容的字段)
• 8. 分组查询
  • 8.1 groupBy(分组的字段)
• 9. 聚合查询
  • 9.1 having(聚合的字段)
• 10. 排序查询
  • 10.1 orderByAsc(升序)
  • 10.2 orderByDesc(降序)
  • 10.3 orderBy(多字段排序定义)
• 11. func 查询
• 12. 逻辑查询
  • 12.1 and(与)
  • 12.2 or(或)
  • 12.3 nested(非)
• 13. 自定义条件查询
  • 13.1 apply(自定义查询条件)
• 14. last 查询(在sql语句的最末尾拼接“字符串”)
• 15. exists查询
  • 15.1 exists(存在查询)
  • 15.2 notExists(不存在查询)
• 16. 字段查询
  • 16.1 select(字段查询)
• 17. 最后：

1. 条件构造器介绍

在实际开发需求中条件查询是非常普遍的，接下来我们就来讲解如何使用 MyBatis Plus 完成条件查询。

首先，想要使用 MyBatis Plus 完成条件查询，基于面向对象的思想，万物皆对象 ，那么查询条件，也需要使用对象来完成封装。我们可以先看看，在 MyBatis Plus 中和条件有关的类有哪些，他们之间有什么关系，理清楚了这个，我们在传递条件对象的时候，就很清晰了。

<code>Wrapper 抽象类，条件类的顶层，提供了一些获取和判断相关的方法。

<code>AbstractWrapper 抽象类，Wrapper 的子类，提供了所有的条件相关方法。

<code>AbstractLambdaWrapper 抽象类，AbstractWrapper 的子类，确定字段参数为方法引用类型。

<code>QueryWrapper 类，AbstractWrapper 的子类，如果我们需要传递 String 类型的字段信息，创建该对象。

<code>LambdaQueryWrapper 类，AbstractLambdaWrapper 的子类，如果我们需要传递方法引用方式的字段信息，创建该对象。

该图为以上各类的关系，我们在编写代码的时候，只需要关注 <code>QueryWrapper 和LambdaQueryWrapper

2. 准备工作：

引入相关的<code>jar 依赖。在 pom.xml 文件当中；

<code><?xml version="1.0" encoding="UTF-8"?>code>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"code>

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">code>

<modelVersion>4.0.0</modelVersion>

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.5.3</version>

<relativePath/> <!-- lookup parent from repository -->

</parent>

<groupId>com.rainbowsea</groupId>

<artifactId>mp03</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>mp03</name>

<description>mp03</description>

<url/>

<licenses>

<license/>

</licenses>

<developers>

<developer/>

</developers>

<scm>

<connection/>

<developerConnection/>

<tag/>

<url/>

</scm>

<properties>

<java.version>8</java.version>

</properties>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

<scope>test</scope>

</dependency>

<!-- spring boot web 依赖-->

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<!-- mysql 驱动依赖-->

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<!-- lombok 的依赖-->

<dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

</dependency>

<!-- druid-->

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

<version>1.2.8</version>

</dependency>

<!-- mybatis-plus 的依赖-->

<dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-boot-starter</artifactId>

<version>3.4.3</version>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

</build>

<repositories>

<repository>

<id>spring-snapshots</id>

<name>Spring Snapshots</name>

<url>https://repo.spring.io/snapshot</url>

<releases>

<enabled>false</enabled>

</releases>

</repository>

</repositories>

<pluginRepositories>

<pluginRepository>

<id>spring-snapshots</id>

<name>Spring Snapshots</name>

<url>https://repo.spring.io/snapshot</url>

<releases>

<enabled>false</enabled>

</releases>

</pluginRepository>

</pluginRepositories>

</project>

编写项目的场景启动器：

<code>package com.rainbowsea;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class Mp03Application {

public static void main(String[] args) {

SpringApplication.run(Mp03Application.class, args);

}

}

创建的数据库表和结构。如下：

编写对应 数据表的 Java Bean 类对象。

<code>package com.rainbowsea.bean;

import com.baomidou.mybatisplus.annotation.TableField;

import com.baomidou.mybatisplus.annotation.TableName;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@NoArgsConstructor

@AllArgsConstructor

public class User {

private Long id;

//@TableField(value = "username")

private String name;

//@TableField(select = false) // 查询时，不对age 字段进行查询

private Integer age;

private String email;

@TableField(value = "`desc`") // 注意：有两层，但最里面的哪个不是引号

private String desc;

@TableField(exist = false) // 表示// ，不让该 online 属性，作为 SQL语句当中的查询字段

private Integer online;

}

在 resources 类路径下，创建一个名为 application.yaml 的yaml文件，编写连接数据库的信息内容。

<code>spring:

datasource:

driver-class-name: com.mysql.cj.jdbc.Driver

url: jdbc:mysql://localhost:3306/mybatisplus?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false

username: root

password: MySQL123

main:

banner-mode: off #关闭 spring boot 在命令行当中的图标显示

mybatis-plus:

global-config:

banner: false # 关闭 mybatis-plus 在命令行当中的图标显示

db-config:

table-prefix: rainbowsea_ # 还可以通过统一添加前缀的方式：

configuration:

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 开启 Log 日志信息打印

map-underscore-to-camel-case: true # 开启驼峰，下划线映射规则

编写配置类，通过配置类的方式，将数据库连接池换成为 Druid 数据库连接池。

<code>package com.rainbowsea.config;

import com.alibaba.druid.pool.DruidDataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration // 标注配置类

public class DruidDataSourceConfig {

@Bean

@ConfigurationProperties(value = "spring.datasource")

public DataSource getDataSource() {

DruidDataSource druidDataSource = new DruidDataSource();

return druidDataSource;

}

}

运行测试：看看是否切换为了 Druid 数据库连接池

<code>package com.rainbowsea;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;

@SpringBootTest

class Mp03ApplicationTests {

@Resource

//@Autowired

private JdbcTemplate jdbcTemplate;

@Test

void getDataSourceTest() {

System.out.println(jdbcTemplate.getDataSource().getClass());

}

}

编写：该User表的 mapper 方法。

<code>package com.rainbowsea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.rainbowsea.bean.User;

import org.apache.ibatis.annotations.Mapper;

@Mapper // 包扫描路径

public interface UserMapper extends BaseMapper<User> {

}

3. 等值查询

3.1 eq (条件筛选属性 = ?)

使用QueryWrapper对象，构建查询条件

等值查询使用：userQueryWrapper.eq()

default Children eq(R column, Object val) {

return eq(true, column, val);

}

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

@Test

void eq() {

// 1.创建条件查询对象

QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

// 2. 设置查询条件，指定查询的字段和匹配的值

QueryWrapper<User> eq = userQueryWrapper.eq("name", "Jone");

// 3. 进行条件查询

User user = userMapper.selectOne(eq);

System.out.println(user);

}

}

我们思考如果每次都是自己进行字段名称的编写，有可能会出现名称写错的情况，怎么避免这种情况呢，我们可以使用LambdaQueryWrapper对象，在构建字段时，使用方法引用的方式来选择字段，这样做可以避免字段拼写错误出现问题。

代码如下：

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 使用: LambdaQueryWrapper 进行引用查询，防止报错

@Test

void eq2() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

LambdaQueryWrapper<User> queryWrapper = lambdaQueryWrapper.eq(User::getName, "Jone");

User user = userMapper.selectOne(queryWrapper);

System.out.println(user);

}

}

还要考虑一种情况，我们构建的条件是从哪里来的？应该是从客户端通过请求发送过来的，

由服务端接收的。在网站中一般都会有多个条件入口，用户可以选择一个或多个条件进行查询，那这个时候在请求时，我们不能确定所有的条件都是有值的，部分条件可能用户没有传值，那该条件就为null。

​比如在电商网站中，可以选择多个查询条件。

那为null的条件，我们是不需要进行查询条件拼接的，否则就会出现如下情况，将为null的条件进行拼接，筛选后无法查询出结果。

当然我们要解决这个问题，可以先判断是否为空，根据判断结果选择是否拼接该字段，这个功能其实不需要我们写，由MybatisPlus的方法已经提供好了。

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 字段名为 不null的

@Test

void isNull2() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

String name = null;

//String name = "Jone";

// public Children eq(boolean condition, R column, Object val) {

lambdaQueryWrapper.eq(name != null, User::getName, name);

//User user = userMapper.selectOne(lambdaQueryWrapper);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

3.2 allEq(满足多个条件字段的值的筛选)

先演示一下如何通过多个<code>eq,构建多条件查询。

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// and 满足多个条件

@Test

void allEql() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.eq(User::getName, "Tom");

lambdaQueryWrapper.eq(User::getAge, 18);

User user = userMapper.selectOne(lambdaQueryWrapper);

System.out.println(user);

}

}

如果此时有多个条件需要同时判断，我们可以将这多个条件放入到Map集合中，更加的方便

allEq(Map<R, V> params, boolean null2IsNull)

参数params:表示传递的Map集合

参数null2IsNull:表示对于为null的条件是否判断isNull

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 属性值为 null 的查询，属性值不为 Null的不查询该字段

@Test

void allEq2() {

HashMap<String, Object> hashMap = new HashMap<>();

hashMap.put("name", "Tom");

hashMap.put("age",18);

//hashMap.put("age", null); // 为null 的属性值，则会不查询

QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

//userQueryWrapper.allEq(hashMap,false); // // 为null 的属性值，则会不查询

userQueryWrapper.allEq(hashMap, true); // 为null,(name = ? AND age IS NULL) 查询

User user = userMapper.selectOne(userQueryWrapper);

System.out.println(user);

}

}

3.3 ne (不等于)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 不等于数值的

@Test

void ne() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.ne(User::getName, "Tom");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4. 范围查询

4.1 gt( > 大于的范围)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// > 查询

@Test

void gt() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

Integer age = 18;

lambdaQueryWrapper.gt(User::getAge, age);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4.2 ge(>= 大于等于的范围)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// >=查询

@Test

void ge() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

Integer age = 18;

lambdaQueryWrapper.ge(User::getAge, age);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4.3 lt(< 小于的范围)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// <

@Test

void lt() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

Integer age = 20;

lambdaQueryWrapper.lt(User::getAge, age);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4.4 le(小于等于的范围)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// <=

@Test

void le() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

Integer age = 20;

LambdaQueryWrapper<User> lambdaQueryWrapper1 = lambdaQueryWrapper.le(User::getAge, age);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4.5 between(在该区间的范围)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 范围之间

@Test

void between() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.between(User::getAge, 10, 20);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

4.6 notBetween(不在该区间的范围)

<code>import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 不在范围的： (age NOT BETWEEN ? AND ?)

@Test

void notBetween() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.notBetween(User::getAge,10,18);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

5. 模糊查询

5.1 like( %值%)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 模糊查询: %J%(String)

@Test

void like() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.like(User::getName,"J");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

5.2 notLike(不满足 %值%)

5.3 likeLeft（%值）

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;

import java.util.List;

@SpringBootTest // 注意这个测试必须要有一个场景类才行，不然是无法运行的。测试的类不同的话，还需要指明

// 包

public class QueryTest {

@Autowired

private UserMapper userMapper;

// 模糊查询: %e(String) 左边

@Test

void likeft() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.likeLeft(User::getName,"e");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

5.4 likeRight(值%)

6. 判空查询

6.1 isNUll(判断是否为 Null )

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// 判断是否为 null WHERE (name IS NULL)

@Test

void isNUll3() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.isNull(User::getName);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

6.2 isNotNull

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// WHERE (name IS NULL)

@Test

void isNotNull() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.isNotNull(User::getName);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

7. 包含查询

7.1 in（包含该内容的字段）

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// 字段 = 值 or 字段 = 值 ->in

// r WHERE (age IN (?,?,?))

@Test

void in() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

ArrayList<Object> arrayList = new ArrayList<>();

Collections.addAll(arrayList, 18, 20, 22);

lambdaQueryWrapper.in(User::getAge, arrayList);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

7.2 notIn(不包含该内容的字段)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// 字段！=值 and 字段！=值 ->not in

// WHERE (age NOT IN (?,?,?))

@Test

void notIn() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

ArrayList<Integer> arrayList = new ArrayList<>();

Collections.addAll(arrayList, 18, 20, 22);

lambdaQueryWrapper.notIn(User::getAge, arrayList);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

7.3 inSql(包含该内容的字段)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// er WHERE (age IN (18,20,22))

@Test

void inSql() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.inSql(User::getAge, "18,20,22");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

7.4 notInSql(不包含该内容的字段)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// age NOT IN (18,20,22))

@Test

void notInsql() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.notInSql(User::getAge, "18,20,22");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

@SpringBootTest

public class QueryTest02 {

@Resource

private UserMapper userMapper;

// RE (age NOT IN (select age from rainbowsea_user where age > 20))

@Test

void notInSql2() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.notInSql(User::getAge, "select age from rainbowsea_user where age > 20");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

8. 分组查询

8.1 groupBy(分组的字段)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// select age,count(*) as field_count from rainbowsea_user group by age;

// 分组查询

@Test

void groupBy() {

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

// 分组字段

queryWrapper.groupBy("age");

// 查询字段

queryWrapper.select("age,count(*) as field_count");

List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);

System.out.println(maps);

}

}

9. 聚合查询

9.1 having(聚合的字段)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// select age,count(*) as field_count from rainbowsea_user group by age HAVING field_count >=2;

// 聚合查询

@Test

void having() {

QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

// 查询字段

userQueryWrapper.select("age,count(*) as field_count");

// 聚合条件筛选

userQueryWrapper.having("field_count = 1");

List<Map<String, Object>> maps = userMapper.selectMaps(userQueryWrapper);

System.out.println(maps);

}

}

10. 排序查询

10.1 orderByAsc(升序)

<code>import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// 降序

@Test

void orderByAsc() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.orderByAsc(User::getAge, User::getId);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

10.2 orderByDesc(降序)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// 升序

@Test

void orderByDesc() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.orderByDesc(User::getAge, User::getId);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

10.3 orderBy(多字段排序定义)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

@Test

void orderBy() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

// 设置排序字段和排序的方式

// 参数1：如果排序字段的值为null的时候，是否还要作为排序字段参与排序

// 参数2：是否升序排序，

// 参数3: 排序字段

//lambdaQueryWrapper.orderBy(true, true, User::getAge);

lambdaQueryWrapper.orderBy(false, true, User::getAge);

lambdaQueryWrapper.orderBy(true, false, User::getId);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

11. func 查询

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// // 可能会根据不同的请情况选择拼接不同的查询条件

@Test

void func() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

// 可能会根据不同的请情况选择拼接不同的查询条件

/* lambdaQueryWrapper.func(new Consumer<LambdaQueryWrapper<User>>() {

@Override

public void accept(LambdaQueryWrapper<User> userLambdaQueryWrapper) {

//if (true) {

if (false) {

userLambdaQueryWrapper.eq(User::getId,1);

} else {

userLambdaQueryWrapper.ne(User::getId,1);

}

}

});*/

// 使用lambad表达式

lambdaQueryWrapper.func(userLambdaQueryWrapper -> {

if (false) {

userLambdaQueryWrapper.eq(User::getId, 1);

} else {

userLambdaQueryWrapper.ne(User::getId, 1);

}

});

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

12. 逻辑查询

12.1 and(与)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// WHERE (age > ? AND age < ?)

@Test

void and() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.gt(User::getAge, 22).lt(User::getAge, 30);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// WHERE (name = ? AND (age > ? OR age < ?))

@Test

void add2() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.eq(User::getName, "wang").and(i -> i.gt(User::getAge, 26).or().lt(User::getAge, 22));

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

12.2 or(或)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// WHERE (age < ? AND age > ?)

@Test

void or() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.lt(User::getAge, 20).gt(User::getAge, 23);// age < 20 || age >=23

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

12.3 nested(非)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// WHERE ((name = ? AND age <> ?))

@Test

void nested() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.nested(i->i.eq(User::getName,"Tom").ne(User::getAge,22));

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

13. 自定义条件查询

13.1 apply(自定义查询条件)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// 自定义条件查询

@Test

void apply() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.apply("id = 1");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

14. last 查询(在sql语句的最末尾拼接“字符串”)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// 最后添加字符拼接

// SELECT id,name,age,email,`desc` FROM rainbowsea_user limit 0,2

@Test

void last() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.last("limit 0,2");

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

15. exists查询

15.1 exists(存在查询)

<code>

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// SELECT * from rainbowsea_user WHERE EXISTS (select id FROM rainbowsea_user WHERE age = 18);

// SELECT * from rainbowsea_user WHERE EXISTS (select id FROM rainbowsea_user WHERE age = 10);

@Test

void exists() {

// 1. 创建QueryWrapper对象

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

// 2. 构建查询条件

//lambdaQueryWrapper.exists("select id FROM rainbowsea_user WHERE age = 18");

lambdaQueryWrapper.exists("select id FROM rainbowsea_user WHERE age = 10");

// 3.查询

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

15.2 notExists(不存在查询)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

// SELECT * from rainbowsea_user WHERE not EXISTS (select id FROM rainbowsea_user WHERE age = 10);

@Test

void notExists() {

// 1. 创建QueryWrapper对象

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

// 2. 构建查询条件

//lambdaQueryWrapper.exists("select id FROM rainbowsea_user WHERE age = 18");

lambdaQueryWrapper.notExists("select id FROM rainbowsea_user WHERE age = 10");

// 3.查询

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

16. 字段查询

16.1 select(字段查询)

<code>

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.rainbowsea.bean.User;

import com.rainbowsea.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.awt.*;

import java.util.List;

import java.util.Map;

import java.util.function.Consumer;

@SpringBootTest

public class QueryTest03 {

@Autowired

private UserMapper userMapper;

@Test

void select() {

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();

lambdaQueryWrapper.select(User::getId,User::getName);

List<User> users = userMapper.selectList(lambdaQueryWrapper);

System.out.println(users);

}

}

17. 最后：

“在这个最后的篇章中，我要表达我对每一位读者的感激之情。你们的关注和回复是我创作的动力源泉，我从你们身上吸取了无尽的灵感与勇气。我会将你们的鼓励留在心底，继续在其他的领域奋斗。感谢你们，我们总会在某个时刻再次相遇。”