自定义mybatis插件实现sql日志打印

mysql插件实现原理

官网的关键信息

参考文档

https://mybatis.org/mybatis-3/zh/configuration.html#plugins

官方文档

MyBatis 允许你在映射语句执行过程中的某一点进行拦截调用。默认情况下，MyBatis 允许使用插件来拦截的方法调用包括：

Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
ParameterHandler (getParameterObject, setParameters)
ResultSetHandler (handleResultSets, handleOutputParameters)
StatementHandler (prepare, parameterize, batch, update, query)

插件运行源码流程

mybatis 在配置文件里面设置 plug , 所以 configuration 可以添加插件

Configuration.java

Executor
ParameterHandler
ResultSetHandler
StatementHandler

通过查看源码 , 知道插件增强的是 mybatis 以上的几个对象

interceptorChain

interceptorChain.pluginAll(…)方法

mybatis 中的 InterceptorChain 类似于 springmvc 中的拦截器链

Interceptor

实现 InvocationHandler , 我们知道 Plugin 类使用了 JDK 动态代理

最后执行插件方法 , 调用的是该类的 invoke 方法

inovke方法

参考pagehelper

实现sql日志打印插件

前置环境

springboot + mybatis + mysql

pom.xml


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.3.4.RELEASEversion>
        <relativePath/> 
    parent>
    <groupId>com.examplegroupId>
    <artifactId>springboot-demoartifactId>
    <version>0.0.1-SNAPSHOTversion>
    <name>springboot-demoname>
    <description>Demo project for Spring Bootdescription>
    <properties>
        <java.version>1.8java.version>
    properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
        dependency>
        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>2.1.4version>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <scope>runtimescope>
        dependency>
        <dependency>
            <groupId>cn.hutoolgroupId>
            <artifactId>hutool-allartifactId>
            <version>5.8.3version>
        dependency>
        <dependency>
            <groupId>com.github.xiaoymingroupId>
            <artifactId>knife4j-spring-boot-starterartifactId>
            <version>2.0.4version>
        dependency>
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>fastjsonartifactId>
            <version>1.2.72version>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintagegroupId>
                    <artifactId>junit-vintage-engineartifactId>
                exclusion>
            exclusions>
        dependency>
    dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
            plugin>
        plugins>
    build>

project>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

实现 Interceptor 接口

package com.example.plug;

import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;

import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * 1.可以用来分析SQL执行效率
 * 2.可以用来获取实际执行的SQL
 */

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})}
)
@Slf4j
public class SqlInterceptor implements Interceptor {
//    private static final Log log = LogFactory.getLog(SqlInterceptor.class);    
    /**
     * 最小打印时间 sql时间超过这个值才打印日志 毫秒
     **/
    private int MIN_SIZE = 0;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        String sqlId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();

        long startTime = System.currentTimeMillis();
        Object result = null;
        try {
            result = invocation.proceed();
        } finally {
            try {
                long sqlCostTime = System.currentTimeMillis() - startTime;
                String sql = getSql(configuration, boundSql);
                formatSqlLog(mappedStatement.getSqlCommandType(), sqlId, sql, sqlCostTime, result);
            } catch (Exception ignored) {
                log.error("SQL插件执行失败 Mapper:{} 参数对象：{}", sqlId, JSON.toJSONString(boundSql.getParameterObject()), ignored);
            }
        }
        return result;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        if (properties == null) {
            return;
        }
        if (properties.containsKey("minLogSize")) {
            MIN_SIZE = Integer.valueOf(properties.getProperty("minLogSize"));
        }
    }

    private String getSql(Configuration configuration, BoundSql boundSql) {
        // 输入sql字符串空判断
        String sql = boundSql.getSql();
        if (StrUtil.isBlank(sql)) {
            return "";
        }

        //去掉换行符
        sql = sql.replaceAll("[\\s\n ]+", " ");

        //填充占位符, 目前基本不用mybatis存储过程调用,故此处不做考虑
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (!parameterMappings.isEmpty() && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = this.replacePlaceholder(sql, 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 = replacePlaceholder(sql, obj);
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = replacePlaceholder(sql, obj);
                    }
                }
            }
        }
        return sql;
    }

    private String replacePlaceholder(String sql, Object parameterObject) {
        String result;
        if (parameterObject == null) {
            result = "NULL";
        } else if (parameterObject instanceof String) {
            result = String.format("'%s'", parameterObject.toString());
        } else if (parameterObject instanceof Date) {
            result = String.format("'%s'", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(parameterObject));
        } else {
            result = parameterObject.toString();
        }
        return sql.replaceFirst("\\?", result);
    }

    private void formatSqlLog(SqlCommandType sqlCommandType, String sqlId, String sql, long costTime, Object obj) {
        if (costTime > MIN_SIZE) {
            if (sqlCommandType == SqlCommandType.UPDATE || sqlCommandType == SqlCommandType.INSERT || sqlCommandType == SqlCommandType.DELETE) {
                log.info("[{}ms] [{}] {}; 影响行数：{}", costTime, sqlId, sql, obj);
            }
            if (sqlCommandType == SqlCommandType.SELECT) {
                log.info("[{}ms] [{}] {}; 结果行数：{}", costTime, sqlId, sql, ((Collection<?>) obj).size());
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

mybatis 添加该插件

SpringBoot 中 SqlSessionFactory 是单例的

通过 SqlSessionFactory 得到 Configuration

configuration 的 addInterceptor 方法添加插件

@Configuration
public class MybatisPlugConfig implements SmartInitializingSingleton {

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public void afterSingletonsInstantiated() {
        sqlSessionFactory.getConfiguration().addInterceptor(new SqlInterceptor());
    }
}
1
2
3
4
5
6
7
8
9
10
11

测试

测试方便 , 省略service

controller

@RestController
@RequestMapping("/test")
public class HelloController {


    @Autowired
    private PersonDao personDao;


    @GetMapping("/ssm")
    public List<PersonEntity> ssm() {
        return personDao.list();
    }
    
    @GetMapping("/insert")
    public String insert(){
        PersonEntity personEntity = new PersonEntity();
        personEntity.setPid(null);
        personEntity.setPname(UUID.randomUUID().toString().substring(8).toString());
        personDao.insert(personEntity);
        return "插入成功";
    }

    @GetMapping("/select")
    public List<PersonEntity> select(Integer pid , String pname){
        return personDao.selectByParam(pid , pname);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

dao

@Mapper
public interface PersonDao  {


     @Select("select * from person")
     List<PersonEntity> list();
     
     int insert(PersonEntity personEntity);

     List<PersonEntity> selectByParam(@Param("pid") Integer pid, @Param("pname") String pname);
}
1
2
3
4
5
6
7
8
9
10
11

控制台打印

2022-10-28 22:07:18.913 INFO 26316 — [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet ‘dispatcherServlet’
2022-10-28 22:07:18.913 INFO 26316 — [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet ‘dispatcherServlet’
2022-10-28 22:07:18.917 INFO 26316 — [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 4 ms
2022-10-28 22:07:27.247 INFO 26316 — [nio-8080-exec-6] com.example.plug.SqlInterceptor : [48ms] [com.example.dao.PersonDao.list] select * from person; 结果行数：5
2022-10-28 22:07:30.759 INFO 26316 — [nio-8080-exec-8] com.example.plug.SqlInterceptor : [34ms] [com.example.dao.PersonDao.selectByParam] select * from person WHERE pname like concat(‘%’,concat(‘1’,‘%’)) and pid = 1; 结果行数：0
2022-10-28 22:07:32.936 INFO 26316 — [nio-8080-exec-9] com.example.plug.SqlInterceptor : [31ms] [com.example.dao.PersonDao.selectByParam] select * from person WHERE pname like concat(‘%’,concat(‘1’,‘%’)); 结果行数：2
2022-10-28 22:07:36.464 INFO 26316 — [io-8080-exec-10] com.example.plug.SqlInterceptor : [34ms] [com.example.dao.PersonDao.selectByParam] select * from person WHERE pid = 1; 结果行数：1
2022-10-28 22:07:38.605 INFO 26316 — [nio-8080-exec-1] com.example.plug.SqlInterceptor : [33ms] [com.example.dao.PersonDao.selectByParam] select * from person; 结果行数：5
oncat(‘1’,‘%’)); 结果行数：2
2022-10-28 22:07:36.464 INFO 26316 — [io-8080-exec-10] com.example.plug.SqlInterceptor : [34ms] [com.example.dao.PersonDao.selectByParam] select * from person WHERE pid = 1; 结果行数：1
2022-10-28 22:07:38.605 INFO 26316 — [nio-8080-exec-1] com.example.plug.SqlInterceptor : [33ms] [com.example.dao.PersonDao.selectByParam] select * from person; 结果行数：5
2022-10-28 22:07:45.440 INFO 26316 — [nio-8080-exec-2] com.example.plug.SqlInterceptor : [53ms] [com.example.dao.PersonDao.insert] INSERT INTO person ( pid, pname ) VALUES ( 6, ‘-9bac-472f-a95c-9d97b11ab0e1’ ); 影响行数：1

相关阅读:
10 个 Java 安全最佳实践
 MySQL--视图、存储过程、触发器
 基于Spring的发布订阅模式 EventListener
如何使用vue的计算属性来处理数据计算？
C\C++中数组指针和二维数组最强最简单粗暴深刻理解！！！一遍包过！
十二.镜头知识之镜头分辨率（解析力）
Starknet的去中心化路线图
 Java中栈
 不同规模的企业如何借助宁盾LDAP统一用户认证实现安全和效率需求？
服了，攻城狮的酒后真言竟然是这些？
原文地址：https://blog.csdn.net/shaoming314/article/details/127580490