How to Show Hibernate/JPA SQL Statements in Spring Boot

Spring Boot applications that use Hibernate/JPA often need to log SQL statements for debugging and performance tuning. This tutorial will guide you through the steps to configure your Spring Boot application to show Hibernate/JPA SQL statements.

Prerequisites

• JDK 17 or later
• Maven or Gradle
• IDE (IntelliJ IDEA, Eclipse, etc.)

Step 1: Set Up a Spring Boot Project

1.1 Create a New Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

• Spring Web
• Spring Data JPA
• H2 Database (or any other database of your choice)

Download and unzip the project, then open it in your IDE.

1.2 Configure application.properties

Set up the application properties for your project. This file is located in the src/main/resources directory.

# src/main/resources/application.properties

# Server configuration
server.port=8080

# H2 Database configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.datasource.platform=h2

# JPA configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

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

Explanation:

• spring.jpa.show-sql=true: Enables logging of SQL statements.
• spring.jpa.properties.hibernate.format_sql=true: Formats the SQL statements for better readability.
• logging.level.org.hibernate.SQL=DEBUG: Sets the logging level for SQL statements to DEBUG.
• logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE: Logs the binding of parameters to SQL statements.

Step 2: Create an Entity Class

Create an entity class to represent a table in the database.

package com.example.demo.entity;

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

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Explanation:

• @Entity: Specifies that the class is an entity and is mapped to a database table.
• @Id: Specifies the primary key of the entity.
• @GeneratedValue(strategy = GenerationType.IDENTITY): Indicates that the primary key is generated automatically.

Step 3: Create a Repository Interface

Create a repository interface to perform CRUD operations on the User entity.

package com.example.demo.repository;

import com.example.demo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}

Explanation:

• @Repository: Indicates that the interface is a Spring Data repository.
• JpaRepository<User, Long>: Extends the JpaRepository interface to provide CRUD operations for the User entity.

Step 4: Create a Service Class

Create a service class to handle business logic related to User entities.

package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public List<User> getAllUsers() {
        return userRepository.findAll();
    }

    public User createUser(User user) {
        return userRepository.save(user);
    }
}

Explanation:

• @Service: Marks the class as a service component in Spring.
• UserRepository: Injected to interact with the database.

Step 5: Create a REST Controller

Create a REST controller to expose endpoints for interacting with User entities.

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    @PostMapping("/users")
    public User createUser(@RequestBody User user) {
        return userService.createUser(user);
    }
}

Explanation:

• @RestController: Marks the class as a REST controller.
• @GetMapping("/users"): Maps HTTP GET requests to the getAllUsers method.
• @PostMapping("/users"): Maps HTTP POST requests to the createUser method.
• @RequestBody: Binds the HTTP request body to the User parameter.

Step 6: Running and Testing the Application

6.1 Run the Application

Run the Spring Boot application using your IDE or the command line:

./mvnw spring-boot:run

6.2 Test the Endpoints

Use a tool like Postman or your browser to test the endpoints.

GET /users

• URL: http://localhost:8080/users
• Method: GET

You should see an empty list [] if no users have been added.

POST /users

• URL: http://localhost:8080/users
• Method: POST
• Body:

  {
      "name": "John Doe",
      "email": "john.doe@example.com"
  }
  

You should see a response with the created user object, including the generated ID.

6.3 Verify the SQL Statements

Check the console output of your application. You should see the SQL statements being logged, including the parameter bindings. The output will look something like this:

2023-05-17 15:34:45.123  DEBUG 12345 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into user (email, name, id) values (?, ?, ?)
2023-05-17 15:34:45.123  TRACE 12345 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [john.doe@example.com]
2023-05-17 15:34:45.123  TRACE 12345 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [John Doe]
2023-05-17 15:34:45.123  TRACE 12345 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [1]

Conclusion

In this tutorial, you have learned how to configure your Spring Boot application to show Hibernate/JPA SQL statements. We covered:

• Setting up a Spring Boot project with JPA and H2 database.
• Configuring application properties to enable SQL logging.
• Creating an entity, repository, service, and controller.
• Running the application and verifying the SQL statements in the console output.

By following these steps, you can effectively monitor and debug the SQL queries generated by Hibernate in your Spring Boot application.