Spring boot show sql parameter binding?

Question

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

Does this answer your question? [How to print a query string with parameter values when using Hibernate](https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate) — Jens Schauder, Feb 09 '21 at 12:11

score 61 · Answer 1 · answered Nov 22 '17 at 18:42

61

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

answered Nov 22 '17 at 18:42

rv.comm

675
1
7
10

greenhorn · Answer 2 · 2020-07-30T07:34:00.360

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]

score 19 · Answer 3 · answered Jan 28 '23 at 06:49

19

For Spring Boot 3, as it uses Hibernate 6, the aboves is not working.

Try:

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

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

answered Jan 28 '23 at 06:49

Alireza Fattahi

42,517
14
123
173

For spring boot 3 it works ideally – kapellan Feb 23 '23 at 09:51
https://stackoverflow.com/a/74587796/175554 worked for me. – ozkanpakdil Apr 08 '23 at 08:33

score 17 · Answer 4 · answered Mar 03 '20 at 12:27

17

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

answered Mar 03 '20 at 12:27

Sanal S

1,105
14
27

score 16 · Accepted Answer · edited Jul 10 '23 at 22:22

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:

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/

Thanks @Alan for the answer . I like your alternative solution suggested. I have not got time to check yet :). If problem persists I will get back to you. — Bhupati Patel, Jun 18 '15 at 09:40

score 1 · Answer 6 · answered Apr 10 '19 at 06:31

1

For Eclipse link, Add these lines in appilication.properties

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

answered Apr 10 '19 at 06:31

parasuraman s

21
5

Spring boot show sql parameter binding?

6 Answers6

Linked