springboot整合mybatis-plus 打印数据库数据修改前后的日志(完整代码实现)

淡淡的烟草味﹌ 2024-03-23 11:42 8阅读 0赞

##### mybatis-plus 打印数据库数据修改前后的日志(完整代码实现) #####

> 前言：在生产中，通过打印日志排查问题的最好一个手段之一。springboot整合mybatisplus开发的项目是非常多，在我的一个文章中已经写清楚怎样在生产项目中打印sql日志了。文章链接：https://blog.csdn.net/qq798867485/article/details/129734277
> 
> 但是这样的日志打印还是有一定的缺点的，就是不管你是查询还是修改都会把sql打印出来，这样是很占项目资源的，很影响项目的性能的。还有一点就是对于修改的数据，你只能看到修改后的sql，无法得知修改前的数据是怎样，没法对比。基于这两点，在本文中对于mybatisplus的项目中，就优化为仅仅打印dml的sql日志，并且把修改前后的数据打印出来。下面就是全部完整代码的实现了。

###### 一、创建数据日志注解 ######

@Target(ElementType.TYPE)
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DataUpdateLog {
        
        String[] value() default {
        "update","delete"};//可选insert,update,delete值
    }

###### 二、数据日志拦截器 ######

@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {
        MappedStatement.class, Object.class})})
    public class DataLogInterceptor implements Interceptor {
        
    
        private static final Logger logger = LoggerFactory.getLogger("SYS_DATA_LOG");
        private static final String DATA_LOG_TABLE_NAME = "sys_data_log";
    
        @Autowired
        DataLogConfiguration dataLogConfiguration;
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
        
    
            try {
        
    
                //未启用数据日志则跳过
                if (!dataLogConfiguration.isEnabled()) {
        
                    return invocation.proceed();
                }
    
                MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
                BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
                Configuration configuration = mappedStatement.getConfiguration();
                String preparedSql = boundSql.getSql();
    
                //如果是新增操作且未启用新增日志，则跳过
                if (SqlCommandType.INSERT.equals(mappedStatement.getSqlCommandType()) && !dataLogConfiguration.isIncludeInsert()) {
        
                    return invocation.proceed();
                }
                //忽略数据日志表操作
                if (preparedSql.contains(DATA_LOG_TABLE_NAME)) {
        
                    return invocation.proceed();
                }
                //SQL语句
                String sql = DataLogUtils.getSql(configuration, boundSql);
                //表名
                Collection<String> tables = new TableNameParser(sql).tables();
                if (CollectionUtils.isEmpty(tables)) {
        //如果找不到表则跳过
                    return invocation.proceed();
                }
                String tableName = CollectionUtils.isNotEmpty(tables) ? tables.iterator().next() : "";//多个表取第一个表名
                //实体
                TableInfo tableInfo = TableInfoHelper.getTableInfos().stream().filter(t -> t.getTableName().equals(tableName)).findFirst().orElse(null);//取第一个实体
                if (tableInfo == null) {
        //如果找不到实体类则跳过
                    return invocation.proceed();
                }
                Class<?> entityType = tableInfo.getEntityType();
                if (entityType == null) {
        //如果找不到实体类则跳过
                    return invocation.proceed();
                }
                if (!entityType.isAnnotationPresent(DataUpdateLog.class)) {
        //如果实体没有数据日志注解则跳过
                    return invocation.proceed();
                }
    
                //保存日志(只处理使用Mybatis更新函数的数据)
                Object et = invocation.getArgs()[1];
                if (et instanceof Map) {
        
                    String key = "et";
                    String listKey = "collection";
                    Map map = (Map) et;
                    if (map.containsKey(key)) {
        
                        this.saveLog(mappedStatement.getSqlCommandType(), sql, tableInfo, map.get(key));
                    } else if (map.containsKey(listKey) && map.get(listKey) instanceof Collection) {
        
                        List<Object> list = (List<Object>) map.get(listKey);
                        for (Object o : list) {
        
                            this.saveLog(mappedStatement.getSqlCommandType(), sql, tableInfo, o);
                        }
                    }
                } else {
        
                    this.saveLog(mappedStatement.getSqlCommandType(), sql, tableInfo, et);
                }
    
    
            } catch (Exception e) {
        
                logger.warn("数据日志保存失败", e);
            }
    
            return invocation.proceed();
    
        }
    
        @Override
        public Object plugin(Object target) {
        
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) {
        
        }
    
    
        /**
         * 保存日志
         */
        private void saveLog(SqlCommandType sqlCommandType, String sql, TableInfo tableInfo, Object entity)  throws Exception{
        
            //日志内容
            DataLog dataLog = new DataLog();
            //操作类型
            dataLog.setType(sqlCommandType.name());
            //操作人
            String token = WebUtils.getToken();
            Long operatorId = 0L;
            String operatorName = "";
            if (StringUtils.isNotEmpty(token)) {
        
                operatorId = WebUtils.getId();
                operatorName = WebUtils.getName();
            }
            dataLog.setOperatorId(Optional.of(operatorId).orElse(0L));
            dataLog.setOperatorName(Optional.ofNullable(operatorName).orElse(""));
            dataLog.setCreateTime(LocalDateTime.now());
            //类名和方法名
            String[] classAndMethod = DataLogUtils.getClassAndMethod();
            dataLog.setClassName(classAndMethod[0]);
            dataLog.setMethodName(classAndMethod[1]);
            //SQL语句
            dataLog.setStatement(sql);
            //表名
            dataLog.setTableName(tableInfo.getTableName());
            //实体名
            dataLog.setEntityName(tableInfo.getEntityType().getSimpleName());
            //比较修改前后数据
            DataCompareResult dataCompareResult = DataLogUtils.getDataCompare(sqlCommandType, sql, tableInfo, entity);
            //主键
            dataLog.setTableId(Optional.ofNullable(dataCompareResult.getId()).map(String::valueOf).orElse(""));
            //数据变动
            dataLog.setDataChange(dataCompareResult.getDataChange().size() > 0 ? JsonUtils.toString(dataCompareResult.getDataChange()) : "");
            //写日志文件
            String requestUrl = WebUtils.getRequestUrl();
            requestUrl = new URL(requestUrl).getPath();
            logger.info("request url: {} ,sql update: {}", requestUrl,dataLog);
            //保存日志
            //DataLogServiceImpl sqlLogService = SpringUtils.getBean(DataLogServiceImpl.class);
            //sqlLogService.addOrEdit(dataLog);
    
        }
    
    }

数据比较

public class DataCompareResult {
        
    
        private Long id;
        private LinkedHashMap<String,Object[]> dataChange;
    
        public Long getId() {
        
            return id;
        }
    
        public void setId(Long id) {
        
            this.id = id;
        }
    
        public LinkedHashMap<String, Object[]> getDataChange() {
        
            return dataChange;
        }
    
        public void setDataChange(LinkedHashMap<String, Object[]> dataChange) {
        
            this.dataChange = dataChange;
        }
    
    }

数据日志对象

public class DataLog implements Serializable {
        
    
        private static final long serialVersionUID = 1L;
    
        @ApiModelProperty(value = "操作类型")
        private String type;
    
        @ApiModelProperty(value = "操作人id")
        private Long operatorId;
    
        @ApiModelProperty(value = "操作人名称")
        private String operatorName;
    
        @ApiModelProperty(value = "类名称")
        private String className;
    
        @ApiModelProperty(value = "方法名称")
        private String methodName;
    
        @ApiModelProperty(value = "实体名称")
        private String entityName;
    
        @ApiModelProperty(value = "表名称")
        private String tableName;
    
        @ApiModelProperty(value = "表主键值")
        private String tableId;
    
        @ApiModelProperty(value = "创建时间")
        @TableField(fill = FieldFill.INSERT)
        private LocalDateTime createTime;
    
        @ApiModelProperty(value = "数据变化")
        private String dataChange;
    
        @ApiModelProperty(value = "查询语句")
        private String statement;
    
    
    }

数据日志配置

@Configuration
    @ConfigurationProperties(prefix = "data-log")
    public class DataLogConfiguration {
        
    
        private boolean enabled=true;//是否开启全部实体操作日志
        private boolean includeInsert=false;//是否包含插入日志
    
        public boolean isEnabled() {
        
            return enabled;
        }
    
        public void setEnabled(boolean enabled) {
        
            this.enabled = enabled;
        }
    
        public boolean isIncludeInsert() {
        
            return includeInsert;
        }
    
        public void setIncludeInsert(boolean includeInsert) {
        
            this.includeInsert = includeInsert;
        }
    
        @Bean
        public DataLogInterceptor dataLogInterceptor() {
        
            return new DataLogInterceptor();
        }
    
    }

数据日志工具

public class DataLogUtils {
        
    
        private static final Logger logger = LoggerFactory.getLogger(DataLogUtils.class);
    
        /**
         * 获取SQL语句
         */
        public static String getSql(Configuration configuration, BoundSql boundSql) {
        
            Object parameterObject = boundSql.getParameterObject();
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
            if (parameterMappings.size() == 0 && parameterObject == null) {
        
                return sql;
            }
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
        
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
            } else {
        
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
        
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
        
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
        
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
            return sql;
        }
    
        /**
         * 获取参数值
         */
        public static String getParameterValue(Object o) {
        
            if (o == null) {
        
                return "";
            }
            if (o instanceof String) {
        
                return "'" + o.toString() + "'";
            }
            if (o instanceof Date) {
        
                DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
                return "'" + formatter.format(o) + "'";
            }
            return o.toString();
        }
    
        /**
         * 获取调用的类和方法名称
         */
        public static String[] getClassAndMethod() {
        
            String[] result = new String[]{
        "", ""};
            StackTraceElement[] stackTraceElementArray = Thread.currentThread().getStackTrace();
            int n = stackTraceElementArray.length;
            for (int i = n - 1; i >= 0; i--) {
        
                String className = stackTraceElementArray[i].getClassName();
                if (className.contains(".service.")) {
        
                    result[0] = getSimpleClassName(className);
                    result[1] = stackTraceElementArray[i].getMethodName();
                    break;
                } else if (className.contains(".controller.")) {
        
                    result[0] = getSimpleClassName(className);
                    result[1] = stackTraceElementArray[i].getMethodName();
                }
            }
            return result;
        }
    
        /**
         * 根据类全称获取简单名称
         */
        public static String getSimpleClassName(String className) {
        
            className = StringUtils.split(className, "$$")[0];
            int index = className.lastIndexOf(".");
            if (index != -1) {
        
                return className.substring(index + 1);
            }
            return className;
        }
    
        /**
         * 获取两个相同实体对象不同属性值
         */
        public static DataCompareResult getDiffValue(Object object1, Object object2) {
        
    
            DataCompareResult dataCompareResult = new DataCompareResult();
    
            //对象都为null，返回空数据
            if (object1 == null && object2 == null) {
        
                return dataCompareResult;
            }
    
            Long id = null;
            LinkedHashMap<String, Object[]> dataChange = new LinkedHashMap<>();
            if (object1 == null) {
        //旧对象为null
                Field[] object2Fields = object2.getClass().getDeclaredFields();
                for (int i = 0; i < object2Fields.length; i++) {
        
                    object2Fields[i].setAccessible(true);
                    Field field = object2Fields[i];
                    try {
        
                        Object value2 = object2Fields[i].get(object2);
                        //忽略表不存在的字段
                        if (field.isAnnotationPresent(TableField.class) && !field.getAnnotation(TableField.class).exist()) {
        
                            continue;
                        }
                        //获取id主键值
                        if ("id".equals(field.getName())) {
        
                            id = Long.parseLong(value2.toString());
                        }
                        dataChange.put(field.getName(), new Object[]{
        "", value2});
                    } catch (IllegalAccessException e) {
        
                        logger.error("非法操作", e);
                    }
                }
            } else if (object2 == null) {
        //新对象为null
                Field[] object1Fields = object1.getClass().getDeclaredFields();
                for (int i = 0; i < object1Fields.length; i++) {
        
                    object1Fields[i].setAccessible(true);
                    Field field = object1Fields[i];
                    try {
        
                        Object value1 = object1Fields[i].get(object1);
                        //忽略表不存在的字段
                        if (field.isAnnotationPresent(TableField.class) && !field.getAnnotation(TableField.class).exist()) {
        
                            continue;
                        }
                        //获取id主键值
                        if ("id".equals(field.getName())) {
        
                            id = Long.parseLong(value1.toString());
                        }
                        dataChange.put(field.getName(), new Object[]{
        value1, ""});
                    } catch (IllegalAccessException e) {
        
                        logger.error("非法操作", e);
                    }
                }
            } else {
        //旧对象和新对象都不为null
                Field[] object1Fields = object1.getClass().getDeclaredFields();
                Field[] object2Fields = object2.getClass().getDeclaredFields();
                for (int i = 0; i < object1Fields.length; i++) {
        
                    object1Fields[i].setAccessible(true);
                    object2Fields[i].setAccessible(true);
                    Field field = object1Fields[i];
    
                    try {
        
                        Object value1 = object1Fields[i].get(object1);
                        Object value2 = object2Fields[i].get(object2);
                        //忽略表不存在的字段
                        if (field.isAnnotationPresent(TableField.class) && !field.getAnnotation(TableField.class).exist()) {
        
                            continue;
                        }
                        //获取id主键值
                        if ("id".equals(field.getName())) {
        
                            id = Long.parseLong(value1.toString());
                        }
                        //新值为null处理
                        if (value2 == null) {
        
                            if (!field.isAnnotationPresent(TableField.class) || !field.getAnnotation(TableField.class).updateStrategy().equals(FieldStrategy.IGNORED)) {
        
                                continue;
                            }
                        }
                        if (!Objects.equals(value1, value2)) {
        
                            dataChange.put(field.getName(), new Object[]{
        value1, value2});
                        }
                    } catch (IllegalAccessException e) {
        
                        logger.error("非法操作", e);
                    }
                }
            }
    
            //返回数据
            dataCompareResult.setId(id);
            dataCompareResult.setDataChange(dataChange);
    
            return dataCompareResult;
    
        }
    
        /**
         * 查询旧数据(只获取第一条数据)
         */
        public static Object selectOldData(String sql, TableInfo tableInfo) {
        
            String selectSql = "AND " + sql.substring(sql.toUpperCase().lastIndexOf("WHERE") + 5);
            Map<String, Object> map = new HashMap<>(1);
            map.put(Constants.WRAPPER, Wrappers.query().eq("1", 1).last(selectSql));
            SqlSessionFactory sqlSessionFactory = SqlHelper.sqlSessionFactory(tableInfo.getEntityType());
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<?> oldData;
            try {
        
                oldData = sqlSession.selectList(tableInfo.getSqlStatement(SqlMethod.SELECT_LIST.getMethod()), map);
            } finally {
        
                SqlSessionUtils.closeSqlSession(sqlSession, sqlSessionFactory);
            }
            return oldData != null && oldData.size() > 0 ? oldData.get(0) : null;
        }
    
        /**
         * 比较修改前后数据
         */
        public static DataCompareResult getDataCompare(SqlCommandType sqlCommandType, String sql, TableInfo tableInfo, Object entity) {
        
            DataCompareResult dataCompareResult = new DataCompareResult();
            if (SqlCommandType.INSERT.equals(sqlCommandType)) {
        //新增
                dataCompareResult = DataLogUtils.getDiffValue(null, entity);
            } else if (SqlCommandType.UPDATE.equals(sqlCommandType)) {
        //修改
                dataCompareResult = DataLogUtils.getDiffValue(selectOldData(sql, tableInfo), entity);
            } else if (SqlCommandType.DELETE.equals(sqlCommandType)) {
        //删除
                dataCompareResult = DataLogUtils.getDiffValue(selectOldData(sql, tableInfo), null);
            }
            return dataCompareResult;
        }
    
    }

###### 三、新建数据日志注解， ######

###### 四、把注释作用于实体类上，就可以生效了 ######

@DataUpdateLog
    @TableName("uc_user")
    public class User implements Serializable {
        
        ....
    }

###### 五、总结 ######

> 如果你服务器支持的话，而且你的生产项目很要求保留这些修改日志，是可以建立一个表来把这些数据保留的。但是个人非常不建议把这些数据保存到数据库，这些数据量是非常大，非常影响性能。建表语句贴在下面，想用的话也可以用。

CREATE TABLE `sys_data_log` (
        `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
        `type` varchar(10) NOT NULL DEFAULT '' COMMENT '操作类型',
        `operator_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '操作人id',
        `operator_name` varchar(20) NOT NULL DEFAULT '' COMMENT '操作人名称',
        `class_name` varchar(200) NOT NULL DEFAULT '' COMMENT '类名称',
        `method_name` varchar(200) NOT NULL DEFAULT '' COMMENT '方法名称',
        `entity_name` varchar(200) NOT NULL DEFAULT '' COMMENT '实体名称',
        `table_name` varchar(200) NOT NULL DEFAULT '' COMMENT '表名称',
        `table_id` varchar(20) NOT NULL DEFAULT '' COMMENT '表主键值',
        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
        `data_change` mediumtext COMMENT '数据变化',
        `statement` mediumtext COMMENT '查询语句',
        PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB COMMENT='数据库日志'