MyBatis-Plus高级查询Wrapper

盖辉

2023-12-01

Wrapper查询构造器

查询条件

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

QueryWrapper 查询条件封装类

方法	说明
select	设置查询字段 select 后面的内容

UpdateWrapper 更新条件封装类

方法	说明
set	设置要更新的字段，MP 拼接 SQL 语句
setSql	参数是 sql 语句，MP 不再处理语句

查询实例

前期准备

创建一个数据库 mybatisplus

创建 user 表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

创建 springboot 工程

导入对应 maven 坐标

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cmy</groupId>
    <artifactId>mybatis_plus</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis_plus</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

mysql数据库相关配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1/mybatisplus?useUnicode=true&characterEncoding=utf-8
    username: root
    password: root

mybatis-plus 日志信息配置

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

创建实体类 User

package com.cmy.mybatis_plus.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;

/**
 * 实体类 user
 */
public class User {
    /**
     * 指定主键id生成的方式
     * value 是主键字段的名称，如果是id，可以不用写
     * type 指定主键的类型，主键的值如何生成。idType.AUTO 自动增长
     */
    @TableId(
            value = "id",
            type = IdType.AUTO
    )
    private Long id;
    private String name;
    private String email;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

自定义 User 的 Mapper 接口

package com.cmy.mybatis_plus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cmy.mybatis_plus.entity.User;

/**
 * 自定义 Mapper 接口，就是 dao 接口
 * 1. 实现BaseMapper
 * 2. 指定实体类（泛型）
 *
 * BaseMapper 是 MP 框架中的对象，定义了 17 个操作方法（CRUD）
 */
public interface UserMapper extends BaseMapper<User> {
}

用于CRUD

查询操作

allEq

条件用 Map 进行封装

“name” -> “zhangsan”

“age” -> 21

@Autowired
private UserMapper userMapper;

@Test
public void testAllEq() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    // 封装条件
    Map<String, Object> hashMap = new HashMap<>();
    hashMap.put("name", "zhangsan");
    hashMap.put("age", 21);

    queryWrapper.allEq(hashMap);

    // 调用 MP 自己的方法
    // SELECT id,name,email,age FROM user WHERE (name = ? AND age = ?)
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(user -> {
        System.out.println(user);
    });
}

allEq(Map<R, V> params, boolean null2IsNull)有两个参数，第二个参数默认为 true，表示查询的条件的值可以为 null。例如age IS NULL

eq

eq("列名", 值)

@Autowired
private UserMapper userMapper;

@Test
public void testEq() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.eq("name", "zhangsan");

    // SELECT id,name,email,age FROM user WHERE (name = ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

ne

ne("列名", 值)

@Autowired
private UserMapper userMapper;

@Test
public void testNe() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.ne("name", "zhangsan");

    // SELECT id,name,email,age FROM user WHERE (name <> ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

gt

gt("age", 20) -> age > 20

@Autowired
private UserMapper userMapper;

@Test
public void testGt() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.gt("age", 20);

    // SELECT id,name,email,age FROM user WHERE (age > ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

ge

ge("age", 21) -> age >= 21

@Autowired
private UserMapper userMapper;

@Test
public void testGe() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.ge("age", 21);

    // SELECT id,name,email,age FROM user WHERE (age >= ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

lt

lt("age", 21) -> age < 21

@Autowired
private UserMapper userMapper;

@Test
public void testLt() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.lt("age", 21);

    // SELECT id,name,email,age FROM user WHERE (age < ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

le

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

@Autowired
private UserMapper userMapper;

@Test
public void testLe() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.le("age", 21);

    // SELECT id,name,email,age FROM user WHERE (age <= ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

between

between("age", 18, 25) -> age BETWEEN 18 AND 25

@Autowired
private UserMapper userMapper;

@Test
public void testBetween() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.between("age", 18, 25);

    // SELECT id,name,email,age FROM user WHERE (age BETWEEN ? AND ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

notBetween

notBetween("age", 18, 25) -> age NOT BETWEEN 18 AND 25

@Autowired
private UserMapper userMapper;

@Test
public void testNotBetween() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.notBetween("age", 18, 25);

    // SELECT id,name,email,age FROM user WHERE (age NOT BETWEEN ? AND ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

like

like 匹配值 -> "%值%"

@Autowired
private UserMapper userMapper;

@Test
public void testLike() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.like("name", "李");

    // SELECT id,name,email,age FROM user WHERE (name LIKE ?) %李%(String)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

notLike

@Autowired
private UserMapper userMapper;

@Test
public void testNotLike() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.notLike("name", "李");

    // SELECT id,name,email,age FROM user WHERE (name NOT LIKE ?) %李%(String)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

likeLeft

likeLeft 匹配值 -> "%值"

@Autowired
private UserMapper userMapper;

@Test
public void testLikeLeft() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.likeLeft("name", "四");

    // SELECT id,name,email,age FROM user WHERE (name LIKE ?) %四(String)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

likeRight

likeRight 匹配值 -> "值%"

@Autowired
private UserMapper userMapper;

@Test
public void testLikeRight() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.likeRight("name", "李");

    // SELECT id,name,email,age FROM user WHERE (name LIKE ?) 李%(String)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

isNull

@Autowired
private UserMapper userMapper;

@Test
public void testIsNull() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.isNull("name");

    // SELECT id,name,email,age FROM user WHERE (name IS NULL)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

isNotNull

@Autowired
private UserMapper userMapper;

@Test
public void testIsNotNull() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.isNotNull("name");

    // SELECT id,name,email,age FROM user WHERE (name IS NOT NULL)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

in

in("name", "张三", "李四") -> name in ("张三", "李四")

@Autowired
private UserMapper userMapper;

@Test
public void testIn() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.in("name", "zs", "zhangsan");

    // SELECT id,name,email,age FROM user WHERE (name IN (?,?))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

notIn

notIn("name", "张三", "李四") -> name not in ("张三", "李四")

@Autowired
private UserMapper userMapper;

@Test
public void testNotIn() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.notIn("name", "zs", "zhangsan");

    // SELECT id,name,email,age FROM user WHERE (name NOT IN (?,?))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

inSql

@Autowired
private UserMapper userMapper;

@Test
public void testInSql() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.inSql("age", "select age from user where id = 10");

    // SELECT id,name,email,age FROM user WHERE (age IN (select age from user where id = 10))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

notInSql

@Autowired
private UserMapper userMapper;

@Test
public void testNotInSql() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.notInSql("age", "select age from user where id = 10");

    // SELECT id,name,email,age FROM user WHERE (age NOT IN (select age from user where id = 10))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

groupBy

groupBy 多个字段

@Autowired
private UserMapper userMapper;

@Test
public void testGroupBy() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.select("name, count(*) personNumbers");
    queryWrapper.groupBy("name");

    // SELECT id,name,email,age FROM user WHERE (age NOT IN (select age from user where id = 10))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

orderByAsc

@Autowired
private UserMapper userMapper;

@Test
public void testOrderByAsc() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.orderByAsc("name", "age");

    // SELECT id,name,email,age FROM user ORDER BY name ASC,age ASC
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

orderByDesc

@Autowired
private UserMapper userMapper;

@Test
public void testOrderByDesc() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.orderByDesc("name", "age");

    // SELECT id,name,email,age FROM user ORDER BY name DESC,age DESC
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

orderBy

@Autowired
private UserMapper userMapper;

@Test
public void testOrderBy() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.orderBy(true, true, "name").orderBy(true, false, "age");

    // SELECT id,name,email,age FROM user ORDER BY name ASC,age DESC
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

or

@Autowired
private UserMapper userMapper;

@Test
public void testOr() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.eq("name", "zs").or().eq("age", 21);

    // SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

and

@Autowired
private UserMapper userMapper;

@Test
public void testAnd() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.eq("name", "zs").eq("age", 21);

    // SELECT id,name,email,age FROM user WHERE (name = ? AND age = ?)
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

last

@Autowired
private UserMapper userMapper;

@Test
public void testLast() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.eq("name", "zs").or().eq("age", 21).last("limit 1");

    // SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?) limit 1
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

exists

@Autowired
private UserMapper userMapper;

@Test
public void testExists() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.exists("select name from user where age > 21");

    // SELECT id,name,email,age FROM user WHERE (EXISTS (select name from user where id = 1))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

notExists

@Autowired
private UserMapper userMapper;

@Test
public void testNotExists() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.notExists("select name from user where age > 21");

    // SELECT id,name,email,age FROM user WHERE (NOT EXISTS (select name from user where id = 1))
    List<User> userList = userMapper.selectList(queryWrapper);

    userList.forEach(user -> System.out.println(user));
}

分页查询

创建一个 config 类，定义方法，将分页拦截器注入到 spring 容器中

package com.cmy.mybatis_plus.config;

import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Configuration 配置文件
 */
@Configuration
public class Config {
    /**
     * 定义方法，方法返回值是java 对象，放入到 spring 容器中
     */
    @Bean
    public PaginationInnerInterceptor paginationInnerInterceptor() {
        return new PaginationInnerInterceptor();
    }
}

编写测试代码

@Autowired
private UserMapper userMapper;

@Test
public void testPage() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    IPage<User> page = new Page<>();

    // 设置分页参数
    page.setCurrent(1); // 第一页
    page.setSize(3); // 每页的记录数

    IPage<User> result = userMapper.selectPage(page, queryWrapper);

    // 获取分页后的记录
    List<User> userList = result.getRecords();
    System.out.println("userList.size() = " + userList.size());

    // 分页的信息
    System.out.println("页数：" + result.getPages());
    System.out.println("总记录数：" + result.getTotal());
    System.out.println("当前页" + result.getCurrent());
    System.out.println("每页多少条记录：" + result.getSize());
}