【p6spy】程序员开发利器P6spy——打印执行sql语句，mybatis、ibatis、Hibernate均可使用

深藏阁楼爱情的钟 2022-06-03 00:00 1255阅读 0赞

一、前言

  1.   在开发的过程中，总希望方法执行完了可以看到完整是sql语句，从而判断执行的是否正确，所以就希望有一个可以打印sql语句的插件。p6spy就是一款针对数据库访问操作的动态监控框架，他可以和数据库无缝截取和操纵，而不必对现有应该用程序的代码做任何修改。
  3.   通过p6spy可以直接打印数据库执行的语句，下面向大家介绍一下p6spy。

二、使用p6spy，需要什么？

  • p6spy的jar包
  • spy.properties
  • 自定义日志格式
  • 修改相关配置文件

三、使用过程

3.1 添加p6spy的依赖

  1. <!--打印数据库SQL语句-->
  2.         <dependency>
  3.             <groupId>p6spy</groupId>
  4.             <artifactId>p6spy</artifactId>
  5.             <version>3.6.0</version>
  6.         </dependency>

3.2 修改Dao相关配置文件

  1.   在连接数据源的配置文件中，添加p6spy连接设置：
  3. <?xml version="1.0" encoding="UTF-8"?>
  4. <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
  5.     <!--p6spy连接设置-->
  6.     <bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource">
  7.         <constructor-arg>
  8.             <ref bean="dataSourceDefault"/>
  9.         </constructor-arg>
  10.     </bean>
  12.     <!-- 数据库连接池 -->
  13.     <!-- 加载配置文件 -->
  14.     <context:property-placeholder location="classpath:conf/db.properties" />
  15.     <!-- 数据库连接池 -->
  16.     <bean id="dataSourceDefault" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
  17.        <!-- 基本属性 url username password driverClassName-->
  18.         <property name="url" value="${jdbc.url}" />
  19.         <property name="username" value="${jdbc.username}" />
  20.         <property name="password" value="${jdbc.password}" />
  21.         <!--<property name="driverClassName" value="${jdbc.driver}" />-->
  23.         <!--配置初始化大小、最小、最多连接数-->
  24.         <property name="initialSize" value="1"/>
  25.         <property name="maxActive" value="100" />
  26.         <property name="minIdle" value="5" />
  28.         <!--配置获取连接等待超时时间-->
  29.         <property name="maxWait" value="3000"/>
  31.         <!--配置间隔多久进行一次检测，检测需要关闭的空闲连接，单位是毫秒-->
  32.         <property name="timeBetweenEvictionRunsMillis" value="6000"/>
  34.         <!--配置一个连接在连接池中，最小生存的时间，单位是毫秒-->
  35.         <property name="minEvictableIdleTimeMillis" value="30000"/>
  37.         <property name="validationQuery" value="SELECT 'x'" />
  38.         <property name="testWhileIdle" value="true" />
  39.         <property name="testOnBorrow" value="false" />
  40.         <property name="testOnReturn" value="false" />
  42.         <!--打开PSCache，并且指定每个连接上的PSCache的大小-->
  43.         <property name="poolPreparedStatements" value="true"/>
  44.         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
  46.         <!-- 配置监控统计拦截的filters，去掉后监控界面sql无法统计 -->
  47.         <property name="filters" value="stat" />
  49.     </bean>
  50.     <!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
  51.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  52.         <!-- 数据库连接池 -->
  53.         <property name="dataSource" ref="dataSource" />
  54.         <!-- 加载mybatis的全局配置文件 -->
  55.         <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml" />
  56.     </bean>
  57.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  58.         <property name="basePackage" value="com.dmsd.dao" />
  59.     </bean>
  60. </beans>

3.2 添加spy.properties

  1.   文件内容如下：
  3. ###
  4. # #%L
  5. # P6Spy
  6. # %%
  7. # Copyright (C) 2013 P6Spy
  8. # %%
  9. # Licensed under the Apache License, Version 2.0 (the "License");
  10. # you may not use this file except in compliance with the License.
  11. # You may obtain a copy of the License at
  12. # 
  13. # http://www.apache.org/licenses/LICENSE-2.0
  14. # 
  15. # Unless required by applicable law or agreed to in writing, software
  16. # distributed under the License is distributed on an "AS IS" BASIS,
  17. # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  18. # See the License for the specific language governing permissions and
  19. # limitations under the License.
  20. # #L%
  21. ###
  22. #################################################################
  23. # P6Spy Options File #
  24. # See documentation for detailed instructions #
  25. # http://p6spy.github.io/p6spy/2.0/configandusage.html #
  26. #################################################################
  27. #################################################################
  28. # MODULES #
  29. # #
  30. # Module list adapts the modular functionality of P6Spy. #
  31. # Only modules listed are active. #
  32. # (default is com.p6spy.engine.logging.P6LogFactory and #
  33. # com.p6spy.engine.spy.P6SpyFactory) #
  34. # Please note that the core module (P6SpyFactory) can't be # 
  35. # deactivated. #
  36. # Unlike the other properties, activation of the changes on #
  37. # this one requires reload. #
  38. #################################################################
  39. #modulelist=com.p6spy.engine.spy.P6SpyFactory,com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
  40. ################################################################
  41. # CORE (P6SPY) PROPERTIES #
  42. ################################################################
  43. # A comma separated list of JDBC drivers to load and register.
  44. # (default is empty)
  45. #
  46. # Note: This is normally only needed when using P6Spy in an
  47. # application server environment with a JNDI data source or when
  48. # using a JDBC driver that does not implement the JDBC 4.0 API
  49. # (specifically automatic registration).
  50. #driverlist=
  51. driverlist=com.mysql.jdbc.Driver
  52. # for flushing per statement
  53. # (default is false)
  54. #autoflush = false
  55. # sets the date format using Java's SimpleDateFormat routine.
  56. # In case property is not set, miliseconds since 1.1.1970 (unix time) is used (default is empty)
  57. #dateformat=
  58. # prints a stack trace for every statement logged
  59. #stacktrace=false
  60. # if stacktrace=true, specifies the stack trace to print
  61. #stacktraceclass=
  62. # determines if property file should be reloaded
  63. # Please note: reload means forgetting all the previously set
  64. # settings (even those set during runtime - via JMX)
  65. # and starting with the clean table 
  66. # (default is false)
  67. #reloadproperties=false
  68. reloadproperties=true
  69. # determines how often should be reloaded in seconds
  70. # (default is 60)
  71. #reloadpropertiesinterval=60
  72. # specifies the appender to use for logging
  73. # Please note: reload means forgetting all the previously set
  74. # settings (even those set during runtime - via JMX)
  75. # and starting with the clean table 
  76. # (only the properties read from the configuration file)
  77. # (default is com.p6spy.engine.spy.appender.FileLogger)
  78. #appender=com.p6spy.engine.spy.appender.Slf4JLogger
  79. #appender=com.p6spy.engine.spy.appender.StdoutLogger
  80. #appender=com.p6spy.engine.spy.appender.FileLogger
  81. appender=com.p6spy.engine.spy.appender.Slf4JLogger
  82. # name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log)
  83. # (used for com.p6spy.engine.spy.appender.FileLogger only)
  84. # (default is spy.log)
  85. #logfile = spy.log
  86. # append to the p6spy log file. if this is set to false the
  87. # log file is truncated every time. (file logger only)
  88. # (default is true)
  89. #append=true
  90. # class to use for formatting log messages (default is: com.p6spy.engine.spy.appender.SingleLineFormat)
  91. #logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
  92. #自定义日志格式，在类中定义
  93. logMessageFormat= com.dmsd.tool.P6SpyLogger
  94. # format that is used for logging of the date/time/... (has to be compatible with java.text.SimpleDateFormat)
  95. # (default is dd-MMM-yy)
  96. #databaseDialectDateFormat=dd-MMM-yy
  97. databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss
  98. # whether to expose options via JMX or not
  99. # (default is true)
  100. #jmx=true
  101. # if exposing options via jmx (see option: jmx), what should be the prefix used?
  102. # jmx naming pattern constructed is: com.p6spy(.<jmxPrefix>)?:name=<optionsClassName>
  103. # please note, if there is already such a name in use it would be unregistered first (the last registered wins)
  104. # (default is none)
  105. #jmxPrefix=
  106. #################################################################
  107. # DataSource replacement #
  108. # #
  109. # Replace the real DataSource class in your application server #
  110. # configuration with the name com.p6spy.engine.spy.P6DataSource #
  111. # (that provides also connection pooling and xa support). #
  112. # then add the JNDI name and class name of the real #
  113. # DataSource here #
  114. # #
  115. # Values set in this item cannot be reloaded using the #
  116. # reloadproperties variable. Once it is loaded, it remains #
  117. # in memory until the application is restarted. #
  118. # #
  119. #################################################################
  120. #realdatasource=/RealMySqlDS
  121. #realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
  122. #################################################################
  123. # DataSource properties #
  124. # #
  125. # If you are using the DataSource support to intercept calls #
  126. # to a DataSource that requires properties for proper setup, #
  127. # define those properties here. Use name value pairs, separate #
  128. # the name and value with a semicolon, and separate the #
  129. # pairs with commas. #
  130. # #
  131. # The example shown here is for mysql #
  132. # #
  133. #################################################################
  134. #realdatasourceproperties=port;3306,serverName;myhost,databaseName;jbossdb,foo;bar
  135. #################################################################
  136. # JNDI DataSource lookup #
  137. # #
  138. # If you are using the DataSource support outside of an app #
  139. # server, you will probably need to define the JNDI Context #
  140. # environment. #
  141. # #
  142. # If the P6Spy code will be executing inside an app server then #
  143. # do not use these properties, and the DataSource lookup will #
  144. # use the naming context defined by the app server. #
  145. # #
  146. # The two standard elements of the naming environment are #
  147. # jndicontextfactory and jndicontextproviderurl. If you need #
  148. # additional elements, use the jndicontextcustom property. #
  149. # You can define multiple properties in jndicontextcustom, #
  150. # in name value pairs. Separate the name and value with a #
  151. # semicolon, and separate the pairs with commas. #
  152. # #
  153. # The example shown here is for a standalone program running on #
  154. # a machine that is also running JBoss, so the JDNI context #
  155. # is configured for JBoss (3.0.4). #
  156. # #
  157. # (by default all these are empty) #
  158. #################################################################
  159. #jndicontextfactory=org.jnp.interfaces.NamingContextFactory
  160. #jndicontextproviderurl=localhost:1099
  161. #jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.nameing:org.jnp.interfaces
  162. #jndicontextfactory=com.ibm.websphere.naming.WsnInitialContextFactory
  163. #jndicontextproviderurl=iiop://localhost:900
  164. ################################################################
  165. # P6 LOGGING SPECIFIC PROPERTIES #
  166. ################################################################
  167. # filter what is logged
  168. # please note this is a precondition for usage of: include/exclude/sqlexpression
  169. # (default is false)
  170. #filter=false
  171. # comma separated list of strings to include
  172. # please note that special characters escaping (used in java) has to be done for the provided regular expression
  173. # (default is empty)
  174. #include =
  175. # comma separated list of strings to exclude
  176. # (default is empty)
  177. #exclude =
  178. # sql expression to evaluate if using regex
  179. # please note that special characters escaping (used in java) has to be done for the provided regular expression
  180. # (default is empty)
  181. #sqlexpression = 
  182. #list of categories to exclude: error, info, batch, debug, statement,
  183. #commit, rollback and result are valid values
  184. # (default is info,debug,result,resultset,batch)
  185. #excludecategories=info,debug,result,resultset,batch
  186. excludecategories=info,debug,result,resultset
  187. # Execution threshold applies to the standard logging of P6Spy.
  188. # While the standard logging logs out every statement 
  189. # regardless of its execution time, this feature puts a time 
  190. # condition on that logging. Only statements that have taken 
  191. # longer than the time specified (in milliseconds) will be 
  192. # logged. This way it is possible to see only statements that 
  193. # have exceeded some high water mark. 
  194. # This time is reloadable. 
  195. #
  196. # executionThreshold=integer time (milliseconds)
  197. # (default is 0)
  198. #executionThreshold=
  199. ################################################################
  200. # P6 OUTAGE SPECIFIC PROPERTIES #
  201. ################################################################
  202. # Outage Detection
  203. #
  204. # This feature detects long-running statements that may be indicative of
  205. # a database outage problem. If this feature is turned on, it will log any
  206. # statement that surpasses the configurable time boundary during its execution.
  207. # When this feature is enabled, no other statements are logged except the long
  208. # running statements. The interval property is the boundary time set in seconds.
  209. # For example, if this is set to 2, then any statement requiring at least 2
  210. # seconds will be logged. Note that the same statement will continue to be logged
  211. # for as long as it executes. So if the interval is set to 2, and the query takes
  212. # 11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals).
  213. #
  214. # outagedetection=true|false
  215. # outagedetectioninterval=integer time (seconds)
  216. #
  217. # (default is false)
  218. #outagedetection=false
  219. # (default is 60)
  220. #outagedetectioninterval=30
  222.   注意：
  224.   在文档中，自定义日志格式，`logMessageFormat= com.dmsd.tool.P6SpyLogger`，在类中定义，指明了路径，在跳转第三步，需要自己定义。

3.3 创建P6SpyLogger类，自定义日志格式

  1.   因为这个都会使用，所以就定义在了tool工具类里:
  3. package com.dmsd.tool;
  5. import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
  6. import java.text.SimpleDateFormat;
  7. import java.util.Date;
  9. public class P6SpyLogger implements MessageFormattingStrategy { 
  10.     private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
  12.     public P6SpyLogger() {
  13.     }
  15.     @Override
  16.     public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
  17.         return !"".equals(sql.trim())?this.format.format(new Date()) + " | took " + elapsed + "ms | " + category + " | connection " + connectionId + "\n " + sql + ";":"";
  18.     }
  19. }

3.5 运行结果对比

  1.   没有使用：什么都没有，看的不清晰。

这里写图片描述

  1.   使用之后：

这里写图片描述

四、小结

  1.   通过测试使用，提高了自己的代码能力，也从一定方向上，提升了思考问题的能力。有的时候就需要我们用工具去解决问题，程序员的创造力是无穷的。

发表评论

表情：
评论列表 （有 0 条评论，1255人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Mybatis 配置p6spy

    在开发中，为了更方便的知道编写的sql语句是否正确，则通过配置p6spy在控制台打印sql语句，从而解决因sql语句错误导致的程序运行失败，配置方式如下： 1，导入p6sp

    秒速五厘米秒速五厘米/ 0 赞/ 103 阅读

    相关 使用P6Spy格式化SQL语句

    公司最近在做一个项目,开发过程中遇到点小问题，查询结果出不来，查看自带日志打印，有点不爽，特别是想复制sql语句到数据库navicat中去调试时，还要手动复制参数值，很是麻烦，

    缺乏、安全感缺乏、安全感/ 0 赞/ 529 阅读

    相关 P6Spy

     P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。通过P6Spy我们可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析

    小灰灰小灰灰/ 0 赞/ 377 阅读