Display actual SQL with parameter values in Hibernate

I have enabled SQL logging in my hibernate.cfg.xml by adding this property:

<property name="show_sql">true</property>

This lets me view the generated SQL queries in the console output. However, the displayed queries contain parameter placeholders instead of the actual values being sent to the database.

For instance, what I currently see is:

select dept_.name from company.department dept_ where dept_.id=?

But what I really want to see is the complete query with substituted values:

select department.name from department where department.id=25

Is there a way to configure Hibernate to show the final SQL statement with all parameter values filled in, exactly as it gets executed against the database?

The Problem:

You’ve enabled SQL logging in your hibernate.cfg.xml to view generated SQL queries, but instead of seeing the actual values, you’re seeing placeholders like ? in your queries. You want to see the complete query with substituted parameter values, for example: Instead of select dept_.name from company.department dept_ where dept_.id=?, you want to see select department.name from department where department.id=25.

TL;DR: The Quick Fix:

Use p6spy! It intercepts JDBC calls and displays the full SQL query with parameter values substituted. This avoids complex Hibernate configuration changes.

:thinking: Understanding the “Why” (The Root Cause):

Hibernate, by default, logs parameterized queries for security and performance reasons. Directly displaying the values in the log could expose sensitive data. p6spy provides a solution that allows you to see the final executed query without compromising security during the standard logging process.

:gear: Step-by-Step Guide:

  1. Add p6spy: Include p6spy as a dependency in your project. The exact method will depend on your build system (Maven, Gradle, etc.). Consult p6spy’s documentation for detailed instructions on adding it to your project.

  2. Configure p6spy: Configure p6spy to log the SQL statements. This usually involves creating a p6spy.properties file and configuring the logMessageFormat property. Refer to the p6spy documentation for specifics, as the configuration depends on your preferences and setup.

  3. Restart Your Application: Restart your application to apply the changes.

:mag: Common Pitfalls & What to Check Next:

  • JDBC Driver Compatibility: Ensure your JDBC driver is compatible with p6spy.
  • p6spy Configuration: Double-check your p6spy.properties file for any typos or incorrect settings. Common issues include incorrect paths to log files or misconfigured log levels.
  • Logging Framework Conflicts: If you have multiple logging frameworks, conflicts might occur. Adjust logging configurations accordingly.
  • Database Driver Version: An outdated database driver might cause compatibility problems with p6spy.

:speech_balloon: Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

You can do this by enabling Hibernate’s parameter logging with the SQL logging. Add <property name="format_sql">true</property> for better formatting and <property name="use_sql_comments">true</property> for extra context. But the best approach is setting your logging framework to DEBUG for org.hibernate.type.descriptor.sql.BasicBinder. This shows the parameter bindings separately, so you’ll see exactly what values get bound to each placeholder. The SQL and parameters show up in separate log entries instead of one combined statement, but you get full visibility into what’s actually executing. I’ve found this combo gives you everything you need without external tools or complex config changes.