自定义sql拼接——可作为通用历史表

DBC 1.3K 0

前言

温馨提示

我们可能在开发的时候遇到这样一种情况，需要对一张表做一个历史表，如果我们每张历史表都去生成对应的crud代码，那么我们的项目将会非常的臃肿，历史表新的字段并不多，几乎和原表一样，所以我们可以使用这种模式来解决，非常的解耦！
我们所有的历史表在后面加上_history即可全自动！

数据库

我们先看我们的数据库表结构

原表

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  `dev_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

原表的历史表

CREATE TABLE `tb_user_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  `dev_id` int(11) DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
  `modify_user` varchar(255) DEFAULT NULL COMMENT '修改用户',
  `modify_type` varchar(10) DEFAULT NULL COMMENT '修改类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
温馨提示

可以看到，我们的历史表只是比原表多出了3个字段：modify_time、modify_user、modify_type，那么我们就没有必要在为它生成对应的crud文件了。

一、我们需要在Mybatic-plus配置类中添加一些必要的代码

    @Bean
    public MyLogicSqlInjector myLogicSqlInjector() {
        return new MyLogicSqlInjector();
    }
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;


import java.util.List;

/**
 * @author DBC
 * @date 2022/6/8 10:45
 * @network dbc655.top
 */

public class MyLogicSqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass,tableInfo);

        methodList.add(new InsertHistory());
        methodList.add(new InsertHistoryBatch());

        return methodList;
    }
}

import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

import java.util.Objects;
import java.util.stream.Collectors;

public class InsertHistory extends AbstractMethod {


    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {

        String newPrefix = "entity.";
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        String sqlColumn = tableInfo.getKeyColumn() + ",\n" + tableInfo.getFieldList().stream()
                .map((i) -> i.getInsertSqlColumnMaybeIf(newPrefix))
                .filter(Objects::nonNull).collect(Collectors.joining("\n"));
        sqlColumn += "\nmodify_type,\nmodify_user,\nmodify_time,";

        String sqlProperty = SqlScriptUtils.safeParam(newPrefix + tableInfo.getKeyProperty()) + ",\n";
        sqlProperty += tableInfo.getFieldList().stream().map((i) -> i.getInsertSqlPropertyMaybeIf(newPrefix))
                .filter(Objects::nonNull).collect(Collectors.joining("\n"));

        sqlProperty += "\n#{historyEntity.modifyType},\n#{historyEntity.modifyUser},\n#{historyEntity.modifyTime},";
        String columnScript = SqlScriptUtils.convertTrim(sqlColumn, "(", ")", null, ",");
        String valuesScript = SqlScriptUtils.convertTrim(sqlProperty, "(", ")", null, ",");

        String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName() + "_history", columnScript, valuesScript);
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertHistory", sqlSource,
                keyGenerator, "hid", "hid");

    }
}

import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

import java.util.Objects;
import java.util.stream.Collectors;


public class InsertHistoryBatch extends AbstractMethod {


    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {

        String newPrefix = "item.";

        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        String sqlColumn = tableInfo.getKeyColumn() + ",\n";
        sqlColumn += tableInfo.getFieldList().stream().map(TableFieldInfo::getInsertSqlColumn)
                .filter(Objects::nonNull).collect(Collectors.joining("\n"));
        sqlColumn += "\nmodify_type,\nmodify_user,\nmodify_time,";

        String sqlProperty = SqlScriptUtils.safeParam(newPrefix + tableInfo.getKeyProperty()) + ",\n";
        sqlProperty += tableInfo.getFieldList().stream().map((i) -> i.getInsertSqlProperty(newPrefix))
                .filter(Objects::nonNull).collect(Collectors.joining("\n"));
        sqlProperty += "\n#{historyEntity.modifyType},\n#{historyEntity.modifyUser},\n#{historyEntity.modifyTime},";
        String columnScript = SqlScriptUtils.convertTrim(sqlColumn, "(", ")", null, ",");

        sqlProperty = SqlScriptUtils.convertTrim(sqlProperty, "(", ")", null, ",");

        String valuesScript = SqlScriptUtils.convertForeach(sqlProperty, "list", "index", "item", ",");

        String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName() + "_history", columnScript, valuesScript);
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertHistoryBatch", sqlSource,
                keyGenerator, "hid", "hid");

    }

}

二、我们只需要改造一下我们的Mapper即可

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author DBC
 * @since 2021-11-05
 */
@Mapper
public interface TbUserMapper extends HistoryMapper<TbUserDO> {


     Page<UserPageVO> selectuserlistpage(Page<UserPageVO> tPage, int id);

     @Select("select * from tb_user u left join tb_dev d on u.dev_id = d.id where u.id = #{id}")
     Page<UserPageVO> selectuserlistpage2(Page<UserPageVO> tPage, int id);

    int xinzenglinshibiao(String name);
}

关键位置如下

自定义sql拼接——可作为通用历史表插图

三、新增HistoryMapper类

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.example.test05.demo.model.HistoryEntity;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

/**
 * @author DBC
 * @date 2022/6/8 10:28
 * @network dbc655.top
 */

public interface HistoryMapper<T> extends BaseMapper<T> {

    int insertHistory(@Param("entity")T entity, @Param("historyEntity") HistoryEntity historyEntity);

    int insertHistoryBatch(@Param("list") List<T> entity, @Param("historyEntity") HistoryEntity historyEntity);
}

四、一些需要用到的类

/**
 * @author DBC
 * @date 2022/6/8 10:29
 * @network dbc655.top
 */

import lombok.Data;

import java.util.Date;
@Data
public class HistoryEntity {

    public HistoryEntity(String modifyType){
        this.modifyType = modifyType;

        this.modifyUser = 666L;

        modifyTime = new Date();
    }

    /**
     * 操作类型
     */
    private String modifyType;

    /**
     * 修改时间
     */
    private Date modifyTime;

    /**
     * 操作用户
     */
    private Long modifyUser;
}

/**
 * @author DBC
 * @date 2022/6/8 10:36
 * @network dbc655.top
 */

public enum OperateEnum {

    INSERT("新增"), DELETE("删除"), MODIFY("修改");

    OperateEnum(String operate) {
        this.operate = operate;
    }

    /**
     * 操作
     */
    private String operate;

    public String getOperate() {
        return operate;
    }

    public void setOperate(String operate) {
        this.operate = operate;
    }
}

简单的工厂

import com.example.test05.demo.model.HistoryEntity;
import com.example.test05.demo.model.OperateEnum;

/**
 * @author DBC
 * @date 2022/6/8 10:36
 * @network dbc655.top
 */

public class OperateFactory {

    /**
     * 创建相应实体
     * @param operateEnum
     * @return
     */
    public static HistoryEntity create(OperateEnum operateEnum) {
        return new HistoryEntity(operateEnum.getOperate());
    }

}

五、大功告成

	@Test
	public void Test5() {
		TbUserDO tbUserDO = new TbUserDO();
		tbUserDO.setId(1);
		tbUserDO.setAge("20");
		tbUserDO.setName("dbc");


		tbUserMapper.insertHistory(tbUserDO, OperateFactory.create(OperateEnum.INSERT));
		System.out.println(tbUserDO);
	}

即可实现相应的效果

自定义sql拼接——可作为通用历史表插图2

本文作者为DBC，转载请注明。

发表评论 取消回复
表情 图片 链接 代码

分享