1. Introduction
In this tutorial, we’ll discuss P6Spy, an open-source, free library useful for intercepting SQL logs in Java applications.
In the first part of the article, we’ll discuss the main advantage of relying on this external library instead of just enabling SQL logging for JPA or Hibernate and the different ways we can integrate the library into our apps. Then we’ll present a simple example of a Spring Boot application working with P6Spy to see some of the most important configurations available.
2. P6Spy Installation
P6Spy needs to be installed on an application server. In general, it’s enough to put the application JAR in the classpath and conveniently configure the drivers and the JDBC connection.
Another way of using P6Spy is through integration with the existing code of our application, assuming that making small code changes is acceptable. In the next section, we’ll see an example of how it’s possible to integrate P6Spy in a Spring Boot application by relying on auto-configuration.
The p6spy-spring-boot-starter is a repository that provides integration with P6Spy and other database monitoring libraries. Thanks to this library, enabling P6Spy logging is as simple as adding a jar on the classpath. With Maven, It’s enough to add this code snippet in the POM.xml:
<dependency>
<groupId>com.github.gavlyukovskiy</groupId>
<artifactId>p6spy-spring-boot-starter</artifactId>
<version>1.9.0</version>
</dependency>
If we want to configure logging, we need to add a file named “spy.properties” in the resources folder:
appender=com.p6spy.engine.spy.appender.FileLogger
logfile=database.log
append=true
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|%(sqlSingleLine)
In this case, we have configured P6Spy to log information on a file in append mode named “database.log” in a custom format. Those are only some of the configurations; others are documented on the project website.
3. Logging Example
To see the logging, we need to run some queries. Let’s add a couple of simple endpoints to our application:
@RestController
@RequestMapping("student")
public class StudentController {
@Autowired
private StudentRepository repository;
@RequestMapping("/save")
public Long save() {
return repository.save(new Student("Pablo", "Picasso")).getId();
}
@RequestMapping("/find/{name}")
public List<Student> getAll(@PathVariable String name) {
return repository.findAllByFirstName(name);
}
}
Supposing that the application is exposed on port 8080, let’s now use CURL to hit those endpoints:
curl http://localhost:8080/student/save
curl http://localhost:8080/student/find/Pablo
We can now see that a file called “database.log” has been created in the project directory with the following content:
1683396972301|0|statement|select next value for student_seq
1683396972318|0|statement|insert into student (first_name, last_name, id) values ('Pablo', 'Picasso', 1)
1683396972320|0|commit|
1683396990989|0|statement|select s1_0.id,s1_0.first_name,s1_0.last_name from student s1_0 where s1_0.first_name='Pablo'
Logging will also work if we use PreparedStatements and manage commits and rollbacks manually. Let’s add another controller in the application to test this behavior:
@RestController
@RequestMapping("jdbc")
public class JDBCController {
@Autowired
private DataSource dataSource;
@RequestMapping("/commit")
public List<Map<String, String>> select() {
List<Map<String, String>> results = new ArrayList<>();
try {
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.executeQuery("SELECT * FROM student");
} catch (Exception e) {
throw new IllegalStateException(e);
}
return results;
}
@RequestMapping("/rollback")
public List<Map<String, String>> rollback() {
List<Map<String, String>> results = new ArrayList<>();
try (Connection connection = dataSource.getConnection()) {
connection.rollback();
} catch (Exception e) {
throw new IllegalStateException(e);
}
return results;
}
@RequestMapping("/query-error")
public void error() {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.execute("SELECT UNDEFINED()");
} catch (Exception ignored) {
}
}
}
And then, consequently, we hit the following endpoints with curl requests:
curl http://localhost:8080/jdbc/commit
curl http://localhost:8080/jdbc/rollback
curl http://localhost:8080/jdbc/query-error
As a result, we’ll see the following log added in the “database.log” file:
1683448381083|0|statement|SELECT * FROM student
1683448381087|0|commit|
1683448386586|0|rollback|
1683448388604|3|statement|SELECT UNDEFINED()
4. Conclusion
In this article, we have seen multiple advantages of relying on an external third-party library such as P6Spy to log database queries. For example, the peculiar configuration we have tried relieves us from the noisy neighbor problem (imagine the log console full of queries).
As always, the code is available over on GitHub.
