How to Print a Query String With Parameter Values When Using Hibernate

How to print a query string with parameter values when using Hibernate

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.

print hibernate sql query string with parameters into logs

In my case i need to print the complete sql query with parameter values which was executed by hibernate into logs, but i am not using log4j API for logging to configure the debug level for org.hibernate.SQL and org.hibernate.type

Whether you're using a logging framework or not, Hibernate will output question marks when logging prepared statements.

If you want to print the "real query" with the bound values, you'll have to use a JDBC Proxy driver like P6Spy (doesn't move anymore) or log4jdbc.

Java + Hibernate - Print query with values to console

You can use datasource-proxy or p6spy

This allows you to view the actual parameter values used in firing the SQL. Example on how to configure can be found here

Sample format would be as below:

Name:DATA_SOURCE_PROXY, Time:6, Success:True,
Type:Prepared, Batch:True, QuerySize:1, BatchSize:3,
Query:["insert into post (title, version, id) values (?, ?, ?)"],
Params:[(Post no. 0, 0, 0), (Post no. 1, 0, 1), (Post no. 2, 0, 2)

How to print a query string with parameter values when using Hibernate

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.

How to print parameters along with sql generated by Hibernate 5.4 using log4j2.properties file

You should add just one more logger:

logger.hibernate-type.name=org.hibernate.type
logger.hibernate-type.level=trace

Spring boot show sql parameter binding?

This is just a hint to the underlying persistence provider e.g. Hibernate, EclipseLink etc. Without knowing what you are using it is difficult to say.

For Hibernate you can configure logging to also output the bind parameters:

http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-log4j/

which will give you output like:

Hibernate: INSERT INTO transaction (A, B) 
VALUES (?, ?)
13:33:07,253 DEBUG FloatType:133 - binding '10.0' to parameter: 1
13:33:07,253 DEBUG FloatType:133 - binding '1.1' to parameter: 2

An alternative solution which should work across all JPA providers is to use something like log4jdbc which would give you the nicer output:

INSERT INTO transaction (A, B) values (10.0, 1.1);

See:

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

---

Related Topics