Code Major

Introduction

When we developers use ORM frameworks, they arguably help us go fast with development. They generate SQL statements for us and we very often do not know exactly what they generate. Knowing what is generated can help us optimize the use of the ORM frameworks, debug issues that occur, and avoid performance issues.

We will look at a couple of ways to view the generated SQL statements. We will use spring boot and hibernate in this article.

Our Model

We have a very simple entity called Product with no relationship to any other entity but a few fields. We will configure the application to show sql statements and then run a test to save it to the database so that we can view the generated statements.

Enable Hibernate Logging

There are 2 ways of enabling hibernate logging. The first way is by simply enabling the debug level as shown below

logging:
  level:
    org:
      hibernate:
        SQL: DEBUG

The example output is shown below

insert into product (business_id,catalog_id,name,refundable,tax_code,id) values (?,?,?,?,?,?)

The second way is using the “show-sql” configuration as displayed below.

spring:
  jpa:
    show-sql: true

If we want the SQL statements formatted, we can add the following configuration.

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true #format for readability

The 2 ways above have a short-coming. They do not show the parameter values passed to the query. If you are interested in debugging issues then this is not the way to go.

To experiment and play around with the above configuration, you can check out the hibernate-show-sql branch of the accompanying repository added in the conclusion section.

Using P6spy

P6spy is a framework that can intercept requests to the database and log them with minimal changes to the database.

There are 2 ways in which we can configure P6spy when using spring boot. For the first method, add the dependency shown below then wrap the data-source in the P6DataSource.

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

Wrap the data-source as shown below.

    @Bean
    DataSource dataSource(HikariConfig hikariConfig) {
        return new P6DataSource(new HikariDataSource(hikariConfig));
    }

Next we need to create a spy.properties file and put it in our classpath with the contents below.

appender=com.p6spy.engine.spy.appender.StdoutLogger
excludecategories=

The configuration above is minimal. It sends the logs to the terminal. For some reason, if you do not include `excludecategories=`, insert statements will not be logged. When you run the ProductServiceTest.saveProduct() in the test method of the accompanying code, you will then get a logged SQL statement outputted like so:

1741607336393|4|batch|connection 3|url jdbc:postgresql://localhost:65188/dataaccessdemo?loggerLevel=OFF|insert into product (business_id,catalog_id,name,refundable,tax_code,id) values (?,?,?,?,?,?)|insert into product (business_id,catalog_id,name,refundable,tax_code,id) values ('55e1cfff-7adf-3bfc-8a69-77359e768b8f','c78d02cf-befd-356b-a0b3-690b446af435','JZPUUY',true,'KPPPIC',1)

The second method is to use a spring the p6spy-spring-boot-starter. Add the dependency below as well as the same spy.properties file mentioned above.

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

For both methods, checkout the p6spy and p6spy-boot-starter branches.

Using Log4jdbc

The log4jdbc library is similar to the P6spy library. It logs SQL statements but it is a little verbose and although it still works, it has not been maintained for some years now.

Add the dependency as shown below.

		<dependency>
			<groupId>org.bgee.log4jdbc-log4j2</groupId>
			<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
			<version>1.16</version>
		</dependency>

Wrap your data source with the DataSourceSpy class as shown below.

    @Bean
    DataSource dataSource(HikariConfig hikariConfig) {
        return new DataSourceSpy(new HikariDataSource(hikariConfig));
    }

You will get logs similar to the following output.

2025-03-10T19:48:25.527+01:00  INFO 43486 --- [dataaccessdemo] [           main] log4jdbc.log4j2                          : 4. batching 1 statements: 1: insert into product (business_id,catalog_id,name,refundable,tax_code,id) 
values (118154a0-a57d-3aed-add3-e4c094f793dc,d338c054-766f-3fc8-8246-55c268a31a45,'YWAO',0,'GCXHDGVG',1)  {executed in 6 ms}

For the code, checkout the log4jdbc branch.

Using datasource-proxy

Another query interceptor is the datasource-proxy. This proxy allows you to provide a listener, as well as to perform logic in before query and after query hooks.

Add the library dependency

		<dependency>
			<groupId>net.ttddyy</groupId>
			<artifactId>datasource-proxy</artifactId>
			<version>1.10.1</version>
		</dependency>

Configure the datasource.

    @Bean
    DataSource spyDataSource(HikariConfig hikariConfig) {
        // https://jdbc-observations.github.io/datasource-proxy/docs/snapshot/user-guide/index.html
        final DataSource dataSourceSpy = new HikariDataSource(hikariConfig);
        SystemOutQueryLoggingListener listener = new SystemOutQueryLoggingListener();
        return ProxyDataSourceBuilder.create(dataSourceSpy)
                                     .name("DS-Proxy")
                                     .listener(listener)
                                     //.multiline()
                                     .countQuery() //metric collection
                                     .logQueryToSysOut()
                                     .retrieveIsolation()
                                     .writeIsolation()
                                     .logSlowQueryToSysOut(1, TimeUnit.SECONDS)
                                     .build();
    }

Checkout the datasource-proxy branch for the demo.

Conclusion

We have seen how to configure our application to log SQL queries. We saw that we could log the queries as easily as just enabling hibernate logging but then we would not get the data passed into the query. We also saw P6spy and Log4jdbc that are light to configure and also give you the queries along with their parameter values. Lastly, we saw datasource-proxy that allows you to hook listeners thereby further extending testing possibilities.

The repository accompanying this tutorial and all its branches can be found here.