Mybatis-Plus高级查询LambdaQueryWrapper&QueryWrapper前言Wrapper查询构造器总结

Posted on by hackdl

目录

前言

Wrapper

查询构造器

查询条件

前期准备

查询条件

allEq

eq

ne

gt

ge

lt

le

between,notBetween

like,notLike

likeLeft

likeRight

isNull 空值查询

isNotNull 非空值查询

in

notIn

inSql、notInSql

groupBy

orderBy、orderByAsc、orderByDesc

or、and

解决方法

last

exists、notExists

总结

附加MySQL语句执行顺序

前言

我刚刚毕业开始进入项目组的时候，从未使用过Mybatis-Plus，只用过Mybatis，Mybatis还是老套的xml配置化，已经快转正了，所以做个总结，现在SpringBoot里面的JPa可以注解实现SQL的增删改查，针对单表查询现在实际项目开发过程中根本不使用原生Hibernate或者Mybatis了，目前使用的是Mybatis-Plus，使用轻便友好，开发代码段少且完美的实现，在这里写一个文档针对我进入工作时是如何开发使用的！

优势：单表查询的话，可以直接的使用对象操作，其实实现起来极其方便而且简单！

如果多表联查的话，一般四张表以上关联或者是很复杂的sql，那就建议使用xml格式的配置化进行关联！

目前在重构一个项目的时候要针对原有的SQL进行重写，所以针对一个好用的Mybatis的插件使用。在这里做一些总结，然后通过我们组内人员使用，统一的改用LambdaQueryWrapper&QueryWrapper

简单对象查询方式用起来极为方便！涉及到单表查询的是该该对象查询继承；

推荐使用：LambdaQueryWrapper

LambdaQueryWrapper使用lambda表达式可以直接通过实体类get()属性，而QueryWrapper必须要与数据库的中表名一致，由于表名可能会很复杂，这时候相较而言LambdaQueryWrapper会比QueryWrapper便捷不少

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 id in (select id from student where id
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

查询构造器

查询条件

前期准备

  • 创建一个数据库 mybatisplus

  • 创建 user 表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
  •  创建 springboot 工程
    • 导入对应 maven 坐标


    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.3
         
    
    com.cmy
    mybatis_plus
    0.0.1-SNAPSHOT
    mybatis_plus
    Demo project for Spring Boot
    
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter
        

        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.0
        

        
        
            mysql
            mysql-connector-java
            runtime
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
    • 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 {
}

查询条件

allEq

条件用 Map 进行封装

“name” -> “张三”

“age” -> 20

public void testAllEq() {
    QueryWrapper queryWrapper = new QueryWrapper();
    // 封装条件
    Map hashMap = new HashMap();
    hashMap.put("name", "张三");
    hashMap.put("age", 20);

    queryWrapper.allEq(hashMap);

    List userList = userMapper.selectList(queryWrapper);
    userList.forEach(user -> {
        System.out.println(user);
    });
}

eq

eq("列名", 值) -> 列名 = 值

    public List listByDictCode(DictCode dictCode) {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.eq(Dict::getDictCode, dictCode.getCode())
               .eq(Dict::getEnabled, DictEnableEnum.VALID.getType());
        return this.baseMapper.selectList(wrapper);
    }

ne

ne("列名", 值) -> 列名 != 值

    public List listByDictCode(DictCode dictCode) {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.ne(Dict::getDictCode, dictCode.getCode())
               .ne(Dict::getEnabled, DictEnableEnum.VALID.getType());
        return this.baseMapper.selectList(wrapper);
    }

gt

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

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.gt(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

ge

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

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.ge(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

lt

lt("age", 20) -> age 

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.lt(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

le

le("age", 21) -> age 

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.le(User::getAge, 20);            
        return this.baseMapper.selectList(wrapper);
    }

between,notBetween

between("age", 18, 25) -> age BETWEEN 18 AND 25 ，年龄在18到25之间

notBetween就是不在18到25之间

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.between(User::getAge, 18,25);                
        return this.baseMapper.selectList(wrapper);
    }

like,notLike

like 匹配值 -> "%值%" 模糊查询

notLike 模糊查询不匹配"%值%"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
         wrapper.like(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

likeLeft

likeLeft 匹配值 -> "%值"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
         wrapper.likeLeft(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

likeRight

likeRight 匹配值 -> "值%"

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.likeRight(User::getName, "张");           
        return this.baseMapper.selectList(wrapper);
    }

isNull 空值查询

isNotNull 非空值查询

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.isNull(User::getName);
        //wrapper.isNotNull(User::getName);              
        return this.baseMapper.selectList(wrapper);
    }

in

in("name", "张三", "李四") -> name in ("张三", "李四") 姓名是张三或李四的用户

notIn

notIn("name", "张三", "李四") -> name not in ("张三", "李四") 姓名不是张三或李四的用户

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
        wrapper.in(User::getName, "张三","李四");
        //wrapper.in(User::getName, "张三","李四");           
        return this.baseMapper.selectList(wrapper);
    }

inSql、notInSql

public List userList() {
    LambdaQueryWrapper wrapper= new LambdaQueryWrapper();
    // SELECT id,name,email,age FROM user WHERE (age IN (select age from user where id = 1))
    wrapper.inSql(User::getAge, "select age from user where id = 1");
    return this.baseMapper.selectList(wrapper);
}

groupBy

分组

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();
    wrapper.groupBy(User::getName);
    return this.baseMapper.selectList(wrapper);
}

orderBy、orderByAsc、orderByDesc

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();
    // SELECT id,name,email,age FROM user ORDER BY name ASC,age DESC
    wrapper.orderBy(true, true, User::getName).orderBy(true, false, User::getAge);
    
    // SELECT id,name,email,age FROM user ORDER BY name ASC,age ASC
    wrapper.orderByAsc(User::getName, User::getAge);

    // SELECT id,name,email,age FROM user ORDER BY name DESC,age DESC
    wrapper.orderByDesc(User::getName, User::getAge);

    return this.baseMapper.selectList(wrapper);
}

or、and

    public List userList() {
        LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();

        // SELECT id,name,email,age FROM user WHERE (name = ? AND id = ?)
        wrapper.eq(User::getName, "张三").and().eq(User::getId,1);

        // SELECT id,name,email,age FROM user WHERE (name = ? OR id = ?)       
        wrapper.eq(User::getName, "张三").or().eq(User::getId,1);     
        return this.baseMapper.selectList(wrapper);
    }

这里说明一下or和and的问题

错误代码

public List userList() {

    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();
    wrapper.eq(User::getId,1);
    wrapper.like(User::getName,"张")
           .or()
           .like(User::getEmail,"163")
           .or()
           .like(User::getAge,1);
}

根据上面的写法写出的sql语句如下：

WHERE id = '1' 
	AND name LIKE '%张%'
	OR email LIKE '%163%'
    OR age LIKE '%1%'

这样明显是不对的，根据mysql语句执行顺序or最后执行 ，这会导致一旦[name like ‘%张%’]条件成立后面的or条件就会失效，所以第一个条件 并没有起到and的作用。

解决方法

public List userList() {

    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();
    wrapper.eq(User::getId,1);
    wrapper.and(wrapper->wrapper.like(User::getName,"张")
                                .or()
                                .like(User::getEmail,"163")
                                .or()
                                .like(User::getAge,1)
               );
}

这样得到的sql语句如下

WHERE id = '1' 
	AND (name LIKE '%张%'
	OR email LIKE '%163%'
    OR age LIKE '%1%')

这样就解决了，这个问题在我的公司中新人(包括我在内)貌似都遇到这个问题，在此说明一下

last

在末尾拼接sql语句

注：last()有sql注入的风险，请谨慎使用！

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();
    // SELECT id,name,email,age FROM user WHERE (name = ? OR age = ?) limit 1
    wrapper.eq(User::getName, "张三").or().eq(User::getAge, 20).last("limit 1");
    return this.baseMapper.selectList(wrapper);
}

exists、notExists

public List userList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper();

    // SELECT id,name,email,age FROM user WHERE (EXISTS (select name from user where age > ?))
    wrapper.exists("select name from user where age > 21");

    // SELECT id,name,email,age FROM user WHERE (NOT EXISTS (select name from user where age > ?))
    wrapper.notExists("select name from user where age > 21");

    return this.baseMapper.selectList(wrapper);
}

总结

略

附加MySQL语句执行顺序

1、from
2、where (or 最后执行)
3、group by
4、having
5、DISTINCT
6、order by
7、limit

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: