场景
mp自带的update会将为null的列过滤掉不更新,即时有几种策略也满足不了场景,有时候我们需要将该列更新为null值,但又不能更改总体策略。
实现
扩展BaseMapper中的方法,参照作者提供InsertBatchSomeColumn扩展样例,简单写了一个UpdateFieldIncludeNull扩展,自己拼接update语句。代码是Kotlin版本,Java原理类似
- 创建UpdateFieldIncludeNull类,源代码:
class UpdateFieldIncludeNull(private val predicate: ((t: TableFieldInfo) -> Boolean)? = null): AbstractMethod("updateFieldIncludeNull") {
override fun injectMappedStatement(
mapperClass: Class<*>,
modelClass: Class<*>,
tableInfo: TableInfo
): MappedStatement {
// 拼接sql
val sqlMethod = SqlMethod.UPDATE
val sql = String.format(
sqlMethod.sql, tableInfo.tableName,
sqlSet(tableInfo),
sqlWhereEntityWrapper(true, tableInfo), sqlComment()
)
val sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass)
// 使用update
return this.addUpdateMappedStatement(mapperClass, modelClass, methodName, sqlSource)
}
/**
* 自定义 set 语句sql
*/
private fun sqlSet(
table: TableInfo
): String {
var fieldList = table.fieldList
// 过滤忽略字段
if(predicate != null) {
fieldList = fieldList.stream().filter(predicate).collect(Collectors.toList())
}
val sqlScript = fieldList.joinToString(NEWLINE) {
field -> getIfSet(field)
} + NEWLINE
return SqlScriptUtils.convertSet(sqlScript)
}
/**
* 因为有忽略列表,忽略不需要更新的字段,所以拼接一层条件
*/
private fun getIfSet(field: TableFieldInfo): String {
// 得到 column=#{xxx},
val sqlSet = field.column + EQUALS + SqlScriptUtils.safeParam(ENTITY_DOT + field.el) + COMMA
// 拼上 <if test="!list.contains('type')">type=#{et.type},</if> list为忽略列表
return SqlScriptUtils.convertIf(sqlSet, String.format("!list.contains('%s')", field.property), false)
}
}
- 添加method,创建一个类继承DefaultSqlInjector, 源代码:
class SqlInjectorX : DefaultSqlInjector() {
override fun getMethodList(mapperClass: Class<*>?, tableInfo: TableInfo?): MutableList<AbstractMethod> {
val methodList = super.getMethodList(mapperClass, tableInfo)
// 添加批量插入
methodList.add(InsertBatchSomeColumn { getFilterUpdateFieldIncludeNull(it) })
// 添加包含null内容的更新
methodList.add(UpdateFieldIncludeNull { getFilterUpdateFieldIncludeNull(it) })
return methodList
}
// 默认忽略掉的字段,包含逻辑删除和自动填充的字段
private fun getFilterUpdateFieldIncludeNull(tbInfo: TableFieldInfo) : Boolean {
return !tbInfo.isLogicDelete
&& tbInfo.fieldFill != FieldFill.INSERT_UPDATE
&& tbInfo.fieldFill != FieldFill.INSERT
}
}
- 创建自己的BaseMapper类继承mp的BaseMapper类,添加咋们扩展的method,本人还另外封装了一层一些常用的方法。源代码如下,其中KtQueryWrapperX也是封装了一层常用的方法,如字段值不为空时拼接条件。
interface BaseMapperX<T> : BaseMapper<T> {
fun selectOneByField(clazz: Class<*>, column: KProperty<*>, value: Any) : T? {
return selectOne(KtQueryWrapperX(clazz).eqX(column, value).lastLimit1() as Wrapper<T>)
}
fun selectListByField(clazz: Class<*>, column: KProperty<*>, value: Any) : List<T> {
return selectList(KtQueryWrapper(clazz).eq(column, value) as Wrapper<T>)
}
fun selectListInField(clazz: Class<*>, column: KProperty<*>, value: List<Any>) : List<T> {
if(value.isEmpty()) return emptyList()
return selectList(KtQueryWrapper(clazz).`in`(column, value) as Wrapper<T>)
}
fun updateByField(entity: T, column: KProperty<*>, value: Any) {
update(entity, KtUpdateWrapper(entity!!::class.java).eq(column, value) as Wrapper<T>)
}
fun deleteByField(clazz: Class<*>, column: KProperty<*>, value: Any) {
delete(KtQueryWrapper(clazz).eq(column, value) as Wrapper<T>)
}
/**
* 批量插入方法,分割列表
*/
fun batchInsertByBatchSize(entityList: List<T>, batchSize: Int = 100) {
if(entityList.isEmpty()) return
ListUtil.partition(entityList, batchSize).forEach {
this.insertBatchSomeColumn(it)
}
}
/**
* 批量插入方法,未分批次处理,有sql超长风险
*/
fun insertBatchSomeColumn(entityList: Collection<T>)
/**
* 更新所有属性包含为null的值
* 基础忽略字段有 主键:id, 逻辑删除:deleted, 自动填充:createDate,modifyDate,createUser,modifyUser
* @param entity 更新的新实体
* @param updateWrapper 条件
* @param ignoreColumn 除了基础忽略字段,需要额外忽略的列
*/
fun updateFieldIncludeNull(@Param(Constants.ENTITY) entity : T,
@Param(Constants.WRAPPER) updateWrapper: Wrapper<T>,
@Param(Constants.LIST) ignoreColumn: List<String> = emptyList()) : Int
/**
* 判断exist=false 表示不是数据库字段
*/
private fun isExistField(field: Field): Boolean {
val tableFieldAnnotation = field.annotations.firstOrNull { it.annotationClass == TableField::class }
return if(tableFieldAnnotation == null) true else (tableFieldAnnotation as TableField).exist
}
/**
* 获取逆序排序后的第一条
*/
fun queryLastOneByFieldOrderByDescField(clazz: Class<*>, column: KProperty<*>, value: Any, orderDescColumn: KProperty<*>): T? {
return selectOne(KtQueryWrapperX(clazz).eqX(column, value).orderByDescX(orderDescColumn).lastLimit1() as Wrapper<T>)
}
/**
* 在Mapper层封装mybatis plus分页
*/
fun selectPage(pageParam : PageParam<*>, @Param(Constants.WRAPPER) queryWrapper: Wrapper<T>) : PageResult<T> {
val mpPage = Page<T>(pageParam.pageNo, pageParam.pageSize)
selectPage(mpPage, queryWrapper)
// 转换返回
return PageResult(pageParam.pageNo, pageParam.pageSize, mpPage.total, mpPage.records)
}
}
- KtQueryWrapperX源码:
class KtQueryWrapperX<T : Any>(entityClass: Class<T>) : KtQueryWrapper<T>(entityClass) {
/**
* 如果有值,就拼上like
*/
fun likeIfPresent(column: KProperty<*>, value: String?): KtQueryWrapperX<T> {
return if (value.isNullOrEmpty()) {
this
} else {
like(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上in
*/
fun inIfPresent(column: KProperty<*>, values: Collection<*>?): KtQueryWrapperX<T> {
return if (values.isNullOrEmpty()) {
this
} else {
`in`(column, values) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上eq
*/
fun eqIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
eq(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上ne
*/
fun neIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
ne(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上gt
*/
fun gtIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
gt(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上ge 大于等于
*/
fun geIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
ge(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上lt
*/
fun ltIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
lt(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上le 小于等于
*/
fun leIfPresent(column: KProperty<*>, value: Any?): KtQueryWrapperX<T> {
return if (value == null) {
this
} else {
le(column, value) as KtQueryWrapperX
}
}
/**
* 如果有值,就拼上between
*/
fun betweenIfPresent(column: KProperty<*>, value1: Any?, value2: Any?): KtQueryWrapperX<T> {
if (value1 != null && value2 != null) {
return between(column, value1, value2) as KtQueryWrapperX
}
if (value1 != null) {
return ge(column, value1) as KtQueryWrapperX
}
if (value2 != null) {
return le(column, value1) as KtQueryWrapperX
}
return this
}
/**
* 如果有值,就拼上add
*/
fun andIfPresent(value: String?, func: (KtQueryWrapper<T>) -> Unit): KtQueryWrapperX<T> {
if(!value.isNullOrEmpty()) {
return and(true, func) as KtQueryWrapperX
}
return this
}
/**
* 如果有值,就拼上or
*/
fun orIfPresent(value: String?, func: (KtQueryWrapper<T>) -> Unit): KtQueryWrapperX<T> {
if(!value.isNullOrEmpty()) {
return or(true, func) as KtQueryWrapperX
}
return this
}
// 重写父类 返回KtQueryWrapperX 进行链式调用 --------
// 添加方法方便链式调用
/**
* 只返回一条
*/
fun lastLimit1(): KtQueryWrapperX<T> {
return last("limit 1") as KtQueryWrapperX
}
/**
* 根据字段逆序
*/
fun orderByDescX(column: KProperty<*>): KtQueryWrapperX<T> {
return orderByDesc(column) as KtQueryWrapperX
}
/**
* 根据字段增序
*/
fun orderByAscX(column: KProperty<*>): KtQueryWrapperX<T> {
return orderByAsc(column) as KtQueryWrapperX
}
/**
* 字段相等
*/
fun eqX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return eq(column, value) as KtQueryWrapperX<T>
}
/**
* 字段大于
*/
fun gtX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return gt(column, value) as KtQueryWrapperX<T>
}
/**
* 字段大于等于
*/
fun geX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return ge(column, value) as KtQueryWrapperX<T>
}
/**
* 字段小于
*/
fun ltX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return lt(column, value) as KtQueryWrapperX<T>
}
/**
* 字段相小于等于
*/
fun leX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return le(column, value) as KtQueryWrapperX<T>
}
/**
* 字段不等于
*/
fun neX(column: KProperty<*>, value : Any?): KtQueryWrapperX<T> {
return ne(column, value) as KtQueryWrapperX<T>
}
/**
* 字段in
*/
fun inX(column: KProperty<*>, values: Collection<*>): KtQueryWrapperX<T> {
return `in`(column, values) as KtQueryWrapperX
}
/**
* 创建时间是今天的条件
*/
fun inToday(column: KProperty<*>): KtQueryWrapperX<T> {
return ge(column, LocalDate.now().toLocalDateTimeStartOfDay())
.le(column, LocalDate.now().toLocalDateTimeEndOfDay()) as KtQueryWrapperX<T>
}
}
- 自己的业务mapper继承BaseMapperX,就可以即用到BaseMapper提供的方法,也可以用到扩展的方法,如:
interface StDepartmentMapper : BaseMapperX<StDepartment> {
// 根据code更新部门信息,如果updateDept有属性为null会更新数据库为null, 忽略掉code,tenantId属性
fun updateAllFieldByCode(updateDept: StDepartment, code: String)
= updateFieldIncludeNull(updateDept, KtQueryWrapperX(StDepartment::class.java).eqX(StDepartment::code, code), listOf(StDepartment::code.name, StDepartment::tenantId.name))
// 普通更新,默认策略,会忽略掉updateDept中为null的字段
fun updateByCode(updateDept: StDepartment, deptCode: String) {
updateByField(updateDept, StDepartment::code, deptCode)
}
}
- 注入bean SqlInjectorX
@Bean
fun sqlInjectorX(): SqlInjectorX = SqlInjectorX()
撒花~