How to print a query string with parameter values

Question

Is it possible in Hibernate to print generated SQL queries with real values instead of question marks?

How would you suggest to print queries with real values if it is not possible with Hibernate API?

Answer 1

You need to enable logging for the the following categories:

• org.hibernate.SQL   - set to debug to log all SQL DML statements as they are executed
• org.hibernate.type - set to trace to log all JDBC parameters

So a log4j configuration could look like:

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug 

# Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace 

The first is equivalent to hibernate.show_sql=true legacy property, the second prints the bound parameters among other things.

Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

Answer 2

This answer is a little variance for the question. Sometimes, we only need the sql only for debug purposes in runtime. In that case, there are a more easy way, using debug on editors.

• Put a breakpoint on org.hibernate.loader.Loader.loadEntityBatch (or navigate on the stack until there);
• When execution is suspended, look the value of variable this.sql ;

This is for hibernate 3. I'm not sure that this work on other versions.

Answer 3

The simplest solution for me is implementing a regular stringReplace to replace parameter inputs with parameter values (treating all parameters as string, for simplicity):

 String debugedSql = sql;
 //then, for each named parameter
     debugedSql = debugedSql.replaceAll(":"+key, "'"+value.toString()+"'");
 //and finnaly
 println(debugedSql);

or something similar for positional parameters (?).
Take care of null values and specific value types like date, if you want a run ready sql to be logged.

Answer 4

Log4Jdbc plugin would be best for your requirement. It shows following-

1. Complete SQL query being hit to the db
2. Parameter values being passed to the query
3. Execution time taken by each query

Refer below link to configure Log4Jdbc-

https://code.google.com/p/log4jdbc/

Answer 5

add following properties and values to your log4j or logback configuration:

org.hibernate.sql=DEBUG
org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Answer 6

I like this for log4j:

log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace
log4j.logger.org.hibernate.type.descriptor.sql.BasicExtractor=error 
log4j.logger.org.hibernate.type.CollectionType=error