Table of contents

• Introduction
• Basic integration
• Configuration
• Why not just use the Hibernate logging?
• Custom event listeners
• Conclusion

Introduction

P6Spy is a framework that enables logging of SQL statements executed by a Java application using JDBC. It works for applications that use JDBC either directly or indirectly (e.g. JPA or Hibernate).

It can be very useful for debugging and performance analysis.

I recently had to use it again in a Spring Boot application and I thought it would be useful to remind the existence of this old but convenient tool and explain how to use it.

Basic integration

P6Spy can be integrated in Spring Boot applications directly. Or you can use the Spring Boot DataSource Decorator library to simplify the process.

That library also supports Datasource Proxy and FlexyPool. Datasource Proxy is comparable to P6Spy. It supports nice features, but I found it more complicated to get what I wanted. In this article, I will focus on P6Spy.

To get started, simply add this dependency to your pom.xml:

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <!-- Use 1.x version for Spring Boot 3 and 2.x version for Spring Boot 4 -->
    <version>2.0.1</version>
</dependency>

Then whenever a JDBC connection is used, a line is added to your standard Spring Boot log output. For example, for a basic SELECT statement with an H2 database:

11:22:54.514 INFO p6spy : #1784971374514 | took 0ms | statement | connection 5| url jdbc:h2:mem:1fe96b11-7dbb-4d3f-b245-e3ee532bf058
select c1_0.id,c1_0.name from customer c1_0 where c1_0.id=?
select c1_0.id,c1_0.name from customer c1_0 where c1_0.id=1;

The default log format shows various attributes:

The log key is used in the next section to customize the log format.

Configuration

The default log format is a bit verbose for my taste. Fortunately, you can customize the log format of P6Spy.

Several configuration properties can be defined in your application.properties file, thanks to Spring Boot DataSource Decorator. For example, you can pick and choose the attributes and categories to display in the log output. Here is an example configuration:

# Uncomment to disable P6Spy logging, for example in production environment
#decorator.datasource.p6spy.enable-logging=false

# Custom log format with only the duration and the SQL statement
# (use "effectiveSqlSingleLine" instead of "sqlSingleLine" to log placeholders instead of actual values)
decorator.datasource.p6spy.log-format=%(executionTime) ms - %(sqlSingleLine)

# The categories to hide
# (available values include: statement, batch, commit, rollback, result, resultset, error, info)
decorator.datasource.p6spy.exclude-categories=result,resultset,commit

There are other configuration properties available, see Spring Boot Datasource Decoration - P6Spy documentation or P6Spy configuration

With the previous configuration, the log is now:

11:37:28.294 INFO p6spy : 0 ms - select c1_0.id,c1_0.name from customer c1_0 where c1_0.id=1

Why not just use the Hibernate logging?

In my Hibernate logging and monitoring guide I explained various ways to log SQL statements. For example, you can enable statement metrics to produce logs with statements duration, such as:

HHH000117: Query: [CRITERIA] select a1_0.id,a1_0.name from author a1_0, time: 2ms, rows: 2

So why bother using P6Spy?

First, you may actually not use Hibernate. If you use JDBC directly, for example through Spring's JdbcTemplate, you do not have access to Hibernate logging.

But also, the Hibernate statement metrics do not cover Spring Data JPA repository methods such as findById, save and delete. This is annoying when you need to be exhaustive.

Finally, P6Spy brings interesting additional features such as event listeners described in the next section.

Custom event listeners

P6Spy can be configured to use event listeners, which can be useful for monitoring and debugging.

Let's implement a simple use case: counting the number of SQL queries executed by some business services.

Add this custom JdbcEventListener to your application:

import com.p6spy.engine.common.PreparedStatementInformation;
import com.p6spy.engine.common.StatementInformation;
import com.p6spy.engine.event.JdbcEventListener;
// Other imports

@Component
public class CustomJdbcEventListener extends JdbcEventListener {

    private static final AtomicLong statementCount = new AtomicLong(0);

    @Override
    public void onAfterExecute(StatementInformation statementInformation, long timeElapsedNanos, String sql, SQLException e) {
        statementCount.incrementAndGet();
    }

    @Override
    public void onAfterExecuteQuery(PreparedStatementInformation statementInformation, long timeElapsedNanos, SQLException e) {
        statementCount.incrementAndGet();
    }

    @Override
    public void onAfterExecuteUpdate(PreparedStatementInformation statementInformation, long timeElapsedNanos, int rowCount, SQLException e) {
        statementCount.incrementAndGet();
    }

    public static long resetStatementCount() {
        return statementCount.getAndSet(0);
    }
}

No need to explicitly register this listener, it will be automatically discovered and loaded.

I chose to implement three callbacks. They were enough for my application. But depending on your needs, you may have to implement other callbacks, see JdbcEventListener Javadoc.

Then, in your business service, you can use it like this:

// Call business service methods that execute SQL queries
someService.doSomething();
otherService.doSomethingElse();

// Log the DB calls count since the last reset
LOGGER.info("Executed {} DB calls", CustomJdbcEventListener.resetStatementCount());

Conclusion

P6Spy is a useful and flexible tool to log SQL statements executed by a Java application using JDBC. It can be used to detect slow or unintended queries to improve the performance of your applications.

You can integrate it manually in Spring Boot applications and personalize the log format. You may also consider using the Spring Boot DataSource Decorator library to make it even easier.