How SpringBoot integrates P6Spy to implement SQL logging

P6Spy Introduction

P6Spy is an open source framework that can be used to intercept and modify data manipulation statements in applications.

SQL statements can be intercepted through P6Spy, which is equivalent to a recorder of SQL statements, so that we can use it for related analysis, such as performance analysis.

Application scenario

pom

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
    </dependencies>

application.yml

spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
#driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql ://localhost:3306/course_db?serverTimezone=GMT+8
# url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT+8
username: root
password: root

# Open the sql log output of mybatis-plus
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

entity

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("course_1")
public class Course {
    @TableField("cid")
    private Long cId;
    private String cName;
    private Integer userId;
    private String cStatus;
}

Mapper

public interface CourseMapper extends BaseMapper<Course> {
}

Startup class

@SpringBootApplication
@MapperScan(basePackages = "cn.zysheep.mapper")
public class ShardingjdbcdemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingjdbcdemoApplication.class, args);
    }
}

Test class

@SpringBootTest
@Slf4j
class ShardingjdbcdemoApplicationTests {
    @Autowired
    private CourseMapper courseMapper;
    @SneakyThrows
    @Test
    void findCourse() {
        courseMapper.selectList(null).forEach(System.out::println);
    }
}

mybatis-plus can also be used The printed sql log is not the effect we want. How to control the desired sql log output can be done by using the P6Spy open source product.

Getting started with P6Spy

spy.properties

resources Add a configuration file to the directory, similar to log4j .xml, record configuration information

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# sql log printout
# 1. logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
# 2. logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
# customLogMessageFormat=%(currentTime) | SQL use time: %(executionTime) ms | connect info: %(category)-%(connectionId) | execute sql: %(sql)
# 3. Custom log printing (fully qualified class name)
logMessageFormat=cn .zysheep.config.P6SPYConfig
# Use the log system to record sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
## Configure logging Log exceptions
excludecategories=info,debug,result ,batc,resultset
# Set to use p6spy driver as proxy
deregisterdrivers=true
# Date format
dateformat=yyyy-MM-dd HH:mm:ss
# Actual driver
driverlist=com.mysql.cj.jdbc.Driver
# Whether to enable slow SQL logging
outagedetection=true
# Slow SQL logging standard seconds
outagedetectioninterval=2

P6Spy has a built-in SQL output format, as shown in the configuration file above. Here we use custom SQL log printing

P6SPYConfig

public class P6SPYConfig  implements MessageFormattingStrategy {
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        Map<String, Object> message = new LinkedHashMap<>(8);
        String newPrepared = prepared.replace("   ", "").replace("\n", " ");
        message.put("prepared", newPrepared);
        String newSql = sql.replace("   ", "").replace("\n", " ");
        message.put("sql", newSql);
        return JSONObject.toJSONString(message, true);
    }
}

application.yml

spring:
datasource:
# driver-class-name : com.mysql.cj.jdbc.Driver
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.alibaba.druid.pool.DruidDataSource
# url: jdbc: mysql://localhost:3306/course_db?serverTimezone=GMT+8
url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT+8
username: root
password: root

# Open the sql log output of mybatis-plus
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

The test class remains unchanged

The above is the detailed content of How SpringBoot integrates P6Spy to implement SQL logging. For more information, please follow other related articles on the PHP Chinese website!