JDBC监听器-P6Spy

来源：互联网发布：上海游戏程序员招聘编辑：程序博客网时间：2024/06/05 07:55

JDBC监听器-P6Spy

sf2gis@163.com

2015年12月7日

1 目标：监听JDBC对数据库的修改，并显示相关的SQL。

2 原理：封装JDBC驱动，监听JDBC调用。

3 流程：下载，安装，配置，结果查看。

参考：http://www.iteye.com/topic/4143

3.1 下载：从SourceForge中下载p6spy-install.jar。

http://sourceforge.net/projects/p6spy/?source=typ_redirect

注意：不要下载github中的zip。

3.2 安装：解压后，将pyspy.jar复制到工程的buildpath。将spy.properties复制到src目录。

3.3 配置：配置spy.properties和hibernate。

基本上保持spy.properties的默认值，只需修改

真正的驱动：realdriver=com.mysql.jdbc.Driver。

注销驱动注册（如果注册早了，会使用真正的驱动，无法监听）：deregisterdrivers=true

//spy.properties

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

# P6Spy Options File #

# See documentation fordetailed instructions #

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

# MODULES #

# #

# Modules provide the P6Spyfunctionality. If a module, such #

# as module_log is commentedout, that functionality will not #

# be available. If it is not commented out (if it isactive), #

# the functionality will beactive. #

# #

# Values set in Modulescannot be reloaded using the #

# reloadpropertiesvariable. Once they are loaded, theyremain #

# in memory until theapplication is restarted. #

# #

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

module.log=com.p6spy.engine.logging.P6LogFactory

#module.outage=com.p6spy.engine.outage.P6OutageFactory

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

# REALDRIVER(s) #

# #

# In your application serverconfiguration file you replace the #

# "real driver"name with com.p6spy.engine.P6SpyDriver. This is #

# where you put the name ofyour real driver P6Spy can find and #

# register your real driverto do the database work. #

# #

# If your application usesseveral drivers specify them in #

# realdriver2,realdriver3. See the documentation formore #

# details. #

# #

# Values set in REALDRIVER(s)cannot be reloaded using the #

# reloadpropertiesvariable. Once they are loaded, theyremain #

# in memory until theapplication is restarted. #

# #

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

# oracle driver

#realdriver=oracle.jdbc.driver.OracleDriver

# mysql Connector/J driver

#realdriver=com.mysql.jdbc.Driver

# informix driver

#realdriver=com.informix.jdbc.IfxDriver

# ibm db2 driver

#realdriver=COM.ibm.db2.jdbc.net.DB2Driver

# the mysql open sourcedriver

realdriver=com.mysql.jdbc.Driver

#specifies another driver touse

realdriver2=

#specifies a third driver touse

realdriver3=

#the DriverManager classsequentially tries every driver that is

#registered to find the rightdriver. In some instances, it's possibleto

#load up the realdriverbefore the p6spy driver, in which case your connections

#will not get wrapped as therealdriver will "steal" the connection before

#p6spy sees it. Set the following property to"true" to cause p6spy to

#explicitily deregister therealdrivers

deregisterdrivers=true

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

# P6LOG SPECIFICPROPERTIES #

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

# no properties currentlyavailable

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

# EXECUTION THRESHOLDPROPERTIES #

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

# This feature applies to thestandard logging of P6Spy. #

# While the standard logginglogs out every statement #

# regardless of its executiontime, this feature puts a time #

# condition on thatlogging. Only statements that havetaken #

# longer than the timespecified (in milliseconds) will be #

# logged. This way it is possible to see onlystatements that #

# have exceeded some highwater mark. #

# This time isreloadable. #

# executionthreshold=integertime (milliseconds)

executionthreshold=

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

# P6OUTAGE SPECIFICPROPERTIES #

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

# Outage Detection

# This feature detectslong-running statements that may be indicative of

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

# statement that surpassesthe configurable time boundary during its execution.

# When this feature isenabled, no other statements are logged except the long

# running statements. Theinterval property is the boundary time set in seconds.

# For example, if this is setto 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 belogged 5 times (at the 2, 4, 6, 8, 10 second intervals).

# outagedetection=true|false

#outagedetectioninterval=integer time (seconds)

outagedetection=false

outagedetectioninterval=

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

# COMMON PROPERTIES #

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

# filter what is logged

filter=false

# comma separated list oftables to include when filtering

include =

# comma separated list oftables to exclude when filtering

exclude =

# sql expression to evaluateif using regex filtering

sqlexpression =

# turn on tracing

autoflush = true

# sets the date format usingJava's SimpleDateFormat routine

dateformat=

#list of categories toexplicitly include

includecategories=

#list of categories toexclude: error, info, batch, debug, statement,

#commit, rollback and resultare valid values

excludecategories=info,debug,result,batch

#allows you to use a regexengine or your own matching engine to determine

#which statements to log

#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher

#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher

stringmatcher=

# prints a stack trace forevery statement logged

stacktrace=false

# if stacktrace=true,specifies the stack trace to print

stacktraceclass=

# determines if property fileshould be reloaded

reloadproperties=false

# determines how often shouldbe reloaded in seconds

reloadpropertiesinterval=60

#if=true then url must beprefixed with p6spy:

useprefix=false

#specifies the appender touse for logging

#appender=com.p6spy.engine.logging.appender.Log4jLogger

#appender=com.p6spy.engine.logging.appender.StdoutLogger

appender=com.p6spy.engine.logging.appender.FileLogger

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

logfile = spy.log

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

# log file is truncated everytime. (file logger only)

append=true

#The following are for log4jlogging only

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender

log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout

log4j.appender.STDOUT.layout.ConversionPattern=p6spy- %m%n

#log4j.appender.CHAINSAW_CLIENT=org.apache.log4j.net.SocketAppender

#log4j.appender.CHAINSAW_CLIENT.RemoteHost=localhost

#log4j.appender.CHAINSAW_CLIENT.Port=4445

#log4j.appender.CHAINSAW_CLIENT.LocationInfo=true

log4j.logger.p6spy=INFO,STDOUT

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

# DataSource replacement #

# #

# Replace the real DataSourceclass in your application server #

# configuration with the namecom.p6spy.engine.spy.P6DataSource,#

# then add the JNDI name andclass name of the real #

# DataSource here #

# #

# Values set in this itemcannot be reloaded using the #

# reloadpropertiesvariable. Once it is loaded, itremains #

# in memory until theapplication is restarted. #

# #

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

#realdatasource=/RealMySqlDS

#realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

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

# DataSource properties #

# #

# If you are using theDataSource support to intercept calls #

# to a DataSource thatrequires properties for proper setup, #

# define those propertieshere. Use name value pairs, separate #

# the name and value with asemicolon, and separate the #

# pairs with commas. #

# #

# The example shown here isfor mysql #

# #

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

#realdatasourceproperties=port;3306,serverName;ibmhost,databaseName;mydb

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

# JNDI DataSource lookup #

# #

# If you are using theDataSource support outside of an app #

# server, you will probablyneed to define the JNDI Context #

# environment. #

# #

# If the P6Spy code will beexecuting inside an app server then #

# do not use theseproperties, and the DataSource lookup will #

# use the naming contextdefined by the app server. #

# #

# The two standard elementsof the naming environment are #

# jndicontextfactory andjndicontextproviderurl. If you need #

# additional elements, usethe jndicontextcustom property. #

# You can define multipleproperties in jndicontextcustom, #

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

# semicolon, and separate thepairs with commas. #

# #

# The example shown here isfor a standalone program running on #

# a machine that is alsorunning JBoss, so the JDNI context #

# is configured for JBoss(3.0.4). #

# #

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

#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

3.4 运行：调用Hibernate操作。

Hibernate工程参见：JavaEE-ORM映射器Hibernate.docx流程章节。

3.5 结果查看：默认的log会生成在工具目录：spy.log。

//spy.log

1449479295263|-1||debug||com.p6spy.engine.common.P6SpyOptionsreloading properties

1449479295267|-1||info||Usingproperties file: /home/sf/test/workspace/HibCRUD/bin/spy.properties

1449479295267|-1||info||Novalue in environment for: getStackTrace, using: false

1449479295267|-1||info||Novalue in environment for: getDeregisterDrivers, using: true

1449479295267|-1||info||Novalue in environment for: getUsePrefix, using: false

1449479295267|-1||info||Novalue in environment for: getExecutionThreshold, using: 0

1449479295267|-1||info||Novalue in environment for: getAutoflush, using: true

1449479295267|-1||info||Novalue in environment for: getExclude, using:

1449479295267|-1||info||Novalue in environment for: getExcludecategories, using: info,debug,result,batch

1449479295267|-1||info||Novalue in environment for: getFilter, using: false

1449479295267|-1||info||Novalue in environment for: getInclude, using:

1449479295268|-1||info||Novalue in environment for: getIncludecategories, using:

1449479295268|-1||info||Novalue in environment for: getLogfile, using: spy.log

1449479295268|-1||info||Novalue in environment for: getAppender, using:com.p6spy.engine.logging.appender.FileLogger

1449479295268|-1||info||Novalue in environment for: getRealdriver, using: com.mysql.jdbc.Driver

1449479295268|-1||info||Novalue in environment for: getRealdriver2, using:

1449479295268|-1||info||Novalue in environment for: getRealdriver3, using:

1449479295268|-1||info||Novalue in environment for: getAppend, using: true

1449479295268|-1||info||Novalue in environment for: getSpydriver, using: com.p6spy.engine.spy.P6SpyDriver

1449479295268|-1||info||Novalue in environment for: getDateformat, using:

1449479295268|-1||info||Novalue in environment for: getDateformatter, using: null

1449479295268|-1||info||Novalue in environment for: getStringmatcher, using:com.p6spy.engine.common.SubstringMatcher

1449479295268|-1||info||Novalue in environment for: getStringMatcherEngine, using:com.p6spy.engine.common.SubstringMatcher@42aaff8e

1449479295268|-1||info||Novalue in environment for: getStackTraceClass, using:

1449479295268|-1||info||Novalue in environment for: getSQLExpression, using: null

1449479295268|-1||info||Novalue in environment for: getReloadProperties, using: false

1449479295268|-1||info||Novalue in environment for: getReloadPropertiesInterval, using: 60

1449479295268|-1||info||Novalue in environment for: getJNDIContextFactory, using: null

1449479295268|-1||info||Novalue in environment for: getJNDIContextProviderURL, using: null

1449479295268|-1||info||Novalue in environment for: getJNDIContextCustom, using: null

1449479295269|-1||info||Novalue in environment for: getRealDataSource, using: null

1449479295269|-1||info||Novalue in environment for: getRealDataSourceClass, using: null

1449479295269|-1||info||Novalue in environment for: getRealDataSourceProperties, using: null

1449479295649|2|0|statement|selectemployee0_.ID as ID0_0_, employee0_.EMP_NAME as EMP2_0_0_,employee0_.EMP_ADDRESS as EMP3_0_0_, employee0_.EMP_MOBILE_NOS as EMP4_0_0_from EMPLOYEE employee0_ where employee0_.ID=?|select employee0_.ID as ID0_0_,employee0_.EMP_NAME as EMP2_0_0_, employee0_.EMP_ADDRESS as EMP3_0_0_,employee0_.EMP_MOBILE_NOS as EMP4_0_0_ from EMPLOYEE employee0_ whereemployee0_.ID=1

1449479295652|-1||resultset|select employee0_.ID as ID0_0_,employee0_.EMP_NAME as EMP2_0_0_, employee0_.EMP_ADDRESS as EMP3_0_0_,employee0_.EMP_MOBILE_NOS as EMP4_0_0_ from EMPLOYEE employee0_ whereemployee0_.ID=1|EMP2_0_0_ = zzz, EMP3_0_0_ = xxx, EMP4_0_0_ = yyy

//Hibernate输出

Hibernate: selectemployee0_.ID as ID0_0_, employee0_.EMP_NAME as EMP2_0_0_,employee0_.EMP_ADDRESS as EMP3_0_0_, employee0_.EMP_MOBILE_NOS as EMP4_0_0_from EMPLOYEE employee0_ where employee0_.ID=?zzz:yyy

4 方法

5 示例

6 扩展

0 0