场景需求：在eos中打印sql语句，包括数据实体，查询实体和命名sql的sql语句。 所需资源： P6spy：负责拦截sql，并打印。 Sqlprofiler：解析p6spy拦截的sql，在基于swing的图形界面上显示，并提供监控分析功能。 Spy.properties：p6spy配置文件，用来指定数据库驱动，打印sql的目的地（文件，控制台，sqlprofiler界面），打印格式等。
################################################################P6Spy Options File #
See documentation for detailed instructions # ################################################################# #################################################################MODULES ##Modules provide the P6Spy functionality. If a module, such #as module_log is commented out, that functionality will not #be available. If it is not commented out (if it is active), #the functionality will be active. ##Values set in Modules cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## ################################################################# module.log=com.p6spy.engine.logging.P6LogFactory #module.outage=com.p6spy.engine.outage.P6OutageFactory #################################################################REALDRIVER(s) ##In your application server configuration file you replace the #"real driver" name with com.p6spy.engine.P6SpyDriver. This is #where you put the name of your real driver P6Spy can find and #register your real driver to do the database work. ##If your application uses several drivers specify them in #realdriver2, realdriver3. See the documentation for more #details. ##Values set in REALDRIVER(s) cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## #################################################################oracle driverrealdriver=oracle.jdbc.driver.OracleDrivermysql Connector/J driverrealdriver=com.mysql.jdbc.Driverinformix driverrealdriver=com.informix.jdbc.IfxDriveribm db2 driverrealdriver=COM.ibm.db2.jdbc.net.DB2Driverthe mysql open source driver realdriver=com.mysql.jdbc.Driver #specifies another driver to use realdriver2= #specifies a third driver to use realdriver3= #the DriverManager class sequentially tries every driver that is #registered to find the right driver. In some instances, it's possible to #load up the realdriver before the p6spy driver, in which case your connections #will not get wrapped as the realdriver will "steal" the connection before #p6spy sees it. Set the following property to "true" to cause p6spy to #explicitily deregister the realdrivers deregisterdrivers=false ################################################################P6LOG SPECIFIC PROPERTIES # ################################################################no properties currently available ################################################################EXECUTION THRESHOLD PROPERTIES # ################################################################This feature 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) # executionthreshold= ################################################################P6OUTAGE SPECIFIC PROPERTIES # ################################################################Outage Detection #This feature detects long-running statements that may be indicative ofa database outage problem. If this feature is turned on, it will log anystatement that surpasses the configurable time boundary during its execution.When this feature is enabled, no other statements are logged except the longrunning 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 2seconds will be logged. Note that the same statement will continue to be loggedfor as long as it executes. So if the interval is set to 2, and the query takes11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals). #outagedetection=true|falseoutagedetectioninterval=integer time (seconds) # outagedetection=false outagedetectioninterval= ################################################################COMMON PROPERTIES # ################################################################filter what is logged filter=falsecomma separated list of tables to include when filtering include =comma separated list of tables to exclude when filtering exclude =sql expression to evaluate if using regex filtering sqlexpression = turn on tracing autoflush = truesets the date format using Java's SimpleDateFormat routine dateformat=yyyy-MM-dd HH:mm:ss:SS #list of categories to explicitly include includecategories= #list of categories to exclude: error, info, batch, debug, statement, #commit, rollback and result are valid values excludecategories=info,debug,result,batch #allows you to use a regex engine 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 for every statement logged stacktrace=falseif stacktrace=true, specifies the stack trace to print stacktraceclass=determines if property file should be reloaded reloadproperties=falsedetermines how often should be reloaded in seconds reloadpropertiesinterval=60 #if=true then url must be prefixed with p6spy: useprefix=false #specifies the appender to use for logging #appender=com.p6spy.engine.logging.appender.Log4jLogger appender=com.p6spy.engine.logging.appender.StdoutLogger #appender=com.p6spy.engine.logging.appender.FileLoggername 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.logappend to the p6spy log file. if this is set to false thelog file is truncated every time. (file logger only) append=true #The following are for log4j logging 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.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender #log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost #log4j.appender.SQLPROFILER_CLIENT.Port=4445 #log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true log4j.logger.p6spy=INFO,STDOUT #################################################################DataSource replacement ##Replace the real DataSource class in your application server #configuration with the name com.p6spy.engine.spy.P6DataSource,#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;ibmhost,databaseName;mydb #################################################################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). ## ################################################################# #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启动eos服务器，控制台在调用数据库查询的时候会打印如下的内容，即配置成功：
#################################################################
P6Spy Options File #See documentation for detailed instructions # ################################################################# #################################################################MODULES ##Modules provide the P6Spy functionality. If a module, such #as module_log is commented out, that functionality will not #be available. If it is not commented out (if it is active), #the functionality will be active. ##Values set in Modules cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## ################################################################# module.log=com.p6spy.engine.logging.P6LogFactory #module.outage=com.p6spy.engine.outage.P6OutageFactory #################################################################REALDRIVER(s) ##In your application server configuration file you replace the #"real driver" name with com.p6spy.engine.P6SpyDriver. This is #where you put the name of your real driver P6Spy can find and #register your real driver to do the database work. ##If your application uses several drivers specify them in #realdriver2, realdriver3. See the documentation for more #details. ##Values set in REALDRIVER(s) cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## #################################################################oracle driverrealdriver=oracle.jdbc.driver.OracleDrivermysql Connector/J driverrealdriver=com.mysql.jdbc.Driverinformix driverrealdriver=com.informix.jdbc.IfxDriveribm db2 driverrealdriver=COM.ibm.db2.jdbc.net.DB2Driverthe mysql open source driver realdriver=com.mysql.jdbc.Driver #specifies another driver to use realdriver2= #specifies a third driver to use realdriver3= #the DriverManager class sequentially tries every driver that is #registered to find the right driver. In some instances, it's possible to #load up the realdriver before the p6spy driver, in which case your connections #will not get wrapped as the realdriver will "steal" the connection before #p6spy sees it. Set the following property to "true" to cause p6spy to #explicitily deregister the realdrivers deregisterdrivers=false ################################################################P6LOG SPECIFIC PROPERTIES # ################################################################no properties currently available ################################################################EXECUTION THRESHOLD PROPERTIES # ################################################################This feature 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) # executionthreshold= ################################################################P6OUTAGE SPECIFIC PROPERTIES # ################################################################Outage Detection #This feature detects long-running statements that may be indicative ofa database outage problem. If this feature is turned on, it will log anystatement that surpasses the configurable time boundary during its execution.When this feature is enabled, no other statements are logged except the longrunning 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 2seconds will be logged. Note that the same statement will continue to be loggedfor as long as it executes. So if the interval is set to 2, and the query takes11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals). #outagedetection=true|falseoutagedetectioninterval=integer time (seconds) # outagedetection=false outagedetectioninterval= ################################################################COMMON PROPERTIES # ################################################################filter what is logged filter=falsecomma separated list of tables to include when filtering include =comma separated list of tables to exclude when filtering exclude =sql expression to evaluate if using regex filtering sqlexpression = turn on tracing autoflush = truesets the date format using Java's SimpleDateFormat routine dateformat=yyyy-MM-dd HH:mm:ss:SS #list of categories to explicitly include includecategories= #list of categories to exclude: error, info, batch, debug, statement, #commit, rollback and result are valid values excludecategories=info,debug,result,batch #allows you to use a regex engine 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 for every statement logged stacktrace=falseif stacktrace=true, specifies the stack trace to print stacktraceclass=determines if property file should be reloaded reloadproperties=falsedetermines how often should be reloaded in seconds reloadpropertiesinterval=60 #if=true then url must be prefixed with p6spy: useprefix=false #specifies the appender to use for logging #appender=com.p6spy.engine.logging.appender.Log4jLogger #appender=com.p6spy.engine.logging.appender.StdoutLogger appender=com.p6spy.engine.logging.appender.FileLoggername 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 = c:/abc/spy.logappend to the p6spy log file. if this is set to false thelog file is truncated every time. (file logger only) append=true #The following are for log4j logging 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 DataSource class in your application server #configuration with the name com.p6spy.engine.spy.P6DataSource,#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;ibmhost,databaseName;mydb #################################################################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). ## ################################################################# #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启动前请在指定的路径下新建日志文件（logfile = c:/abc/spy.log），启动EOS服务器会在c:/abc/spy.log看到如下内容，表示配置成功：
#################################################################
P6Spy Options File #See documentation for detailed instructions # ################################################################# #################################################################MODULES ##Modules provide the P6Spy functionality. If a module, such #as module_log is commented out, that functionality will not #be available. If it is not commented out (if it is active), #the functionality will be active. ##Values set in Modules cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## ################################################################# module.log=com.p6spy.engine.logging.P6LogFactory #module.outage=com.p6spy.engine.outage.P6OutageFactory #################################################################REALDRIVER(s) ##In your application server configuration file you replace the #"real driver" name with com.p6spy.engine.P6SpyDriver. This is #where you put the name of your real driver P6Spy can find and #register your real driver to do the database work. ##If your application uses several drivers specify them in #realdriver2, realdriver3. See the documentation for more #details. ##Values set in REALDRIVER(s) cannot be reloaded using the #reloadproperties variable. Once they are loaded, they remain #in memory until the application is restarted. ## #################################################################oracle driverrealdriver=oracle.jdbc.driver.OracleDrivermysql Connector/J driverrealdriver=com.mysql.jdbc.Driverinformix driverrealdriver=com.informix.jdbc.IfxDriveribm db2 driverrealdriver=COM.ibm.db2.jdbc.net.DB2Driverthe mysql open source driver realdriver=com.mysql.jdbc.Driver #specifies another driver to use realdriver2= #specifies a third driver to use realdriver3= #the DriverManager class sequentially tries every driver that is #registered to find the right driver. In some instances, it's possible to #load up the realdriver before the p6spy driver, in which case your connections #will not get wrapped as the realdriver will "steal" the connection before #p6spy sees it. Set the following property to "true" to cause p6spy to #explicitily deregister the realdrivers deregisterdrivers=false ################################################################P6LOG SPECIFIC PROPERTIES # ################################################################no properties currently available ################################################################EXECUTION THRESHOLD PROPERTIES # ################################################################This feature 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) # executionthreshold= ################################################################P6OUTAGE SPECIFIC PROPERTIES # ################################################################Outage Detection #This feature detects long-running statements that may be indicative ofa database outage problem. If this feature is turned on, it will log anystatement that surpasses the configurable time boundary during its execution.When this feature is enabled, no other statements are logged except the longrunning 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 2seconds will be logged. Note that the same statement will continue to be loggedfor as long as it executes. So if the interval is set to 2, and the query takes11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals). #outagedetection=true|falseoutagedetectioninterval=integer time (seconds) # outagedetection=false outagedetectioninterval= ################################################################COMMON PROPERTIES # ################################################################filter what is logged filter=falsecomma separated list of tables to include when filtering include =comma separated list of tables to exclude when filtering exclude =sql expression to evaluate if using regex filtering sqlexpression = turn on tracing autoflush = truesets the date format using Java's SimpleDateFormat routine dateformat= #list of categories to explicitly include includecategories= #list of categories to exclude: error, info, batch, debug, statement, #commit, rollback and result are valid values excludecategories=info,debug,result,batch #allows you to use a regex engine 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 for every statement logged stacktrace=falseif stacktrace=true, specifies the stack trace to print stacktraceclass=determines if property file should be reloaded reloadproperties=falsedetermines how often should be reloaded in seconds reloadpropertiesinterval=60 #if=true then url must be prefixed with p6spy: useprefix=false #specifies the appender to use for logging appender=com.p6spy.engine.logging.appender.Log4jLogger #appender=com.p6spy.engine.logging.appender.StdoutLogger #appender=com.p6spy.engine.logging.appender.FileLoggername 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.logappend to the p6spy log file. if this is set to false thelog file is truncated every time. (file logger only) append=true #The following are for log4j logging 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.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost log4j.appender.SQLPROFILER_CLIENT.Port=4445 log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true #log4j.logger.p6spy=INFO,STDOUT log4j.logger.p6spy=DEBUG, SQLPROFILER_CLIENT #################################################################DataSource replacement ##Replace the real DataSource class in your application server #configuration with the name com.p6spy.engine.spy.P6DataSource,#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;ibmhost,databaseName;mydb #################################################################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). ## ################################################################# #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打开命令行窗口，进入sqlprofiler所在的路径，我这里是：D:\Primeton\eos702\apache-tomcat-5.5.20\webapps\default\WEB-INF\lib，执行java -jar sqlprofiler.jar命令，弹出如下窗口： 启动EOS服务器，调用数据库查询功能，sqlprofiler中会显示类似于如下的信息： 配置完成。
