Show Hibernate/JPA SQL Statements in Spring Boot

Seeing the SQL statements generated by Hibernate/JPA can be very helpful for debugging and performance tuning. Spring Boot makes it easy to enable SQL logging. In this guide, we'll cover how to configure your Spring Boot application to show SQL statements and the associated parameter values.

Prerequisites

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

Step-by-Step Guide

Step 1: Set Up a Spring Boot Project

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

• Project: Maven Project
• Language: Java
• Spring Boot: 3.2.x
• 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.

Step 2: Configure the Database

2.1 Configure application.properties

Set up your database configuration in the src/main/resources/application.properties file. For this guide, we'll use an in-memory H2 database and configure the necessary properties to show SQL statements.

# src/main/resources/application.properties

# 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

# Hibernate configuration
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.highlight_sql=true
spring.jpa.properties.hibernate.use_sql_comments=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 the logging of SQL statements.
• spring.jpa.properties.hibernate.format_sql=true: Formats the SQL statements to make them more readable.
• spring.jpa.properties.hibernate.highlight_sql=true: Highlights the SQL statements in the logs.
• spring.jpa.properties.hibernate.use_sql_comments=true: Adds comments to SQL statements for better readability.
• logging.level.org.hibernate.SQL=DEBUG: Sets the logging level for Hibernate SQL statements to DEBUG.
• logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE: Logs the binding of parameters to SQL statements.

Step 3: Define Your Entity

Create a simple Student entity in the com.example.demo.entity package.

package com.example.demo.entity;

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

@Entity
public class Student {

    @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;
    }
}

Step 4: Create a Repository

Create an interface named StudentRepository in the com.example.demo.repository package.

package com.example.demo.repository;

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

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
}

Step 5: Create a Service

Create a service class named StudentService in the com.example.demo.service package.

package com.example.demo.service;

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

import java.util.List;

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    public List<Student> getAllStudents() {
        return studentRepository.findAll();
    }

    public Student saveStudent(Student student) {
        return studentRepository.save(student);
    }
}

Step 6: Create a Controller

Create a controller class named StudentController in the com.example.demo.controller package.

package com.example.demo.controller;

import com.example.demo.entity.Student;
import com.example.demo.service.StudentService;
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 StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("/students")
    public List<Student> getAllStudents() {
        return studentService.getAllStudents();
    }

    @PostMapping("/students")
    public Student saveStudent(@RequestBody Student student) {
        return studentService.saveStudent(student);
    }
}

Step 7: Running and Testing the Application

7.1 Run the Application

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

./mvnw spring-boot:run

7.2 Test the SQL Logging

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

1. Create a new student:

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

     {
       "name": "John Doe",
       "email": "[email protected]"
     }
     

2. Retrieve all students:

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

Check the console output to see the SQL statements and parameter values being logged. You should see formatted SQL statements, highlighted if supported by your console, and detailed logs showing parameter binding.

Conclusion

In this guide, you have learned how to enable and configure SQL statement logging in a Spring Boot application using Hibernate/JPA. We covered:

• Setting up a Spring Boot project with the necessary dependencies.
• Configuring the application.properties file to enable SQL logging.
• Creating a simple entity, repository, service, and controller.
• Running and testing the application to see the SQL statements in the logs.

By following these steps, you can easily debug and monitor the SQL statements generated by Hibernate/JPA in your Spring Boot applications.