首页 专题 文章 代码 归档
MybatisPlus条件构造器
2020.02.10 12:33 2020.02.10 12:33

1. AllEq

例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null

例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'

@Test
public void testAllEq() {

    QueryWrapper<User> wrapper = new QueryWrapper<>();
    Map<String, Object> map = new HashMap<>();
    map.put("password", "8989");
    map.put("age", "28");

    wrapper.allEq(map);

    List<User> users = userMapper.selectList(wrapper);
    System.out.println("users = " + users);
}

注:

QueryWrapper一般都有一个构造方法,参数是Boolean b开头的,代表说该字段是否存在,若存在才进行后面的比较。

2. 基本比较

2.1. eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)
  • 等于 =
  • 例: eq("name", "老王")--->name = '老王'

2.2. ne

ne(R column, Object val)
ne(boolean condition, R column, Object val)
  • 不等于 <>
  • 例: ne("name", "老王")--->name <> '老王'

2.3. gt

gt(R column, Object val)
gt(boolean condition, R column, Object val)
  • 大于 >
  • 例: gt("age", 18)--->age > 18

2.4. ge

ge(R column, Object val)
ge(boolean condition, R column, Object val)
  • 大于等于 >=
  • 例: ge("age", 18)--->age >= 18

2.5. lt

lt(R column, Object val)
lt(boolean condition, R column, Object val)
  • 小于 <
  • 例: lt("age", 18)--->age < 18

2.6. le

le(R column, Object val)
le(boolean condition, R column, Object val)
  • 小于等于 <=
  • 例: le("age", 18)--->age <= 18

2.7. between

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)
  • BETWEEN 值1 AND 值2
  • 例: between("age", 18, 30)--->age between 18 and 30

2.8. notBetween

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
  • NOT BETWEEN 值1 AND 值2
  • 例: notBetween("age", 18, 30)--->age not between 18 and 30

2.9. like

like(R column, Object val)
like(boolean condition, R column, Object val)
  • LIKE '%值%'
  • 例: like("name", "王")--->name like '%王%'

2.10. notLike

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
  • NOT LIKE '%值%'
  • 例: notLike("name", "王")--->name not like '%王%'

2.11. likeLeft

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
  • LIKE '%值'
  • 例: likeLeft("name", "王")--->name like '%王'

2.12. likeRight

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
  • LIKE '值%'
  • 例: likeRight("name", "王")--->name like '王%'

2.13. isNull

isNull(R column)
isNull(boolean condition, R column)
  • 字段 IS NULL
  • 例: isNull("name")--->name is null

2.14. isNotNull

isNotNull(R column)
isNotNull(boolean condition, R column)
  • 字段 IS NOT NULL
  • 例: isNotNull("name")--->name is not null

2.15. in

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
  • 字段 IN (value.get(0), value.get(1), ...)
  • 例: in("age",{1,2,3})--->age in (1,2,3)
in(R column, Object... values)
in(boolean condition, R column, Object... values)
  • 字段 IN (v0, v1, ...)
  • 例: in("age", 1, 2, 3)--->age in (1,2,3)

2.16. notIn

notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
  • 字段 IN (value.get(0), value.get(1), ...)
  • 例: notIn("age",{1,2,3})--->age not in (1,2,3)
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)
  • 字段 NOT IN (v0, v1, ...)
  • 例: notIn("age", 1, 2, 3)--->age not in (1,2,3)

2.17. inSql

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)
  • 字段 IN ( sql语句 )
  • 例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
  • 例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)

2.18. notInSql

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)
  • 字段 NOT IN ( sql语句 )
  • 例: notInSql("age", "1,2,3,4,5,6")--->age not in (1,2,3,4,5,6)
  • 例: notInSql("id", "select id from table where id < 3")--->age not in (select id from table where id < 3)

2.19. groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)
  • 分组:GROUP BY 字段, ...
  • 例: groupBy("id", "name")--->group by id,name

2.20. orderByAsc

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
  • 排序:ORDER BY 字段, ... ASC
  • 例: orderByAsc("id", "name")--->order by id ASC,name ASC

2.21. orderByDesc

orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)
  • 排序:ORDER BY 字段, ... DESC
  • 例: orderByDesc("id", "name")--->order by id DESC,name DESC

2.22. orderBy

orderBy(boolean condition, boolean isAsc, R... columns)
  • 排序:ORDER BY 字段, ...
  • 例: orderBy(true, true, "id", "name")--->order by id ASC,name ASC

2.23. having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)
  • HAVING ( sql语句 )
  • 例: having("sum(age) > 10")--->having sum(age) > 10
  • 例: having("sum(age) > {0}", 11)--->having sum(age) > 11

2.24. or

or()
or(boolean condition)
  • 拼接 OR

注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

  • 例: eq("id",1).or().eq("name","老王")--->id = 1 or name = '老王'
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)
  • OR 嵌套
  • 例: or(i -> i.eq("name", "李白").ne("status", "活着"))--->or (name = '李白' and status <> '活着')

2.25. and

and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
  • AND 嵌套
  • 例: and(i -> i.eq("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')
nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)
  • 正常嵌套 不带 AND 或者 OR
  • 例: nested(i -> i.eq("name", "李白").ne("status", "活着"))--->(name = '李白' and status <> '活着')

2.26. apply

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)
  • 拼接 sql

注意事项:

该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!

  • 例: apply("id = 1")--->id = 1
  • 例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
  • 例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

2.27. last

last(String lastSql)
last(boolean condition, String lastSql)
  • 无视优化规则直接拼接到 sql 的最后

注意事项:

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

  • 例: last("limit 1")

2.28. exists

exists(String existsSql)
exists(boolean condition, String existsSql)
  • 拼接 EXISTS ( sql语句 )
  • 例: exists("select id from table where age = 1")--->exists (select id from table where age = 1)

2.29. notExists

notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)
  • 拼接 NOT EXISTS ( sql语句 )
  • 例: notExists("select id from table where age = 1")--->not exists (select id from table where age = 1)

3. 模糊查询

@Test
public void testLike() {

    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.like("user_name", "五");
    List<User> users = userMapper.selectList(wrapper);

    System.out.println("users = " + users);
}

4. 排序查询

@Test
public void testOrder() {


    QueryWrapper<User> wrapper = new QueryWrapper<>();
    // wrapper.orderByAsc();
    wrapper.orderByDesc("age");
    List<User> users = userMapper.selectList(wrapper);

    System.out.println("users = " + users);
}

5. 逻辑查询

@Test
public void testOr() {

    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.eq("user_name", "赵六").or().eq("user_name","孙七");

    List<User> users = userMapper.selectList(wrapper);

    System.out.println("users = " + users);
}

SELECT id,user_name,email AS mail,age FROM user WHERE (user_name = ? OR user_name = ?)

6. Select字段

@Test
public void testSelect() {

    QueryWrapper<User> wrapper = new QueryWrapper<>();

    // select 指定查询字段
    wrapper.eq("user_name", "赵六").or().eq("user_name", "孙七").select("id", "user_name");

    List<User> users = userMapper.selectList(wrapper);

    System.out.println("users = " + users);
}
JDBC Connection [com.mysql.jdbc.JDBC4Connection@14be750c] will not be managed by Spring
==>  Preparing: SELECT id,user_name FROM user WHERE (user_name = ? OR user_name = ?) 
==> Parameters: 赵六(String), 孙七(String)
<==    Columns: id, user_name
<==        Row: 4, 赵六
<==        Row: 5, 孙七
<==      Total: 2
本节阅读完毕!
二维码图片 扫描关注我们哟