MP查询条件

最新推荐文章于 2024-05-14 13:26:45 发布

夜光下丶

最新推荐文章于 2024-05-14 13:26:45 发布

阅读量2.7k

点赞数 1

分类专栏： mybatis MySQL Java 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46058921/article/details/125919770

版权

Java 同时被 3 个专栏收录

31 篇文章 1 订阅

订阅专栏

mybatis

11 篇文章 0 订阅

订阅专栏

MySQL

11 篇文章 0 订阅

订阅专栏

Wrapper介绍

Wrapper：条件构造抽象类
AbstractWrapper:用于查询条件封装，生成sql语句的where条件
QueryWrapper：Entity 对象封装操作类，不是用lambda语法
UpdateWrapper：Update条件封装，用于Entity对象更新操作
AbstractLambdaWrapper：使用Wrapper统一处理解析lambda获取column
LambdaQueryWrapper ：用Lambda语法使用的查询Wrapper
LambdaUpdateWrapper ： Lambda 更新封装Wrapper

常见方法

ge、gt、le、lt、isNull、isNotNull

gt(R column, Object val)
gt(boolean condition, R column, Object val)

ge(R column, Object val)
ge(boolean condition, R column, Object val)

lt(R column, Object val)
lt(boolean condition, R column, Object val)

le(R column, Object val)
le(boolean condition, R column, Object val)

isNull(R column)
isNull(boolean condition, R column)

isNotNull(R column)
isNotNull(boolean condition, R column)

//ge(大于等于)    gt(大于)    le(小于等于)    lt(小于)    
//isNull(为空)    isNotNull(非空)

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("order_code")
                .isNotNull("status")
                .gt("seq_num", 10);
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (order_code IS NULL AND status IS NOT NULL AND seq_num > ?)

eq、ne

eq(R column, Object val)
eq(boolean condition, R column, Object val)

ne(R column, Object val)
ne(boolean condition, R column, Object val)

// eq(等于)  ne(不等于)
QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("order_code", "NMGDR")
                .ne("status", "A");
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (order_code = ? AND status <> ?)

between、notBetween

包含大小边界

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
    queryWrapper.between("id", 2, 10)
                .notBetween("contract_id", 51, 100);
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (id BETWEEN ? AND ? AND contract_id NOT BETWEEN ? AND ?)

allEq

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();

HashMap<String, Object> map = new HashMap<>();
map.put("order_code", "001");
map.put("fid", 100);
map.put("status", "A");

queryWrapper.allEq(map);

List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (order_code = ? AND fid = ? AND status = ?)

like、notLike、likeLeft、likeRight

like(R column, Object val)
like(boolean condition, R column, Object val)

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
queryWrapper.likeLeft("order_code", "001")
            .likeRight("contract_code", "HSC")
            .like("product_type_code", "001")
            .notLike("material_name", "机械");
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (order_code LIKE ? AND contract_code LIKE ? AND product_type_code LIKE ? AND material_name NOT LIKE ?)

Parameters: %001(String), HSC%(String), %001%(String), %机械%(String)

in、notIn、inSql、notinSql

in(R column, Collection value)
in(boolean condition, R column, Collection value)
in(R column, Object… values)
in(boolean condition, R column, Object… values)

notIn(R column, Collection value)
notIn(boolean condition, R column, Collection value)
notIn(R column, Object… values)
notIn(boolean condition, R column, Object… values)

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();

queryWrapper.in("contract_code", Arrays.stream(new String[]{"a", "b", "c", "d"}).toArray())
            .notIn("order_code", "1", "2", "3", "4")
            .inSql("product_type_code", "1,2,3,4,5")
            .notInSql("material_name", "select material_name from t_purchase_order where fid = 100");

List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (
    contract_code IN (?,?,?,?) 
    AND order_code NOT IN (?,?,?,?) 
    AND product_type_code IN (1,2,3,4,5) 
    AND material_name NOT IN (select material_name from t_purchase_order where fid = 100)
    )
Parameters: a(String), b(String), c(String), d(String), 1(String), 2(String), 3(String), 4(String)

or、and

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("contract_code", "HMC")
            .and(i -> i.eq("order_code", "11")
                       .eq("product_type_code", "22"))
            .or(i -> i.eq("material_name", "机械")
                      .eq("specification", "A"));
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (
    contract_code = ? 
    AND (order_code = ? AND product_type_code = ?) 
    OR (material_name = ? AND specification = ?)
    )

last

直接拼接到 sql 的最后

注意：只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");
queryWrapper.last("limit 2");
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
limit 2

apply

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

参数说明：

applySql：要拼接的 SQL 语句，该 SQL 语句中可能会有 {0}、{1}、...、{n} 等字符串，用来替换参数。
params：参数列表，用于替换 applySql SQL 语句中的 {0}、{1}、...、{n} 等
condition：用于指定当前这个条件是否有效；如果为 true，则应用当前条件；如果为 false，则忽略当前条件。

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("contract_code", "HMC")
            .apply("date_format(update_time,'%Y-%m-%d') = {0}", "2020-10-08")
            .apply("status != {0}", "A");
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT * 
FROM t_purchase_order 
WHERE (contract_code = ? AND date_format(update_time,'%Y-%m-%d') = ? AND status != ?)

select

QueryWrapper<PurchaseOrder> queryWrapper = new QueryWrapper<>();
queryWrapper.select("contract_code", "status");
List<PurchaseOrder> orders = this.baseMapper.selectList(queryWrapper);

SELECT contract_code,status 
FROM t_purchase_order

nested

nested(Consumer<Param> consumer) 
nested(boolean condition, Consumer<Param> consumer)

参数说明：

consumer：构造内嵌SQL条件

condition：用于指定当前这个条件是否有效；如果为 true，则应用当前条件；如果为 false，则忽略当前条件。

实例：查询所有姓黄，性别为男，年龄大于 30，薪水大于 9000 的用户信息，如下：

QueryWrapper<UserBean> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", "黄");
wrapper.nested(new Consumer<QueryWrapper<UserBean>>() {
    @Override
    public void accept(QueryWrapper<UserBean> userBeanQueryWrapper) {
        userBeanQueryWrapper.eq("sex", "男")
                            .gt("age", 30)
                            .gt("salary", 9000);
    }
});

SELECT user_id,name,sex,age,face,salary,borthday 
FROM user 
WHERE (
    name LIKE ? 
    AND (
        sex = ? 
        AND age > ? 
        AND salary > ?
        )
     )