When debugging a hibernate related issue, it is useful to examine queries in the console. Check out how to print them with binding parameters.
To log queries with values as console output, add the following lines to the application-development.properties file:
|
1 2 3 4 |
#awesome-project/src/main/resources/application-development.properties logging.level.org.hibernate.SQL=debug spring.jpa.properties.hibernate.format_sql=true logging.level.org.hibernate.type.descriptor.sql=trace |
Check out the example output given after calling a simple action for creating a new user account:
|
1 2 3 4 5 6 7 8 9 10 11 |
# console output 2018-12-16 08:30:57.070 DEBUG 9161 --- [nio-8080-exec-1] org.hibernate.SQL: insert into user (id, password, role, username) values (null, ?, ?, ?) TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [$2a$10$oA1XYAouefHO4EvgDsW3s.JAmqrYJApqESKgFsnlpPJMWTSQx7QqK] TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [ROLE_USER] TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] - [user] |
| For security and performance reasons print queries only when you need to debug them and avoid doing this on the production environment. |
Properties explained
Show only queries
|
1 2 |
#awesome-project/src/main/resources/application-development.properties logging.level.org.hibernate.SQL=debug |
This property allows us to see only the prepared statement:
|
1 2 |
# console output DEBUG 12006 --- org.hibernate.SQL : insert into user (id, password, role, username) values (null, ?, ?, ?) |
Format output
To beautify the output and make it more readable we can add the format property:
|
1 2 3 |
#awesome-project/src/main/resources/application-development.properties logging.level.org.hibernate.SQL=debug spring.jpa.properties.hibernate.format_sql=true |
Now the same query is displayed in a more comprehensible way:
|
1 2 3 4 5 6 7 8 |
# console output 2018-12-16 08:30:57.070 DEBUG 11700 --- [nio-8080-exec-2] org.hibernate.SQL : insert into user (id, password, role, username) values (null, ?, ?, ?) |
Include binding parameters
To print values we need the following line:
|
1 2 |
#awesome-project/src/main/resources/application-development.properties logging.level.org.hibernate.type.descriptor.sql=trace |
Finally, the output contains also the binding parameters values:
|
1 2 3 4 |
# console output TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [$2a$10$oA1XYAouefHO4EvgDsW3s.JAmqrYJApqESKgFsnlpPJMWTSQx7QqK] TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [ROLE_USER] TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] - [user] |
Photo by frank mckenna on StockSnap