Hibernate print SQL and additional parameters

Tag: URI indicates SQL statement icon Mat View span ONS

Today, during the project run, a org.hibernate.exception.GenericJDBCException:could not insert exception is reported, and Root cause is the IBM DB2 errorcode=- 180,sqlstate=22007, after Google, found that the reason for this error is because the timestamp format is not standardized, but specifically, but not very clear, if you can print out the SQL statement that causes the problem, then the positioning of such problems will be very easy.

There are 3 settings in Hibernate's profile hibernate.cfg.xml that are related to the display of SQL statements whose values are Boolean values:
(1), Show_sql: Whether to display SQL statements
(2), Format_sql: whether to format the output string, enhance the readability of SQL
(3), use_sql_comments: Whether a comment is displayed, indicating what operation produced the SQL statement.

By default, Hibernate prints SQL statements on the console, so after you turn on the settings above, you can see the following SQL statements on the console:

Console code

2. /* Load Collection Cc.unmi.test.model.Post.securities */select
4. securities0_.post_id as Post1_7_1_,
6. Security1_.shareclassid as sharecla1_16_0_,
8. security1_.company_id as company2_16_0_,
10. From
12. Post_security_relationship securities0_
14. INNER JOIN
16. Unmi.securities security1_
18. On Securities0_.shareclassid=security1_.shareclassid
20. where
22. Securities0_.post_id=?

Can be found in the console at all, the SQL statement corresponding parameters, in general, Hibernate will be used in conjunction with log4j, so you can more flexible control hibernate log file output. In hibernate, the default output level for the corresponding parameter of the SQL statement is trace, which is lower than the default log4j log level debug, so in order to display the parameters, you need to manually set the log4j configuration. Change the output level under Hibernate to trace:
Log4j.logger.org.hibernate.type.descriptor.sql.basicbinder=trace
Log4j. Loggerorg.hibernate.type.descriptor.sql.basicextractor=trace
When this is modified, the printed SQL statement changes to the following form:

Console code

2. :40.710 [http-8080-1] DEBUG Org.hibernate.SQL-
4. /* Load Collection Cc.unmi.test.model.Post.categories */select
6. categories0_.post_id as Post1_7_1_,
8. Elementite1_.id as id3_0_,
10. From
12. Post_category_relationship categories0_
14. INNER JOIN
16. Unmi.element_item elementite1_
18. On Categories0_.category_id=elementite1_.id
20. where
22. Categories0_.post_id=?
24. :40.710 [http-8080-1] TRACE org.hibernate.type.descriptor.sql.basicbinder-binding parameter [ 1] As [INTEGER]-
26. :40.710 [http-8080-1] TRACE org.hibernate.type.descriptor.sql.basicextractor-found [ 1002] As column [id3_0_]
28. :40.710 [http-8080-1] TRACE Org.hibernate.type.descriptor.sql.basicextractor-found [Ten] As column [post1_7_1_]

If you also want to see the values of named parameters in the query, you also need to add the following values in the log4j configuration file:
Log4j.logger.org.hibernate.engine.queryparameters=debug
Log4j.logger.org.hibernate.engine.query.hqlqueryplan=debug
After this modification, you can get the following results:

Console code

2. :40.710 [http-8080-1] org.hibernate.engine.query.hqlqueryplan-find:from User where email =: Email
4. :40.710 [http-8080-1] org.hibernate.engine.queryparameters-named parameters: {[email protected ]}  
6. :40.726 [http-8080-1] org.hibernate.SQL-
8. /* Named HQL Query Finduserbyemail */SELECT
10. User0_.id as id0_,
12. User0_.email as email0_,
14. User0_.enabled as enabled0_,
16. User0_.encodedpassword as Encodedp8_0_
18. From
20. User user0_
22. where
24. User0_.email=?

Hibernate print SQL and additional parameters