CODEWITHSUNDEEP
mysqlspring-boot
Sanjay Amin
Sanjay Amin

Reputation: 411

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

I've Spring Boot Application that uses Spring data JPA and MySQL. With setting SQL properties in application.yml file, I can see the sql but I need to print the values with SQL. How can I achieve that?

Upvotes: 1

Views: 13142

Answers (4)

jon
jon

Reputation: 11

For spring-boot version 3.1.2 works following properties configuration

jpa:
  properties:
    hibernate:
      show_sql: true
      format_sql: true
logging:
  level:
    org:
      hibernate:
        orm:
          jdbc:
            bind: trace|

Upvotes: 0

HungNM2
HungNM2

Reputation: 3425

Spring application.yml:

spring:    
  jpa:
    show-sql: true                # show SQL with System.out.println() 
    properties:
      show_sql: true              # show SQL with Slf4j 
      hibernate.format_sql: true  # show SQL pretty

logging:
  level:
    root: INFO
    org.hibernate: DEBUG        # DEBUG or TRACE is ok

Upvotes: 4

TechGeek
TechGeek

Reputation: 508

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

Upvotes: 7

Sanjay Amin
Sanjay Amin

Reputation: 411

You can follow the steps given to achieve this:

  1. Add following to pom.xml

     <dependency>
     <groupId>com.github.gavlyukovskiy</groupId>
     <artifactId>p6spy-spring-boot-starter</artifactId>
     <version>1.6.3</version>
 </dependency>

  2. If you are using Eclipse/IntelliJ, Add Program arguments in Run -> Edit Configuration

 -Dspy.properties=/PathToP6SpyDir/spy.properties

  3. Add following to spy.properties file:

driverlist=com.mysql.jdbc.Driver
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=time %(executionTime)|con %(connectionId)|%(sqlSingleLine)

Upvotes: 0

Related Questions