How to Log SQL Statements in Spring Boot

How can I log SQL statements in Spring Boot?

Try using this in your properties file:

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

Spring enable SQL logs in application.yml

We can use any one of these:

spring:
  jpa:
    show_sql: true

or

logging:
  level:
    org:
    hibernate:
        sql=debug
        type:
        descriptor:
            sql:trace

How to log SQL statements and parameters with Spring Data JDBC

Spring Data JDBC uses a JdbcTemplate to execute all SQL statements.
So you really need to configure logging for that.

The statements itself are logged by the template itself with DEBUG level.
To see these you have to set the log level of org.springframework.jdbc.core.JdbcTemplate to DEBUG.

The arguments for the bind variables are logged by org.springframework.jdbc.core.StatementCreatorUtils on TRACE level.

How you configure those depends on your project setup.
You can use the configuration options of Log4J, Logback or whatever logging implementation you are using.
Or if you happen to use Spring Boot the following rows in the application.properties should do the trick.

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

Or simply

logging.level.org.springframework.jdbc.core = TRACE

If you don't mind possibly getting some extra output.

How to print sql statements with values in spring boot JPA application

You can add these two lines in your application.properties file if you are using JPA and Hibernate. This should enables to write the query on console.

spring.jpa.properties.hibernate.show_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace

Activate SQL-Logging via Spring Boot and Hibernate via Environment variables (casing problem)

Use name : logging.level.org.hibernate.SQL

How can I log Hibernate sql statements in a log file instead of console

Add following lines to the configuration please(How to log SQL statements in Spring Boot?)

#show sql statement
logging.level.org.hibernate.SQL=debug
#show sql values
logging.level.org.hibernate.type.descriptor.sql=trace

spring.jpa.show-sql causees that statements are logged into stdout Hibernate show sql is equal to Stdout

If you want a different file for Hibernate logs, you could add logback.xml. and define a file appender for org.hibernate logger

<configuration>
     <appender name="fileAppender"
                  class="ch.qos.logback.core.rolling.RollingFileAppender">
            <file>${DEV_HOME}/[yourlognamefile].log</file>
            <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
                <Pattern>
                    %d{yyyy-MM-dd HH:mm:ss} - %msg%n
                </Pattern>
            </encoder>
    
            <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
                <!-- rollover daily -->
                <fileNamePattern>${DEV_HOME}/archived/[yourlognamefile].%d{yyyy-MM-dd}.%i.log
                </fileNamePattern>
                <timeBasedFileNamingAndTriggeringPolicy
                        class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                    <maxFileSize>10MB</maxFileSize>
                </timeBasedFileNamingAndTriggeringPolicy>
            </rollingPolicy>
    
        </appender>
    
    <logger name="org.hibernate" level="DEBUG" />
         <appender-ref ref="fileAppender"/>
    </logger>
</configuration>

For more information, you could have a look at:
https://dzone.com/articles/configuring-logback-with-spring-boot

How to log SQL queries, their parameters and results with Log4jdbc in Spring Boot projects?

JDBC logging

With log4jdbc-spring-boot-starter we can easily log all JDBC statements, their parameters and results in Spring Boot/Spring Data JPA projects.

For example, when we perform some JPQL query in our application:

select u from User u where u.name = 'john'

then we see the following SQL query with its parameter in the application log:

select ... from users users0_ where users0_.name='john'

And its result in the table form:

|---|---------|
|id |name     |
|---|---------|
|1  |john     |
|---|---------|

To use this starter we have to add its dependency to our project:

<dependency>
    <groupId>com.integralblue</groupId>
    <artifactId>log4jdbc-spring-boot-starter</artifactId>
    <version>1.0.2</version>
</dependency>

and add these parameters to application.properties:

logging.level.jdbc.resultsettable=info
logging.level.jdbc.sqltiming=info
logging.level.jdbc.sqlonly=fatal
logging.level.jdbc.audit=fatal
logging.level.jdbc.resultset=fatal
logging.level.jdbc.connection=fatal

Additionally, we can add these log4jdbc parameters to get the output in one line:

log4jdbc.dump.sql.addsemicolon=true
log4jdbc.dump.sql.maxlinelength=0
log4jdbc.trim.sql.extrablanklines=false

---

Related Topics