CODEWITHSUNDEEP
jpaspring-bootspring-data
Bhupati Patel
Bhupati Patel

Reputation: 1670

Spring boot show sql parameter binding?

I am new to spring boot. What is the configuration setting for sql parameter binding? For example, in the following line I should be able to see values for all '?'.

SELECT * FROM MyFeed WHERE feedId > ? AND isHidden = false ORDER BY feedId DESC LIMIT ?

Currently, I have the configuration as

spring.jpa.show-sql: true

Upvotes: 74

Views: 141664

Answers (9)

Alireza Fattahi
Alireza Fattahi

Reputation: 45475

For Spring Boot 3, as it uses Hibernate 6, the above solutions are not working.

Try:

logging:
  level:
    org.hibernate.orm.jdbc.bind: trace

See: https://stackoverflow.com/a/74587796/2648077 and https://stackoverflow.com/a/74862954/2648077

Upvotes: 74

nei-azevedo
nei-azevedo

Reputation: 41

Try this. It works great for me with spring-boot-starter-parent version 3.2.1.

logging.level.org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl=trace

Upvotes: 1

Amr Khaled Nowahy
Amr Khaled Nowahy

Reputation: 11

Add the below lines to your application.properties file

logging.level.org.hibernate.orm.jdbc.bind=trace
logging.level.org.hibernate.type=trace
logging.level.org.hibernate.stat=debug

Upvotes: 0

Michael Isvy
Michael Isvy

Reputation: 4553

using Spring Boot 3.1.3 and Hibernate 6.2.7, the following works for me:

spring.jpa.properties.hibernate.show_sql=true
logging.level.org.hibernate.orm.jdbc.bind=trace

# Easier to read but takes more space
# spring.jpa.properties.hibernate.format_sql=true

(last row is optional, it's only if you wish to have formatted SQL in the logs)

Upvotes: 10

Alan Hay
Alan Hay

Reputation: 23226

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:

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/

Upvotes: 16

greenhorn
greenhorn

Reputation: 654

In the application yml add the following property.

logging:
  level:
    org:
      hibernate:
        type: trace

Add the following to print the formatted SQL in the console

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true

Presume you are finding a student record by the id and you will be able to see the binding param as follows

Hibernate: select student0_.id as id8_5_0_ from student student0_ where student0_.id=?

2020-07-30 12:20:44.005 TRACE 1328 --- [nio-8083-exec-8] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [1]

Upvotes: 26

Sanal S
Sanal S

Reputation: 1152

Add these to the property file

#to show sql
spring.jpa.properties.hibernate.show_sql=true
#formatting
spring.jpa.properties.hibernate.format_sql=true
#printing parameter values in order
logging.level.org.hibernate.type.descriptor.sql=trace

Upvotes: 17

parasuraman s
parasuraman s

Reputation: 21

For Eclipse link, Add these lines in appilication.properties

jpa.eclipselink.showsql=true
jpa.eclipselink.logging-level=FINE

Upvotes: 1

rv.comm
rv.comm

Reputation: 783

Add these to application.properties and you should see the logs in details.

logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace

Upvotes: 71

Related Questions