1. 场景需求

接上回如果mybatis-plus需要物理删除的时候，之前发生过一个场景，不小心将客户的数据删除了且回滚非常麻烦，为了防止下次再次出现这种问题，一种是通过binlog将delete语句转成insert，但是如果要将这个功能做成需求，让交付进行操作，那么用binlog就比较麻烦。那么可否用java就进行该操作？

2. 功能实现

1.写一个SqlInstanceUtils工具类

public class SqlInstanceUtils {
    /**
     * 生成插入语句(insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...))
     *
     * @param t 有数据的实体
     */
    public static <T> String getInsertSql(T t) {
        String sql;
        String tableName = t.getClass().getAnnotation(TableName.class).value();
        Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), true);
        StringBuilder topHalf = new StringBuilder("insert into " + tableName + " (");
        StringBuilder afterHalf = new StringBuilder("values (");
        for (Field field : fields) {
            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }
            TableField tableField = field.getAnnotation(TableField.class);
            if (tableField != null && !tableField.exist()) {
                continue;
            }
            topHalf.append(tableField != null && StrUtil.isNotEmpty(tableField.value()) ? tableField.value() : StrUtil.toUnderlineCase(field.getName())).append(",");
            if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
                afterHalf.append("'").append(ReflectUtil.getFieldValue(t, field.getName())).append("',");
            } else {
                afterHalf.append(ReflectUtil.getFieldValue(t, field.getName())).append(",");
            }
        }
        topHalf = new StringBuilder(StrUtil.removeSuffix(topHalf.toString(), ","));
        afterHalf = new StringBuilder(StrUtil.removeSuffix(afterHalf.toString(), ","));
        topHalf.append(")");
        afterHalf.append(");");
        sql = topHalf.toString() + afterHalf.toString();
        return sql;
    }

    /**
     * 生成插入语句(insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...),(value1,value2, ...))
     *
     * @param list 有数据的列表
     */
    public static <T> String getInsertBatchSql(List<T> list) {
        if (CollUtil.isEmpty(list)) {
            return "";
        }
        String sql;
        T t = list.get(0);
        String tableName = t.getClass().getAnnotation(TableName.class).value();
        Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), true);
        StringBuilder topHalf = new StringBuilder("insert into " + tableName + " (");
        StringBuilder afterHalf = new StringBuilder("values");
        for (Field field : fields) {
            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }
            TableField tableField = field.getAnnotation(TableField.class);
            if (tableField != null && !tableField.exist()) {
                continue;
            }
            topHalf.append(tableField != null && StrUtil.isNotEmpty(tableField.value()) ? tableField.value() : StrUtil.toUnderlineCase(field.getName())).append(",");
        }
        afterHalf.append(list.stream().map(s -> {
            StringBuilder stringBuilder = new StringBuilder(" (");
            for (Field field : fields) {
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                TableField tableField = field.getAnnotation(TableField.class);
                if (tableField != null && !tableField.exist()) {
                    continue;
                }
                Object fieldValue = ReflectUtil.getFieldValue(s, field.getName());
                if (fieldValue instanceof String || fieldValue instanceof LocalDateTime || fieldValue instanceof LocalDate || fieldValue instanceof LocalTime) {
                    stringBuilder.append("'").append(ReflectUtil.getFieldValue(s, field.getName())).append("',");
                } else {
                    stringBuilder.append(ReflectUtil.getFieldValue(s, field.getName())).append(",");
                }
            }
            stringBuilder = new StringBuilder(StrUtil.removeSuffix(stringBuilder.toString(), ","));
            return stringBuilder.append(")");
        }).collect(Collectors.joining(",")));
        topHalf = new StringBuilder(StrUtil.removeSuffix(topHalf.toString(), ","));
        afterHalf = new StringBuilder(StrUtil.removeSuffix(afterHalf.toString(), ","));
        topHalf.append(")");
        afterHalf.append(";");
        sql = topHalf.toString() + afterHalf.toString();
        return sql;
    }
}

2. 如果物理删除每次都需要先查询出来的话使用就比较麻烦，所以我将查询的逻辑封装到一个BaseMapperX接口中，该接口继承了BaseMapper,并且新增了两个default方法，只需要传入StringBuilder对象即可

public interface BaseMapperX<T> extends BaseMapper<T> { 
    int deleteAbsoluteById(Serializable id); 
    int deleteAbsoluteById(T entity); 
    int deleteAbsolute(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper); 
    int deleteAbsoluteBatchByIds(@Param(Constants.COLL) Collection<?> idList); 

    default void deleteAbsolute(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper, StringBuilder sb){
        List<T> ts = selectList(queryWrapper);
        sb.append(SqlInstanceUtils.getInsertBatchSql(ts)).append("\n");
        deleteAbsolute(queryWrapper);
    }

    @SuppressWarnings("unchecked")
    default void deleteAbsoluteBatchByIdList(Collection<?> idList, StringBuilder sb) {
        if (CollUtil.isNotEmpty(idList)) {
            List<T> ts = selectBatchIds((Collection<? extends Serializable>) idList);
            sb.append(SqlInstanceUtils.getInsertBatchSql(ts)).append("\n");
            deleteAbsoluteBatchByIds(idList);
        }
    }
}

3. 最后处理StringBuilder，写入到File中，或者上传到oss即可。

File file = null;
try {
    file = FileUtil.file(ResourceUtils.getURL("") + DateUtil.formatLocalDateTime(LocalDateTime.now()));
} catch (FileNotFoundException e) {
    throw new RuntimeException(e);
}
FileUtil.writeString(sb.toString(), file, Charset.defaultCharset());

恢复操作

@Autowired
private JdbcTemplate jdbcTemplate;

@Test
public void recovery() {
    File file = null;
    String fileName = "";
    try {
        file = FileUtil.file(ResourceUtils.getURL("") + fileName);
    } catch (FileNotFoundException e) {
        throw new RuntimeException(e);
    }
    String s = FileUtil.readString(file, Charset.defaultCharset());
    jdbcTemplate.execute(s);
}