MyBatis-Plus——条件构造器

时间:2025-02-12 08:42:30

条件构造器:

条件构造器是Mybatis-Plus中的一种核心功能,作用是使我们在单表的增删改查等所有操作,不需要自己写sql语句。

// 将Java中的类和数据库中的表明确对应,并且按照驼峰法自动解析
@TableName("student")  

使用条件构造器进行查询

  1. Java的传统写法

public AjaxResult selectStudentByNumber(@RequestBody Student student) {
    // 1. 定义条件构造器
    // 要使用哪个表进行查询,那么<>内就填写哪个类
    QueryWrapper<Student> qw = new QueryWrapper();
    // 2. 构造条件构造器
    // 第一个变量是书库据表中的字段,第二个变量是sql语句查询中要查找的值
    ("stu_number", ());
    // 3. 查询
    // 在result内,就进行完了查询
    List<Student> result = (qw);
    // 4. 返回
    return (result);
}
  1. lambda表达式方式

// 定义条件构造器
LambdaQueryWrapper<Student> lqw = new LambdaQueryWrapper<Student>().eq(Student::getStuNumber,stuNumber);
List<Student> result = (lqw);
return (result);
传统的方法中,如果数据库表的字段不小心写错,就会报错,不好发现。
建议使用lambda表达式方法,不容易出错。

用法:

AbstractWrapper
说明:
QueryWrapper(LambdaQueryWrapper) 和UpdateWrapper(LambdaUpdateWrapper) 的父类用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件
注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为

allEq:全部等于
// params : key为数据库字段名,value为字段值
allEq(Map<R, V> params)
举例:
allEq({id:1,name:"某某",age:null})--->id = 1 and name = '某某' and age is null

代码演示:

/**
 * SQL:SELECT * FROM student WHERE (stu_name = ? AND id = ?)
 */
public AjaxResult selectStudent(@RequestBody Student student) {
        QueryWrapper qw = new QueryWrapper();
        Map map = new HashMap<>();
        ("id","2");
        ("stu_name","某某");
        (map);
        List<User> list = (qw);
        return (list);
}

eq:等于 =
eq(R column, Object val)
举例:
eq("name", "某某")--->name = '某某'

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  id = ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("id", ());
        List<User> list = (qw);
        return (list);
}

nq:不等于 <>
nq(R column, Object val)
举例:
nq("name", "某某")--->name <> '某某'

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  id <> ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("id", ());
        List<User> list = (qw);
        return (list);
}
gt:大于 >
gt(R column, Object val)
举例:
gt("age", 18)---> age > 18

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  age > ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("age", ());
        List<User> list = (qw);
        return (list);
}

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

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  age >= ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("age", ());
        List<User> list = (qw);
        return (list);
}

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

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  age < ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("age", ());
        List<User> list = (qw);
        return (list);
}

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

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  age <= ?
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("age", ());
        List<User> list = (qw);
        return (list);
}

between:BETWEEN 值1 AND 值2(闭区间)
between(R column, Object val1, Object val2)
举例:
between("age", 18, 30)--->age between 18 and 30

代码演示:

/**
 * SQL:SELECT * FROM student WHERE  (age BETWEEN ? AND ?)
 */
public AjaxResult selectStudent(@RequestBody Student student) {        
        QueryWrapper qw = new QueryWrapper();
        ("age", 18, 25);
        List<User> list = (qw);
        return (list);
}

notBetween:BETWEEN 值1 AND 值2(闭区间)
notBetween(R column, Object val1, Object val2)
举例:
notBetween("age", 18, 30)--->age not between 18 and 30

like:like '%值%'
like(R column, Object val)
举例:
like("name", "王")--->name like '%王%'

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

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

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

notLikeLeft:NOT LIKE '%值'
notLikeLeft(R column, Object val)
举例:
notLikeLeft("name", "王")--->name not like '%王'

notLikeRight:NOT LIKE '值%'
notLikeRight(R column, Object val)
举例:
notLikeRight("name", "王")--->name not like '王%'

isNull:字段 IS NULL
isNull(R column)
举例:
isNull("name")--->name is null

isNotNull:字段 IS NOT NULL
sNotNull(R column)
举例:
isNotNull("name")--->name is not null

in:
  • 字段 IN ((0), (1), ...)

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

in(R column, Object... values)
举例:
in("age", 1, 2, 3)--->age in (1,2,3)

notIn:
  • 字段 NOT IN ((0), (1), ...)

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

notIn(R column, Object... values)
举例:
notIn("age", 1, 2, 3)--->age not in (1,2,3)

inSql:字段 IN ( sql语句 )
inSql(R column, String inValue)
举例:
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)

notInSql:字段 NOT IN ( sql语句 )
notInSql(R column, String inValue)
举例:
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")--->id not in (select id from table where id < 3)

groupBy:
  • 分组:GROUP BY 字段, ...

groupBy(R... columns)
举例:
groupBy("id", "name")--->group by id,name

orderByAsc:
  • 排序:ORDER BY 字段, ... ASC

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

orderByDesc(R... columns)
举例:
orderByDesc("id", "name")--->order by id DESC,name DESC

orderBy:
  • 排序:ORDER BY 字段, ...

orderBy(boolean condition, boolean isAsc, R... columns)
举例:
orderBy(true, true, "id", "name")--->order by id ASC,name ASC

having:
having(String sqlHaving, Object... params)
举例:
having("sum(age) > 10")--->having sum(age) > 10
having("sum(age) > {0}", 11)--->having sum(age) > 11

func
  • func 方法(主要方便在出现if...else下调用不同方法能不断链)

func(Consumer<Children> consumer)
举例:
func(i -> if(true) {("id", 1)} else {("id", 1)})

or:
  • 拼接

or()

注意事项:

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

举例:
eq("id",1).or().eq("name","老王")--->id = 1 or name = '老王'
  • 嵌套

or(Consumer<Param> consumer)
举例:
or(i -> ("name", "李白").ne("status", "活着"))--->or (name = '李白' and status <> '活着')

and:嵌套
and(Consumer<Param> consumer)
举例:
and(i -> ("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')

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

apply:拼接sql
apply(String applySql, Object... params)

注意事项:

该方法可用于数据库函数 动态入参的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'")

last:无视优化规则直接拼接到 sql 的最后
last(String lastSql)

注意事项:

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

举例:
last("limit 1")

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

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