How can I log SQL statements in Spring Boot?

Question

I want to log SQL statements to a file.

I have the following properties in application.properties:

spring.datasource.url=...
spring.datasource.username=user
spring.datasource.password=1234
spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

security.ignored=true
security.basic.enabled=false

logging.level.org.springframework.web=INFO
logging.level.org.hibernate=INFO
logging.file=c:/temp/my-log/app.log

When I run my application,

cmd> mvn spring-boot:run

I can see SQL statements in the console, but they don't appear in app.log. The file contains only basic logs from Spring.

What should I do to see SQL statements in the log file?

Follow instructions on baeldung.com/sql-logging-spring-boot

Ilya Serbis
– Ilya Serbis

2021-02-04 19:31:28 +00:00
Commented Feb 4, 2021 at 19:31 — Ilya Serbis
– Ilya Serbis, Commented Feb 4, 2021 at 19:31
logging.level.sql=trace

Andriy Tolstoy
– Andriy Tolstoy

2024-05-29 10:27:27 +00:00
Commented May 29, 2024 at 10:27 — Andriy Tolstoy
– Andriy Tolstoy, Commented May 29, 2024 at 10:27

Peter Mortensen · Accepted Answer · 2022-05-31 14:21:02Z

680

Try using this in your properties file:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

edited May 31, 2022 at 14:21

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jul 6, 2015 at 15:39

Paul Woods

7,0891 gold badge17 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

elysch Over a year ago

If you want to log values too: logging.level.org.hibernate.type=TRACE

Josh Over a year ago

But this logs only few bind values. How can I log values from the criteria-API? If I use Specifications, I get no output for bound parameters created with the CriteriaBuilder.

payne Over a year ago

What about people that use EclipseLink instead of Hibernate ?

Dónal Over a year ago

@payne post a separate question

user2076909 Over a year ago

In my project using spring-boot-starter-data-jpa:3.1.4 and hibernate 6.2.9, the "logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE" did not work to show the values. I had to change it to "logging.level.org.hibernate.orm.jdbc.bind=TRACE"

|

Peter Mortensen · Accepted Answer · 2022-05-31 14:37:56Z

317

This works for standard output too:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

To log values:

logging.level.org.hibernate.type=trace

Just add this to application.properties.

edited May 31, 2022 at 14:37

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered May 26, 2016 at 15:20

v.ladynev

20.1k9 gold badges50 silver badges70 bronze badges

14 Comments

elysch Over a year ago

If you want to log values too: spring.jpa.properties.hibernate.type=trace

Muhammad Hewedy Over a year ago

This doesn't write to log file, this writes to STDOUT

Adeynack Over a year ago

I still see only ? instead of the parameters. Was that solution supposed to show them to me?

gstackoverflow Over a year ago

spring.jpa.properties.hibernate.type=trace doesn't affect my log file ;(

Matze Over a year ago

We can even have syntax highlighting via ANSI escape codes: spring.jpa.properties.hibernate.highlight_sql=true

|

Peter Mortensen · Accepted Answer · 2022-05-31 14:25:48Z

161

This works for me (YAML):

spring:
  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: true
logging:
  level:
    org:
      hibernate:
        type: trace

edited May 31, 2022 at 14:25

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 11, 2017 at 15:37

Michel

9,48013 gold badges48 silver badges60 bronze badges

2 Comments

ka3ak Over a year ago

I always thought that show_sql: true belongs to jpa

Damian Szewc Over a year ago

You are right, it should be in jpa section

Peter Mortensen · Accepted Answer · 2022-06-18 22:40:29Z

157

Settings to avoid

You should not use this setting:

spring.jpa.show-sql=true

The problem with show-sql is that the SQL statements are printed in the console, so there is no way to filter them, as you'd normally do with a Logging framework.

Using Hibernate logging

In your log configuration file, if you add the following logger:

<logger name="org.hibernate.SQL" level="debug"/>

Then, Hibernate will print the SQL statements when the JDBC PreparedStatement is created. That's why the statement will be logged using parameter placeholders:

INSERT INTO post (title, version, id) VALUES (?, ?, ?)

If you want to log the bind parameter values, just add the following logger as well:

<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="trace"/>

Once you set the BasicBinder logger, you will see that the bind parameter values are logged as well:

DEBUG [main]: o.h.SQL - insert into post (title, version, id) values (?, ?, ?)
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [1] as [VARCHAR] - [High-Performance Java Persistence, part 1]
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [2] as [INTEGER] - [0]
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [3] as [BIGINT] - [1]

Using datasource-proxy

The datasource-proxy OSS framework allows you to proxy the actual JDBC DataSource, as illustrated by the following diagram:

You can define the dataSource bean that will be used by Hibernate as follows:

@Bean
public DataSource dataSource(DataSource actualDataSource) {
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    loggingListener.setQueryLogEntryCreator(new InlineQueryLogEntryCreator());
    return ProxyDataSourceBuilder
        .create(actualDataSource)
        .name(DATA_SOURCE_PROXY_NAME)
        .listener(loggingListener)
        .build();
}

Notice that the actualDataSource must be the DataSource defined by the connection pool you are using in your application.

Next, you need to set the net.ttddyy.dsproxy.listener log level to debug in your logging framework configuration file. For instance, if you're using Logback, you can add the following logger:

<logger name="net.ttddyy.dsproxy.listener" level="debug"/>

Once you enable datasource-proxy, the SQL statement are going to be logged as follows:

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)]

edited Jun 18, 2022 at 22:40

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 18, 2020 at 20:33

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

3 Comments

Christos Karapapas Over a year ago

What exactly does to enable the datasource-proxy means here? Is it another configuration? Is there something extra that needs configuration apart from creating the bean with the proper datasource? Do we also need to configure something in the log4j.properties at this point?

Vlad Mihalcea Over a year ago

I updated the answer to answer your questions.

Алексеев станислав Over a year ago

It do not work,how does Hibernate know to use this bean?

Peter Mortensen · Accepted Answer · 2022-05-31 14:28:17Z

29

Please use:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE
spring.jpa.show-sql=true

edited May 31, 2022 at 14:28

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Dec 21, 2017 at 8:50

rahulnikhare

1,5461 gold badge19 silver badges29 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-05-31 14:27:14Z

27

If you have a logback-spring.xml file or something like that, add the following code to it:

<logger name="org.hibernate.SQL" level="trace" additivity="false">
    <appender-ref ref="file" />
</logger>

That works for me.

To get bind variables as well:

<logger name="org.hibernate.type.descriptor.sql" level="trace">
    <appender-ref ref="file" />
</logger>

edited May 31, 2022 at 14:27

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jul 17, 2017 at 6:41

Edye Chan

4145 silver badges9 bronze badges

2 Comments

Ortomala Lokni Over a year ago

With Spring Boot you have to use <appender-ref ref="FILE" />

Raja Anbazhagan Over a year ago

appender ref is the name of the appender that you have defined in the logback xml. It is only a variable

storm_buster · Accepted Answer · 2022-11-11 14:05:30Z

20

For hibernate 6: it is:

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

answered Nov 11, 2022 at 14:05

storm_buster

7,56818 gold badges56 silver badges76 bronze badges

1 Comment

Nathan Karasch Over a year ago

I verified this for Spring Boot 3.0.0, which uses hibernate 6.1.5. Though, for me, I used logging.level.org.hibernate.SQL=DEBUG and logging.level.org.hibernate.orm.jdbc.bind=TRACE.

Peter Mortensen · Accepted Answer · 2022-05-31 14:24:01Z

15

For the SQL Server driver (Microsoft SQL Server JDBC Driver), try using:

logging.level.com.microsoft.sqlserver.jdbc=debug

in your application.properties file.

My personal preference is to set:

logging.level.com.microsoft.sqlserver.jdbc=info
logging.level.com.microsoft.sqlserver.jdbc.internals=debug

You can look at these links for reference:

edited May 31, 2022 at 14:24

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 21, 2016 at 19:29

Javier Z.

7924 silver badges10 bronze badges

Comments

Robert.Li · Accepted Answer · 2019-03-12 21:39:54Z

13

Translated accepted answer to YAML works for me

logging:
  level:
    org:
      hibernate:
        SQL:
          TRACE
        type:
          descriptor:
            sql:
              BasicBinder:
                TRACE

answered Mar 12, 2019 at 21:39

Robert.Li

1691 silver badge7 bronze badges

1 Comment

MarcinJ Over a year ago

You can also use flat properties in YAML if you don't want to nest for single use props, like: logging.level.org.hibernate.SQL: TRACE logging.level.org.hibernate.type.descriptor.sql.BasicBinder: TRACE

Peter Mortensen · Accepted Answer · 2022-05-31 14:27:44Z

11

According to documentation it is:

spring.jpa.show-sql=true # Enable logging of SQL statements.

edited May 31, 2022 at 14:27

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Nov 21, 2017 at 20:54

Max Farsikov

2,8191 gold badge23 silver badges29 bronze badges

1 Comment

Kalpesh Soni Over a year ago

I have reverse problem, setting this to false, and org.hibernate to level ERROR and its still printing drop/create/insert/select

Peter Mortensen · Accepted Answer · 2022-06-18 21:40:35Z

11

Log in to standard output

Add to application.properties

### To enable
spring.jpa.show-sql=true

### To make the printing SQL beautify
spring.jpa.properties.hibernate.format_sql=true

This is the simplest way to print the SQL queries, though it doesn't log the parameters of prepared statements.

And it’s not recommended since it’s not such as optimized logging framework.

Using a logging framework

Add to application.properties

### Logs the SQL queries
logging.level.org.hibernate.SQL=DEBUG
### Logs the prepared statement parameters
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

### To make the printing SQL beautify
spring.jpa.properties.hibernate.format_sql=true

By specifying the above properties, log entries will be sent to the configured log appender, such as log-back or Log4j.

edited Jun 18, 2022 at 21:40

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Oct 23, 2019 at 6:28

Saveendra Ekanayake

3,3236 gold badges39 silver badges45 bronze badges

Comments

Mohit Kaushal · Accepted Answer · 2022-06-29 07:27:16Z

10

We can log SQL statements using two approaches in Spring boot: 1: using logger 2: standard approach

For logger You should add this line to application.properties file:

logging.level.org.hibernate.SQL=DEBUG

Standard Approach You should add these lines in application.properties file:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

answered Jun 29, 2022 at 7:27

Mohit Kaushal

1611 silver badge5 bronze badges

1 Comment

Mubasher Over a year ago

This standard approach only works for console log.

Muhammed_G · Accepted Answer · 2023-03-23 14:27:50Z

10

I was able to solve that problem by adding these variables lines in application.properties file:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE
spring.jpa.properties.hibernate.format_sql=true

That made me able to see debugging SQL queries (in formatted shape) and the passed variables (parameters) values.

And my log output was like this:

17:21:45.965 [http-nio-7001-exec-5] DEBUG org.hibernate.SQL - 
    select
        a1_0.action_id,
        a1_0.action_nr,
        a1_0.app_version
    from
        action a1_0 
    where
        a1_0.guid=? limit ?
17:21:45.965 [http-nio-7001-exec-5] TRACE org.hibernate.orm.jdbc.bind - binding parameter [1] as [VARCHAR] - [60154cd5-3f51-4c45-9c65-2af724c5c693]
17:21:45.965 [http-nio-7001-exec-5] TRACE org.hibernate.orm.jdbc.bind - binding parameter [2] as [INTEGER] - [1]

answered Mar 23, 2023 at 14:27

Muhammed_G

5145 silver badges11 bronze badges

1 Comment

dashtinejad Over a year ago

I can confirm it works with SpringBoot 3!

Peter Mortensen · Accepted Answer · 2022-05-31 14:30:49Z

8

If you want to view the actual parameters used to query you can use

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql=TRACE

Then notice that actual parameter value is shown as binding parameter...:

    2018-08-07 14:14:36.079 DEBUG 44804 --- [           main] org.hibernate.SQL                        : select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.joining_date as joining_3_0_, employee0_.name as name4_0_ from employee employee0_ where employee0_.joining_date=?
     2018-08-07 14:14:36.079 TRACE 44804 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [TIMESTAMP] - [Tue Aug 07 00:00:00 SGT 2018]

edited May 31, 2022 at 14:30

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Aug 7, 2018 at 6:17

Udara S.S Liyanage

6,51310 gold badges35 silver badges36 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-05-31 14:47:03Z

7

We can use any one of these in the application.properties file:

spring.jpa.show-sql=true

Example:

//Hibernate: select country0_.id as id1_0_, country0_.name as name2_0_ from country country0_

or

logging.level.org.hibernate.SQL=debug

Example:

2018-11-23 12:28:02.990 DEBUG 12972 --- [nio-8086-exec-2] org.hibernate.SQL   : select country0_.id as id1_0_, country0_.name as name2_0_ from country country0_

edited May 31, 2022 at 14:47

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Nov 23, 2018 at 6:58

Lova Chittumuri

3,4011 gold badge38 silver badges39 bronze badges

Comments

cigien · Accepted Answer · 2021-12-09 06:16:09Z

4

You can simply add the below lines in application.properties for stdout SQL queries:

spring.jpa.properties.hibernate.show_sql=true

edited Dec 9, 2021 at 6:16

cigien

61.2k11 gold badges86 silver badges124 bronze badges

answered Dec 9, 2021 at 5:29

Mohit Kaushal

1611 silver badge5 bronze badges

2 Comments

Peter Mortensen Over a year ago

What are "stdout SQL queries"? "standard out SQL queries"? Can you elaborate? Or do you mean "standard SQL queries"?

Mohit Kaushal Over a year ago

By stdout queries, I mean to say that the queries which are further generated by JPA that are going to run over our DB can be logged over console or log files.

Peter Mortensen · Accepted Answer · 2022-06-18 22:48:31Z

4

In my YAML file:

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql: TRACE

Spring Boot version: 2.3.5.RELEASE

edited Jun 18, 2022 at 22:48

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Aug 31, 2021 at 5:40

shenyu1997

6507 silver badges5 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-06-18 22:53:58Z

4

Use this code in the file application.properties:

# Enable logging for configuration troubleshooting
logging.level.org.hibernate.SQL=DEBUG
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

edited Jun 18, 2022 at 22:53

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Aug 3, 2020 at 12:57

BRAIEK AYEMN

871 silver badge6 bronze badges

Comments

SJX · Accepted Answer · 2019-09-16 14:00:49Z

Putting spring.jpa.properties.hibernate.show_sql=true in application.properties didn't help always.

You can try to add properties.put("hibernate.show_sql", "true"); to the properties of the database configuration.

public class DbConfig {

    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    entityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("dataSource") DataSource dataSource
    ) {
        Map<String, Object> properties = new HashMap();
        properties.put("hibernate.hbm2ddl.auto", "validate");
        properties.put("hibernate.show_sql", "true");

        return builder
                .dataSource(dataSource)
                .packages("com.test.dbsource.domain")
                .persistenceUnit("dbsource").properties(properties)
                .build();
    }

Peter Mortensen · Accepted Answer · 2022-06-18 22:44:46Z

3

You just need to set spring.jpa.show-sql=true in application.properties.

For example, you may refer to ConfigServerRepo/application.yaml.

edited Jun 18, 2022 at 22:44

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jun 15, 2020 at 12:17

noob

3992 silver badges8 bronze badges

Comments

Shorn · Accepted Answer · 2019-09-09 02:39:15Z

2

If you're having trouble with this setting and it seems to work sometimes and not other times - consider if the times where it doesn't work are during unit tests.

Many people declare custom test-time properties via the @TestPropertySources annotation declared somewhere in your test inheritance hierarchy. This will override whatever you put in your application.properties or other production properties settings so those values you're setting are effectively being ignored at test-time.

answered Sep 9, 2019 at 2:39

Shorn

22.1k18 gold badges110 silver badges209 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-06-18 22:56:39Z

2

The basic way is to add the following lines in your application.properties. This will enable Spring Boot to log all your SQL queries that get executed:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

The second line is used to beautify the SQL statements.

If you want to use loggers, you can use the following lines:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

The second line is used to print all the parameters that get bound with your queries.

edited Jun 18, 2022 at 22:56

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Oct 8, 2021 at 7:26

Souradeep Banerjee

191 bronze badge

Comments

F_V · Accepted Answer · 2024-01-17 17:16:01Z

2

logging.level.org.hibernate.type.descriptor.sql.BasicBinder

has been renamed to:

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

answered Jan 17, 2024 at 17:16

F_V

354 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-06-18 22:51:06Z

0

Add these in the properties. Quoting Hibernate Show SQL:

# Show SQL statement
logging.level.org.hibernate.SQL=debug

# Show SQL values
logging.level.org.hibernate.type.descriptor.sql=trace

edited Jun 18, 2022 at 22:51

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Nov 18, 2021 at 18:13

AnkitSingh2099

12 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2022-06-18 22:53:33Z

0

If you are using JdbcTemplate, add the below in application.properties file to log SQL and parameter values.

logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

edited Jun 18, 2022 at 22:53

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 28, 2022 at 18:15

Shafiul

1,53216 silver badges21 bronze badges

Collectives™ on Stack Overflow

How can I log SQL statements in Spring Boot?

25 Answers 25

6 Comments

14 Comments

2 Comments

Settings to avoid

Using Hibernate logging

Using datasource-proxy

3 Comments

Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

Log in to standard output

Using a logging framework

Comments

1 Comment

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

25 Answers 25

6 Comments

14 Comments

2 Comments

Settings to avoid

Using Hibernate logging

Using datasource-proxy

3 Comments

Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

Log in to standard output

Using a logging framework

Comments

1 Comment

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related