如何獲取MyBatis Plus執(zhí)行的完整的SQL語句

更新時間：2024年07月17日 10:50:15 作者：梁云亮

這篇文章主要介紹了如何獲取MyBatis Plus執(zhí)行的完整的SQL語句問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

原理

自定義插件，將SQL語句中的?替換成具體的參數(shù)值。

實現(xiàn)

自定義插件

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.tiku.utils.LogUtil;
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.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.sql.SQLException;
import java.text.DateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.regex.Matcher;

public class MyBatisPlusSqlLogInterceptor implements InnerInterceptor {
    private static boolean printSQL = true;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        if (printSQL) {
            MappedStatement mappedStatement = ms;
            String sqlId = mappedStatement.getId();
            Configuration configuration = mappedStatement.getConfiguration();
            String sql = getSql(configuration, boundSql, sqlId);

            final String ip = RequestUtil.getIp();
            final String requestType = RequestUtil.getMethod();
            final String url = RequestUtil.getRequestUrl();
            final String parameters = RequestUtil.getParameters();

            String userAgent = RequestUtil.getUserAgent();
            UserAgent agent = UserAgent.parseUserAgentString(userAgent);
            final String browser = agent.getBrowser().getName();
            final String os = agent.getOperatingSystem().getName();

            LogUtil.println("用戶訪問了：{}，瀏覽器是：{}，操作系統(tǒng)是：{}，IP是：{}，請求方式是：{}，請求參數(shù)是：{}，SQL語句是：{}", url, browser, os, ip, requestType, parameters, sql);
        }
    }

    @Override
    public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
        if (printSQL) {
            MappedStatement mappedStatement = ms;
            String sqlId = mappedStatement.getId();
            Configuration configuration = mappedStatement.getConfiguration();
            BoundSql boundSql = mappedStatement.getBoundSql(parameter);
            String sql = getSql(configuration, boundSql, sqlId);

            final String ip = RequestUtil.getIp();
            final String requestType = RequestUtil.getMethod();
            final String url = RequestUtil.getRequestUrl();
            final String parameters = RequestUtil.getParameters();

            String userAgent = RequestUtil.getUserAgent();
            UserAgent agent = UserAgent.parseUserAgentString(userAgent);
            final String browser = agent.getBrowser().getName();
            final String os = agent.getOperatingSystem().getName();

            LogUtil.println("用戶訪問了：{}，瀏覽器是：{}，操作系統(tǒng)是：{}，IP是：{}，請求方式是：{}，請求參數(shù)是：{}，SQL語句是：{}", url, browser, os, ip, requestType, parameters, sql);
        }
    }

    private String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
        try {
            String sql = showSql(configuration, boundSql);
            StringBuilder str = new StringBuilder(100);
            str.append(sqlId);
            str.append(" ==> ");
            str.append(sql);
            str.append(";");
            return str.toString();
        } catch (Error e) {
            LogUtil.error("解析 sql 異常", e);
        }
        return "";
    }

    private String showSql(Configuration configuration, BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (parameterMappings != null && parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(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 = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    }
                }
            }
        }
        return sql;
    }

    private 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(obj) + "'";
        } else if (obj instanceof LocalDate) {
            value = "'" + ((LocalDate) obj).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "'";
        } else if (obj instanceof LocalDateTime) {
            value = "'" + ((LocalDateTime) obj).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) + "'";

        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        return value;
    }
}

上面代碼用到的兩個工具類：

RequestUtil.java

import com.tiku.enums.ResultEnum;
import com.tiku.ex.GlobalException;
import jakarta.servlet.http.HttpServletRequest;

import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * @author hc
 */
public class RequestUtil {
    /**
     * 私有構(gòu)造方法，防止實例化
     */
    private RequestUtil() {

    }

    /**
     * 獲取所有的請求參數(shù)及值
     *
     * @return eg：pageNum=3&pageSize=5&state=0
     */
    public static String getParameters() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        Enumeration<String> paraNames = request.getParameterNames();
        if (paraNames == null) {
            return null;
        }
        StringBuilder sb = new StringBuilder();
        while (paraNames.hasMoreElements()) {
            String paraName = paraNames.nextElement();
            sb.append("&").append(paraName).append("=").append(request.getParameter(paraName));
        }
        return sb.toString();
    }

    public static Map<String, String> getParameterMap(HttpServletRequest request) {
        // 參數(shù)Map
        Map<String, String[]> properties = request.getParameterMap();
        // 返回值Map
        Map<String, String> returnMap = new HashMap<>(16);
        Iterator<Map.Entry<String, String[]>> entries = properties.entrySet().iterator();
        Map.Entry<String, String[]> entry;
        String name;
        String value = "";
        while (entries.hasNext()) {
            entry = entries.next();
            name = entry.getKey();
            Object valueObj = entry.getValue();
            if (null == valueObj) {
                value = "";
            } else {
                String[] values = (String[]) valueObj;
                for (String value1 : values) {
                    value = value1 + ",";
                }
                value = value.substring(0, value.length() - 1);
            }
            returnMap.put(name, value);
        }
        return returnMap;
    }

    /**
     * 獲取用戶所有的請求參數(shù)，以Map的形式返回
     *
     * @return
     */
    public static Map<String, String[]> getParametersMap() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return new HashMap<>();
        }
        return request.getParameterMap();
    }

    /**
     * 獲取請求頭中指定名稱的值
     *
     * @param name
     * @return
     */
    public static String getHeader(String name) {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        return request.getHeader(name);
    }

    public static String getReferer() {
        return getHeader("Referer");
    }

    /**
     * 獲取用戶代理對象
     *
     * @return
     */
    public static String getUserAgent() {
        return getHeader("User-Agent");
    }

    /**
     * 獲取請求用戶的IP地址
     *
     * @return
     */
    public static String getIp() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        return IPUtil.getIpAddr(request);
    }

    /**
     * 獲取用戶請求的Controller的路徑
     *
     * @return 比如：http://localhost/list
     */
    public static String getRequestUrl() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        return request.getRequestURL().toString();
    }

    /**
     * 獲取URI
     *
     * @return
     */
    public static String getRequestUri() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        return request.getRequestURI();
    }

    /**
     * 獲取用戶請求的方式，值有： POST GET 3 PUT DELETE
     *
     * @return
     */
    public static String getMethod() {
        HttpServletRequest request = SpringContextUtil.getRequest();
        if (null == request) {
            return null;
        }
        return request.getMethod();
    }

    /**
     * 判斷用戶的請求是否是AJAX請求
     *
     * @param request
     * @return
     */
    public static boolean isAjax(HttpServletRequest request) {
        if (null == request) {
            request = SpringContextUtil.getRequest();
        }
        if (null == request) {
            return false;
        }
        // 解析原錯誤信息，封裝后返回，此處返回非法的字段名稱，原始值，錯誤信息
        // 使用HttpServletRequest中的header檢測請求是否為ajax, 如果是ajax則返回json, 如果為非ajax則返回view(即ModelAndView)
        String contentTypeHeader = request.getHeader("Content-Type");
        String acceptHeader = request.getHeader("Accept");
        String xRequestedWith = request.getHeader("X-Requested-With");
        return (contentTypeHeader != null && contentTypeHeader.contains("application/json"))
                || (acceptHeader != null && acceptHeader.contains("application/json"))
                || "XMLHttpRequest".equalsIgnoreCase(xRequestedWith);
    }

    /**
     * 從HttpServletReqeust對象中獲取key對應(yīng)的值 先從Reqeust Parameter中獲取，如果沒有再從Header中獲取
     *
     * @param key
     * @return
     */
    public static String getValueFromRequest(String key) {
        HttpServletRequest request = SpringContextUtil.getRequest();
        return getValueFromRequest(request, key);
    }

    /**
     * 從HttpServletReqeust對象中獲取key對應(yīng)的值 先從Reqeust Parameter中獲取，如果沒有再從Header中獲取
     *
     * @param request
     * @param key
     * @return
     */
    public static String getValueFromRequest(HttpServletRequest request, String key) {
        String token = request.getParameter(key);
        // 從header中獲取值
        if (token == null) {
            token = request.getHeader(key);
        }
        if (token == null) {
            throw new GlobalException(ResultEnum.TOKEN_INVALID);
        }
        return token;
    }
}

UserAgent

該工具類是第三方j(luò)ar包中定義的：UserAgentUtils-1.21.jar

注冊插件

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        interceptor.addInnerInterceptor(new MyBatisPlusSqlLogInterceptor());
        return interceptor;
    }

}

這樣在執(zhí)行CRUD操作數(shù)據(jù)時，就會在控制臺中輸出相應(yīng)的完整的SQL語句

比如：

2024-05-20 07:46:22.365 [INFO ] [] 6868 -- SqlLogInterceptor.beforeQuery() Line: 48 Line:100 : 用戶訪問了：http://127.0.0.1:8081/tiku/user/v1/login，瀏覽器是：Chrome 12，操作系統(tǒng)是：Windows 10，IP是：127.0.0.1，請求方式是：POST，請求參數(shù)是：，SQL語句是：com.tiku.mapper.UserDetailsMapper.selectById ==> SELECT id,num,nickname,`name`,avatar,gender,birth,email,tel,qq,wechat,credit,interest,country_id,addr,verify_time,login_time,login_ip,login_addr,creator_id,updater_id,info,priority,create_time,update_time FROM sys_user_details WHERE id=1;