Springboot项目集成Durid数据源和P6Spy以及dbType not support问题

        项目开发阶段,mybatis的SQL打印有占位符,调试起来还是有点麻烦,随想整合P6Spy打印可以直接执行的SQL,方便调试,用的Durid连接池。

Springboot项目集成Durid

<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.18</version></dependency>

配置文件application.yml

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=trueusername: 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环境,生产环境不建议使用,有性能损耗。

<dependency><groupId>p6spy</groupId><artifactId>p6spy</artifactId><version>3.9.1</version>
</dependency>

或者只添加到开发环境

<profiles><profile><id>dev</id><activation><activeByDefault>true</activeByDefault></activation><properties><profiles.active>dev</profiles.active></properties><dependencies><dependency><groupId>p6spy</groupId><artifactId>p6spy</artifactId><version>3.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?allowPublicKeyRetrieval=true&useSSL=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=falseusername: rootpassword: 123456driver-class-name: com.p6spy.engine.spy.P6SpyDriver

 

添加spy.properties文件,默认会在项目根目录生成一个spy.log文件,把SQL打印到该文件中。

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory# 自定义日志打印logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger#日志输出到控制台appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger# 使用日志系统记录 sql#appender=com.p6spy.engine.spy.appender.Slf4JLogger# 设置 p6spy driver 代理deregisterdrivers=true# 取消JDBC URL前缀useprefix=true# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.excludecategories=info,debug,result,batch,resultset# 日期格式dateformat=yyyy-MM-dd HH:mm:ss# 真实JDBC driver , 多个以 逗号 分割 默认为空#driverlist=org.h2.Driverdriverlist=oracle.jdbc.OracleDriver# 是否开启慢SQL记录outagedetection=true# 慢SQL记录标准 2 秒outagedetectioninterval=2

集成完成。

spy.properties参考:

mybatis-plus:

#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

官网的例子:

################################################################## 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=# for flushing per statement# (default is false)#autoflush=false# sets the date format using Java's 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#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# 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# 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-dd'T'HH:mm:ss.SSSZ)#databaseDialectDateFormat=yyyy-MM-dd'T'HH: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-dd'T'HH:mm:ss.SSSZ)#databaseDialectTimestampFormat=yyyy-MM-dd'T'HH:mm:ss.SSSZ# format that is used for logging booleans, possible values: boolean, numeric# (default is boolean)#databaseDialectBooleanFormat=boolean# Specifies the format for logging binary data. Not applicable if excludebinary is true.# (default is com.p6spy.engine.logging.format.HexEncodedBinaryFormat)#databaseDialectBinaryFormat=com.p6spy.engine.logging.format.PostgreSQLBinaryFormat#databaseDialectBinaryFormat=com.p6spy.engine.logging.format.MySQLBinaryFormat#databaseDialectBinaryFormat=com.p6spy.engine.logging.format.HexEncodedBinaryFormat# 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(.<jmxPrefix>)?:name=<optionsClassName># 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 JNDI 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.naming: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, result and resultset are valid values# (default is info,debug,result,resultset,batch)#excludecategories=info,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)#excludebinary=false# 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

第二种:

添加依赖:

<dependency><groupId>com.github.gavlyukovskiy</groupId><artifactId>p6spy-spring-boot-starter</artifactId><version>1.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,这是没有识别出dbType,Durid数据源会存在该问题。

 解决方式:修改Durid配置中的filter,在stat和wall指定dbType和enabled

 

总结: 除了dbType not support这个问题外,我这还遇到了connection error的问题,最后发现其实和P6Spy无关。我这不是纯净的框架,数据源配置都是jar包的方式继承的父项目,改起来很费劲。P6Spy配置其实很简单,Durid连接池用MySQL的时候会有dbType不识别问题,其余的数据源都很好集成,如果遇到其余的稀奇古怪的问题,请先排查框架本身是否正常。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/83382.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Redis基础命令大全

这里写目录标题 第一章、Redis 命令大全1.1&#xff09;通用命令语法&#xff1a;ping语法&#xff1a;dbsize语法&#xff1a;select db语法&#xff1a;flushdb语法&#xff1a;exit 或 quit语法&#xff1a;redis-cli 1.2&#xff09;Redis 的 Key 的操作命令语法&#xff1…

微信小程序 地图map(电子围栏圆形和多边形)

正常情况下是没有手机上画电子围栏的&#xff0c;公共平台上我也没找到&#xff0c;所以走了一个歪点子&#xff0c;就是给地图添加点击事件&#xff0c;记录点的位置&#xff0c;在画到电子围栏上就是添加电子围栏了&#xff0c;如果只是显示电子围栏就简单了 一、多边形电子…

机器人CPP编程基础-01第一个程序Hello World

很多课程先讲C/C或者一些其他编程课&#xff0c;称之为基础课程。然后到本科高年级进行机器人专业课学习&#xff0c;这样时间损失非常大&#xff0c;效率非常低。 C/单片机/嵌入式/ROS等这些编程基础可以合并到一门课中进行实现&#xff0c;这些素材已经迭代三轮以上&#xf…

ctfshow web93-98

web93 打开环境是一个代码审计题目 简单分析就是输入一个变量num&#xff0c;其值不能等于4476与包含字母&#xff0c;但是他的值需要为4476 函数intval作用为获取变量的整数值&#xff0c;第二个参数的意思是进制&#xff0c;默认为10进制。题目参数为0&#xff0c;就根据变…

享元模式(C++)

定义 运用共享技术有效地支持大量细粒度的对象。 使用场景 在软件系统采用纯粹对象方案的问题在于大量细粒度的对象会很快充斥在系统中&#xff0c;从而带来很高的运行时代价——主要指内存需求方面的代价。如何在避免大量细粒度对象问题的同时&#xff0c;让外部客户程序仍…

立即开始使用 3D 图像

一、说明 这个故事介绍了使用这种类型的数据来训练机器学习3D模型。特别是&#xff0c;我们讨论了Kaggle中可用的MNIST数据集的3D版本&#xff0c;以及如何使用Keras训练模型识别3D数字。 3D 数据无处不在。由于我们希望构建AI来与我们的物理世界进行交互&#xff0c;因此使用3…

8月9日上课内容 nginx负载均衡

负载均衡工作当中用的很多的&#xff0c;也是面试会问的很重要的一个点 负载均衡&#xff1a;通过反向代理来实现&#xff08;nginx只有反向代理才能做负载均衡&#xff09; 正向代理的配置方法&#xff08;用的较少&#xff09; 反向代理的方式&#xff1a;四层代理与七层代…

【多重信号分类】超分辨率测向方法——依赖于将观测空间分解为噪声子空间和源/信号子空间的方法具有高分辨率(HR)并产生准确的估计(Matlab代码实现)

&#x1f4a5;&#x1f4a5;&#x1f49e;&#x1f49e;欢迎来到本博客❤️❤️&#x1f4a5;&#x1f4a5; &#x1f3c6;博主优势&#xff1a;&#x1f31e;&#x1f31e;&#x1f31e;博客内容尽量做到思维缜密&#xff0c;逻辑清晰&#xff0c;为了方便读者。 ⛳️座右铭&a…

牛客小白月赛75C题题解

文章目录 方豆子问题建模问题分析代码 方豆子 问题建模 给定一个整数n&#xff0c;要求输出n级好豆豆&#xff0c;n级好豆豆由3个n-1级好豆豆和1个n-1级坏豆豆组成&#xff0c;已经给出了1级好豆豆和1级坏豆豆。 问题分析 由于最终的豆豆是由其上一级的豆豆产生的&#xff0…

单片机外部晶振故障后自动切换内部晶振——以STM32为例

单片机外部晶振故障后自动切换内部晶振——以STM32为例 作者日期版本说明Dog Tao2023.08.02V1.0发布初始版本 文章目录 单片机外部晶振故障后自动切换内部晶振——以STM32为例背景外部晶振与内部振荡器STM32F103时钟系统STM32F407时钟系统 代码实现系统时钟设置流程时钟源检测…

ArcGIS Pro实践技术应用——暨基础入门、制图、空间分析、影像分析、三维建模、空间统计分析与建模、python融合、案例应用全流程科研能力提升

查看原文>>>ArcGIS Pro实践技术应用——暨基础入门、制图、空间分析、影像分析、三维建模、空间统计分析与建模、python融合能力 本文将利用ArcGIS Pro 将您的 GIS 工作组织到工程中&#xff0c;您可以使用 ArcGIS Pro 映射 2D 和 3D 数据。借助 ArcGIS Pro&#xff…

float 属性的作用是什么?

聚沙成塔每天进步一点点 ⭐ 专栏简介⭐ float 是什么&#xff1f;⭐ float 属性值⭐ 使用 float 的注意事项&#xff1a;⭐ 写在最后 ⭐ 专栏简介 前端入门之旅&#xff1a;探索Web开发的奇妙世界 记得点击上方或者右侧链接订阅本专栏哦 几何带你启航前端之旅 欢迎来到前端入门…

力扣hot100刷题记录

二刷hot100&#xff0c;坚持每天打卡&#xff01;&#xff01;&#xff01; 1. 两数之和 // 先求差&#xff0c;再查哈希表 public int[] twoSum(int[] nums, int target) {Map<Integer,Integer> map new HashMap<>();for(int i 0;i<nums.length;i){int key …

51单片机(普中HC6800-EM3 V3.0)实验例程软件分析 实验四 蜂鸣器

目录 前言 一、原理图及知识点介绍 1.1、蜂鸣器原理图&#xff1a; 二、代码分析 前言 第一个实验:51单片机&#xff08;普中HC6800-EM3 V3.0&#xff09;实验例程软件分析 实验一 点亮第一个LED_ManGo CHEN的博客-CSDN博客 第二个实验:51单片机&#xff08;普中HC6800-EM…

《2023年中国企业数字化转型发展白皮书》发布

导读 本报告主要采用市场调查、行业深度访谈、桌面研究等方法&#xff0c;并使用艾媒咨询旗下各大数据计算系统和相关计算模型。 对部分相关的公开信息进行筛选&#xff0c;通过对行业专家、相关企业与网民进行深度访谈&#xff0c;了解相关行业主要情况&#xff0c;获得相应…

解决Qt的列表加载大量数据卡顿的问题

问题概述 本人在使用QListView插入大量数据时&#xff0c;界面卡顿十分严重。数据量大概只有上千左右&#xff0c;但是每个Item的内容比较多。当数据不停地插入一段时间后&#xff0c;卡顿到鼠标的移动都有点困难。 解决思路 QListView是典型的MVC思想的产物。界面呈现出来的数…

创建型模式-单例模式

文章目录 一、创建型模式1. 单例设计模式1.1 单例模式的结构1.2 单例模式的实现&#xff08;1&#xff09;饿汉式-方式1&#xff08;静态变量方式&#xff09;&#xff08;2&#xff09;饿汉式-方式2&#xff08;静态代码块方式&#xff09;&#xff08;3&#xff09;懒汉式-方…

vue+element 下载压缩包和导出

export function goodsInspectionReportDwnloadZip (params) {return axios({url: "/warehouse-entry-server/v1/goodsInspectionReport/downloadZip",method: "get",params,responseType: "blob"}) } //下载handleDownloadFile() {if (!this.$r…

AMBA总线(AHB)

1.AMBA AMBA(Advanced Microcontroller Bus Architecture)高级微控制器总线结构&#xff0c;定义了用于设计高性能嵌入式微控制器的片上通信标准。   AMBA规范中定义了三种不同的总线&#xff1a;   &#xff08;1&#xff09;高级高性能总线&#xff08;Advanced High-per…

基于nodejs+vue+uniapp微信小程序的短视频分享系统

开发语言 node.js 框架&#xff1a;Express 前端:Vue.js 数据库&#xff1a;mysql 数据库工具&#xff1a;Navicat 开发软件&#xff1a;VScode 3.1小程序端 用户注册页面&#xff0c;输入用户的个人信息点击注册即可。 注册完成后会返回到登录页面&#xff0c;用户输入自己注…