SQL Monitoring, the Key Technology of Java Call Chain Tracking

I. General Thought of SQL Monitoring

In Java, SQL monitoring is generally implemented through proxy mode, as follows:

  1. On the right is the JDBC interface class.
  2. On the left is the proxy class, which needs to implement the JDBC interface and hold object instances of the interface class.
  3. The database connection factory used to get the JDBC interface class and continue to operate. It changed to get the proxy class to operate. Because the proxy class implements the JDBC interface, it is insensitive to the caller and still calls in the original way.

Therefore, the worst case is to implement the proxy class by itself, which is not very difficult, but all the interface methods should be implemented once, which is pure manual work.

2. Open source JAVA SQL monitoring tool p6spy

P6spy is a Java open source monitoring tool, which has been out for more than ten years. It should be very mature. Here's how to use p6spy.
1. Introducing maven dependencies, the latest version 3.8.3

<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>3.8.3</version>
</dependency>

2. Implementing monitoring classes
As long as the following interface classes are implemented, the logSQL method can already obtain the necessary information and directly use it in the implementation classes.

package com.p6spy.engine.spy.appender;

import com.p6spy.engine.logging.Category;

public interface P6Logger {

        /**
         * Logs the {@code SQL}.
         * 
         * @param connectionId
         *            connection identifier.
         * @param now
         *            current time.
         * @param elapsed
         * @param category
         *            the category to be used for logging.
         * @param prepared
         *            the prepared statement to be logged.
         * @param sql
         *            the {@code SQL} to be logged.
         * @param url
         *            the database url where the sql statement executed
         */
        public void logSQL(int connectionId, String now, long elapsed,
                        Category category, String prepared, String sql, String url);

        /**
         * Logs the stacktrace of the exception.
         * 
         * @param e
         *            exception holding the stacktrace to be logged.
         */
        public void logException(Exception e);

        /**
         * Logs the text.
         * 
         * @param text
         *            to be logged
         */
        public void logText(String text);

        /**
         * @param category
         *            the category to be evaluated.
         * @return {@code true} if category is enabled. Otherwise returns
         *         {@code false}
         */
        public boolean isCategoryEnabled(Category category);
}

3. Configuration
Put the p6spy/src/main/assembly/individualFiles/spy.properties file under the p6spy project into the resources directory or classpath, find appender to modify the class name to your own implementation class, and remove#:

#appender=com.p6spy.engine.spy.appender.FileLogger

All other configurations can be monitored to SQL without modification.
4. Modify Spring's configuration
1) jdbc.driver replaced by com.p6spy.engine.spy.P6SpyDriver

spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

2) Replace jdbc.url with jdbc:p6spy:mysql:/xxx

spring.datasource.url=jdbc:p6spy:mysql://XXX

XXX part is replaced by itself. So far, you can monitor the SQL statements.

p6spy project address: https://github.com/p6spy/p6spy

end.

Join the "Java Stack Camp" knowledge planet, participate in discussions, more real code sharing, not just a few kilograms of apples, a few glorious props!

https://t.zsxq.com/RNzfi2j

Tags: Java SQL JDBC Spring

Posted on Sun, 04 Aug 2019 04:13:50 -0700 by Simply Me