一、条件构造器的介绍
条件构造器Wrapper最顶级的条件构造抽象类，主要有下述四个子类，我们可以使用它们来构造相关条件从而进行数据库交互操作。
QueryWrapper：用于封装查询条件
UpdateWrapper：用于封装更新条件
LambdaQueryWrapper：用于使用Lambda语法封装查询条件
LambdaUpdateWrapper：用于使用Lambda语法封装更新条件
二、数据准备
在demo库中新建一张名为“fruit”的表：
DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(10) NOT NULL COMMENT '名称',
`price` int(11) NOT NULL COMMENT '价格',
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='水果信息表';然后执行下述SQL往“fruit”表中插入一些测试数据：
insert into `fruit` (`id`, `name`, `price`, `is_delete`) values('1','西瓜','25','0');
insert into `fruit` (`id`, `name`, `price`, `is_delete`) values('2','哈密瓜','27','0');
insert into `fruit` (`id`, `name`, `price`, `is_delete`) values('3','橘子','21','0');
insert into `fruit` (`id`, `name`, `price`, `is_delete`) values('4','苹果','18','0');
insert into `fruit` (`id`, `name`, `price`, `is_delete`) values('5','香蕉','20','0');三、编写实体类
package com.example.demo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("fruit")
public class Fruit {
@TableId(type = IdType.AUTO)
private Long id;
@TableField(value = "name")
private String name;
@TableField(value = "price")
private Integer price;
@TableLogic
@TableField(value = "is_delete")
private int isDelete =0;
}
实体类中定义了isDelete字段，数据类型为int，为0未删除，为1逻辑删除。这里我们在isDelete字段中加上@TableLogic注解，MyBatisPlus在使用条件构造器Wrapper进行数据库交互时，可以自动为我们实现逻辑删除和数据过滤。
物理删除：将数据从数据库直接删除，之后无法查询到被删除的信息。
逻辑删除：设置一个删除的状态码，为0未删除，为1逻辑删除，但是还是存在数据库中，之后还可以查询到，解决了数据误删除无法恢复的问题。
四、构建Mapper接口
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Fruit;
import org.springframework.stereotype.Repository;
@Repository
public interface FruitMapper extends BaseMapper<Fruit> {
}五、引入依赖
特别说明：当前Spring Boot的版本为2.7.6，Mybatis Plus的版本为3.5.3.1。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.bc</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>6.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.11</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>六、配置yml
在application.yml文件中添加下述配置：
server:
port: 10086
spring:
application:
name: demo
datasource:
url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
mybatis:
mapper-locations: classpath:mapper/*.xml
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl七、QueryWrapper
7.1 模糊查询
查询水果信息(fruit)表里面name包含“瓜”，并且价格在20与26之间的数据：
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test(){
QueryWrapper<Fruit> queryWrapper =new QueryWrapper<>();
queryWrapper.like("name","瓜")
.between("price",20,26);
List<Fruit> list = fruitMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
}执行上述代码，其输出结果如下所示：
Fruit(id=1, name=西瓜, price=25, isDelete=0)7.2 降序排序
按照价格降序排序查询：
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
QueryWrapper<Fruit> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("price");
List<Fruit> list = fruitMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
}执行上述代码，其输出结果如下所示：
Fruit(id=2, name=哈密瓜, price=27, isDelete=0)
Fruit(id=1, name=西瓜, price=25, isDelete=0)
Fruit(id=3, name=橘子, price=21, isDelete=0)
Fruit(id=5, name=香蕉, price=20, isDelete=0)
Fruit(id=4, name=苹果, price=18, isDelete=0)7.3 逻辑删除
将价格大于26的数据进行逻辑删除：
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
QueryWrapper<Fruit> queryWrapper =new QueryWrapper<>();
queryWrapper.gt("price",26);
int delete = fruitMapper.delete(queryWrapper);
System.out.println(delete);
}
}执行上述代码，其输出结果如下所示：
17.4 数据更新
将价格大于24的水果价格都变成23：
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
QueryWrapper<Fruit> queryWrapper =new QueryWrapper<>();
queryWrapper.gt("price",24);
Fruit fruit =new Fruit();
fruit.setPrice(23);
fruitMapper.update(fruit,queryWrapper);
}
}这里需要说明的是针对已经进行逻辑删除的数据，是不会再对其进行更新操作：
八、UpdateWrapper
将价格小于23的水果的价格修改为25：
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
UpdateWrapper<Fruit> updateWrapper =new UpdateWrapper<>();
updateWrapper.lt("price",23);
updateWrapper.set("price",25);
fruitMapper.update(null,updateWrapper);
}
}九、LambdaQueryWrapper
查询水果名包含“橘”，价格大于23的数据。前提条件是name不为空，price不为空。
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
String name="橘";
Integer price=23;
LambdaQueryWrapper<Fruit> lambdaQueryWrapper =new LambdaQueryWrapper<>();
lambdaQueryWrapper.like(StrUtil.isNotBlank(name), Fruit::getName, name).
gt(ObjectUtil.isNotEmpty(price),Fruit::getPrice,price);
List<Fruit> list = fruitMapper.selectList(lambdaQueryWrapper);
list.forEach(System.out::println);
}
}执行上述代码，其输出结果如下所示：
Fruit(id=3, name=橘子, price=25, isDelete=0)十、LambdaUpdateWrapper
查询水果名包含“橘”，价格大于23的数据。前提条件是name不为空，price不为空。然后将价格修改为27。
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
String name="橘";
Integer price=23;
LambdaUpdateWrapper<Fruit> lambdaUpdateWrapper =new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.like(StrUtil.isNotBlank(name), Fruit::getName, name).
gt(ObjectUtil.isNotEmpty(price),Fruit::getPrice,price);
lambdaUpdateWrapper.set(Fruit::getPrice,27);
fruitMapper.update(null,lambdaUpdateWrapper);
}
}执行上述代码，其输出结果如下图所示：
十一、自定义SQL调用QueryWrapper
项目中需要实现自定义的查询，但是仍然想用QueryWrapper对象里面的那些查询，这个时候有两种方法。
第一种方法：在mapper接口中使用@Select注解构建自定义查询语句，然后在对应的方法声明中添加“@Param(Constants.WRAPPER) QueryWrapper<T> queryWrapper”参数。@Param注解中的Constants.WRAPPER其实就是"ew"，使用“${ew.customSqlSegment}”就可以调用QueryWrapper对象的条件SQL。
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.demo.entity.Fruit;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface FruitMapper extends BaseMapper<Fruit> {
@Select("select * from fruit ${ew.customSqlSegment}")
List<Fruit> queryList(@Param(Constants.WRAPPER) QueryWrapper<Fruit> queryWrapper);
}import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
QueryWrapper<Fruit> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("price", 25);
List<Fruit> fruitList = fruitMapper.queryList(queryWrapper);
fruitList.forEach(System.out::println);
}
}执行上述代码，其输出结果为：
Fruit(id=2, name=哈密瓜, price=27, isDelete=1)
Fruit(id=3, name=橘子, price=27, isDelete=0)第二种方法：在mapper接口中对应的方法声明处添加“@Param(Constants.WRAPPER) QueryWrapper<T> queryWrapper”参数，然后在mapper.xml中构建自定义查询语句，并引用“${ew.customSqlSegment}”调用QueryWrapper对象的条件SQL
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.demo.entity.Fruit;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface FruitMapper extends BaseMapper<Fruit> {
List<Fruit> queryList(@Param(Constants.WRAPPER) QueryWrapper<Fruit> queryWrapper);
}<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.FruitMapper">
<select id="queryList" resultType="com.example.demo.entity.Fruit">
select * from fruit ${ew.customSqlSegment}
</select>
</mapper>import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.entity.Fruit;
import com.example.demo.mapper.FruitMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class DemoApplicationTests {
@Autowired
private FruitMapper fruitMapper;
@Test
void test() {
QueryWrapper<Fruit> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("price", 25);
List<Fruit> fruitList = fruitMapper.queryList(queryWrapper);
fruitList.forEach(System.out::println);
}
}执行上述代码，其输出结果为：
Fruit(id=2, name=哈密瓜, price=27, isDelete=1)
Fruit(id=3, name=橘子, price=27, isDelete=0)
825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
