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?
Add a comment
|
4 Answers
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
Comments
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
Comments
You can follow the steps given to achieve this:
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)
2 Comments
Ashish
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?
Sanjay Amin
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.
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|
