Connector/J 8.0 拦截器的使用与探索

Connector/J 文档介绍

8.0 文档中替换了 5.0 的 StatementInterceptorV2

• connectionLifecycleInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor interface. In these kinds of interceptor classes, you might log events such as rollbacks, measure the time between transaction start and end, or count events such as calls to setAutoCommit().
• exceptionInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.cj.exceptions.ExceptionInterceptor interface. In these kinds of interceptor classes, you might add extra diagnostic information to exceptions that can have multiple causes or indicate a problem with server settings. exceptionInterceptors classes are called when handling an Exception thrown from Connector/J code.
• queryInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.cj.interceptors.QueryInterceptor interface. In these kinds of interceptor classes, you might change or augment the processing done by certain kinds of statements, such as automatically checking for queried data in a memcached server, rewriting slow queries, logging information about statement execution, or route requests to remote servers.

5.0 拦截器接口代码

5.0 中的 statementInterceptors 被 8.0 queryInterceptors替代，其拦截方法缺失了异常获取参数。5.0 中全实现 StatementInterceptorV2 即可拦截mysql查询前后细节和错误信息，如下代码中 postProcess 方法存在 SQLException var8参数。

package com.mysql.jdbc;

import java.sql.SQLException;
import java.util.Properties;

public interface StatementInterceptorV2 extends Extension {
    void init(Connection var1, Properties var2) throws SQLException;

    ResultSetInternalMethods preProcess(String var1, Statement var2, Connection var3) throws SQLException;

    boolean executeTopLevelOnly();

    void destroy();

    ResultSetInternalMethods postProcess(String var1, Statement var2, ResultSetInternalMethods var3, Connection var4, int var5, boolean var6, boolean var7, SQLException var8) throws SQLException;
}

preProcess 和 postProcess方法做前置处理和后置处理，其中 postProcess 方法可以获取异常信息（如果有异常抛出），而 8.0 则没有。

8.0 拦截器接口代码

package com.mysql.cj.interceptors;

import java.util.Properties;
import java.util.function.Supplier;

import com.mysql.cj.MysqlConnection;
import com.mysql.cj.Query;
import com.mysql.cj.log.Log;
import com.mysql.cj.protocol.Message;
import com.mysql.cj.protocol.Resultset;
import com.mysql.cj.protocol.ServerSession;

/**
 * Implement this interface to be placed "in between" query execution, so that you can influence it.
 * 
 * QueryInterceptors are "chainable" when configured by the user, the results returned by the "current" interceptor will be passed on to the next on in the
 * chain, from left-to-right order, as specified by the user in the driver configuration property "queryInterceptors".
 */
public interface QueryInterceptor {

    /**
     * Called once per connection that wants to use the interceptor
     * 
     * The properties are the same ones passed in in the URL or arguments to
     * Driver.connect() or DriverManager.getConnection().
     * 
     * @param conn
     *            the connection for which this interceptor is being created
     * @param props
     *            configuration values as passed to the connection. Note that
     *            in order to support javax.sql.DataSources, configuration properties specific
     *            to an interceptor <strong>must</strong> be passed via setURL() on the
     *            DataSource. QueryInterceptor properties are not exposed via
     *            accessor/mutator methods on DataSources.
     * @param log
     *            logger
     * @return {@link QueryInterceptor}
     */
    QueryInterceptor init(MysqlConnection conn, Properties props, Log log);

    /**
     * Called before the given query is going to be sent to the server for processing.
     * 
     * Interceptors are free to return a result set (which must implement the
     * interface {@link Resultset}), and if so,
     * the server will not execute the query, and the given result set will be
     * returned to the application instead.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param sql
     *            the Supplier for SQL representation of the query
     * @param interceptedQuery
     *            the actual {@link Query} instance being intercepted
     * @param <T>
     *            {@link Resultset} object
     * 
     * @return a {@link Resultset} that should be returned to the application instead
     *         of results that are created from actual execution of the intercepted
     *         query.
     */
    <T extends Resultset> T preProcess(Supplier<String> sql, Query interceptedQuery);

    /**
     * Called before the given query packet is going to be sent to the server for processing.
     * 
     * Interceptors are free to return a PacketPayload, and if so,
     * the server will not execute the query, and the given PacketPayload will be
     * returned to the application instead.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param queryPacket
     *            original {@link Message}
     * @param <M>
     *            {@link Message} object
     * @return processed {@link Message}
     */
    default <M extends Message> M preProcess(M queryPacket) {
        return null;
    }

    /**
     * Should the driver execute this interceptor only for the
     * "original" top-level query, and not put it in the execution
     * path for queries that may be executed from other interceptors?
     * 
     * If an interceptor issues queries using the connection it was created for,
     * and does not return <code>true</code> for this method, it must ensure
     * that it does not cause infinite recursion.
     * 
     * @return true if the driver should ensure that this interceptor is only
     *         executed for the top-level "original" query.
     */
    boolean executeTopLevelOnly();

    /**
     * Called by the driver when this extension should release any resources
     * it is holding and cleanup internally before the connection is
     * closed.
     */
    void destroy();

    /**
     * Called after the given query has been sent to the server for processing.
     * 
     * Interceptors are free to inspect the "original" result set, and if a
     * different result set is returned by the interceptor, it is used in place
     * of the "original" result set.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param sql
     *            the Supplier for SQL representation of the query
     * @param interceptedQuery
     *            the actual {@link Query} instance being intercepted
     * @param originalResultSet
     *            a {@link Resultset} created from query execution
     * @param serverSession
     *            {@link ServerSession} object after the query execution
     * @param <T>
     *            {@link Resultset} object
     * 
     * @return a {@link Resultset} that should be returned to the application instead
     *         of results that are created from actual execution of the intercepted
     *         query.
     */
    <T extends Resultset> T postProcess(Supplier<String> sql, Query interceptedQuery, T originalResultSet, ServerSession serverSession);

    /**
     * Called after the given query packet has been sent to the server for processing.
     * 
     * Interceptors are free to return either a different PacketPayload than the originalResponsePacket or null.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param queryPacket
     *            query {@link Message}
     * @param originalResponsePacket
     *            response {@link Message}
     * @param <M>
     *            {@link Message} object
     * @return {@link Message}
     */
    default <M extends Message> M postProcess(M queryPacket, M originalResponsePacket) {
        return null;
    }
}

8.0 的 QueryInterceptor 拦截器中没有获取异常信息的方式，通过查阅文档错误信息已经不能在后置处理方法中获取了，通过全实现EceptionInterceptor类可以处理异常信息。

我的方案

问题

但是这种改进对我的二次开发产生了影响，即我希望记录一次mysql处理的前后处理信息，把这些信息记录在一个对象中，而 QueryInterceptor中无法做到错误信息处理，这将带来较大的麻烦。我们知道spring boot启动后会维护一个线程池来接受用户请求，因此在处理拦截器问题上有主意 ThreadLocal 变量的使用。二当一次请求执行到 后置处理方法时我们并不知道此条sql执行是否错误，若发生错误才会被 EceptionInterceptor 拦截，若没有错误我们将得不到任何反馈。

解决方案

为了解决记录一次sql处理的前后信息包括异常，我目前采用的方法是做一个延时队列，当后置处理器处理完后，将该记录的数据拷贝一份到内存，并标记一唯一ID和为未处理完成，然后将其 offer 到延迟队列（延迟数百毫秒），如果当前sql线程产生异常并被ExceptionInterceptor 拦截，就可以通过 唯一 ID 从队列中找到该记录对象并添加上异常信息记录。这样就能解决一条sql执行的普通拦截信息和错误信息的同步存储。不过感觉使用延时队列来处理这样的问题有点大材小用，目前还在探索中，希望大家能提出更好的解决方案。