MyBatisPlus的测试案例（更新中）
2025-4-30
对21号的代码进行模糊查询+分页查询案例的扩充：
我以一个接口为例：
注意点：用mybatisplus以单表的形式实现多表的字段的查询和筛选
1.这里的（分页+模糊）查询，我并没有用xml文件写，用xml写sql的话会简单很多，因为要连三张表的话会很简单，模糊查询的sql语句，筛选起来也很简单
2.这里我也没有使用项目提供的分页插件，所以很多数据都暴露在外面进行封装了
3.这里用的mybatisplus写的，具体也涉及到了链式查询，.select（） .in（）等等
4.在下列的模糊查询中：我觉得也用到了许多其他的东西，
比如：查询结果的判空，不做判断，那返回的实体类，你在调用它的属性就会报空指针错误
比如：我们做模糊查询+分页查询的话，查询的内容返回给前端的话，肯定不是一个实体类，因为还有其他表的字段名，所以我们需要设计一个返回的VO实体类。所以，我们就需要进行集合之间的数据赋值和传递
//用VO的形式返回
List<DeviceVO> deviceVOList = new ArrayList<>();
for (Device device : deviceList) {
DeviceVO deviceVO = new DeviceVO();
BeanUtils.copyProperties(device, deviceVO);
deviceVO.setVenueName(venueList.stream().filter(v -> v.getId()
.equals(device.getVenueId())).findFirst().get().getName());
//这里有问题 -- 已修改
deviceVO.setDistributionName(distributeList.stream().filter(v -> v.getId()
.equals(device.getDistributeUsersId())).findFirst().get().getName());
deviceVOList.add(deviceVO);
}
实现的逻辑：用的朋友的方法：小表驱动大表 先模糊查出小表数据 根据标识查出大表数据
附：具体的代码
/**
* 分页查询1.0
* @param queryForm
* @return
*/
public ResponseDTO<PageResult<DeviceVO>> queryByPage(DeviceQueryForm queryForm) {
//此处测试
//先进行场地表进行模糊查询，查询结果封装到list集合里面去
VenueEntity ve = new VenueEntity();
ve.setName(queryForm.getVenueName());
LambdaQueryWrapper<VenueEntity> lqw = new LambdaQueryWrapper<>();
lqw.select(VenueEntity::getName, VenueEntity::getId);
lqw.like(null!=queryForm.getName(),VenueEntity::getName,ve.getName());
List<VenueEntity> venueEntities = venueMapper.selectList(lqw);
//这里对场地表进行判空操作，如果查询结果为空，直接结束查询
if(venueEntities==null || venueEntities.size()==0) {
PageResult<DeviceVO> pageResult = new PageResult<>();
//封装数据
pageResult.setTotal(0L);
pageResult.setPages(0L);
pageResult.setPageNum(0L);
pageResult.setPageSize(0L);
pageResult.setList(null);
return ResponseDTO.ok(pageResult);
}
List<Long> ids = new ArrayList<>();
for (VenueEntity venueEntity : venueEntities) {
ids.add(venueEntity.getId());
}
//下面是正常运行的
IPage page = new Page(queryForm.getPageNum(), queryForm.getPageSize());
LambdaQueryWrapper<Device> queryWrapper = new LambdaQueryWrapper<>();
// 错误点
// 我在这里的sql语句，在代码编写的时候，queryWrapper.in(Device::getVenueId, ids); 写成了 queryWrapper.in(Device::getId, ids);
// Execute SQL：SELECT id,name,type,sn_number,status,distribute_users_id,venue_id,hardware_version_number,create_time FROM t_device
// WHERE (venue_id IN (1) AND name LIKE '%%' AND sn_number LIKE '%%') LIMIT 10
queryWrapper.in(Device::getVenueId, ids);
//模糊查询
queryWrapper.like(null != queryForm.getName(), Device::getName, queryForm.getName());//根据设备名称查
queryWrapper.like(null != queryForm.getSnNumber(), Device::getSnNumber, queryForm.getSnNumber());//根据SN号码查
//版本号码是存在数据字典里面的，我第一次操作，所以我要查询 t_dict_data数据字典信息这个表 对应的信息
new DictDataEntity().setDataValue(queryForm.getHardwareVersionNumber());
DictDataEntity dictDataEntity = dictDataMapper.selectOne(
new LambdaQueryWrapper<DictDataEntity>()
.select(DictDataEntity::getDataValue, DictDataEntity::getDataLabel)
.eq(DictDataEntity::getDataValue, queryForm.getHardwareVersionNumber())
);
//错误点
// 这个报了一个空指针异常，前端没有传数据，DictDataEntity这个实体类就是空的，所以dictDataEntity.getDataLabel()下面这句执行就会报错，程序就中止了
//
if (null != dictDataEntity) {
queryWrapper.like(null != queryForm.getHardwareVersionNumber(), Device::getHardwareVersionNumber, dictDataEntity.getDataLabel());//根据版本号码查
}
//queryWrapper.like(Device::getNameV)//根据场地名称查
//如果查询结果为空，则直接赋0返回
if (deviceMapper.selectPage(page, queryWrapper).getRecords().size() == 0) {
IPage iPage = deviceMapper.selectPage(page, null);
PageResult<DeviceVO> pageResult = new PageResult<>();
//封装数据
pageResult.setTotal(iPage.getTotal());
pageResult.setPages(iPage.getPages());
pageResult.setPageNum(iPage.getCurrent());
pageResult.setPageSize(iPage.getSize());
pageResult.setList(null);
return ResponseDTO.ok(pageResult);
} else {
IPage iPage = deviceMapper.selectPage(page, queryWrapper);
List<Device> deviceList = iPage.getRecords();
PageResult<DeviceVO> pageResult = new PageResult<>();
//封装数据
pageResult.setTotal(iPage.getTotal());
pageResult.setPages(iPage.getPages());
pageResult.setPageNum(iPage.getCurrent());
pageResult.setPageSize(iPage.getSize());
//打印数据库查到的数据
System.out.println(deviceList);
//根据场地id查询场地的基本信息
List<Long> list = new ArrayList<>();
list = deviceList.stream().map(Device::getId).collect(Collectors.toList());
List<VenueEntity> venueList = venueMapper.selectList(new LambdaQueryWrapper<VenueEntity>()
.select(VenueEntity::getId, VenueEntity::getName)
.in(VenueEntity::getId, list)
);
List<DistributeEntity> distributeList = distributeMapper.selectList(new LambdaQueryWrapper<DistributeEntity>()
.select(DistributeEntity::getName, DistributeEntity::getId)
.in(DistributeEntity::getId, list)
);
//测试打印场地信息 正确
System.out.println(venueList);
//测试打印分销用户信息
System.out.println(distributeList);
//用VO的形式返回
List<DeviceVO> deviceVOList = new ArrayList<>();
for (Device device : deviceList) {
DeviceVO deviceVO = new DeviceVO();
BeanUtils.copyProperties(device, deviceVO);
deviceVO.setVenueName(venueList.stream().filter(v -> v.getId()
.equals(device.getVenueId())).findFirst().get().getName());
//这里有问题 -- 已修改
deviceVO.setDistributionName(distributeList.stream().filter(v -> v.getId()
.equals(device.getDistributeUsersId())).findFirst().get().getName());
deviceVOList.add(deviceVO);
}
pageResult.setList(deviceVOList);
return ResponseDTO.ok(pageResult);
}
}
2025-4-21
创建config文件夹-创建MybatisPlusConfig，配置分页查询的拦截器
package com.test.springboot_test.config;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mpInterceptor() {
//1.定义mybatisplus的拦截器
MybatisPlusInterceptor mpInterceptor = new MybatisPlusInterceptor();
//2.在mybatisplus的拦截器中添加小的拦截器
mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return mpInterceptor;
}
}
测试案例：代码
//2025-4-21对mp进行复盘
@Test
void review() {
//1.分页查询
/* QueryWrapper<Employee> employeeQueryWrapper = new QueryWrapper<>();
IPage page = new Page(1,4);
mapper.selectPage(page, employeeQueryWrapper);
System.out.println("当前页码" + page.getCurrent());
System.out.println("当前页面显示数" + page.getSize());
System.out.println("当前页码的数据" + page.getRecords());
System.out.println("总记录数" + page.getTotal());
System.out.println("总页码" + page.getPages());*/
//2.等值查询
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(Employee::getId, 1);
List<Employee> employees = mapper.selectList(queryWrapper);
for (Employee employee : employees) {
System.out.println(employee);
}
*/
//等值查询：selectOne,根据id查询只有一条数据
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(Employee::getId, 1);
Employee employee = mapper.selectOne(queryWrapper);
System.out.println(employee);
*/
//3.1模糊查询 王%
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
//这里查询的是：%在右边，王字开头的数据，日志里面是：王%
queryWrapper.likeRight(Employee::getName, "王");
List<Employee> list = mapper.selectList(queryWrapper);
for (Employee employee : list) {
System.out.println(employee);
}
*/
//3.2 %五 以五结尾
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.likeLeft(Employee::getName, "五");
List<Employee> employees = mapper.selectList(queryWrapper);
for (Employee employee : employees) {
System.out.println(employee);
}
*/
//4.1范围查询 between 查询1-3之间的数据 结果是 id=1,2,3的三条记录
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.between(Employee::getId, 1, 3);
List<Employee> list = mapper.selectList(queryWrapper);
for (Employee employee : list) {
System.out.println(employee);
}
*/
//4.2 lt gt 的使用 结果是id = 2，3，4的三条记录
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.lt(Employee::getId, 5);//id<5
queryWrapper.gt(Employee::getId, 1);//id>1
List<Employee> employees = mapper.selectList(queryWrapper);
for (Employee employee : employees) {
System.out.println(employee);
}
*/
//4.3 le ge 的使用 结果是id = 1，2，3，4，5的五条记录（根据数据库里面的id来决定）
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.le(Employee::getId, 5);//id<=5
queryWrapper.ge(Employee::getId, 1);//id>=1
List<Employee> list = mapper.selectList(queryWrapper);
for (Employee employee : list) {
System.out.println(employee);
}
*/
//5条件非空判断
/*
EmployeeQuery employeeQuery = new EmployeeQuery();
employeeQuery.setId(1);
employeeQuery.setId2(3);
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.le(null!=employeeQuery.getId2(),Employee::getId, employeeQuery.getId2());//id<=3
queryWrapper.ge(null!=employeeQuery.getId(),Employee::getId, employeeQuery.getId());//id>=1
List<Employee> employees = mapper.selectList(queryWrapper);
for (Employee employee : employees) {
System.out.println(employee);
}
*/
//6链式编程
/*
LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.lt(Employee::getId, 4).gt(Employee::getId, 2);
List<Employee> list = mapper.selectList(queryWrapper);
for (Employee employee : list) {
System.out.println(employee);
}
*/
//7.用QueryWrapper,查询具体字段（查询投影）
/*
QueryWrapper<Employee> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id,name,email");//这里不查询password
List<Employee> list = mapper.selectList(queryWrapper);
for (Employee employee : list) {
System.out.println(employee);//所以数据库查出来是null
}
*/
//8.用QueryWrapper,使用聚合函数（查询投影）,统计表中的记录数，再进行分组，(这里是QueryWrapper独特的使用场景，没有使用Lambda格式)
/*
QueryWrapper<Employee> queryWrapper = new QueryWrapper<>();
queryWrapper.select("count(*) as count,email");
queryWrapper.groupBy("email");
//查询分完组的每个模块的总记录数
//SELECT count(*) as count,email FROM employee GROUP BY email
List<Employee> employees = mapper.selectList(queryWrapper);
for (Employee employee : employees) {
System.out.println(employee);
}
*/
}
pom.xml 文件
<?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>3.4.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.test</groupId>
<artifactId>springboot_test</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot_test</name>
<description>springboot_test</description>
<url/>
<licenses>
<license/>
</licenses>
<developers>
<developer/>
</developers>
<scm>
<connection/>
<developerConnection/>
<tag/>
<url/>
</scm>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<!--解决HttpServletRequest爆红问题-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>
<!--参数验证valication-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<!--分页查询插件-->
<!--下面的配置是可以实现一个mybatisplus单表增删改查的的配置-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<annotationProcessorPaths>
<path>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</path>
</annotationProcessorPaths>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
数据库
/*
Navicat Premium Dump SQL
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80040 (8.0.40)
Source Host : localhost:3306
Source Schema : springboot_test
Target Server Type : MySQL
Target Server Version : 80040 (8.0.40)
File Encoding : 65001
Date: 16/04/2025 15:12:17
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, '王一', '123', '');
INSERT INTO `employee` VALUES (2, '李四', NULL, NULL);
INSERT INTO `employee` VALUES (3, '王五', NULL, NULL);
INSERT INTO `employee` VALUES (4, '王六', NULL, NULL);
INSERT INTO `employee` VALUES (5, '王七', NULL, NULL);
INSERT INTO `employee` VALUES (6, '王八', NULL, NULL);
INSERT INTO `employee` VALUES (7, '王九', NULL, NULL);
INSERT INTO `employee` VALUES (8, '王十', NULL, NULL);
INSERT INTO `employee` VALUES (9, '王五', '123123', '123@qq.com');
INSERT INTO `employee` VALUES (10, '王五', '1231231', '123@qq.com');
SET FOREIGN_KEY_CHECKS = 1;
application.properties
spring.application.name=springboot_test
spring.datasource.url=jdbc:mysql://localhost:3306/springboot_test?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
# 配置mybatisplus的日志,一般在遇见问题的时候会打开查看里面的日志sql数据
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#关闭spring和mybatis在控制台的视图展示，主要是为了页面整洁
spring.main.banner-mode=off
mybatis-plus.global-config.banner=false
扫一扫
3311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
