自己实现一个mybaits日志打印

我们在使用springboot + mybatis的时候,会发现官方提供的日志打印是debug级别的,而且不一定符合自己的日志习惯.因此基于mybatis的 Interceptor,可以自己实现一个完整sql打印 + sql执行耗时的小功能.不多说直接上代码

package com.ke.jiaoyi.ordercore.common.interceptor;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.db.sql.SqlUtil;
import com.google.common.collect.Lists;
import com.ke.jiaoyi.ordercore.common.util.Services;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;

import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * Sql执行时间记录拦截器
 */

@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
	@Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
	@Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
public class SqlCostInterceptor implements Interceptor {

	@Value("${sql.format:false}")
	private boolean sqlFormat;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		Object target = invocation.getTarget();
		long startTime = System.currentTimeMillis();
		StatementHandler statementHandler = (StatementHandler) target;
		try {
			return invocation.proceed();
		} finally {
			printSqlLog(startTime, statementHandler);
		}
	}

	/**
	 * 打印sql 日志
	 */
	public void printSqlLog(long startTime, StatementHandler statementHandler) {
		long endTime = System.currentTimeMillis();
		long sqlCost = endTime - startTime;

		BoundSql boundSql = statementHandler.getBoundSql();
		Object parameterObject = boundSql.getParameterObject();
		String sql = boundSql.getSql();

		// 格式化Sql语句，去除换行符，替换参数
		String formattedSql = null;
		try {
			formattedSql = getFllSql(boundSql, parameterObject);
		} catch (Exception e) {
			LOGGER.warn("Failed to format SQL: {}", e.getMessage());
		}

		if (formattedSql == null) {
			formattedSql = sql;
		}

		LOGGER.info("SQL: [{}]  Execution time: [{}ms]", formattedSql, sqlCost);
	}

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

	@Override
	public void setProperties(Properties properties) {

	}


	/**
	 * 获取完整的sql（推荐方案）
	 */
	public String getFllSql(BoundSql boundSql, Object parameterObject) {
		// 从
		SqlSessionFactory sqlSessionFactory = Services.of(SqlSessionFactory.class);
		TypeHandlerRegistry typeHandlerRegistry = sqlSessionFactory.getConfiguration().getTypeHandlerRegistry();
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		if (CollectionUtil.isEmpty(parameterMappings)) {
			return null;
		}
		StringBuilder sqlBuilder = new StringBuilder(boundSql.getSql());
		List<Object> paramValues = Lists.newArrayListWithExpectedSize(parameterMappings.size());

		for (ParameterMapping parameterMapping : parameterMappings) {
			if (parameterMapping.getMode() == ParameterMode.OUT) {
				continue;
			}
			String propertyName = parameterMapping.getProperty();
			Object value;
			if (boundSql.hasAdditionalParameter(propertyName)) {
				value = boundSql.getAdditionalParameter(propertyName);
			} else if (parameterObject == null) {
				value = null;
			} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
				value = parameterObject;
			} else {
				MetaObject metaObject = sqlSessionFactory.getConfiguration().newMetaObject(parameterObject);
				value = metaObject.getValue(propertyName);
			}
			paramValues.add(value);
		}
		for (Object paramValue : paramValues) {
			sqlBuilder.replace(sqlBuilder.indexOf("?"), sqlBuilder.indexOf("?") + 1, formatParamValue(paramValue));
		}
		String sql = sqlBuilder.toString();
		if (sqlFormat) {
			return SqlUtil.formatSql(sql);
		} else {
			return sql;
		}
	}

	public String formatParamValue(Object paramValue) {
		if (paramValue == null) {
			return "null";
		} else if (paramValue instanceof String || paramValue instanceof Date) {
			return "'" + paramValue.toString() + "'";
		} else {
			return paramValue.toString();
		}
	}
}

代码说明,基于mybatis的拦截器,在sql执行完后,打印完整sql语句和耗时.其中Services类是一个Spring applicationContext 的工具类,就不提供了.

SqlUtil是hutool工具包里面的,自行依赖hutool即可.

下面是sql执行效果

2023-03-30 15:21:16.110  INFO 14064 --- [nio-8080-exec-3] c.k.j.o.c.i.SqlCostInterceptor           : SQL: [SELECT id,content,created_at,updated_at  FROM t_document  WHERE  id = 404]  Execution time: [14ms]