网站建设shopify,广州免费建站找哪家,惠州seo按天计费,定远县建设局网站三#xff0c;MyBatis-Plus 的各种查询的“超详细说明”#xff0c;比如(等值查询#xff0c;范围查询#xff0c;模糊查询…) 文章目录 三#xff0c;MyBatis-Plus 的各种查询的“超详细说明”#xff0c;比如(等值查询#xff0c;范围查询#xff0c;模糊查询...)1. …三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 中和条件有关的类有哪些他们之间有什么关系理清楚了这个我们在传递条件对象的时候就很清晰了。
Wrapper 抽象类条件类的顶层提供了一些获取和判断相关的方法。 AbstractWrapper 抽象类Wrapper 的子类提供了所有的条件相关方法。 AbstractLambdaWrapper 抽象类AbstractWrapper 的子类确定字段参数为方法引用类型。 QueryWrapper 类AbstractWrapper 的子类如果我们需要传递 String 类型的字段信息创建该对象。 LambdaQueryWrapper 类AbstractLambdaWrapper 的子类如果我们需要传递方法引用方式的字段信息创建该对象。 该图为以上各类的关系我们在编写代码的时候只需要关注 QueryWrapper 和LambdaQueryWrapper 2. 准备工作
引入相关的jar 依赖。在 pom.xml 文件当中 ?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.5.3/versionrelativePath/ !-- lookup parent from repository --/parentgroupIdcom.rainbowsea/groupIdartifactIdmp03/artifactIdversion0.0.1-SNAPSHOT/versionnamemp03/namedescriptionmp03/descriptionurl/licenseslicense//licensesdevelopersdeveloper//developersscmconnection/developerConnection/tag/url//scmpropertiesjava.version8/java.version/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependency!-- spring boot web 依赖--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependency!-- mysql 驱动依赖--dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactId/dependency!-- lombok 的依赖--dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/dependency!-- druid--dependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.2.8/version/dependency!-- mybatis-plus 的依赖--dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.4.3/version/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactId/plugin/plugins/buildrepositoriesrepositoryidspring-snapshots/idnameSpring Snapshots/nameurlhttps://repo.spring.io/snapshot/urlreleasesenabledfalse/enabled/releases/repository/repositoriespluginRepositoriespluginRepositoryidspring-snapshots/idnameSpring Snapshots/nameurlhttps://repo.spring.io/snapshot/urlreleasesenabledfalse/enabled/releases/pluginRepository/pluginRepositories/project
编写项目的场景启动器 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 类对象。 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文件编写连接数据库的信息内容。 spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/mybatisplus?serverTimezoneUTCcharacterEncodingutf8useUnicodetrueuseSSLfalseusername: rootpassword: MySQL123main: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 数据库连接池。 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 {BeanConfigurationProperties(value spring.datasource)public DataSource getDataSource() {DruidDataSource druidDataSource new DruidDataSource();return druidDataSource;}
}
运行测试看看是否切换为了 Druid 数据库连接池 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//Autowiredprivate JdbcTemplate jdbcTemplate;Testvoid getDataSourceTest() {System.out.println(jdbcTemplate.getDataSource().getClass());}} 编写该User表的 mapper 方法。 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 BaseMapperUser {}
3. 等值查询
3.1 eq (条件筛选属性 ?)
使用QueryWrapper对象构建查询条件 等值查询使用userQueryWrapper.eq()
default Children eq(R column, Object val) {return eq(true, column, val);
}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 {Autowiredprivate UserMapper userMapper;Testvoid eq() {// 1.创建条件查询对象QueryWrapperUser userQueryWrapper new QueryWrapper();// 2. 设置查询条件指定查询的字段和匹配的值QueryWrapperUser eq userQueryWrapper.eq(name, Jone);// 3. 进行条件查询User user userMapper.selectOne(eq);System.out.println(user);}
}我们思考如果每次都是自己进行字段名称的编写有可能会出现名称写错的情况怎么避免这种情况呢我们可以使用LambdaQueryWrapper对象在构建字段时使用方法引用的方式来选择字段这样做可以避免字段拼写错误出现问题。
代码如下 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 {Autowiredprivate UserMapper userMapper;// 使用: LambdaQueryWrapper 进行引用查询防止报错Testvoid eq2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();LambdaQueryWrapperUser queryWrapper lambdaQueryWrapper.eq(User::getName, Jone);User user userMapper.selectOne(queryWrapper);System.out.println(user);}
}还要考虑一种情况我们构建的条件是从哪里来的应该是从客户端通过请求发送过来的
由服务端接收的。在网站中一般都会有多个条件入口用户可以选择一个或多个条件进行查询那这个时候在请求时我们不能确定所有的条件都是有值的部分条件可能用户没有传值那该条件就为null。
比如在电商网站中可以选择多个查询条件。 那为null的条件我们是不需要进行查询条件拼接的否则就会出现如下情况将为null的条件进行拼接筛选后无法查询出结果。 当然我们要解决这个问题可以先判断是否为空根据判断结果选择是否拼接该字段这个功能其实不需要我们写由MybatisPlus的方法已经提供好了。 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 {Autowiredprivate UserMapper userMapper;// 字段名为 不null的Testvoid isNull2() {LambdaQueryWrapperUser 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);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}3.2 allEq(满足多个条件字段的值的筛选)
先演示一下如何通过多个eq,构建多条件查询。 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 {Autowiredprivate UserMapper userMapper;// and 满足多个条件Testvoid allEql() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.eq(User::getName, Tom);lambdaQueryWrapper.eq(User::getAge, 18);User user userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);}
}如果此时有多个条件需要同时判断我们可以将这多个条件放入到Map集合中更加的方便 allEq(MapR, V params, boolean null2IsNull)
参数params:表示传递的Map集合
参数null2IsNull:表示对于为null的条件是否判断isNullimport 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 {Autowiredprivate UserMapper userMapper;// 属性值为 null 的查询属性值不为 Null的不查询该字段Testvoid allEq2() {HashMapString, Object hashMap new HashMap();hashMap.put(name, Tom);hashMap.put(age,18);//hashMap.put(age, null); // 为null 的属性值则会不查询QueryWrapperUser 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 (不等于) 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 {Autowiredprivate UserMapper userMapper;// 不等于数值的Testvoid ne() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.ne(User::getName, Tom);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4. 范围查询
4.1 gt( 大于的范围) 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 {Autowiredprivate UserMapper userMapper;// 查询Testvoid gt() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();Integer age 18;lambdaQueryWrapper.gt(User::getAge, age);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4.2 ge( 大于等于的范围) 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 {Autowiredprivate UserMapper userMapper;// 查询Testvoid ge() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();Integer age 18;lambdaQueryWrapper.ge(User::getAge, age);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4.3 lt( 小于的范围) 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 {Autowiredprivate UserMapper userMapper;// Testvoid lt() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();Integer age 20;lambdaQueryWrapper.lt(User::getAge, age);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4.4 le(小于等于的范围) 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 {Autowiredprivate UserMapper userMapper;// Testvoid le() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();Integer age 20;LambdaQueryWrapperUser lambdaQueryWrapper1 lambdaQueryWrapper.le(User::getAge, age);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4.5 between(在该区间的范围) 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 {Autowiredprivate UserMapper userMapper;// 范围之间Testvoid between() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.between(User::getAge, 10, 20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}4.6 notBetween(不在该区间的范围) 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 {Autowiredprivate UserMapper userMapper;// 不在范围的 (age NOT BETWEEN ? AND ?)Testvoid notBetween() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notBetween(User::getAge,10,18);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}5. 模糊查询
5.1 like( %值%) 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 {Autowiredprivate UserMapper userMapper;// 模糊查询: %J%(String)Testvoid like() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.like(User::getName,J);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}5.2 notLike(不满足 %值%) 5.3 likeLeft%值 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 {Autowiredprivate UserMapper userMapper;// 模糊查询: %e(String) 左边Testvoid likeft() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.likeLeft(User::getName,e);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}5.4 likeRight(值%) 6. 判空查询
6.1 isNUll(判断是否为 Null ) 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 {Resourceprivate UserMapper userMapper;// 判断是否为 null WHERE (name IS NULL)Testvoid isNUll3() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.isNull(User::getName);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}6.2 isNotNull 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 {Resourceprivate UserMapper userMapper;// WHERE (name IS NULL)Testvoid isNotNull() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.isNotNull(User::getName);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}7. 包含查询
7.1 in包含该内容的字段 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 {Resourceprivate UserMapper userMapper;// 字段 值 or 字段 值 -in// r WHERE (age IN (?,?,?))Testvoid in() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();ArrayListObject arrayList new ArrayList();Collections.addAll(arrayList, 18, 20, 22);lambdaQueryWrapper.in(User::getAge, arrayList);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}7.2 notIn(不包含该内容的字段) 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 {Resourceprivate UserMapper userMapper;// 字段值 and 字段值 -not in// WHERE (age NOT IN (?,?,?))Testvoid notIn() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();ArrayListInteger arrayList new ArrayList();Collections.addAll(arrayList, 18, 20, 22);lambdaQueryWrapper.notIn(User::getAge, arrayList);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}7.3 inSql(包含该内容的字段) 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 {Resourceprivate UserMapper userMapper;// er WHERE (age IN (18,20,22))Testvoid inSql() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.inSql(User::getAge, 18,20,22);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}7.4 notInSql(不包含该内容的字段) 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 {Resourceprivate UserMapper userMapper;// age NOT IN (18,20,22))Testvoid notInsql() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notInSql(User::getAge, 18,20,22);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}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 {Resourceprivate UserMapper userMapper;// RE (age NOT IN (select age from rainbowsea_user where age 20))Testvoid notInSql2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notInSql(User::getAge, select age from rainbowsea_user where age 20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}8. 分组查询
8.1 groupBy(分组的字段) 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 {Autowiredprivate UserMapper userMapper;// select age,count(*) as field_count from rainbowsea_user group by age;// 分组查询Testvoid groupBy() {QueryWrapperUser queryWrapper new QueryWrapper();// 分组字段queryWrapper.groupBy(age);// 查询字段queryWrapper.select(age,count(*) as field_count);ListMapString, Object maps userMapper.selectMaps(queryWrapper);System.out.println(maps);}
}9. 聚合查询
9.1 having(聚合的字段) 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 {Autowiredprivate UserMapper userMapper;// select age,count(*) as field_count from rainbowsea_user group by age HAVING field_count 2;// 聚合查询Testvoid having() {QueryWrapperUser userQueryWrapper new QueryWrapper();// 查询字段userQueryWrapper.select(age,count(*) as field_count);// 聚合条件筛选userQueryWrapper.having(field_count 1);ListMapString, Object maps userMapper.selectMaps(userQueryWrapper);System.out.println(maps);}
}10. 排序查询
10.1 orderByAsc(升序) 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 {Autowiredprivate UserMapper userMapper;// 降序Testvoid orderByAsc() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.orderByAsc(User::getAge, User::getId);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}10.2 orderByDesc(降序) 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 {Autowiredprivate UserMapper userMapper;// 升序Testvoid orderByDesc() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.orderByDesc(User::getAge, User::getId);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}10.3 orderBy(多字段排序定义) 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 {Autowiredprivate UserMapper userMapper;Testvoid orderBy() {LambdaQueryWrapperUser 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);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}11. func 查询 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 {Autowiredprivate UserMapper userMapper;// // 可能会根据不同的请情况选择拼接不同的查询条件Testvoid func() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();// 可能会根据不同的请情况选择拼接不同的查询条件/* lambdaQueryWrapper.func(new ConsumerLambdaQueryWrapperUser() {Overridepublic void accept(LambdaQueryWrapperUser 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);}});ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}12. 逻辑查询
12.1 and(与) 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 {Autowiredprivate UserMapper userMapper;// WHERE (age ? AND age ?)Testvoid and() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.gt(User::getAge, 22).lt(User::getAge, 30);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}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 {Autowiredprivate UserMapper userMapper;// WHERE (name ? AND (age ? OR age ?))Testvoid add2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.eq(User::getName, wang).and(i - i.gt(User::getAge, 26).or().lt(User::getAge, 22));ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}12.2 or(或) 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 {Autowiredprivate UserMapper userMapper;// WHERE (age ? AND age ?)Testvoid or() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.lt(User::getAge, 20).gt(User::getAge, 23);// age 20 || age 23ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}12.3 nested(非) 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 {Autowiredprivate UserMapper userMapper;// WHERE ((name ? AND age ?))Testvoid nested() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.nested(i-i.eq(User::getName,Tom).ne(User::getAge,22));ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}13. 自定义条件查询
13.1 apply(自定义查询条件) 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 {Autowiredprivate UserMapper userMapper;// 自定义条件查询Testvoid apply() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.apply(id 1);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}14. last 查询(在sql语句的最末尾拼接“字符串”) 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 {Autowiredprivate UserMapper userMapper;// 最后添加字符拼接// SELECT id,name,age,email,desc FROM rainbowsea_user limit 0,2Testvoid last() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.last(limit 0,2);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}}15. exists查询
15.1 exists(存在查询) SpringBootTest
public class QueryTest03 {Autowiredprivate 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);Testvoid exists() {// 1. 创建QueryWrapper对象LambdaQueryWrapperUser 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.查询ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}15.2 notExists(不存在查询) 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 {Autowiredprivate UserMapper userMapper;// SELECT * from rainbowsea_user WHERE not EXISTS (select id FROM rainbowsea_user WHERE age 10);Testvoid notExists() {// 1. 创建QueryWrapper对象LambdaQueryWrapperUser 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.查询ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
}16. 字段查询
16.1 select(字段查询) 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 {Autowiredprivate UserMapper userMapper;Testvoid select() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.select(User::getId,User::getName);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}}17. 最后 “在这个最后的篇章中我要表达我对每一位读者的感激之情。你们的关注和回复是我创作的动力源泉我从你们身上吸取了无尽的灵感与勇气。我会将你们的鼓励留在心底继续在其他的领域奋斗。感谢你们我们总会在某个时刻再次相遇。”
