Print Hibernate SQL query string with parameter values in Spring Boot

In the spring boot application, if the hibernate log is enabled using the JPA spring boot configuration, the sql query will be printed. The value of the parameters is not logged in the hibernate log files. The default JPA spring boot configuration will enable sql queries to be logged as they are. Configuration will not enable the values of the parameter to log.

If the sql query log is enabled using the hibernate logger directly, the hibernate class will log the query and query parameter values. The spring boot JPA will allow to print only sql queries.



Hibernate log JPA Configuration

The following jpa configurations will enable hibernate log in the spring boot application. These configuration will print the hibernate sql queries in the console window. The sql queries like prepared statement will be logged with the parameters place holder. The prepared statement will be printed as it is created. The value of the parameter will not be printed in the log file. For debug the spring boot application, the parameter value is required.

The hibernate jpa configuration will be shown as like below.

application.properties

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

The first configuration line will enable the hibernate log. The second configuration line will show the hibernate query in user friendly format. The sample output of the jpa configuration.

Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into employee (name, id) values (?, ?)


Hibernate SQL query with Parameter values

If the log is configured using the hibernate logger, the hibernate class will print the log with parameter values.

The hibernate query will be printed using the first configuration. The second configuration will show all values of the parameters in the same order. These configurations will be used in the development and debug mode. These configurations are not recommended for use in the production environment or in applications dealing with sensitive data.

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

hibernate log

2020-11-10 15:40:24.582 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL                        : select next_val as id_val from hibernate_sequence for update
2020-11-10 15:40:24.592 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL                        : update hibernate_sequence set next_val= ? where next_val=?
2020-11-10 15:40:24.608 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into employee (name, id) values (?, ?)
2020-11-10 15:40:24.610 TRACE 92677 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [test]
2020-11-10 15:40:24.611 TRACE 92677 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [1]


Hibernate log to File

By default, hibernate logs are printed in the spring boot console. Using the configuration below, hibernate logs can be moved to the files. Configuring the log file name would transfer all the log of the spring boot console to the file. The second configuration would remove logs from the console window.

application.properties

logging.file.name=hibernate_log_file_name.log
logging.pattern.console=

In the above spring boot configuration, all the log statements will be moved to the configured log file. you can not differentiate the hibernate log from the other log statements. If you want to log the hibernate logs in a different log file, the logbook.xml or underlying log configuration file must be used to configure the log file names in the spring boot application.



Leave a Reply