业务背景
在使用 @TableLogic (逻辑删)注解的基础上，提供一个全局方法，使得插入数据的时候，一旦发现有相同主键 Id 的记录就先删除再插入（系统搬家的时候遇到。因为系统搬家产生这个问题的场景：先导入搬家的数据，然后在目标系统删除该数据[假删除]，再次重复导入就会 SQL 物理主键冲突报错）~
解决方案
实现一个插入方法，在插入的时候，先删除该记录 Id 并且 del == 1（假删除标记），然后再插入！
实现思路
在玩 MP 的时候我们都知道 BaseMapper，那么优秀的框架一定会给我们扩展的机会，果真如此
1、【DBaseMapper】实现一个自定义 Mapper 继承 BaseMapper 来替代 BaseMapper 在 Dao 的继承上
import cn.hutool.core.util.StrUtil; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableLogic; import com.baomidou.mybatisplus.core.conditions.Wrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.toolkit.Constants; import org.apache.ibatis.annotations.Param; import java.lang.reflect.Field; import java.util.Objects; /** * @author Lux Sun * @date 2022/1/14 */ public interface DBaseMapper<T> extends BaseMapper<T> { Integer IS_DEL = 1; /** * 插入一条记录(解决主键冲突) * * @param entity */ default int insertWithPrimary(T entity) { // 主键 Id Field[] fields = entity.getClass().getDeclaredFields(); // 删除条件 QueryWrapper<T> qw = new QueryWrapper<>(); String fieldName; for (Field field : fields) { fieldName = field.getName(); // 装配逻辑删字段 == 1 if (Objects.nonNull(field.getAnnotation(TableLogic.class))) { qw.eq(StrUtil.toUnderlineCase(fieldName), IS_DEL); } // 装配主键 Id else if (Objects.nonNull(field.getAnnotation(TableId.class))) { try { field.setAccessible(true); qw.eq(StrUtil.toUnderlineCase(fieldName), field.get(entity)); } catch (IllegalAccessException e) { e.printStackTrace(); } } } this.deletePhysically(qw); return this.insert(entity); } /** * 根据 entity 条件, 删除记录(物理删除) * * @param wrapper 实体对象封装操作类(可以为 null) */ int deletePhysically(@Param(Constants.WRAPPER) Wrapper<T> wrapper); }
2、因为插入的方法 BaseMapper 本身自带，但是没有一个方法可以在 @TableLogic 注解的基础上删除，等下给几段官方源码你就明白了，因为 MyBatis 只要识别出 POJO 有该注解，就会对一系列必要地方进行特判
TableInfo.java
该类可以简单理解为和你的 POJO 有着千丝万缕的关系，言外之意你在 POJO 里的属性配置啥的都会在该类里更详细的体现出来
package com.baomidou.mybatisplus.core.metadata; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.KeySequence; import com.baomidou.mybatisplus.core.MybatisConfiguration; import com.baomidou.mybatisplus.core.toolkit.*; import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils; import lombok.AccessLevel; import lombok.Data; import lombok.Getter; import lombok.Setter; import lombok.experimental.Accessors; import org.apache.ibatis.mapping.ResultFlag; import org.apache.ibatis.mapping.ResultMap; import org.apache.ibatis.mapping.ResultMapping; import org.apache.ibatis.session.Configuration; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Objects; import java.util.function.Predicate; import static java.util.stream.Collectors.joining; /** * 数据库表反射信息 * * @author hubin * @since 2016-01-23 */ @Data @Setter(AccessLevel.PACKAGE) @Accessors(chain = true) public class TableInfo implements Constants { /** * 是否开启逻辑删除 */ @Setter(AccessLevel.NONE) private boolean logicDelete; /** * 设置逻辑删除 */ void setLogicDelete(boolean logicDelete) { if (logicDelete) { this.logicDelete = true; } } /** * 获取逻辑删除字段的 sql 脚本 * * @param startWithAnd 是否以 and 开头 * @param deleteValue 是否需要的是逻辑删除值 * @return sql 脚本 */ public String getLogicDeleteSql(boolean startWithAnd, boolean deleteValue) { if (logicDelete) { TableFieldInfo field = fieldList.stream().filter(TableFieldInfo::isLogicDelete).findFirst() .orElseThrow(() -> ExceptionUtils.mpe("can't find the logicFiled from table {%s}", tableName)); String logicDeleteSql = formatLogicDeleteSql(field, deleteValue); if (startWithAnd) { logicDeleteSql = " AND " + logicDeleteSql; } return logicDeleteSql; } return EMPTY; } // 省略其他代码... }
- Delete.java
- 因为我们参考的是该方法的代码来进行编码彻底删除的逻辑，所以我们先看下原先该方法的模样
/* * Copyright (c) 2011-2020, baomidou (jobob@qq.com). * <p> * Licensed under the Apache License, Version 2.0 (the "License"); you may not * use this file except in compliance with the License. You may obtain a copy of * the License at * <p> * https://www.apache.org/licenses/LICENSE-2.0 * <p> * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the * License for the specific language governing permissions and limitations under * the License. */ package com.baomidou.mybatisplus.core.injector.methods; import com.baomidou.mybatisplus.core.enums.SqlMethod; import com.baomidou.mybatisplus.core.injector.AbstractMethod; import com.baomidou.mybatisplus.core.metadata.TableInfo; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlSource; /** * 根据 entity 条件删除记录 * * @author hubin * @since 2018-04-06 */ public class Delete extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) { String sql; SqlMethod sqlMethod = SqlMethod.LOGIC_DELETE; // 使用 logicDelete 进行判断 if (tableInfo.isLogicDelete()) { sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), sqlLogicSet(tableInfo), sqlWhereEntityWrapper(true, tableInfo), sqlComment()); SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass); return addUpdateMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource); } else { sqlMethod = SqlMethod.DELETE; sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), sqlWhereEntityWrapper(true, tableInfo), sqlComment()); SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass); return this.addDeleteMappedStatement(mapperClass, getMethod(sqlMethod), sqlSource); } } }
AbstractMethod.java - sqlWhereEntityWrapper 函数中的 TableInfo.getLogicDeleteSql 函数
sqlWhereEntityWrapper 该函数是实现对 SQL 语句 WHERE 的生成，所以会涉及到 “DEL = ?” 的逻辑
package com.baomidou.mybatisplus.core.injector; import com.baomidou.mybatisplus.core.metadata.TableFieldInfo; import com.baomidou.mybatisplus.core.metadata.TableInfo; import com.baomidou.mybatisplus.core.toolkit.Constants; import com.baomidou.mybatisplus.core.toolkit.StringPool; import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils; import org.apache.ibatis.builder.MapperBuilderAssistant; import org.apache.ibatis.executor.keygen.KeyGenerator; import org.apache.ibatis.executor.keygen.NoKeyGenerator; import org.apache.ibatis.logging.Log; import org.apache.ibatis.logging.LogFactory; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlCommandType; import org.apache.ibatis.mapping.SqlSource; import org.apache.ibatis.mapping.StatementType; import org.apache.ibatis.scripting.LanguageDriver; import org.apache.ibatis.session.Configuration; import java.util.List; import java.util.function.Function; import java.util.function.Predicate; import java.util.stream.Stream; import static java.util.stream.Collectors.joining; /** * 抽象的注入方法类 * * @author hubin * @since 2018-04-06 */ public abstract class AbstractMethod implements Constants { /** * EntityWrapper方式获取select where * * @param newLine 是否提到下一行 * @param table 表信息 * @return String */ protected String sqlWhereEntityWrapper(boolean newLine, TableInfo table) { if (table.isLogicDelete()) { String sqlScript = table.getAllSqlWhere(true, true, WRAPPER_ENTITY_DOT); sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY), true); // 关键代码点 sqlScript += (NEWLINE + table.getLogicDeleteSql(true, false) + NEWLINE); String normalSqlScript = SqlScriptUtils.convertIf(String.format("AND ${%s}", WRAPPER_SQLSEGMENT), String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT, WRAPPER_NONEMPTYOFNORMAL), true); normalSqlScript += NEWLINE; normalSqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT), String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT, WRAPPER_EMPTYOFNORMAL), true); sqlScript += normalSqlScript; sqlScript = SqlScriptUtils.convertChoose(String.format("%s != null", WRAPPER), sqlScript, table.getLogicDeleteSql(false, false)); sqlScript = SqlScriptUtils.convertWhere(sqlScript); return newLine ? NEWLINE + sqlScript : sqlScript; } else { String sqlScript = table.getAllSqlWhere(false, true, WRAPPER_ENTITY_DOT); sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY), true); sqlScript += NEWLINE; sqlScript += SqlScriptUtils.convertIf(String.format(SqlScriptUtils.convertIf(" AND", String.format("%s and %s", WRAPPER_NONEMPTYOFENTITY, WRAPPER_NONEMPTYOFNORMAL), false) + " ${%s}", WRAPPER_SQLSEGMENT), String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT, WRAPPER_NONEMPTYOFWHERE), true); sqlScript = SqlScriptUtils.convertWhere(sqlScript) + NEWLINE; sqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT), String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT, WRAPPER_EMPTYOFWHERE), true); sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER), true); return newLine ? NEWLINE + sqlScript : sqlScript; } } // 省略其他代码... }
Ps：以上几段框架中的代码都能说明 @TableLogic 注解的重要性，但同样是我们的要解决的难点，废话不多说了，看下面我们的解决方案
