Hibernate show real SQL

The ABCs of Hibernate's SQL representation

Hibernate provides several properties to log and better visualize SQL statements:

• hibernate.show_sql: Displays SQL statements in the STDOUT, but the displayed SQL is formatted by Hibernate, so they might slightly differ from the actual SQL sent to the DB.

hibernate.show_sql=true
# Setting above property to true is akin to asking Hibernate:
#    "Hey Hibernate, you mind sharing what SQLs you're sending down to the DB?"
# To the delight of developers and distress of bugs, Hibernate obliges. 


• hibernate.format_sql: Interprets the SQL query into a more readable format.

hibernate.format_sql=true
# It's like asking Hibernate to kindly make the SQL "pretty"!


• hibernate.use_sql_comments: Attach comments to SQL statements to make them more understandable.

hibernate.use_sql_comments=true
# Enabling this wraps SQL statements in lovely comments; they're like warm fuzzy blankets for your SQLs!


• In generated queries, an instance of the table is aliased as this_ — "this_", the friendly table alias!

Proxies for a go-between

For critical situations where you need the exact SQL that Hibernate sends to the database, JDBC driver proxies such as P6Spy or log4jdbc come to your rescue. They log every byte sent to your database. Simply add the respective JAR to your classpath and update your hibernate.cfg.xml or persistence.xml.

Setting the stage at the application level

• Check your logging environment: If using the likes of SLF4J with Logback, they can override Hibernate's logging.

• Proceed with caution if Log4J is your friend in need. Tweak the log4j.properties to ensure the correct log levels are set.

• Setting up Trace Threshold on your log appender will ensure that nothing escapes logging.

Poring over SQL safely

Not to worry, while dissecting SQL queries through show_sql, rest assured that they're crafted by Hibernate's parameter binding mechanism, effectively shielding against SQL Inject attacks.

Handling common logging issues

If the SQL logging is not happening as expected, ensure that the properties are properly set within hibernate.cfg.xml, persistence.xml, or application.properties.

Best practices for logging

To improve logging performance while logging large volumes of SQL queries:

• Disable org.hibernate.type.descriptor.sql at INFO level to prevent performance degradation.

• Set up conditional logging based on query execution time to focus debugging efforts only on slower queries.

Advanced logging tactics

For robust logging layouts:

• Unleash P6Spy or log4jdbc-log4j2 for a comprehensive logging setup.

• Customize Logback or Log4J 's configurations to separate log levels for your SQL and application logs.

• Use analyzers to format and analyze logs for efficient debugging.