How to log SQL statements in Spring Boot

Question

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 gt 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

User · Answer

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

User · Answer

if you hava a logback-spring xml or something like that  add the following code to it   lt logger name  org hibernate SQL  level  trace  additivity  false  gt       lt appender-ref ref  file    gt   lt  logger gt    works for me   To get bind variables as well    lt logger name  org hibernate type descriptor sql  level  trace  gt       lt appender-ref ref  file    gt   lt  logger gt

User · Answer

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   lt logger name  quot org hibernate SQL quot  level  quot debug quot   gt   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   lt logger name  quot org hibernate type descriptor sql BasicBinder quot  level  quot trace quot   gt   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   lt logger name  quot net ttddyy dsproxy listener quot  level  quot debug quot   gt   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   quot insert into post  title  version  id  values           quot    Params   Post no  0  0  0    Post no  1  0  1    Post no  2  0  2

User · Answer

try using this in your properties file   logging level org hibernate SQL DEBUG logging level org hibernate type descriptor sql BasicBinder TRACE

User · Answer

This works for me  YAML    spring    jpa      properties        hibernate          show sql  true         format sql  true logging    level      org        hibernate          type  trace

User · Answer

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

User · Answer

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

User · Answer

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    https   msdn microsoft com en-us library ms378517 v sql 110  aspx  https   docs spring io spring-boot docs current reference html howto-logging html

User · Answer

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 lt String  Object gt  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

User · Answer

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

User · Answer

According to documentation it is   spring jpa show-sql true   Enable logging of SQL statements

User · Answer

Translated accepted answer to YAML works for me  logging    level      org        hibernate          SQL            TRACE         type            descriptor              sql                BasicBinder                  TRACE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Please use   logging level org hibernate SQL DEBUG logging level org hibernate type TRACE spring jpa show-sql true

[java] How to log SQL statements in Spring Boot?

Examples related to java

Examples related to spring

Examples related to hibernate

Examples related to spring-boot

Examples related to logging