[Spring Data JPA Logging] Logging SQL with p6spy-spring-boot-starter in Spring Boot

Posted on 2021-02-20 Edited on 2023-09-02 In Programming Language , Java , Spring , Spring Boot Views: Word count in article: 3.6k Reading time ≈ 3 mins.

Logging SQL with p6spy

P6Spy is a framework that enables database data to be seamlessly intercepted and logged with no code changes to existing application. The P6Spy distribution includes P6Log, an application which logs all JDBC transactions for any Java application.

Spring Boot autoconfiguration is handled by the separate project: gavlyukovskiy/spring-boot-data-source-decorator: Spring Boot integration with p6spy, datasource-proxy, flexy-pool and spring-cloud-sleuth - https://github.com/gavlyukovskiy/spring-boot-data-source-decorator, please consult the respective documentation for usage.

Installation

Spring Boot integration with p6spy, datasource-proxy and flexy-pool.

Gradle

// bundle.gradle

dependencies {
    // https://mvnrepository.com/artifact/com.github.gavlyukovskiy/p6spy-spring-boot-starter
    implementation group: 'com.github.gavlyukovskiy', name: 'p6spy-spring-boot-starter', version: '1.7.1'
}

Maven

<!-- pom.xml -->

<!-- https://mvnrepository.com/artifact/com.github.gavlyukovskiy/p6spy-spring-boot-starter -->
<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.7.1</version>
</dependency>

Configuration

Spring Boot Configuration

Update database url and driver class:

# application.properties

# spring.datasource.url=jdbc:postgresql://postgres:5432/cloudolife_development
# Update JDBC URL in the datasource with p6spy:
spring.datasource.url=jdbc:p6spy:postgresql://postgres:5432/cloudolife_development
spring.datasource.username=cloudolife
spring.datasource.password=cloudolife

# Change driver in the datasource
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

Or application.yml

# application.yml

spring:
  datasource:
    # url: jdbc:postgresql://postgres:5432/cloudolife_development
    # Update JDBC URL in the datasource with p6spy:
    url: jdbc:p6spy:postgresql://postgres:5432/cloudolife_development
    username: cloudolife
    password: cloudolife

    # Change driver in the datasource
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver

Replace jdbc:p6spy:postgresql to jdbc:p6spy:mysql or others in other databses.

p6spy Configuration

Configuration follows layered approach, where each layer overrides the values set by the lower ones (leaving those not provided unchanged):

JMX set properties (please note, that these are reset on next reload)
System properties
Environment variables
spy.properties
defaults

For the full list of available options, see the section Common Property File Settings. Please note that providing any of these via System properties/Environment variables is possible, using the particular property name following naming rule: p6spy.config.<property name><property value>;

See Configuration and Usage - https://p6spy.readthedocs.io/en/latest/configandusage.html to learn more.