【MyBatis-plus】条件构造器详解，mysql 索引原理及 btree
BETWEEN 值 1 AND 值 2
例:
between("age", 18, 30)—>age between 18 and 30
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
like
like(R column, Object val)
like(boolean condition, R column, Object val)
LIKE ‘%值 %’
例:
like("name", "王")—>name like '%王%'
notLike
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
NOT LIKE ‘%值 %’
例:
notLike("name", "王")—>name not like '%王%'
likeLeft
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
LIKE ‘%值’
例:
likeLeft("name", "王")—>name like '%王'
likeRight
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
LIKE ‘值 %’
例:
likeRight("name", "王")—>name like '王%'
isNull
isNull(R column)
isNull(boolean condition, R column)
字段 IS NULL
例:
isNull("name")—>name is null
isNotNull
isNotNull(R column)
isNotNull(boolean condition, R column)
字段 IS NOT NULL
例:
isNotNull("name")—>name is not null
in
字段 IN (value.get(0), value.get(1), …)
in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
例
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)
notIn
notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
字段 NOT 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)
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)
notInSql
notInSql(R co
lumn, 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")—>id not in (select id from table where id < 3)
groupBy
groupBy(R... columns)
groupBy(boolean condition, R... columns)
分组：GROUP BY 字段, …
例:
groupBy("id", "name")—>group by id,name
orderByAsc
排序：ORDER BY 字段, … ASC
orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
实例
orderByAsc("id", "name")—>order by id ASC,name ASC
orderByDesc
orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)
排序：ORDER BY 字段, … DESC
例:
orderByDesc("id", "name")—>order by id DESC,name DESC
orderBy
orderBy(boolean condition, boolean isAsc, R... columns)
排序：ORDER BY 字段, …
例:
orderBy(true, true, "id", "name")—>order by id ASC,name ASC
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
func
func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)
func 方法(主要方便在出现 if…else 下调用不同方法能不断链)
例:
func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or
or()
or(boolean condition)
拼接 OR
::: tip 注意事项:
主动调用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 <> '活着')
and
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
AND 嵌套
例:
and(i -> i.eq("name", "李白").ne("status", "活着"))—>and (name = '李白' and status <> '活着')
nested
nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)
正常嵌套 不带 AND 或者 OR
例:
nested(i -> i.eq("name", "李白").ne("status", "活着"))—>(name = '李白' and status <> '活着')
apply-拼接 SQL
apply(String applySql, Object... params)
apply(boolean condition, 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
last(String lastSql)
last(boolean condition, String lastSql)
无视优化规则直接拼接到 sql 的最后
::: tip 注意事项:
只能调用一次,多次调用以最后一次为准
有 sql 注入的风险,请谨慎使用
:::
例:
last("limit 1")
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)
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)
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取。
select
select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)
设置查询字段
::: tip 说明:
以上方法分为两类.
第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值!
这两类方法重复调用以最后一次为准
:::
例:
select("id", "name", "age")例:
select(i -> i.getProperty().startsWith("test"))
::: tip 说明:
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取!
:::
set
set(String column, Object val)
set(boolean condition, String column, Object val)
SQL SET 字段
例:
set("name", "老李头")例:
set("name", "")—>数据库字段值变为空字符串例:
set("name", null)—>数据库字段值变为null
setSql
setSql(String sql)
设置 SET 部分 SQL
例:
setSql("name = '老李头'")
lambda
获取
LambdaWrapper
在QueryWrapper中是获取LambdaQueryWrapper
在UpdateWrapper中是获取LambdaUpdateWrapper
::: tip 注意事项:
需要mybatis-plus版本 >= 3.0.7
param 参数名要么叫ew,要么加上注解@Param(Constants.WRAPPER)
使用${ew.customSqlSegment}
不支持 Wrapper 内的 entity 生成 where 语句
:::
kotlin 持久化对象定义最佳实践
由于kotlin相比于java多了数据对象（data class)，在未说明情况下可能会混用。建议按照以下形式定义持久化对象
@TableName("sys_user")
class User {
@TableId(type = IdType.AUTO)
var id: Int? = null
@TableField("username")
var name: String? = null
var roleId: Int? = null
}
注意：这里的TableId及TableField并非必要，只是为了展示Mybatis-Plus中的annotation使用
这里所有成员都需要定义为可空类型（?），并赋予null的初始值，方便我们在以下场景中使用（类似 java 中的updateSelective）
val wrapper = KtUpdateWrapper(User::class.java).eq(User::id, 2)
val newRecord = User()
newRecord.name = "newName"
userMapper!!.update(newRecord, wrapper)
不建议使用data class及全参数构造方法，这样我们会写很多不必要的null来构造一个空对象
用注解
@Select("select * from mysql_data ${ew.customSqlSegment}")
List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);
用 XML
List<MysqlData> getAll(Wrapper ew);
<select id="getAll" resultType="MysqlData">
SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>
链式调用 lambda 式
// 区分:
// 链式调用 普通
UpdateChainWrapper<T> update();
// 链式调用 lambda 式。注意：不支持 Kotlin
LambdaUpdateChainWrapper<T> lambdaUpdate();
评论