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 https://www.baeldung.com/sql-logging-spring-boot — Ilya Serbis, Feb 04 '21 at 19:31

score 647 · Accepted Answer · edited May 31 '22 at 14:21

647

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 '22 at 14:21

Peter Mortensen

30,738
21
105
131

answered Jul 06 '15 at 15:39

Paul Woods

6,769
1
15
10

124

If you want to log values too: `logging.level.org.hibernate.type=TRACE` – elysch Jun 11 '16 at 00:48
5

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. – Josh Aug 10 '16 at 05:48
1

What about people that use `EclipseLink` instead of `Hibernate` ? – payne Oct 26 '21 at 21:12
@payne post a separate question – Dónal Dec 20 '22 at 10:53

score 303 · Answer 2 · edited May 31 '22 at 14:37

303

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 '22 at 14:37

Peter Mortensen

30,738
21
105
131

answered May 26 '16 at 15:20

v.ladynev

19,275
8
46
67

21

If you want to log values too: `spring.jpa.properties.hibernate.type=trace` – elysch Jun 11 '16 at 00:48
2

This doesn't write to log file, this writes to STDOUT – Muhammad Hewedy Jun 29 '16 at 08:34
8

I still see only `?` instead of the parameters. Was that solution supposed to show them to me? – Adeynack Feb 02 '18 at 08:54
@Adeynack Sorry I can't help you. I just added it according _elysch's_ words. – v.ladynev Feb 02 '18 at 09:59
@v.ladynev: But does it shows the parameters' value to you or does it shows question marks? – Adeynack Feb 05 '18 at 21:41
1

spring.jpa.properties.hibernate.type=trace doesn't affect my log file ;( – gstackoverflow Sep 04 '18 at 16:54
1

The "type=trace" is not a spring property, so it does not work. The solution given out below https://stackoverflow.com/a/41594913/5107365 is the right one for that. – Raj Feb 06 '20 at 01:00
@Raj Thank you very much. I have updated the answer. – v.ladynev Feb 06 '20 at 16:00
What about people that use `EclipseLink` instead of `Hibernate` ? – payne Oct 26 '21 at 21:12
1

This works for me, I'm using spring boot 2.5.9 – ErisoHV Dec 19 '22 at 20:37

score 155 · Answer 3 · edited May 31 '22 at 14:25

155

This works for me (YAML):

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

edited May 31 '22 at 14:25

Peter Mortensen

30,738
21
105
131

answered Jan 11 '17 at 15:37

Michel

9,220
13
44
59

2

I always thought that `show_sql: true` belongs to `jpa` – ka3ak Jan 13 '22 at 09:12

score 146 · Answer 4 · edited Jun 18 '22 at 22:40

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

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? — Christos Karapapas, Oct 02 '20 at 18:23

score 28 · Answer 5 · edited May 31 '22 at 14:28

28

Please use:

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

edited May 31 '22 at 14:28

Peter Mortensen

30,738
21
105
131

answered Dec 21 '17 at 08:50

rahulnikhare

1,362
1
18
25

5

`logging.level.org.hibernate.SQL=DEBUG` made it work for me and was missing for other answers. Thanks! – Vic Mar 27 '19 at 16:58

score 26 · Answer 6 · edited May 31 '22 at 14:27

26

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 '22 at 14:27

Peter Mortensen

30,738
21
105
131

answered Jul 17 '17 at 06:41

Edye Chan

404
5
7

1

With Spring Boot you have to use `` – Ortomala Lokni May 03 '18 at 15:28
appender ref is the name of the appender that you have defined in the logback xml. It is only a variable – Raja Anbazhagan Jul 19 '18 at 12:25

score 15 · Answer 7 · edited May 31 '22 at 14:24

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:

score 13 · Answer 8 · answered Mar 12 '19 at 21:39

13

Translated accepted answer to YAML works for me

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

answered Mar 12 '19 at 21:39

Robert.Li

169
1
7

6

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` – MarcinJ Jan 23 '20 at 15:53

score 11 · Answer 9 · edited May 31 '22 at 14:27

11

According to documentation it is:

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

edited May 31 '22 at 14:27

Peter Mortensen

30,738
21
105
131

answered Nov 21 '17 at 20:54

Max Farsikov

2,451
19
25

I have reverse problem, setting this to false, and org.hibernate to level ERROR and its still printing drop/create/insert/select – Kalpesh Soni Nov 01 '18 at 22:14

score 11 · Answer 10 · edited Jun 18 '22 at 21:40

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.

score 11 · Answer 11 · answered Nov 11 '22 at 14:05

11

For hibernate 6: it is:

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

answered Nov 11 '22 at 14:05

storm_buster

7,362
18
53
75

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`. – Nathan Karasch Jan 26 '23 at 16:03

score 9 · Answer 12 · answered Jun 29 '22 at 07:27

9

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 '22 at 07:27

Mohit Kaushal

151
1
4

This standard approach only works for console log. – Mubasher Apr 03 '23 at 09:32

score 8 · Answer 13 · edited May 31 '22 at 14:30

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]

score 7 · Answer 14 · edited May 31 '22 at 14:47

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_

score 4 · Answer 15 · edited Jun 18 '22 at 22:53

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 '22 at 22:53

Peter Mortensen

30,738
21
105
131

answered Aug 03 '20 at 12:57

BRAIEK AYEMN

89
1
6

score 4 · Answer 16 · edited Dec 09 '21 at 06:16

4

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

spring.jpa.properties.hibernate.show_sql=true

edited Dec 09 '21 at 06:16

cigien

57,834
11
73
112

answered Dec 09 '21 at 05:29

Mohit Kaushal

151
1
4

What are *"stdout SQL queries"*? *"standard out SQL queries"*? Can you elaborate? Or do you mean *"standard SQL queries"*? – Peter Mortensen Jun 18 '22 at 22:52
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. – Mohit Kaushal Jul 07 '22 at 05:11

score 3 · Answer 17 · edited Jun 18 '22 at 22:44

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 '22 at 22:44

Peter Mortensen

30,738
21
105
131

answered Jun 15 '20 at 12:17

noob

349
2
8

score 3 · Answer 18 · edited Jun 18 '22 at 22:48

3

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 '22 at 22:48

Peter Mortensen

30,738
21
105
131

answered Aug 31 '21 at 05:40

shenyu1997

602
7
5

score 3 · Answer 19 · answered Mar 23 '23 at 14:27

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]

score 2 · Answer 20 · answered Sep 09 '19 at 02:39

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.

score 2 · Answer 21 · answered Sep 16 '19 at 14:00

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();
    }

score 2 · Answer 22 · edited Jun 18 '22 at 22:56

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.

score 0 · Answer 23 · edited Jun 18 '22 at 22:51

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 '22 at 22:51

Peter Mortensen

30,738
21
105
131

answered Nov 18 '21 at 18:13

AnkitSingh2099

1
2

score 0 · Answer 24 · edited Jun 18 '22 at 22:53

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 '22 at 22:53

Peter Mortensen

30,738
21
105
131

answered Jan 28 '22 at 18:15

Shafiul

1,452
14
21

How can I log SQL statements in Spring Boot?

24 Answers24

Settings to avoid

Using Hibernate logging

Using datasource-proxy

Log in to standard output

Using a logging framework

Linked

Related