MP query criteria

Wrapper Introduce

• Wrapper： Conditionally construct abstract classes
• AbstractWrapper: Used to encapsulate query conditions , Generate sql Of the statement where Conditions
• QueryWrapper：Entity Object encapsulates the operation class , Not with lambda grammar
• UpdateWrapper：Update Conditional encapsulation , be used for Entity Object update operation
• AbstractLambdaWrapper： Use Wrapper Unified processing and parsing lambda obtain column
• LambdaQueryWrapper ： use Lambda Query used by syntax Wrapper
• LambdaUpdateWrapper ： Lambda Update package Wrapper

Common methods

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( Greater than or equal to )    gt( Greater than )    le( Less than or equal to )    lt( Less than )    
//isNull( It's empty )    isNotNull( Non empty )

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( be equal to )  ne( It's not equal to )
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

Include size boundaries

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", " mechanical ");
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), % mechanical %(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", " mechanical ")
                      .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

Splice directly to sql Last

Be careful ： It can only be called once , Multiple calls are subject to the last one Yes sql The risk of Injection , Please use with caution

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)

Parameter description ：

• applySql： To be spliced SQL sentence , The SQL There may be {0}、{1}、...、{n} Etc , Used to replace parameters .
• params： parameter list , Used for replacement applySql SQL Statement  {0}、{1}、...、{n} etc.
• condition： Used to specify whether the current condition is valid ; If true, Then apply the current condition ; If false, Then ignore the current condition .

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)

Parameter description ：

• consumer： Construct infill SQL Conditions

• condition： Used to specify whether the current condition is valid ; If true, Then apply the current condition ; If false, Then ignore the current condition .

example ： Query all surnames Huang , Gender is male , Older than 30, Salary greater than 9000 User information for , as follows ：

QueryWrapper<UserBean> wrapper = new QueryWrapper<>();
wrapper.likeRight("name", " yellow ");
wrapper.nested(new Consumer<QueryWrapper<UserBean>>() {
    @Override
    public void accept(QueryWrapper<UserBean> userBeanQueryWrapper) {
        userBeanQueryWrapper.eq("sex", " male ")
                            .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 > ?
        )
     )