快捷導(dǎo)航

Mybatis控制臺(tái)打印SQL執(zhí)行信息的方法詳解

更新時(shí)間：2024年11月27日 11:31:26 作者：對(duì)酒當(dāng)歌丶人生幾何

SQL性能監(jiān)控是一個(gè)程序必要的功能,通常我們可以使用數(shù)據(jù)庫(kù)自帶的客戶(hù)端工具進(jìn)行SQL性能分析,本章節(jié)只實(shí)現(xiàn)Mybatis執(zhí)行時(shí)對(duì)執(zhí)行SQL進(jìn)行攔截,控制臺(tái)打印執(zhí)行SQL包括參數(shù)、執(zhí)行方法以及執(zhí)行時(shí)間,需要的朋友可以參考下

前言

SQL性能監(jiān)控是一個(gè)程序必要的功能，通常我們可以使用數(shù)據(jù)庫(kù)自帶的客戶(hù)端工具進(jìn)行SQL性能分析。然而對(duì)于一些專(zhuān)業(yè)度不高的人員來(lái)說(shuō)，當(dāng)程序出現(xiàn)卡頓或者響應(yīng)速度變慢時(shí)，排查問(wèn)題變得困難。當(dāng)程序出現(xiàn)卡頓，通常通過(guò)檢查服務(wù)器磁盤(pán)使用情況、程序內(nèi)存大小，網(wǎng)絡(luò)帶寬以及數(shù)據(jù)庫(kù)I/O等方面進(jìn)行問(wèn)題排查。然而數(shù)據(jù)庫(kù)I/O打高的情況通常是由于SQL執(zhí)行效率過(guò)低導(dǎo)致的。一般項(xiàng)目制的公司都有屬于自己的實(shí)施人員，然而要讓實(shí)施人員去排查具體SQL執(zhí)行過(guò)慢問(wèn)題，這顯然對(duì)于專(zhuān)業(yè)度不高的工作人員來(lái)說(shuō)是一種挑戰(zhàn)和煎熬。因此本系列文章將介紹如何使用Mybatis的攔截器功能完成對(duì)SQL執(zhí)行的時(shí)間記錄，并通過(guò)MQ推送至SQL記錄服務(wù)，記錄具體的慢SQL信息，后續(xù)可以通過(guò)頁(yè)面進(jìn)行展示。通過(guò)可視化的方式讓實(shí)施人員快速定位到問(wèn)題所在。

一、基本功能介紹

本章節(jié)只實(shí)現(xiàn)Mybatis執(zhí)行時(shí)對(duì)執(zhí)行SQL進(jìn)行攔截，控制臺(tái)打印執(zhí)行SQL包括參數(shù)、執(zhí)行方法以及執(zhí)行時(shí)間。大致結(jié)構(gòu)圖如下：

1.1本章功能效果預(yù)覽圖:

Mapper Method：顯示該SQL是由哪個(gè)Mapper方法進(jìn)行調(diào)用執(zhí)行。
Execute SQL：打印出完整執(zhí)行的SQL，自動(dòng)填充了參數(shù)。
Spend Time：記錄本次SQL執(zhí)行花費(fèi)的時(shí)間。

二、可執(zhí)行源碼

2.1 yaml基礎(chǔ)配置

需要在yaml配置文件中配置是否打印SQL執(zhí)行信息。當(dāng)然該配置可以放入Redis中，以方便后續(xù)面向微服務(wù)時(shí)，可以一鍵開(kāi)啟和關(guān)閉，這里就不再演示，后續(xù)擴(kuò)展可有您自主實(shí)現(xiàn)。

mybatis-analyze:
  show-log: true #SQL打印到控制臺(tái)

2.2 MybatisAnalyzeSQLInterceptor實(shí)現(xiàn)SQL攔截

源碼可直接復(fù)制運(yùn)行?。。。?！

package com.hl.by.common.mybatis.interceptor;

import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.StopWatch;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * @Author: DI.YIN
 * @Date: 2024/11/25 16:32
 * @Version: 1.0.0
 * @Description: Mybatis SQL分析插件
 **/
@Slf4j
@Intercepts(value = {
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
        @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}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
})
@Component
public class MybatisAnalyzeSQLInterceptor implements Interceptor {

    @Value("${mybatis-analyze.show-log:false}")
    private Boolean showLog;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        StopWatch startedWatch = StopWatch.createStarted();
        Object returnValue = null;
        Exception proceedSQLException = null;
        try {
            returnValue = invocation.proceed();
        } catch (Exception e) {
            proceedSQLException = e;
        }
        startedWatch.stop();
        long spendTime = startedWatch.getTime(TimeUnit.MILLISECONDS);
        if (invocation.getArgs() == null || !(invocation.getArgs()[0] instanceof MappedStatement)) {
            return returnValue;
        }
        // just handle mappedStatement
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        // get BoundSql
        BoundSql boundSql = null;
        for (int i = invocation.getArgs().length - 1; i >= 0; i--) {
            if (invocation.getArgs()[i] instanceof BoundSql) {
                boundSql = (BoundSql) invocation.getArgs()[i];
                break;
            }
        }
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler routingStatementHandler = (RoutingStatementHandler) invocation.getTarget();
            boundSql = routingStatementHandler.getBoundSql();
        }
        if (boundSql == null) {
            Object parameter = null;
            if (invocation.getArgs().length > 1) {
                parameter = invocation.getArgs()[1];
            }
            boundSql = mappedStatement.getBoundSql(parameter);
        }
        //
        printProcessedSQL(boundSql, mappedStatement.getConfiguration(), mappedStatement.getId(), spendTime);
        // If an exception occurs during SQL execution,throw exception
        if (proceedSQLException != null) {
            throw proceedSQLException;
        }
        return returnValue;
    }

    /**
     * Parse SQL and Print SQL
     *
     * @param boundSql
     * @param configuration
     * @param statement
     * @param spendTime
     */
    private void printProcessedSQL(BoundSql boundSql, Configuration configuration, String statement, long spendTime) {
        Map<Integer, Object> parameterValueMap = parseParameterValues(configuration, boundSql);
        String finalSQL = fillSqlParams(boundSql.getSql(), parameterValueMap);
        finalSQL = finalSQL.replaceAll("\n", "");
        String printData = "\n===============Start Print SQL===============\n" +
                "Mapper Method: [ " + statement + " ]\n" +
                "Execute SQL: " + finalSQL + " \n" +
                "Spend Time: " + spendTime + " ms \n" +
                "===============End Print SQL===============\n";
        if (showLog) {
            log.info(printData);
        }
    }

    public static String fillSqlParams(String statementQuery, Map<Integer, Object> parameterValues) {
        final StringBuilder sb = new StringBuilder();
        int currentParameter = 0;
        for (int pos = 0; pos < statementQuery.length(); pos++) {
            char character = statementQuery.charAt(pos);
            if (statementQuery.charAt(pos) == '?' && currentParameter <= parameterValues.size()) {
                Object value = parameterValues.get(currentParameter);
                sb.append(value != null ? value.toString() : new MybatisAnalyzeSQLInterceptor.Values().toString());
                currentParameter++;
            } else {
                sb.append(character);
            }
        }
        return sb.toString();
    }

    /**
     * 用于解析參數(shù)值
     *
     * @param configuration
     * @param boundSql
     * @return Map<Integer, Object>
     */
    private static Map<Integer, Object> parseParameterValues(Configuration configuration, BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Map<Integer, Object> parameterValues = new HashMap<>();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    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 = configuration.newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                    }
                    parameterValues.put(i, new MybatisAnalyzeSQLInterceptor.Values(value));
                }
            }
            return parameterValues;
        }
        return Collections.emptyMap();
    }

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

    @Override
    public void setProperties(Properties properties0) {
    }

    @Setter
    @Getter
    public static class Values {
        public static final String NORM_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss";

        public static final String databaseDialectDateFormat = NORM_DATETIME_PATTERN;
        public static final String databaseDialectTimestampFormat = NORM_DATETIME_PATTERN;
        private Object value;

        public Values(Object valueToSet) {
            this();
            this.value = valueToSet;
        }

        public Values() {
        }

        @Override
        public String toString() {
            return convertToString(this.value);
        }

        public String convertToString(Object value) {
            String result;

            if (value == null) {
                result = "NULL";
            } else {
                if (value instanceof byte[]) {
                    result = new String((byte[]) value);
                } else if (value instanceof Timestamp) {
                    result = new SimpleDateFormat(databaseDialectTimestampFormat).format(value);
                } else if (value instanceof Date) {
                    result = new SimpleDateFormat(databaseDialectDateFormat).format(value);
                } else if (value instanceof Boolean) {
                    result = Boolean.FALSE.equals(value) ? "0" : "1";
                } else {
                    result = value.toString();
                }
                result = quoteIfNeeded(result, value);
            }

            return result;
        }

        private String quoteIfNeeded(String stringValue, Object obj) {
            if (stringValue == null) {
                return null;
            }
            if (Number.class.isAssignableFrom(obj.getClass()) || Boolean.class.isAssignableFrom(obj.getClass())) {
                return stringValue;
            } else {
                return "'" + escape(stringValue) + "'";
            }
        }

        private String escape(String stringValue) {
            return stringValue.replaceAll("'", "''");
        }
    }
}

到此這篇關(guān)于Mybatis控制臺(tái)打印SQL執(zhí)行信息的方法詳解的文章就介紹到這了,更多相關(guān)Mybatis控制臺(tái)打印SQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: