pom.xml

　　p6spy 　　p6spy 　　3.8.1

application.yml修改mysql数据库连接

spring:
datasource:
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:p6spy:mysql://xxxx:3306/jw_user?useUnicode=true&characterEncoding=utf-8
username: xxxx
password: xxxx

spy.properties

这里需要注意的是2个文件的路径需要修改（文件代码在下面）

appender=com.example.mybatis_3_5.p6spy.P6spyLogger

logMessageFormat=com.example.mybatis_3_5.p6spy.P6spyLogFormat

#################################################################

P6Spy Options File #

See documentation for detailed instructions #

http://p6spy.github.io/p6spy/2.0/configandusage.html #

#################################################################

#################################################################

MODULES #

#

Module list adapts the modular functionality of P6Spy. #

Only modules listed are active. #

(default is com.p6spy.engine.logging.P6LogFactory and #

com.p6spy.engine.spy.P6SpyFactory) #

Please note that the core module (P6SpyFactory) can't be #

deactivated. #

Unlike the other properties, activation of the changes on #

this one requires reload. #

#################################################################
#modulelist=com.p6spy.engine.spy.P6SpyFactory,com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory

################################################################

CORE (P6SPY) PROPERTIES #

################################################################

A comma separated list of JDBC drivers to load and register.

(default is empty)

Note: This is normally only needed when using P6Spy in an

application server environment with a JNDI data source or when

using a JDBC driver that does not implement the JDBC 4.0 API

(specifically automatic registration).

driverlist=com.mysql.jdbc.Driver

for flushing per statement

(default is false)

#autoflush = false

sets the date format using Java's SimpleDateFormat routine.

In case property is not set, miliseconds since 1.1.1970 (unix time) is used (default is empty)

dateformat=yyyy-MM-dd HH:mm:ss

prints a stack trace for every statement logged

#stacktrace=false

if stacktrace=true, specifies the stack trace to print

#stacktraceclass=

determines if property file should be reloaded

Please note: reload means forgetting all the previously set

settings (even those set during runtime - via JMX)

and starting with the clean table

(default is false)

#reloadproperties=false

determines how often should be reloaded in seconds

(default is 60)

#reloadpropertiesinterval=60

specifies the appender to use for logging

Please note: reload means forgetting all the previously set

settings (even those set during runtime - via JMX)

and starting with the clean table

(only the properties read from the configuration file)

(default is com.p6spy.engine.spy.appender.FileLogger)

#appender=com.p6spy.engine.spy.appender.Slf4JLogger
#appender=com.p6spy.engine.spy.appender.StdoutLogger
#appender=com.p6spy.engine.spy.appender.FileLogger
#自定义的SQL格式化输出
appender=com.example.mybatis_3_5.p6spy.P6spyLogger

name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log)

(used for com.p6spy.engine.spy.appender.FileLogger only)

(default is spy.log)

#logfile = spy.log

append to the p6spy log file. if this is set to false the

log file is truncated every time. (file logger only)

(default is true)

#append=true

class to use for formatting log messages (default is: com.p6spy.engine.spy.appender.SingleLineFormat)

logMessageFormat=com.example.mybatis_3_5.p6spy.P6spyLogFormat
#logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat

format that is used for logging of the date/time/… (has to be compatible with java.text.SimpleDateFormat)

(default is dd-MMM-yy)

databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss

whether to expose options via JMX or not

(default is true)

#jmx=true

if exposing options via jmx (see option: jmx), what should be the prefix used?

jmx naming pattern constructed is: com.p6spy(.)?:name=

please note, if there is already such a name in use it would be unregistered first (the last registered wins)

(default is none)

#jmxPrefix=

if set to true, the execution time will be measured in nanoseconds as opposed to milliseconds

(default is false)

#useNanoTime=false

#################################################################

DataSource replacement #

#

Replace the real DataSource class in your application server #

configuration with the name com.p6spy.engine.spy.P6DataSource #

(that provides also connection pooling and xa support). #

then add the JNDI name and class name of the real #

DataSource here #

#

Values set in this item cannot be reloaded using the #

reloadproperties variable. Once it is loaded, it remains #

in memory until the application is restarted. #

#

#################################################################
#realdatasource=/RealMySqlDS
#realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

#################################################################

DataSource properties #

#

If you are using the DataSource support to intercept calls #

to a DataSource that requires properties for proper setup, #

define those properties here. Use name value pairs, separate #

the name and value with a semicolon, and separate the #

pairs with commas. #

#

The example shown here is for mysql #

#

#################################################################
#realdatasourceproperties=port;3306,serverName;myhost,databaseName;jbossdb,foo;bar

#################################################################

JNDI DataSource lookup #

#

If you are using the DataSource support outside of an app #

server, you will probably need to define the JNDI Context #

environment. #

#

If the P6Spy code will be executing inside an app server then #

do not use these properties, and the DataSource lookup will #

use the naming context defined by the app server. #

#

The two standard elements of the naming environment are #

jndicontextfactory and jndicontextproviderurl. If you need #

additional elements, use the jndicontextcustom property. #

You can define multiple properties in jndicontextcustom, #

in name value pairs. Separate the name and value with a #

semicolon, and separate the pairs with commas. #

#

The example shown here is for a standalone program running on #

a machine that is also running JBoss, so the JDNI context #

is configured for JBoss (3.0.4). #

#

(by default all these are empty) #

#################################################################
#jndicontextfactory=org.jnp.interfaces.NamingContextFactory
#jndicontextproviderurl=localhost:1099
#jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.nameing:org.jnp.interfaces

#jndicontextfactory=com.ibm.websphere.naming.WsnInitialContextFactory
#jndicontextproviderurl=iiop://localhost:900

################################################################

P6 LOGGING SPECIFIC PROPERTIES #

################################################################

filter what is logged

please note this is a precondition for usage of: include/exclude/sqlexpression

(default is false)

#filter=false

comma separated list of strings to include

please note that special characters escaping (used in java) has to be done for the provided regular expression

(default is empty)

#include =

comma separated list of strings to exclude

(default is empty)

#exclude =

sql expression to evaluate if using regex

please note that special characters escaping (used in java) has to be done for the provided regular expression

(default is empty)

#sqlexpression =

#list of categories to exclude: error, info, batch, debug, statement,
#commit, rollback and result are valid values

(default is info,debug,result,resultset,batch)

#excludecategories=info,debug,result,resultset,batch

Execution threshold applies to the standard logging of P6Spy.

While the standard logging logs out every statement

regardless of its execution time, this feature puts a time

condition on that logging. Only statements that have taken

longer than the time specified (in milliseconds) will be

logged. This way it is possible to see only statements that

have exceeded some high water mark.

This time is reloadable.

executionThreshold=integer time (milliseconds)

(default is 0)

#executionThreshold=

################################################################

P6 OUTAGE SPECIFIC PROPERTIES #

################################################################

Outage Detection

This feature detects long-running statements that may be indicative of

a database outage problem. If this feature is turned on, it will log any

statement that surpasses the configurable time boundary during its execution.

When this feature is enabled, no other statements are logged except the long

running statements. The interval property is the boundary time set in seconds.

For example, if this is set to 2, then any statement requiring at least 2

seconds will be logged. Note that the same statement will continue to be logged

for as long as it executes. So if the interval is set to 2, and the query takes

11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals).

outagedetection=true|false

outagedetectioninterval=integer time (seconds)

(default is false)

#outagedetection=false

(default is 60)

#outagedetectioninterval=30

P6spyLogFormat.java

package com.example.mybatis_3_5.p6spy;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.springframework.util.StringUtils;

/**
*

* P6spy SQL 日志格式化 *

*
* @author tzq
* @date 2019-08-08 9:50
*/
public class P6spyLogFormat implements MessageFormattingStrategy {

@Override  
public String formatMessage(final int connectionId, final String now, final long elapsed, final String category, final String prepared, final String sql, final String url) {  
    return !StringUtils.isEmpty(sql) ? "Execute SQL：" + sql.replaceAll("\[\\\\s\]+", " ") : null;  
}  

}

P6spyLogger.java

package com.example.mybatis_3_5.p6spy;

import com.p6spy.engine.logging.Category;
import com.p6spy.engine.spy.appender.FormattedLogger;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

/**
*

* P6spy日志实现 *

*
* @author tzq
* @date 2019-08-08 9:54
*/
@Slf4j
public class P6spyLogger extends FormattedLogger {

@Override  
public void logException(Exception e) {  
    log.info("", e);  
}

@Override  
public void logText(String text) {  
    log.info(text);  
}

@Override  
public void logSQL(int connectionId, String now, long elapsed, Category category, String prepared, String sql, String url) {  
    final String msg = strategy.formatMessage(connectionId, now, elapsed,  
        category.toString(), prepared, sql, url);

    if (StringUtils.isEmpty(msg)) {  
        return;  
    }  
    if (Category.ERROR.equals(category)) {  
        log.error(msg);  
    } else if (Category.WARN.equals(category)) {  
        log.warn(msg);  
    } else if (Category.DEBUG.equals(category)) {  
        log.debug(msg);  
    } else {  
        log.info(msg);  
    }  
}

@Override  
public boolean isCategoryEnabled(Category category) {  
    if (Category.ERROR.equals(category)) {  
        return log.isErrorEnabled();  
    } else if (Category.WARN.equals(category)) {  
        return log.isWarnEnabled();  
    } else if (Category.DEBUG.equals(category)) {  
        return log.isDebugEnabled();  
    } else {  
        return log.isInfoEnabled();  
    }  
}  

}