Mybatis拦截器实现在SQL执行过程前后对SQL执行时间、SQL信息、Mapper信息进行日志打印

落日映苍穹つ 2021-12-10 09:03 359阅读 0赞

### 源码 ###

**GitHub：**[https://github.com/291685399/springboot-learning/tree/master/springboot-mybatis02][https_github.com_291685399_springboot-learning_tree_master_springboot-mybatis02]

--------------------

### 目标 ###

**在SQL执行过程前后进行打印SQL执行时间、SQL信息、Mapper信息进行日志打印如下图所示：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjIwNTAx_size_16_color_FFFFFF_t_70]

--------------------

**SqlStatementInterceptor**：**`实现Interceptor接口，在SQL执行过程前后进行打印SQL实行时间、SQL信息、Mapper信息进行日志打印输出`**

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.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 org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.text.DateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Locale;
    import java.util.Properties;
    import java.util.regex.Matcher;
    
    /** * 实现Interceptor接口，在SQL执行过程前后进行打印SQL实行时间、SQL信息、Mapper信息进行日志打印输出 * * @author wyj */
    @Intercepts(value = { 
            @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.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 = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
    })
    public class SqlStatementInterceptor implements Interceptor { 
    
        private static Logger logger = LoggerFactory.getLogger(SqlStatementInterceptor.class);
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable { 
            long startTime = System.currentTimeMillis();
            try { 
                return invocation.proceed();//执行sql过程
            } finally { 
                long endTime = System.currentTimeMillis();
                MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];//获取xml中的一个select/update/insert/delete节点，主要描述的是一条SQL语句
                Object parameter = "";
                if (invocation.getArgs().length > 1) { 
                    parameter = invocation.getArgs()[1];
                    logger.info("==> transmit Parameters is [ {} ]", parameter);//打印入参参数
                }
                String sqlId = mappedStatement.getId(); // 获取到节点的id,即sql语句的id
                String[] mappers = getMapper(sqlId);
                logger.info("==> execute Mapper name is [ {} ]", mappers[0]);//打印执行的Mapper名称
                logger.info("==> execute Mapper method is [ {} ]", mappers[1]);//打印执行的Mapper方法
                BoundSql boundSql = mappedStatement.getBoundSql(parameter);// BoundSql就是封装myBatis最终产生的sql类
                Configuration configuration = mappedStatement.getConfiguration();// 获取节点的配置
                logger.info("==> execute SQL with Parameters is [ {} ]", getSql(configuration, boundSql));//打印执行带参SQL
                logger.info("==> execute SQL cost [ {} ] ms", (endTime - startTime));//打印SQL执行时间
            }
        }
    
        /** * 根据节点id获取执行的Mapper name和Mapper method * * @param sqlId 节点id * @return */
        public static String[] getMapper(String sqlId) { 
            int index = sqlId.lastIndexOf(".");
            String mapper = sqlId.substring(0, index);
            String method = sqlId.substring(index + 1);
            return new String[]{ mapper, method};
        }
    
        /** * 获取带参SQL * * @param configuration 节点的配置 * @param boundSql 封装myBatis最终产生的sql类 * @return */
        public static String getSql(Configuration configuration, BoundSql boundSql) { 
            Object parameterObject = boundSql.getParameterObject();//获取参数
            List<ParameterMapping> parameterMappings = boundSql
                    .getParameterMappings();
            String sql = boundSql.getSql().replaceAll("[\\s]+", " ");//sql语句中多个空格都用一个空格代替
            if (parameterMappings.size() > 0 && parameterObject != null) { 
                TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); //获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换。如果根据parameterObject.getClass(）可以找到对应的类型，则替换
                if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { 
                    sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
                } else { 
                    MetaObject metaObject = configuration.newMetaObject(parameterObject);//MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
                    for (ParameterMapping parameterMapping : parameterMappings) { 
                        String propertyName = parameterMapping.getProperty();
                        if (metaObject.hasGetter(propertyName)) { 
                            Object obj = metaObject.getValue(propertyName);
                            sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                        } else if (boundSql.hasAdditionalParameter(propertyName)) { 
                            Object obj = boundSql.getAdditionalParameter(propertyName);//该分支是动态sql
                            sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                        } else { 
                            sql = sql.replaceFirst("\\?", "缺失");//打印出缺失，提醒该参数缺失并防止错位
                        }
                    }
                }
            }
            return sql;
        }
    
        /** * 对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;
        }
    
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) { 
    
        }
    }

--------------------

**MyBatisConfig**：**`用于将实现的mybatis拦截器、mapper xml配置文件的路径和别名包的路径设置到SqlSessionFactory中`**

import com.wyj.interceptor.SqlStatementInterceptor;
    import org.apache.ibatis.plugin.Interceptor;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    /** * 用于将实现的mybatis拦截器、mapper xml配置文件的路径和别名包的路径设置到SqlSessionFactory中 * * @author wyj */
    @Configuration
    public class MyBatisConfig { 
    
        @Autowired
        private DataSource dataSource;
    
        @Value("${mybatis.mapper-locations}")
        private String mapperLocations;
    
        @Value("${mybatis.type-aliases-package}")
        private String typeAliasesPackage;
    
        @Bean
        public SqlSessionFactory sqlSessionFactory() throws Exception { 
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource);
            sqlSessionFactoryBean.setPlugins(new Interceptor[]{ new SqlStatementInterceptor()});
            PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
            sqlSessionFactoryBean.setMapperLocations(pathMatchingResourcePatternResolver.getResources(mapperLocations));
            sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
            return sqlSessionFactoryBean.getObject();
        }
    }

--------------------

**application.properties**：**`这里需要配置mapper的包路径日志级别为DEBUG`**

### tomcat ###
    server.port=8080
    ### datasource ###
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot-mybatis02?allowMultiQueries=true&useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
    spring.datasource.username=root
    spring.datasource.password=root
    ### mybatis ###
    mybatis.mapper-locations=classpath*:mapper/*.xml
    mybatis.type-aliases-package=com.wyj.entity
    #logging
    logging.level.com.wyj.mapper=debug

--------------------

**执行效果**：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjIwNTAx_size_16_color_FFFFFF_t_70]

[https_github.com_291685399_springboot-learning_tree_master_springboot-mybatis02]: https://github.com/291685399/springboot-learning/tree/master/springboot-mybatis02
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjIwNTAx_size_16_color_FFFFFF_t_70]: /images/20211210/96e07aaf892b43138c8316248b24b18a.png