How to log SQL statements in Spring Boot?
JavaSpringHibernateSpring BootLoggingJava Problem Overview
I want to log SQL statements in 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 a file app.log. The file contains only basic logs from spring.
What should I do to see sql statements in the log file?
Java Solutions
Solution 1 - Java
try using this in your properties file:
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
Solution 2 - Java
This works for stdout 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
.
Solution 3 - Java
This works for me (YAML):
spring:
jpa:
properties:
hibernate:
show_sql: true
format_sql: true
logging:
level:
org:
hibernate:
type: trace
Solution 4 - Java
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][2] 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)]
Solution 5 - Java
Please use:
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE
spring.jpa.show-sql=true
Solution 6 - Java
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>
Solution 7 - Java
For the MS-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:
Solution 8 - Java
Translated accepted answer to YAML works for me
logging:
level:
org:
hibernate:
SQL:
TRACE
type:
descriptor:
sql:
BasicBinder:
TRACE
Solution 9 - Java
According to documentation it is:
spring.jpa.show-sql=true # Enable logging of SQL statements.
Solution 10 - Java
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 the simplest way to print the SQL queries though it doesn't log the parameters of prepared statements. And its is not recommended since its not such as optimized logging framework.
Using 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 above properties, logs entries will be sent to the configured log appender such as log-back or log4j.
Solution 11 - Java
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]
Solution 12 - Java
We can use any one of these in 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_
Solution 13 - Java
use this code in the file application.properties:
#Enable logging for config troubeshooting
logging.level.org.hibernate.SQL=DEBUG
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
Solution 14 - Java
You can simply add the below lines in application.properties for stdout SQL queries:
spring.jpa.properties.hibernate.show_sql=true
Solution 15 - Java
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.
Solution 16 - Java
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();
}
Solution 17 - Java
You just need to set
spring.jpa.show-sql=true
in application.properties
for example you may reffer this https://github.com/007anwar/ConfigServerRepo/blob/master/application.yaml
Solution 18 - Java
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 2nd 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 2nd line is used to print all the parameters that get bound with your queries. Hope this works for you. Please let me know if you have any further queries!
Solution 19 - Java
In my yaml:
logging:
level:
org.hibernate.SQL: DEBUG
org.hibernate.type.descriptor.sql: TRACE
Spring boot version: 2.3.5.RELEASE
Solution 20 - Java
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
Solution 21 - Java
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