1

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?

Sanjay Amin
  • 341
  • 1
  • 4
  • 13

3 Answers3

5

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
TechGeek
  • 480
  • 1
  • 8
  • 22
2

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
HungNM2
  • 3,101
  • 1
  • 30
  • 21
0

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)
Sanjay Amin
  • 341
  • 1
  • 4
  • 13
  • What is it the answer by @TechGeek did not resolve. Is this solution specific to your case. Most people wont be allowed to use third party libraries as in this solution. Can you highlight if the answer by TechGeek did not help you and why? – KnockingHeads Apr 30 '21 at 06:42
  • Ashish, Thanks for bringing this. The one I suggested helped me to find real sql like statements while using JPA . Answer from @TechGeek also works with giving extracted value for each field. The one I suggested, I would recommend only for local or dev env. and not for prod env. – Sanjay Amin Apr 30 '21 at 18:09