How do I let the console output the encapsulated SQL when using Hibernate's Gethibernatetemplate ()? How do I print it in a log?

When we use Hibernate, we tend to focus on whether the generated SQL statements are displayed, but sometimes not enough. By default, Hibernate executes SQL statements that are printed on the console, and can also be configured to output to log4j or Log4back, as well as to show more detailed parameters and value information. Here's a brief talk.

Hibernate configuration file Hibernate.cfg.xml provides three configuration items for displaying SQL, and if it is federated with spring, it can also be configured in spring configuration. Their value is a Boolean value.

• 1) Hibernate.show_sql-whether the generated SQL statements are displayed, we most often deal with it
• 2) Hibernate.format_sql-whether to format the generated SQL statements, to increase readability, or to squeeze them all in one line
• 3) Hibernate.use_sql_comments-Whether a comment is displayed to indicate what operation generated the SQL statement, this configuration is slightly unfamiliar compared to the two above

Take a look at the effect of adding the above three configurations, and after performing the Hibernate query, produce the following output on the console:

Hibernate:      /*  * / Select         securities0_.post_id as post1_7_1_,         Security1_.shareclassid as Sharecla1_16 _0_,         security1_.company_id as company2_16_0_,     from         post_security_relationship securities0_     INNER JOIN         unmi.securities security1_ on             securities0_.shareclassid=Security1_.shareclassid     where         securities0_.post_id=?

Hibernate.show_sql controls whether the generated SQL statements are displayed globally, hibernate.format_sql the effect of formatting, or a row, and the hibernate.use_sql_comments output is a red part, Indicates the SQL statement that was executed when Gar securities the collection.

Hibernate defaults to the output of SQL statements to the console, while the contents of the console are not easy to read, for example, content that exceeds the console cache is cleared, and not everyone can see the console, which is difficult to correlate with time. While some application servers redirect console output to a file, there is no easy way for professional logging tools such as log4j or SLF4J.

Because Hibernate uses the logger name Org.hibernate.SQL to output SQL, you want to have the SQL statement output to log4j or SLF4J log (the log file is either recorded elsewhere, as determined by Appender), as long as the L Og4j.properties (Log4j.xml refer to the corresponding configuration), add:

Log4j.logger.org.hibernate.sql=debug

Remember to set the Hibernate.show_sql in the Hibernate configuration file (or in the configuration of hibernate in Spring) to false at the same time, or there may be a double output under the console (log4j configured Consoleappend ER).

If you are using Log4back, add the following in the Log4back configuration file Log4back.xml:

<logger name= "Org.hibernate.SQL" level= "DEBUG"/>

At this time the log output format and the console is not much different, just follow log4j or slf4j run, like:

20:13:40.757 [http-8080-1] DEBUG Org.hibernate.SQL-     * */ Select         tags0_.post _id as post1_7_1_,         tags0_.tag_id as tag2_1_,         elementite1_.id as id3_0_,     from         post_tag_relationship tags0_     INNER JOIN         unmi.element_item elementite1_ on             tags0_.tag_id=elementite1_.id     where         tags0_.post_id=?

Only the red ones are different, the log4j or SLF4J configuration is applied, and you can see the execution time, thread and other related information.

We would like to know the previous output of the SQL statement? The parameter represents the actual value of what is also required to open a log Org.hibernate.type.descriptor.sql.BasicBinder output level for TRACE, where the The output level of the Org.hibernate.type.descriptor.sql.BasicExtractor is also set to TRACE to see the effect:

Log4j.properties is configured as: Log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=TRACE log4j. Loggerorg.hibernate.type.descriptor.sql.BasicExtractor=<logger name= " Org.hibernate.type.descriptor.sql.BasicBinder "level=" TRACE "/> <logger name=" Org.hibernate.type.descriptor.sql.BasicExtractor "level=" TRACE "/>

Then look at the output:

 20:13:40.710 [http-8080-1] DEBUG Org.hibernate.SQL-/*   Load collection cc.unmi.test.model.Post.categories  */  select categories0_.post_id as post1_7_1_, elementite1_.id as id3_0_, from Post_category_relationship categories0_ INNER JOIN Unmi.element_item elementite1_ on Categori es0_.category_id  =elementite1_.id where categories0_.post_id  =? 20:13:40.710 [http-8080-1] TRACE org.hibernate.type.descriptor.sql.basicbinder-binding parameter [1] as [INTEGER]-10 2 0:13:40.710 [http-8080-1] TRACE org.hibernate.type.descriptor.sql.basicextractor-found [1002" as column [id3_0_]  20:13:40.710 [http-8080-1] TRACE Org.hibernate.type.descriptor.sql.BasicExtractor -found [ten] as column [post1_7_1_] 

The red part is org.hibernate.type.descriptor.sql.basicbinder=trace controlled and shows a list of parameters bound to SQL. The blue section is Org.hibernate.type.descriptor.sql.basicextractor=trace controlled, showing the field values recorded after the query. Note that these two properties are to be set to the TRACE level, so they are not affected under the global DEBUG level of the general log.

Sometimes you want to display the value of a named parameter in a query using: email instead? form, you also need to introduce two

Log4j.logger.org.hibernate.engine.queryparameters=debug

Log4j.logger.org.hibernate.engine.query.hqlqueryplan=debug

The effect is this:

20:13:40.710 [http-8080-1] org.hibernate.engine.query.hqlqueryplan-find:from User where email =20:13:40.710 [Http-8080-1] org.hibernate.engine.queryparameters-named parameters: {email=20:13:40.726 [http-8080-1] Org.hibernate.SQL-     * */ Select         user0_.id as id0_,         User0_.email as Email0 _,         user0_.enabled as enabled0_,         User0_.encodedpassword as encodedp8_0_     from         User user0_     where         user0_.email=?

For the above synthesis, a better configuration can be consulted:

Configuration in Hibernate.cfg.xml:

<property name= "Hibernate.show_sql" >false</property><property name= "Hibernate.format_sql" >true </property><property name= "Hibernate.use_sql_comments" >true</property>

Or the configuration properties for Hibernate in Spring:

<prop key= "Hibernate.show_sql" >false</prop><prop key= "Hibernate.format_sql" >true</prop> <prop key= "Hibernate.use_sql_comments" >true</prop>

In the log configuration, as in Log4j.properties, configure:

Log4j.logger.org.hibernate.type.descriptor.sql.basicbinder= Tracelog4j.logger.org.hibernate.type.descriptor.sql.basicextractor=tracelog4j.logger.org.hibernate.sql= debuglog4j.logger.org.hibernate.engine.queryparameters= Debuglog4j.logger.org.hibernate.engine.query.hqlqueryplan=debug

If you are using slf4j words, configure them in Logback.xml:

<logger name= "Org.hibernate.type.descriptor.sql.BasicBinder" level= "TRACE"/><logger name= " Org.hibernate.type.descriptor.sql.BasicExtractor "level=" TRACE "/><logger name=" Org.hibernate.SQL "level=" Debug "/><logger name=" org.hibernate.engine.QueryParameters "level=" Debug "/><logger name=" Org.hibernate.engine.query.HQLQueryPlan "level=" DEBUG "/>

If you think there are too many logs, I would think so anyway, so consider setting Org.hibernate.type.descriptor.sql.BasicExtractor to DEBUG, or not setting the configuration item

How do I let the console output the encapsulated SQL when using Hibernate's Gethibernatetemplate ()? How do I print it in a log?