Hi everyone! If you’re a Java developer working on Spring Boot applications, you need to know how to deal with errors. One common error is an internal server error (500 error). This error can be caused by a variety of things, including invalid SQL or database errors. When this happens, you need to know which SQL statements were executed by Hibernate, JPA, or your database library.

Logging SQL statements can help you track down the source of errors. In this article, we’ll show you how to log SQL statements in Spring Boot.

Enabling Database or SQL Logging in Spring Boot

We are going to discuss two methods used in Spring boot for SQL logging:

Sending Messages to the Screen
Using Loggers.

Let’s begin by exploring a way to display SQL statements on the screen.

1. How to Enable SQL Logging in Spring Boot to Standard Output

Spring Boot makes it easy to log SQL statements. You can enable SQL logging in your Spring Boot application by adding the following property to your application.properties file:

spring.jpa.show-sql=true

This will cause Spring Boot to log all SQL statements that are executed by your application. You can view the logged SQL statements in the application logs.

If you want to format the logged SQL statements, you can add the following property to your application.properties file:

spring.jpa.properties.hibernate.format_sql=true

This will cause Spring Boot to format the logged SQL statements for easier readability.

For example, if you have a Spring Boot application that saves a new student to the database, the following SQL statement will be logged:

Hibernate: insert into student (id, stuID, stuName, stuMarks) values (?, ?, ?, ?)

If you have the spring.jpa.properties.hibernate.format_sql property set to true, the following formatted SQL statement will be logged:

INSERT INTO student (id, studentid, name, grade) VALUES (?, ?, ?, ?)

2. How to Enable SQL Logging in Spring Boot via Loggers

To track down errors in SQL statements in Spring Boot, you can use loggers. This method also allows you to trace the statement parameters. To do this, you need to update the application.properties file in your Spring application with the following code lines:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

This will cause Spring Boot to log all SQL statements that are executed, as well as the statement parameters. This can be helpful for debugging SQL errors.

Here is an example of what the logs might look like:

2023–06–03 10:12:26.329 DEBUG 2041 - - [io-8000-exec-12] org.hibernate.SQL : insert into program (id, studentid, name, grade) values (?, ?, ?, ?)
2023–06–03 10:12:26.329 DEBUG 2041 - - [io-8000-exec-12] org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [1] as [java.lang.Long] - 1 
2023–06–03 10:12:26.329 DEBUG 2041 - - [io-8000-exec-12] org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [2] as [java.lang.Long] - 2 
2023–06–03 10:12:26.329 DEBUG 2041 - - [io-8000-exec-12] org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [3] as [java.lang.String] -Saumil
2023–05–03 10:12:26.329 DEBUG 2041 - - [io-8000-exec-12] org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [4] as [java.lang.Integer] - 999

As you can see, the logs show the SQL statement that was executed, as well as the values of the parameters. This information can help debug SQL errors.

The question marks (?) are still there, but now you can see the values that will be used for those parameters. This is different from the other method, where you could only see the question marks. You can use this method or the standard output method to log SQL statements in Spring Boot.

3. How to log SQL statements in Spring Boot

I have the following properties in the application.properties file.

spring.datasource.url=...
spring.datasource.username=saumil
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
security.basic.enabled=false
logging.level.org.springframework.web=INFO
logging.level.org.hibernate=INFO
logging.file=d:/newfolder/logs/applicationn.log

If you run the following command:

mvn spring-boot:run

You will see SQL statements in the console, but they will not appear in the app.log file. The app.log file will only contain basic Spring logs.

To make the SQL statements appear in the applicationn.log file, you can add the following properties to your application.properties file.

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

These properties will tell Spring Boot to log all SQL statements at the DEBUG level and to log all prepared statement parameters at the TRACE level.

Once you have added these properties to your application.properties file, you can restart your Spring Boot application. The SQL statements will now appear in the app.log file.

Here is a brief explanation of what each property does:

logging.level.org.hibernate.SQL=DEBUG tells Spring Boot to log all SQL statements at the DEBUG level.
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE tells Spring Boot to log all prepared statement parameters at the TRACE level.

The DEBUG level is a relatively low logging level, so it will not cause your application to generate a lot of log output. The TRACE level is a higher logging level, so it will cause your application to generate more log output. You can adjust the logging level to suit your needs.

What to avoid

One setting that you should avoid is spring.jpa.show-sql=true. This setting will cause the SQL statements to be printed to the console. While this can be useful for debugging, it is not a good way to log SQL statements in production. The reason for this is that there is no way to filter the SQL statements that are printed to the console. This means that you will end up with a lot of noise in your logs, which can make it difficult to find the information that you are looking for.

What to use instead

Instead of spring.jpa.show-sql=true, you should use a logging framework. There are a number of different logging frameworks available, but one popular option is Logback. With Logback, you can configure your application to log SQL statements at the DEBUG level. This will cause the SQL statements to be logged to a file, which you can then view and filter as needed.

Here are the steps on how to log SQL statements in Spring Boot using Logback:

Add the following dependency to your project:

<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
</dependency>

2. Create a logback.xml file in the root of your project.

3. In the logback.xml file, add the following configuration:

<configuration>
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>
    <logger name="org.hibernate.SQL" level="DEBUG" />
    <logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="DEBUG" />
    <root level="INFO">
        <appender-ref ref="console" />
    </root>
</configuration>

4. Restart your application.

Once you have completed these steps, all SQL statements will be logged to the console at the DEBUG level. You can then view and filter the SQL statements as needed.

Benefits of logging SQL statements

There are several benefits to logging SQL statements in Java and Spring Boot applications. These benefits include:

Identifying performance bottlenecks: By logging SQL statements, you can identify performance bottlenecks in your application. This can help you to improve the performance of your application by optimizing the SQL queries that are executed.
Troubleshooting functional issues: By logging SQL statements, you can troubleshoot functional issues in your application. This can help you to identify the root cause of the issue and fix it.
Understanding how your application interacts with the database: By logging SQL statements, you can understand how your application interacts with the database. This can help you to make changes to your application to improve the performance or functionality of the database interaction.
Debugging database errors: By logging SQL statements, you can debug database errors. This can help you to identify the cause of the error and fix it.

Conclusion

This is everything you need to know about logging SQL statements in a Java and Spring Boot application. It’s a really useful tip if you’re working on a real-world Spring Boot application, as it can save you a lot of time when debugging and troubleshooting performance or functional issues. By logging the SQL commands in a log file, you can see exactly which commands were executed. You can also copy them and directly run them on a database using tools like SQL Server Management Studio or Oracle SQL Developer.

Saumil Patel's Blog