配置p6spy打印完整sql语句-蒲公英云

在项目中我们经常遇到控制台输出sql语句带有“？”例如：INSERT INTO tb_user ( name ) VALUES ( ? )

这种情况非常影响我们调试程序，做为程序猿的我们是绝对不允许的。
引用p6spy解决这个问题。

首先去maven下载p6spy的Jar包

p6spy
p6spy
最新版本
在resources目录新建一个spy.properties文件
spy.properties文件里面的内容为

#
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 HHss

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.p6spy.engine.spy.appender.StdoutLogger

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.p6spy.engine.spy.appender.SingleLineFormat

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 HHss

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
配置spring数据源

在这里插入图片描述

修改连接驱动

//localhost:3306/test 以前连接配置" class="reference-link">jdbc.jdbcUrl=jdbc//localhost:3306/test 以前连接配置
jdbc.jdbcUrl=jdbcmysql://localhost:3306/test p6spy连接配置

jdbc.driverClass=com.mysql.jdbc.Driver 以前驱动配置
jdbc.driverClass=com.p6spy.engine.spy.P6SpyDriver p6spy驱动配置
jdbc.user=root
jdbc.password=root

在控制台就可以显示我们需要sql的语句了

深入了解

在spy.properties配置文件中有个logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat 我们可以点进去看看SingleLineFormat这个类，该方法如下：

public class SingleLineFormat implements MessageFormattingStrategy {
    public SingleLineFormat() {
    }
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        return now + "|" + elapsed + "|" + category + "|connection " + connectionId + "|url " + url + "|" + P6Util.singleLine(prepared) + "|" + P6Util.singleLine(sql);
    }
}