MP查询条件

野性酷女 2023-09-30 14:34 3阅读 0赞

### Wrapper介绍 ###

![86e4c07bd6cf42b587bc41bb054a5c85.png][]

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

### 常见方法 ###

![96138a2cb4b34fe892e8f48b585e937a.png][]

#### 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 > ?
            )
         )

[86e4c07bd6cf42b587bc41bb054a5c85.png]: https://img-blog.csdnimg.cn/86e4c07bd6cf42b587bc41bb054a5c85.png
[96138a2cb4b34fe892e8f48b585e937a.png]: https://img-blog.csdnimg.cn/96138a2cb4b34fe892e8f48b585e937a.png