Springboot project integrates Durid data source and P6Spy and dbType not support problems

In the project development stage, the SQL printing of mybatis has placeholders, and debugging is still a bit cumbersome. If you want to integrate P6Spy to print SQL that can be directly executed, it is convenient for debugging, and the Durid connection pool is used.

Springboot project integrates Durid

<dependency>

    <groupId>com.alibaba</groupId>

    <artifactId>druid-spring-boot-starter</artifactId>

    <version>1.2.18</version>

</dependency>

Configuration file application.yml

spring:

  datasource:

    driver-class-name: com.mysql.cj.jdbc.Driver

    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true &characterEncoding=utf8 &useSSL=true

    username: root

    password: 123456

    # Specify the data source as DruidDataSource, the default value is HikariDataSource

    type: com.alibaba.druid.pool.DruidDataSource

    druid:

      initial-size: 5

      min-idle: 5

      max-active: 15

      max-wait: 30000

      min-evictable-idle-time-millis: 30000

      time-between-eviction-runs-millis: 30000

      validation-query: SELECT 1 FROM DUAL

      test-while-idle: true

      test-on-borrow: false

      test-on-return: false

      pool-prepared-statements: true

      max-pool-prepared-statement-per-connection-size: 5

      filters: stat, wall

      use-global-data-source-stat: true

      stat-view-servlet:

        enabled: true

        url-pattern: /druid/*

        login-username: admin

        login-password: 111111

      web-stat-filter:

        enabled: true

        url-pattern: /*

        exclusions: /druid/*, *.js, *.jpeg, *.jpg, *.png, *.gif, *.css

      filter:

        stat:

          merge-sql: true

          slow-sql-millis: 3000

          log-slow-sql: true

There are two ways to configure P6Spy, one is to add dependent jar packages, and the other is to use p6spy-spring-boot-starter.

Type 1:

Add dependency: it can be added to the dev environment, it is not recommended to use in the production environment, and there will be performance loss.

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

or just add to the development environment

<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>

To modify the data source configuration, you need to change 2 places, url and driver-class-name

before fixing:

After modification:

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=false
    username: root
    password: 123456
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver

Add the spy.properties file. By default, a spy.log file will be generated in the project root directory, and the SQL will be printed to the file.

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory

# Custom log printing

logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger

# log output to the console

appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger

# Use the logging system to record sql

#appender=com.p6spy.engine.spy.appender.Slf4JLogger

# Set p6spy driver proxy

deregisterdrivers=true

# Cancel the JDBC URL prefix

useprefix=true

# Configuration records Log exceptions, the result sets that can be removed include error, info, batch, debug, statement, commit, rollback, result, resultset.

excludecategories=info,debug,result,batch,resultset

# date format

dateformat=yyyy-MM-dd HH:mm:ss

# Real JDBC driver, multiple separated by comma, default is empty

#driverlist=org.h2.Driver

driverlist=oracle.jdbc.OracleDriver

# Whether to enable slow SQL logging

outage detection=true

# Slow SQL record standard 2 seconds

outagedetectioninterval=2

The integration is complete.

spy.properties Reference:

mybatis-plus:

#3.2.1 above use
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory, com.p6spy.engine.outage.P6OutageFactory
#3.2.1 The following use or do not configure
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# Custom log printing
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
# log output to the console
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# Use the logging system to record sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# Set p6spy driver proxy
deregisterdrivers=true
# Cancel the JDBC URL prefix
useprefix=true
# Configuration records Log exceptions, the result sets that can be removed include error, info, batch, debug, statement, commit, rollback, result, resultset.
excludecategories=info,debug,result,commit,resultset
# date format
dateformat=yyyy-MM-dd HH:mm:ss
# The actual driver can be multiple
#driverlist=org.h2.Driver
# Whether to enable slow SQL logging
outage detection=true
# Slow SQL record standard 2 seconds
outagedetectioninterval=2

Example from the official website:

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

# 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 stacktrace 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

Second:

Add dependencies:

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.0</version>
</dependency>

Modify data source configuration: Same as the first method, after modification

Add configuration: This is different from the first method, you can add a switch enabled: true

decorator:

  datasource:

    p6spy:

      logging: file

      log-file: spy.log

      log-format: executionTime:%(executionTime) | sql:%(sqlSingleLine)

The integration is complete.

The spy.log file, the log format can be modified through the configuration file, please refer to the online information separately.

Problems that may be encountered: dbType not support: null, this is because the dbType is not recognized, and this problem will exist in the Durid data source.

Solution: Modify the filter in Durid configuration, specify dbType and enabled in stat and wall

Summary: In addition to the problem of dbType not support, I also encountered the problem of connection error, and finally found that it has nothing to do with P6Spy. I am not a pure framework. The data source configuration is inherited from the parent project in the form of a jar package. It is very laborious to change. P6Spy configuration is actually very simple. When the Durid connection pool uses MySQL, there will be a problem of dbType not being recognized. The rest of the data sources are well integrated. If you encounter other strange problems, please check whether the framework itself is normal.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge Cloud native entry skill treeHomepageOverview 14780 people are studying systematically