设计logo网站免费横屏纯色杭州制作网站的公司
一，目标
- 替换?为具体的参数值
- 统计sql执行时间
- 记录执行时间过长的sql，并输出信息到文档（以天为单位进行存储）
平常打印出来的sql都是sql一行，参数一行。如图：
二，理论
这里我们主要通过Mybatis的Interceptor接口与Java自身的IO操作来实现。
Interceptor接口
MyBatis 的 Interceptor 是一个强大的功能，它允许开发者在执行数据库操作的过程中插入自定义逻辑。通过使用拦截器，我们可以在执行 SQL 语句之前或之后进行处理，记录日志、修改输入参数、管理事务、监控性能等功能。
@Intercepts注解
Mybatis提供的一个用于定义拦截器的注解。
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface Intercepts {Signature[] value(); //这里表示可以添加多个注解Signature作为value
}
@Intercepts注解
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface Signature {Class<?> type(); // 拦截哪个类String method(); // 类的哪个方法Class<?>[] args(); // 什么参数
}这里需要注意一点
方法和参数与你当前项目中引入的版本有关。 写时请必须确认你写的类中确实有该方法，该参数。否则就会出现类似
### Error opening session. Cause: org.apache.ibatis.plugin.PluginException: Could not find method on interface org.apache.ibatis.executor.Executor named query. Cause: java.lang.NoSuchMethodException: org.apache.ibatis.executor.Executor.query(org.apache.ibatis.mapping.MappedStatement,java.lang.Object) ### Cause: org.apache.ibatis.plugin.PluginException: Could not find method on interface org.apache.ibatis.executor.Executor named query. Cause: java.lang.NoSuchMethodException: org.apache.ibatis.executor.Executor.query(org.apache.ibatis.mapping.MappedStatement,java.lang.Object)] with root cause java.lang.NoSuchMethodException: org.apache.ibatis.executor.Executor.query(org.apache.ibatis.mapping.MappedStatement,java.lang.Object)
的异常。
三，实操
1, 核心类--SqlPrintInterceptor
package com.luojie.config.myInterface.mybatisIntercept;import com.luojie.common.Conditions;
import com.luojie.util.TxtUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
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.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
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 java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;/*** 自定义打印日志功能的拦截器*/
@Intercepts({// 拦截 Executor 接口的 query 方法，包含不同的参数组合@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}),@Signature(type = Executor.class, method = "queryCursor", args = {MappedStatement.class, Object.class, RowBounds.class}),@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Slf4j
public class SqlPrintInterceptor implements Interceptor {private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");@Overridepublic Object intercept(Invocation invocation) throws Throwable {// 记录开始时间long startTime = System.currentTimeMillis();Object proceed = null;// 执行原始方法try {proceed = invocation.proceed();} catch (Throwable t) {log.error("Error during SQL execution", t);throw t; // 重新抛出异常}// 记录结束时间long endTime = System.currentTimeMillis();long executionTime = endTime - startTime; // 计算执行时间// 转换执行时间为 "XXs.XXms" 格式String formattedExecutionTime = formatExecutionTime(executionTime);// 生成打印的 SQL 语句String printSql = generateSql(invocation);// 输出 SQL 和执行时间System.out.println(Conditions.RED + "SQL: " + printSql);System.out.println("Execution time: " + formattedExecutionTime);System.out.print(Conditions.RESET);log.info("SQL: " + printSql);log.info("Execution time: " + formattedExecutionTime);// 记录慢sql(这里我为了方便观察，所以设置界限为0，各位可以根据实际情况设置)if ((executionTime / 1000) >= 0) {writeSlowSqlToLocation(printSql, formattedExecutionTime);}return proceed; // 返回原始方法的结果}// 记录慢sqlprivate void writeSlowSqlToLocation(String sql, String executeTime) {String formattedDate = dateFormat.format(new Date());String logs = formattedDate + " SQL: " + sql + " 执行耗时: " + executeTime;TxtUtil.writeLog(logs);}// 新增格式化执行时间的方法private String formatExecutionTime(long executionTime) {long seconds = executionTime / 1000; // 获取秒数long milliseconds = executionTime % 1000; // 获取剩余的毫秒数return String.format("%ds.%03dms", seconds, milliseconds); // 格式化为 "XXs.XXXms"}private String generateSql(Invocation invocation) {// 获取 MappedStatement 对象MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = null;// 获取参数对象if (invocation.getArgs().length > 1) {parameter = invocation.getArgs()[1];}// 获取 MyBatis 配置Configuration configuration = mappedStatement.getConfiguration();// 获取 BoundSql 对象BoundSql boundSql = mappedStatement.getBoundSql(parameter);// 获取参数对象Object parameterObject = boundSql.getParameterObject();// 获取参数映射列表List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// 获取执行的 SQL 语句String sql = boundSql.getSql();// 替换 SQL 中多个空格为一个空格sql = sql.replaceAll("[\\s]+", " ");// 如果参数对象和参数映射不为空if (!ObjectUtils.isEmpty(parameterObject) && !ObjectUtils.isEmpty(parameterMappings)) {// 如果只有一个参数，直接替换if (parameterObject instanceof String && parameterMappings.size() == 1) {return sql.replaceFirst("\\?", String.valueOf(parameterObject)); // 处理缺少值的情况}// 遍历每个参数映射for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty(); // 获取属性名MetaObject metaObject = configuration.newMetaObject(parameterObject); // 创建 MetaObjectObject obj = null; // 初始化参数对象// 如果参数对象有对应的 getter 方法if (metaObject.hasGetter(propertyName)) {obj = metaObject.getValue(propertyName); // 获取参数值} else if (boundSql.hasAdditionalParameter(propertyName)) {obj = boundSql.getAdditionalParameter(propertyName); // 获取附加参数}// 替换 SQL 中的占位符if (obj != null) {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));} else {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(propertyName)); // 处理缺少值的情况}}}return sql; // 返回生成的 SQL 语句}private String getParameterValue(Object parameterObject) {// 如果参数对象为空，返回 "null"if (parameterObject == null) {return "null";}// 返回参数对象的字符串表示return parameterObject.toString();}@Overridepublic Object plugin(Object target) {// 生成插件对象return Interceptor.super.plugin(target);}@Overridepublic void setProperties(Properties properties) {// 设置属性Interceptor.super.setProperties(properties);}
}
2, txt工具类--TxtUtil
package com.luojie.util;import lombok.extern.slf4j.Slf4j;import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.Date;/*** 简单的txt文件工具 类**/
@Slf4j
public class TxtUtil {private static final String LOG_DIRECTORY = "D:\\tmp\\log"; // 指定日志文件夹private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");public static void writeLog(String message) {// 获取当前日期String currentDate = dateFormat.format(new Date());// 构建文件路径String filePath = LOG_DIRECTORY + "/" + currentDate + ".txt";// 创建目录如果不存在try {Files.createDirectories(Paths.get(LOG_DIRECTORY));} catch (IOException e) {log.error(e.getMessage());}// 追加写入文件try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath, true))) {writer.write(message);writer.newLine(); // 换行} catch (IOException e) {log.error(e.getMessage());}}
}
3, 常量类--Conditions
package com.luojie.common;/*** 常量类*/
public class Conditions {/*** 控制print的打印颜色*/public static final String RED = "\033[0;31m"; // 红色public static final String GREEN = "\033[0;32m"; // 绿色public static final String YELLOW = "\033[0;33m"; // 黄色public static final String BLUE = "\033[0;34m"; // 蓝色public static final String MAGENTA = "\033[0;35m"; // 品红public static final String CYAN = "\033[0;36m"; // 青色public static final String WHITE = "\033[0;37m"; // 白色/*** 重置print所有颜色*/public static final String RESET = "\033[0m"; // 重置
}
这里是为了控制打印到控制台的颜色，方便观察
4, 让mybatis使用上这个拦截器
package com.luojie.config;import com.luojie.config.myInterface.mybatisIntercept.SqlPrintInterceptor;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;@Configuration
@MapperScan(basePackages = "com.luojie.dao.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1")
public class DataSource1Config {@Value("${datasource1.url}")private String url;@Value("${datasource1.username}")private String username;@Value("${datasource1.password}")private String password;@Bean(name = "dataSource1")public DataSource dataSource1() {return DataSourceBuilder.create().url(url).username(username).password(password)// 使用HikariCP数据连接池管理.type(HikariDataSource.class).build();}@Bean(name = "sqlSessionFactory1")public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource) throws Exception {SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();sessionFactoryBean.setDataSource(dataSource);sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:rojerTestMapper/mapper1/*.xml"));// 增加自定义的sql日志打印器// 用new Interceptor[]{new SqlPrintInterceptor()}而不是直接new SqlPrintInterceptor()是为了后续方便扩展sessionFactoryBean.setPlugins(new Interceptor[]{new SqlPrintInterceptor()});return sessionFactoryBean.getObject();}
}5, 测试结果
只有一个参数时
存在多个参数时
文件保存确认
四， 扩展
我这里简单将慢sql信息记录到本地，大家可以根据自己的项目需要，通过消息中间件实现和短信发送的方式，实现慢sql监控告警等功能。
以上。
祝各位大佬前途光明。