切面+mybatis 实现mapper执行的每一条sql的日志记录

参考来源

https://blog.csdn.net/sdzhangshulong/article/details/104393244.

遇见一个需求，是要记录下每一次执行的sql语句，用于后续问题分析，想到的方法有拦截器、过滤器、切面。这篇文章记录一下实现的过程及遇到的问题。

1.普通切面编写

首先实现一个简单的切面即可，通知方法仅输出一下执行的方法的名字。

import com.jgybzx.mappers.LogSqlMapper;
import com.jgybzx.utils.SqlUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.util.*;
/**
 * @author jgybzx
 * @date 2020/11/20 10:00
 * @description 记录mapper每一次执行的sql及其相关信息
 */
@Aspect
@Component
public class LogAspect {
    /**
     * 切点 扫描整个mapper
     * @Pointcut("execution(* com.jgybzx.controller.*.*(..)) && @annotation(com.jgybzx.aspect.LogAnnotation)")
     * @Pointcut("execution(* com.jgybzx.mappers.*.*(..))")
     */
    @Pointcut("execution(* com.jgybzx.mappers.*.*(..)) && !execution(* com.jgybzx.mappers.LogSqlMapper.*(..))")
    public void logRecord() {
        // 定义
    }
    /**
     * 前置通知
     * @param jp
     */
    @Before(value = "logRecord()")
    public void before(JoinPoint jp) {
        String name = jp.getSignature().getName();
        System.out.println(name + "方法开始执行");
    }
    /**
     * 后置通知
     * @param jp
     */
    @After(value = "logRecord()")
    public void after(JoinPoint jp) {
        String name = jp.getSignature().getName();
        System.out.println(name + "方法执行结束...");
    }
    /**
     * 返回通知
     * @param jp
     * @param result
     */
    @AfterReturning(value = "logRecord()", returning = "result")
    public void afterReturning(JoinPoint jp, Object result) {
        String name = jp.getSignature().getName();
        System.out.println(name + "方法返回值为：" + result);
    }
    /**
     * 异常通知
     * @param jp
     * @param e
     */
    @AfterThrowing(value = "logRecord()", throwing = "e")
    public void afterThrowing(JoinPoint jp, Exception e) {
        String name = jp.getSignature().getName();
        System.out.println(name + "方法异常：" + e.getMessage());
    }
    
    @Around(value = "logRecord()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        return pjp.proceed();
    }
}

2.日志表建立(比较简单，可以根据需求自定义表)

CREATE TABLE logsql (
  log_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  sql_str varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '执行的sql',
  method_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '方法名字',
  method_url varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '方法地址',
  request_url varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '请求地址',
  create_date timestamp NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (log_id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.修改环绕通知，加入日志保存

环绕通知

    @Around(value = "logRecord()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        // 获取请求信息
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = requestAttributes.getRequest();
        Signature signature = pjp.getSignature();
        String logId = String.valueOf(System.currentTimeMillis());
        String methodName = signature.getName();
        String methodUrl = signature.toShortString();
        String requestUrl = request.getRequestURL().toString();
        String sql = SqlUtils.getMybatisSql(pjp, sqlSessionFactory);
        System.out.println("执行的sql语句为###" + sql + "###");
        /*//<editor-fold desc="1、jdbc 保存日志">
        Connection connection = JdbcUtils.getConnection();
        Statement statement = connection.createStatement();
        String insertSql = "insert into logsql(log_id, sql_str, method_name, method_url, request_url, create_date) " +
                "values (" + "\'" + logId + "\'" + ","
                + "\"" + sql + "\"" + ","
                + "\"" + methodName + "\"" + ","
                + "\'" + methodUrl + "\'" + ","
                + "\'" + requestUrl + "\'" + ","
                + "NOW()" + ")";
        statement.executeUpdate(insertSql);
        //</editor-fold>*/

        /*//<editor-fold desc="2、mybatis 通过model方式保存">
        LogSql logSql = new LogSql();
        logSql.setLogId(logId);
        logSql.setMethodName(methodName);
        logSql.setMethodUrl(methodUrl);
        logSql.setSqlStr(sql);
        logSql.setRequestUrl(requestUrl);
        logSqlMapper.saveLogByModel(logSql);
        //</editor-fold>*/

        //<editor-fold desc="3、mybatis 通过 map方式保存">
        Map<String, String> map = new HashMap<>(16);
        map.put("log_id", logId);
        map.put("sql_str", sql);
        map.put("method_name", methodName);
        map.put("method_url", methodUrl);
        map.put("request_url", requestUrl);
        logSqlMapper.saveLogByMap(map);
        //</editor-fold>
        return pjp.proceed();
    }

工具类

package com.jgybzx.utils;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.reflect.MethodSignature;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.text.DateFormat;
import java.util.*;
/**
 * @author jgybzx
 * @date 2020/11/20 10:16
 * @description  获取aop中的SQL语句，借鉴来源https://blog.csdn.net/sdzhangshulong/article/details/104393244
 */
public class SqlUtils {

    /**
     * 获取aop中的SQL语句
     *
     * @param pjp
     * @param sqlSessionFactory
     * @return
     * @throws IllegalAccessException
     */
    public static String getMybatisSql(ProceedingJoinPoint pjp, SqlSessionFactory sqlSessionFactory) throws IllegalAccessException {
        Map<String, Object> map = new HashMap<>(16);
        //1.获取namespace+methdoName
        MethodSignature signature = (MethodSignature) pjp.getSignature();
        Method method = signature.getMethod();
        String namespace = method.getDeclaringClass().getName();
        String methodName = method.getName();
        //2.根据namespace+methdoName获取相对应的MappedStatement
        Configuration configuration = sqlSessionFactory.getConfiguration();
        MappedStatement mappedStatement = configuration.getMappedStatement(namespace + "." + methodName);
        //3.获取方法参数列表名
        Parameter[] parameters = method.getParameters();
        //4.形参和实参的映射,获取实参
        Object[] objects = pjp.getArgs();
        Annotation[][] parameterAnnotations = method.getParameterAnnotations();
        for (int i = 0; i < parameterAnnotations.length; i++) {
            Object object = objects[i];
            //说明该参数没有注解，此时该参数可能是实体类，也可能是Map，也可能只是单参数
            if (parameterAnnotations[i].length == 0) {
                if (object.getClass().getClassLoader() == null && object instanceof Map) {
                    map.putAll((Map<? extends String, ?>) object);
                    System.out.println("该对象为Map");
                } else {//形参为自定义实体类
                    map.putAll(objectToMap(object));
                    System.out.println("该对象为用户自定义的对象");
                }
            } else {//说明该参数有注解，且必须为@Param
                for (Annotation annotation : parameterAnnotations[i]) {
                    if (annotation instanceof Param) {
                        map.put(((Param) annotation).value(), object);
                    }
                }
            }
        }
        //5.获取boundSql
        BoundSql boundSql = mappedStatement.getBoundSql(map);
        return showSql(configuration, boundSql);
    }

    /**
     * 解析BoundSql，生成不含占位符的SQL语句
     *
     * @param configuration
     * @param boundSql
     * @return
     */
    private static String showSql(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) {
            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();
                    String[] s = metaObject.getObjectWrapper().getGetterNames();
                    s.toString();
                    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;
    }

    /**
     * 若为字符串或者日期类型，则在参数两边添加''
     *
     * @param obj
     * @return
     */
    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        return value;
    }

    /**
     * 获取利用反射获取类里面的值和名称
     *
     * @param obj
     * @return
     * @throws IllegalAccessException
     */
    private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
        Map<String, Object> map = new HashMap<>(16);
        Class<?> clazz = obj.getClass();
        System.out.println(clazz);
        // 获取本类及其父类的属性，↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null) {
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        // 获取本类及其父类的属性，↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
        for (Field field : fieldList) {
            field.setAccessible(true);
            String fieldName = field.getName();
            Object value = field.get(obj);
            map.put(fieldName, value);
        }
        return map;
    }
}

mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jgybzx.mappers.LogSqlMapper">

    <insert id="saveLogByModel" parameterType="com.jgybzx.model.LogSql">
        insert into logsql(log_id, sql_str, method_name, method_url, request_url, create_date)
        values
        (#{logId},
        #{sqlStr},
        #{methodName},
        #{methodUrl},
        #{requestUrl},
        NOW())
    </insert>
    <insert id="saveLogByMap" parameterType="map">
        insert into logsql(
        <foreach collection="map" item="value" index="key" separator=",">
            ${key}
        </foreach>
        , create_date)
        values
        (
        <foreach collection="map" item="value" index="key" separator=",">
            #{value}
        </foreach>
        ,
        NOW())
    </insert>
</mapper>

4.遇到的问题

1、借鉴的获取sql的工具有一处问题，objectToMap，在获取类属性的时候，没有获取其父类的属性，导致得到的sql没有完整的显示 具体可以看https://blog.csdn.net/sdzhangshulong/article/details/104393244下的评论
2、需要注意的地方，切点扫描的时候，需要把日志的mapper给排除掉，不然会造成死循环，内存溢出。!execution(* com.jgybzx.mappers.LogSqlMapper.*(…))
3、尝试三种方式将日志写入表中，其中第一种原生jdbc方式，不推荐，在处理特殊符号的时候比较复杂，推荐使用使用model的方式，简单明了。

以上为个人记录，如果能帮到你或者有问题可以一起讨论。