How to Print SQL Queries with Values in Spring Boot with Spring Data JPA

1. Introduction

When working with Spring Boot and Spring Data JPA, it’s crucial to understand how SQL queries are generated and executed. This article will guide you through printing SQL queries, including parameter values and formatted output, directly in the console. We’ll use H2 as an in-memory database for simplicity, but the configurations can be applied to any SQL databases like MySQL, PostgreSQL, or Oracle.

2. Setting Up Spring Boot Project to Print SQL Queries

Step 1: Create a Spring Boot Project

Start by creating a Spring Boot project with the necessary dependencies. You’ll need spring-boot-starter-data-jpa for JPA support and h2 for the in-memory database.

Add the following dependencies to your pom.xml:

<dependency>
  <groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

Step 2: Configure H2

Configure H2 in a Spring Boot application, add these properties to application.properties:

# H2 specific properties
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

This setup creates an in-memory H2 database named testdb, uses default credentials (sa with no password), and enables the H2 console at /h2-console for easy database management via a web interface.

Step 3: Enable SQL Query Logging

Enable SQL query logging by adding the following properties in your application.properties file:

# Enable logging of SQL queries
spring.jpa.show-sql=true

# Format SQL output for readability
spring.jpa.properties.hibernate.format_sql=true

# Add SQL comments to indicate the source of queries
spring.jpa.properties.hibernate.use_sql_comments=true

• spring.jpa.show-sql=true: This property enables the logging of SQL statements generated by Hibernate. However, this alone will not show the parameter values.
• spring.jpa.properties.hibernate.format_sql=true: This property formats the SQL queries for better readability. Without this, the queries will appear in a single line, which can be hard to read.
• spring.jpa.properties.hibernate.use_sql_comments=true: This adds comments to the SQL queries, indicating which part of the code triggered the query. This is particularly useful when debugging.

Step 4: Enable Detailed SQL and Parameter Logging

To see the SQL query with the actual parameter values, you need to configure the logging levels for specific Hibernate classes:

# Enable detailed logging of SQL queries
logging.level.org.hibernate.SQL=DEBUG

# Enable logging of parameter values
logging.level.org.hibernate.orm.jdbc.bind=TRACE

• logging.level.org.hibernate.SQL=DEBUG: This property logs the SQL queries at the DEBUG level.
• logging.level.org.hibernate.orm.jdbc.bind=TRACE: This property logs the values bound to the query parameters, showing what is being passed to the SQL statements.

NOTE: To avoid duplicate queries in the console, use either spring.jpa.show-sql=true or logging.level.org.hibernate.SQL=DEBUG, not both.

Step 5: Additional Logging for Performance and Caching

You can also configure additional logging for statistics, slow queries, and the second-level cache:

# Enable logging for Hibernate statistics
logging.level.org.hibernate.stat=debug

# Enable logging for slow queries
logging.level.org.hibernate.SQL_SLOW=info

# Enable logging for the second-level cache
logging.level.org.hibernate.cache=debug

• logging.level.org.hibernate.stat=debug: Logs detailed statistics about Hibernate sessions, including entity loading times and query execution times.
• logging.level.org.hibernate.SQL_SLOW=info: Logs SQL queries that take longer than a specified threshold.
• logging.level.org.hibernate.cache=debug: Logs interactions with Hibernate’s second-level cache, useful for performance tuning.

3. Example Application

Let’s see how these configurations work in a simple Spring Boot application. We will perform insert, select, update, and delete operations using Spring Data JPA, and observe the output in the console.

3.1 Creating an Entity and Repository

Let’s define a User entity and a corresponding JPA repository:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String email;
    
    // Getters and setters
}

import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByName(String name);
}

3.2 Testing SQL Query Logging with Different Operations

Now, let’s create a command-line runner to test various SQL operations like select, insert, update, and, delete:

import com.bootcamptoprod.entity.User;
import com.bootcamptoprod.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

@SpringBootApplication
public class SpringBootDataJpaSqlQueriesLoggingApplication implements CommandLineRunner {

    @Autowired
    private UserRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(SpringBootDataJpaSqlQueriesLoggingApplication.class, args);
    }

    @Override
    public void run(String... args) {


        // Define SQL operation here
        // Insert
        // Select
        // Update
        // Delete
    }
}

Running this application will print SQL queries in the console, including values for insert, update, delete, and select operations. This output helps you understand how to print SQL queries with values using Spring JPA.

3.3 Interpreting the Console Output

3.3.1 Insert Operation Example

Code:

System.out.println("\nStarting Insert Operations");

User user1 = new User();
user1.setName("John Doe");
user1.setEmail("john.doe@example.com");
userRepository.save(user1);

User user2 = new User();
user2.setName("Jane Doe");
user2.setEmail("jane.doe@example.com");
userRepository.save(user2);

System.out.println("\nInsert Operations Completed");

Output:

• SQL Statement: Hibernate is preparing to insert a new user by selecting the next value from the users_seq sequence.

Starting Insert Operations
2024-08-11T17:00:15.306+05:30 DEBUG 3161 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    select
        next value for users_seq

• SQL Insert Query: This is the actual SQL insert query. Note the use of placeholders (?) for the parameter values.

2024-08-11T17:00:15.326+05:30 DEBUG 3161 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    /* insert for
        com.bootcamptoprod.entity.User */insert 
    into
        users (email, name, id) 
    values
        (?, ?, ?)

• Parameter Binding: These lines show the actual values being bound to the placeholders in the SQL insert query. For example, the email parameter is bound to “john.doe@example.com”. The name parameter is bound to “John Doe” and id parameter is bound to “1”.

2024-08-11T17:00:15.327+05:30 TRACE 3161 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [john.doe@example.com]
2024-08-11T17:00:15.327+05:30 TRACE 3161 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (2:VARCHAR) <- [John Doe]
2024-08-11T17:00:15.327+05:30 TRACE 3161 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (3:BIGINT) <- [1]

The same logs will be printed for the second record, including ID generation using sequence, the SQL insert query, and parameter values.

3.3.2 Select Operation Example

Code:

// Select
System.out.println("\nStarting Select All Users Operation");

List < User > users = userRepository.findAll();
users.forEach(System.out::println);

System.out.println("\nSelect All Users Operation Completed");

Output:

• Select Query: This is a SQL select query that retrieves all users from the users table. The output shows the retrieved user entities.

Starting Select All Users Operation
2024-08-11T17:09:43.020+05:30 DEBUG 3279 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    /* <criteria> */ select
        u1_0.id,
        u1_0.email,
        u1_0.name 
    from
        users u1_0
com.bootcamptoprod.entity.User@21539796
com.bootcamptoprod.entity.User@34a99d8

Select All Users Operation Completed

3.3.3 Update Operation Example

Code:

// Update
System.out.println("\nStarting Update Operation");

User userToUpdate = userRepository.findById(user1.getId()).orElseThrow();
userToUpdate.setEmail("new.john.doe@example.com");
userRepository.save(userToUpdate);

System.out.println("\nUpdate Operation Completed");

Output:

• Select Query: Before performing an update, Hibernate first executes a select query to fetch the current state of the entity from the database. This ensures that the update operation works with the latest data.

Why Two Select Queries?

In some scenarios, Hibernate may execute the select query twice:

• First Select: Ensures the entity exists and is loaded from the database.
• Second Select: May be due to optimistic locking checks or versioning where Hibernate re-fetches the data to confirm its consistency before applying the update. This behavior can be influenced by settings related to transaction isolation or locking mechanisms.

Starting Update Operation
2024-08-11T17:34:37.616+05:30 DEBUG 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    select
        u1_0.id,
        u1_0.email,
        u1_0.name 
    from
        users u1_0 
    where
        u1_0.id=?
        
2024-08-11T17:34:37.616+05:30 TRACE 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:BIGINT) <- [1]

2024-08-11T17:34:37.618+05:30 DEBUG 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    select
        u1_0.id,
        u1_0.email,
        u1_0.name 
    from
        users u1_0 
    where
        u1_0.id=?
2024-08-11T17:34:37.618+05:30 TRACE 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:BIGINT) <- [1]

• Update Query: Hibernate’s SQL update query binds all parameters by default, including those for columns that haven’t changed, to ensure consistency with the entity’s state. For instance, even if only the email field is updated, the update query includes and binds all columns (email, name, and id) as shown in the log output.
• To optimize the update query, the @DynamicUpdate annotation can be used, which instructs Hibernate to generate SQL statements that include only the modified columns. This avoids redundant updates, improves performance, and reduces database row locking by updating only the relevant fields. For more information, you can refer to our article: Achieving Flexibility and Efficiency with Dynamic Insert and Update in Spring Data JPA

2024-08-11T17:34:37.620+05:30 DEBUG 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    /* update
        for com.bootcamptoprod.entity.User */update users 
    set
        email=?,
        name=? 
    where
        id=?
2024-08-11T17:34:37.620+05:30 TRACE 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [new.john.doe@example.com]
2024-08-11T17:34:37.620+05:30 TRACE 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (2:VARCHAR) <- [John Doe]
2024-08-11T17:34:37.620+05:30 TRACE 3623 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (3:BIGINT) <- [1]

Update Operation Completed

3.3.4 Delete Operation Example

Code:

// Delete
System.out.println("\nStarting Delete Operation");

userRepository.deleteById(user2.getId());

System.out.println("\nDelete Operation Completed");

Output:

• Delete Query: During the delete operation, Hibernate first executes a SELECT query to fetch the current state of the entity with the specified ID, which helps ensure that the entity exists before proceeding with the deletion. This SELECT statement binds the parameter id to verify the record being deleted, as shown in the log.
• Once verified, Hibernate then issues a DELETE query to remove the record from the users table where the id matches the bound parameter. The DELETE query, which also binds the id parameter, ensures the correct record is deleted. This sequence of operations guarantees that the record to be deleted is identified accurately and removed from the database.

Starting Delete Operation
2024-08-11T17:49:19.243+05:30 DEBUG 3758 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    select
        u1_0.id,
        u1_0.email,
        u1_0.name 
    from
        users u1_0 
    where
        u1_0.id=?
2024-08-11T17:49:19.243+05:30 TRACE 3758 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:BIGINT) <- [2]

2024-08-11T17:49:19.245+05:30 DEBUG 3758 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    /* delete for com.bootcamptoprod.entity.User */delete 
    from
        users 
    where
        id=?
2024-08-11T17:49:19.245+05:30 TRACE 3758 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:BIGINT) <- [2]

Delete Operation Completed

3.3.5 Select with parameter Operation Example

Code:

// Select with parameter
System.out.println("\nStarting Select with Parameter Operation");

List < User > johns = userRepository.findByName("John Doe");
johns.forEach(System.out::println);

System.out.println("\nSelect with Parameter Operation Completed");

Output:

• Select Query: In the “Select with Parameter Operation,” Hibernate executes a SELECT query to retrieve records from the users table where the name matches a specified parameter. Initially, it issues the SELECT statement, as seen in the log, which includes a placeholder (?) for the name field. Hibernate then binds the actual value "John Doe" to this placeholder to identify the correct records. The result is a list of User entities with the name "John Doe", which is then returned and represented by the User object in the log output. This process ensures that the query targets only those records that match the provided parameter value, enabling precise data retrieval.

Starting Select with Parameter Operation
2024-08-11T17:52:23.966+05:30 DEBUG 3775 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.SQL                        : 
    /* <criteria> */ select
        u1_0.id,
        u1_0.email,
        u1_0.name 
    from
        users u1_0 
    where
        u1_0.name=?
2024-08-11T17:52:23.967+05:30 TRACE 3775 --- [spring-boot-data-jpa-sql-queries-logging] [           main] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [John Doe]
com.bootcamptoprod.entity.User@28f6a008

Select with Parameter Operation Completed

4. Source Code

The complete source code of the above examples can be found here.

5. Things to Consider

Here are some important considerations to keep in mind when printing SQL queries in logs:

• Log Level Configuration: Ensure you configure the correct log levels for Hibernate to print SQL queries. Typically, you’ll use DEBUG or TRACE levels for Hibernate’s SQL logging.
• Impact on Performance: Be aware that logging SQL queries can significantly impact performance, especially in a production environment. It’s advisable to enable detailed SQL logging only during development or debugging sessions.
• Understanding Multiple Queries: Sometimes, you may notice multiple select queries in the logs, even for operations like updates. This can occur due to entity state management, lazy loading, or optimization techniques used by Hibernate. Investigate why multiple queries are generated and ensure they align with your application’s behavior and performance expectations.
• Parameter Binding: Pay attention to how parameters are bound to SQL queries. Understanding the parameter binding helps in verifying that the correct values are being used and can aid in debugging issues related to incorrect data being processed.
• Security Considerations: Be cautious about logging sensitive information. Ensure that SQL logs do not expose any sensitive data that could be exploited if accessed by unauthorized individuals.
• Debugging and Troubleshooting: Use SQL logs as a debugging tool to trace and resolve issues related to SQL queries. Analyze the logs to understand query behavior, parameter values, and any discrepancies in the data.

6. FAQs

7. Conclusion

In conclusion, understanding how to print and interpret SQL queries with their parameters in Spring Boot helps improve your ability to monitor and debug database interactions. By configuring your application to log these details, you gain deeper insights into how your queries are executed and can address performance issues or unexpected behavior more efficiently. Whether you’re dealing with inserts, selects, updates, or complex joins, this knowledge is crucial for maintaining robust and reliable applications.

8. Learn More