Mybatis-plus条件构造器QueryWrapper实例

1、添加依赖

<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.0.7.1</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
</dependency>

2、wapper介绍

Wrapper ： 条件构造抽象类，最顶端父类，抽象类中提供4个方法西面贴源码展示
AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
AbstractLambdaWrapper ： Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
LambdaQueryWrapper ：看名称也能明白就是用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper ： Lambda 更新封装Wrapper
QueryWrapper ： Entity 对象封装操作类，不是用lambda语法
UpdateWrapper ： Update 条件封装，用于Entity对象更新操作

3、条件构造大全

函数名	说明	示例
eq	等于 =	例：eq("name", "老王")--->name = '老王'
ne	不等于 <>	例: ne("name", "老王")--->name <> '老王'
ge	大于 >	例: gt("age", 18)--->age > 18
ge	大于等于 >=	例: ge("age", 18)--->age >= 18
lt	小于 <	例: lt("age", 18)--->age < 18
le	小于等于 <=	例: le("age", 18)--->age <= 18
between	BETWEEN 值1 AND 值2	例: between("age", 18, 30)--->age between 18 and 30
notBetween	NOT BETWEEN 值1 AND 值2	例: notBetween("age", 18, 30)--->age not between 18 and 30
like	LIKE '%值%'	例: like("name", "王")--->name like '%王%'
notLike	NOT LIKE '%值%'	例: notLike("name", "王")--->name not like '%王%'
likeLeft	LIKE '%值'	例: likeLeft("name", "王")--->name like '%王'
likeRight	LIKE '值%'	例: likeRight("name", "王")--->name like '王%'
isNull	字段 IS NULL	例: isNull("name")--->name is null
isNotNull	字段 IS NOT NULL	例: isNotNull("name")--->name is not null
in	字段 IN (value.get(0), value.get(1), ...) 字段 IN (v0, v1, ...)	例: in("age",{1,2,3})--->age in (1,2,3)例: in("age", 1, 2, 3)--->age in (1,2,3)
notIn	字段 NOT IN (value.get(0), value.get(1), ...) 字段 NOT IN (v0, v1, ...)	例:  notIn("age",{1,2,3})--->age not in (1,2,3)例: notIn("age", 1, 2, 3)--->age not in (1,2,3)
groupBy	分组：GROUP BY 字段, ...	例: groupBy("id", "name")--->group by id,name
orderByAsc	排序：ORDER BY 字段, ... ASC	例: orderByAsc("id", "name")--->order by id ASC,name ASC
orderByDesc	排序：ORDER BY 字段, ... DESC	例: orderByDesc("id", "name")--->order by id DESC,name DESC

setSqlSelect	设置 SELECT 查询字段
where	WHERE 语句，拼接 + WHERE 条件
and	AND 语句，拼接 + AND 字段=值
and	New AND 语句，拼接 + AND (字段=值)
or	OR 语句，拼接 + OR 字段=值
orNew	OR 语句，拼接 + OR (字段=值)
eq	等于=
allEq	基于 map 内容等于=
ne	不等于<>
gt	大于>
ge	大于等于>=
lt	小于<
le	小于等于<=
like	模糊查询 LIKE
notLike	模糊查询 NOT LIKE
in	IN 查询
notIn	NOT IN 查询
isNull	NULL 值查询
isNotNull	IS NOT NULL
groupBy	分组 GROUP BY
having	HAVING 关键词
orderBy	排序 ORDER BY
orderAsc	ASC 排序 ORDER BY
orderDesc	DESC 排序 ORDER BY
exists	EXISTS 条件语句
notExists	NOT EXISTS 条件语句
between	BETWEEN 条件语句
notBetween	NOT BETWEEN 条件语句
addFilter	自由拼接 SQL
last	拼接在最后，例如：last(“LIMIT 1”)

4、实际调用方式 

4.1 User实体类

package com.shucha.deveiface.biz.model;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @author tqf
 * @Description
 * @Version 1.0
 * @since 2022-04-07 13:53
 */
@Data
public class User {

    /**
     * 主键ID
     */
    private Long id;

    /**
     *用户名
     */
    private String userName;

    /**
     * 用户密码
     */
    private String passWord;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 性别(0-男,1-女,2-未知)
     */
    private Integer sex;

    /**
     * 创建时间
     */
    private Date createTime;
}

4.2 UserService

package com.shucha.deveiface.biz.service;

import com.sdy.mvc.service.BaseService;
import com.shucha.deveiface.biz.model.User;

/**
 * @author tqf
 * @Description
 * @Version 1.0
 * @since 2022-04-07 13:58
 */
public interface UserService extends BaseService<User> {
}

 4.3 UserServiceImpl

package com.shucha.deveiface.biz.service.impl;

import com.sdy.mvc.service.impl.BaseServiceImpl;
import com.shucha.deveiface.biz.model.User;
import com.shucha.deveiface.biz.service.UserService;

/**
 * @author tqf
 * @Description
 * @Version 1.0
 * @since 2022-04-07 13:59
 */
public class UserServiceImpl extends BaseServiceImpl<User> implements UserService {
}

4.4 UserController

package com.shucha.deveiface.web.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.shucha.deveiface.biz.model.User;
import com.shucha.deveiface.biz.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

/**
 * @author tqf
 * @Description
 * @Version 1.0
 * @since 2022-04-07 13:53
 */
@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("test")
    public void test(){
        List<User> list = new ArrayList<>();
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        // eq  等于
        queryWrapper.eq(User::getUserName,"张三");
        // ne 不等于
        queryWrapper.ne(User::getSex,2);
        // gt 大于
        queryWrapper.gt(User::getAge,18);
        // ge 大于等于
        queryWrapper.ge(User::getAge,25);
        // le 小于
        queryWrapper.lt(User::getAge,25);
        // lt 小于等于
        queryWrapper.le(User::getAge,25);
        // between BETWEEN 值1 AND 值2
        queryWrapper.between(User::getCreateTime,"2022-04-01","2022-04-02");
        // notBetween
        queryWrapper.notBetween(User::getAge,18,35);
        // like
        queryWrapper.like(User::getUserName,"李");
        // notLike
        queryWrapper.notLike(User::getUserName,"谭");
        // likeLeft
        queryWrapper.likeLeft(User::getUserName,"啊");
        // likeRight
        queryWrapper.likeRight(User::getUserName,"福");
        // is null
        queryWrapper.isNull(User::getSex);
        // is not null
        queryWrapper.isNotNull(User::getAge);
        // in
        queryWrapper.in(User::getUserName,"张三","李四");
        // not in
        queryWrapper.notIn(User::getUserName,"张三");
        // groupBy
        queryWrapper.groupBy(User::getSex);
        // orderByAsc
        queryWrapper.orderByAsc(User::getAge);
        // orderByDesc
        queryWrapper.orderByDesc(User::getAge);
        // last
        queryWrapper.last("limit 10");
        list = userService.list(queryWrapper);
    }
}