条件构造器:
条件构造器是Mybatis-Plus中的一种核心功能,作用是使我们在单表的增删改查等所有操作,不需要自己写sql语句。
// 将Java中的类和数据库中的表明确对应,并且按照驼峰法自动解析
@TableName("student")
使用条件构造器进行查询
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);
}
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)