网站开发语言怎么样,浙江省建设局教育网站,app与手机网站,九江建筑项目开发阶段#xff0c;mybatis的SQL打印有占位符#xff0c;调试起来还是有点麻烦#xff0c;随想整合P6Spy打印可以直接执行的SQL#xff0c;方便调试#xff0c;用的Durid连接池。
Springboot项目集成Durid
dependencygroupIdcom.alibaba/group… 项目开发阶段mybatis的SQL打印有占位符调试起来还是有点麻烦随想整合P6Spy打印可以直接执行的SQL方便调试用的Durid连接池。
Springboot项目集成Durid
dependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.2.18/version/dependency
配置文件application.yml
spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test?useUnicodetruecharacterEncodingutf8useSSLtrueusername: rootpassword: 123456# 指定数据源为 DruidDataSource默认值为 HikariDataSourcetype: com.alibaba.druid.pool.DruidDataSourcedruid:initial-size: 5min-idle: 5max-active: 15max-wait: 30000min-evictable-idle-time-millis: 30000time-between-eviction-runs-millis: 30000validation-query: SELECT 1 FROM DUALtest-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 5filters: stat,walluse-global-data-source-stat: truestat-view-servlet:enabled: trueurl-pattern: /druid/*login-username: adminlogin-password: 111111web-stat-filter:enabled: trueurl-pattern: /*exclusions: /druid/*, *.js, *.jpeg, *.jpg, *.png, *.gif, *.cssfilter:stat:merge-sql: trueslow-sql-millis: 3000log-slow-sql: true
配置P6Spy有两种方式一种是添加依赖jar包一种是用p6spy-spring-boot-starter。
第一种
添加依赖可以添加到dev环境生产环境不建议使用有性能损耗。
dependencygroupIdp6spy/groupIdartifactIdp6spy/artifactIdversion3.9.1/version
/dependency
或者只添加到开发环境
profilesprofileiddev/idactivationactiveByDefaulttrue/activeByDefault/activationpropertiesprofiles.activedev/profiles.active/propertiesdependenciesdependencygroupIdp6spy/groupIdartifactIdp6spy/artifactIdversion3.9.1/version/dependency/dependencies/profile
/profiles 修改数据源配置需要改2个地方url和driver-class-name
修改前 修改后
spring:datasource:url: jdbc:p6spy:mysql://x.x.x.x:3306/my_test?allowPublicKeyRetrievaltrueuseSSLtrueserverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingutf8allowMultiQueriestrueuseSSLfalseusername: rootpassword: 123456driver-class-name: com.p6spy.engine.spy.P6SpyDriver 添加spy.properties文件默认会在项目根目录生成一个spy.log文件把SQL打印到该文件中。
module.logcom.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory# 自定义日志打印logMessageFormatcom.baomidou.mybatisplus.extension.p6spy.P6SpyLogger#日志输出到控制台appendercom.baomidou.mybatisplus.extension.p6spy.StdoutLogger# 使用日志系统记录 sql#appendercom.p6spy.engine.spy.appender.Slf4JLogger# 设置 p6spy driver 代理deregisterdriverstrue# 取消JDBC URL前缀useprefixtrue# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.excludecategoriesinfo,debug,result,batch,resultset# 日期格式dateformatyyyy-MM-dd HH:mm:ss# 真实JDBC driver , 多个以 逗号 分割 默认为空#driverlistorg.h2.Driverdriverlistoracle.jdbc.OracleDriver# 是否开启慢SQL记录outagedetectiontrue# 慢SQL记录标准 2 秒outagedetectioninterval2
集成完成。
spy.properties参考
mybatis-plus:
#3.2.1以上使用
modulelistcom.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelistcom.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormatcom.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appendercom.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appendercom.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdriverstrue
# 取消JDBC URL前缀
useprefixtrue
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategoriesinfo,debug,result,commit,resultset
# 日期格式
dateformatyyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlistorg.h2.Driver
# 是否开启慢SQL记录
outagedetectiontrue
# 慢SQL记录标准 2 秒
outagedetectioninterval2 官网的例子
################################################################## 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) cant be ## deactivated. ## Unlike the other properties, activation of the changes on ## this one requires reload. ###################################################################modulelistcom.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# for flushing per statement# (default is false)#autoflushfalse# sets the date format using Javas SimpleDateFormat routine.# In case property is not set, milliseconds since 1.1.1970 (unix time) is used (default is empty)#dateformat# prints a stack trace for every statement logged#stacktracefalse# if stacktracetrue, 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)#reloadpropertiesfalse# determines how often should be reloaded in seconds# (default is 60)#reloadpropertiesinterval60# 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)#appendercom.p6spy.engine.spy.appender.Slf4JLogger#appendercom.p6spy.engine.spy.appender.StdoutLogger#appendercom.p6spy.engine.spy.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 com.p6spy.engine.spy.appender.FileLogger only)# (default is spy.log)#logfilespy.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)#appendtrue# class to use for formatting log messages (default is: com.p6spy.engine.spy.appender.SingleLineFormat)#logMessageFormatcom.p6spy.engine.spy.appender.SingleLineFormat# Custom log message format used ONLY IF logMessageFormat is set to com.p6spy.engine.spy.appender.CustomLineFormat# default is %(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)# Available placeholders are:# %(connectionId) the id of the connection# %(currentTime) the current time expressing in milliseconds# %(executionTime) the time in milliseconds that the operation took to complete# %(category) the category of the operation# %(effectiveSql) the SQL statement as submitted to the driver# %(effectiveSqlSingleLine) the SQL statement as submitted to the driver, with all new lines removed# %(sql) the SQL statement with all bind variables replaced with actual values# %(sqlSingleLine) the SQL statement with all bind variables replaced with actual values, with all new lines removed#customLogMessageFormat%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)# format that is used for logging of the java.util.Date implementations (has to be compatible with java.text.SimpleDateFormat)# (default is yyyy-MM-ddTHH:mm:ss.SSSZ)#databaseDialectDateFormatyyyy-MM-ddTHH:mm:ss.SSSZ# format that is used for logging of the java.sql.Timestamp implementations (has to be compatible with java.text.SimpleDateFormat)# (default is yyyy-MM-ddTHH:mm:ss.SSSZ)#databaseDialectTimestampFormatyyyy-MM-ddTHH:mm:ss.SSSZ# format that is used for logging booleans, possible values: boolean, numeric# (default is boolean)#databaseDialectBooleanFormatboolean# Specifies the format for logging binary data. Not applicable if excludebinary is true.# (default is com.p6spy.engine.logging.format.HexEncodedBinaryFormat)#databaseDialectBinaryFormatcom.p6spy.engine.logging.format.PostgreSQLBinaryFormat#databaseDialectBinaryFormatcom.p6spy.engine.logging.format.MySQLBinaryFormat#databaseDialectBinaryFormatcom.p6spy.engine.logging.format.HexEncodedBinaryFormat# whether to expose options via JMX or not# (default is true)#jmxtrue# if exposing options via jmx (see option: jmx), what should be the prefix used?# jmx naming pattern constructed is: com.p6spy(.jmxPrefix)?:nameoptionsClassName# 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)#useNanoTimefalse################################################################## 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#realdatasourceclasscom.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 ## ###################################################################realdatasourcepropertiesport;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 JNDI context ## is configured for JBoss (3.0.4). ## ## (by default all these are empty) ###################################################################jndicontextfactoryorg.jnp.interfaces.NamingContextFactory#jndicontextproviderurllocalhost:1099#jndicontextcustomjava.naming.factory.url.pkgs;org.jboss.naming:org.jnp.interfaces#jndicontextfactorycom.ibm.websphere.naming.WsnInitialContextFactory#jndicontextproviderurliiop://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)#filterfalse# 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, result and resultset are valid values# (default is info,debug,result,resultset,batch)#excludecategoriesinfo,debug,result,resultset,batch#whether the binary values (passed to DB or retrieved ones) should be logged with placeholder: [binary] or not.# (default is false)#excludebinaryfalse# 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.## executionThresholdinteger 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).## outagedetectiontrue|false# outagedetectionintervalinteger time (seconds)## (default is false)#outagedetectionfalse# (default is 60)#outagedetectioninterval30 第二种
添加依赖:
dependencygroupIdcom.github.gavlyukovskiy/groupIdartifactIdp6spy-spring-boot-starter/artifactIdversion1.9.0/version
/dependency
修改数据源配置同第一种方式修改后 添加配置这里和第一种方式不一样可以添加个开关 enabled: true
decorator:datasource:p6spy:logging: filelog-file: spy.loglog-format: executionTime:%(executionTime) | sql:%(sqlSingleLine) 集成完成。
spy.log文件日志格式可以通过配置文件修改请另行参考网上的资料。 可能遇到的问题dbType not support : null这是没有识别出dbTypeDurid数据源会存在该问题。 解决方式修改Durid配置中的filter在stat和wall指定dbType和enabled 总结 除了dbType not support这个问题外我这还遇到了connection error的问题最后发现其实和P6Spy无关。我这不是纯净的框架数据源配置都是jar包的方式继承的父项目改起来很费劲。P6Spy配置其实很简单Durid连接池用MySQL的时候会有dbType不识别问题其余的数据源都很好集成如果遇到其余的稀奇古怪的问题请先排查框架本身是否正常。
