spring.jpa.show-sql=true
Hibernate 通过以上配置打印Hibernate的sql日志无法完整的打印参数。
现可p6spy类似代理打印完整sql,方便调试开发。
p6spy就是一款针对数据库访问操作的动态监控框架,他可以和数据库无缝截取和操纵,而不必对现有应该用程序的代码做任何修改。
1.引入pom包
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.1</version>
</dependency>
2.添加配置
-spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
当然也可以通过JVM参数临时覆盖调试
-Dspring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
3.修改jdbc连接
jdbc:p6spy:mysql://xxx:3306/jj?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
jdbc:p6spy:xxx 兼容各种驱动,替换当前使用的数据库即可
4.添加p6spy配置spy.properties
在resource下创建文件spy.properties
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|%(sqlSingleLine)
按照以上配置即可在控制台打印
测试效果:完整打印sql参数了
2024-03-08 11:00:46.550 INFO 28024 --- [viceWithCache-1] p6spy : 1709866846550|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:46.566 INFO 28024 --- [viceWithCache-1] p6spy : 1709866846566|0|statement|select appnamespa0_."Id" as id1_2_, appnamespa0_."DataChange_CreatedBy" as datachan2_2_, appnamespa0_."DataChange_CreatedTime" as datachan3_2_, appnamespa0_."DataChange_LastModifiedBy" as datachan4_2_, appnamespa0_."DataChange_LastTime" as datachan5_2_, appnamespa0_."DeletedAt" as deleteda6_2_, appnamespa0_."IsDeleted" as isdelete7_2_, appnamespa0_."AppId" as appid8_2_, appnamespa0_."Comment" as comment9_2_, appnamespa0_."Format" as format10_2_, appnamespa0_."IsPublic" as ispubli11_2_, appnamespa0_."Name" as name12_2_ from "AppNamespace" appnamespa0_ where ( appnamespa0_."IsDeleted" = false) and appnamespa0_."Id">45 order by appnamespa0_."Id" asc limit 500
2024-03-08 11:00:46.872 INFO 28024 --- [essageScanner-1] p6spy : 1709866846872|0|commit|
2024-03-08 11:00:46.873 INFO 28024 --- [essageScanner-1] p6spy : 1709866846873|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:46.873 INFO 28024 --- [viceWithCache-1] p6spy : 1709866846873|0|statement|select accesskey0_."Id" as id1_0_, accesskey0_."DataChange_CreatedBy" as datachan2_0_, accesskey0_."DataChange_CreatedTime" as datachan3_0_, accesskey0_."DataChange_LastModifiedBy" as datachan4_0_, accesskey0_."DataChange_LastTime" as datachan5_0_, accesskey0_."DeletedAt" as deleteda6_0_, accesskey0_."IsDeleted" as isdelete7_0_, accesskey0_."AppId" as appid8_0_, accesskey0_."IsEnabled" as isenable9_0_, accesskey0_."Secret" as secret10_0_ from "AccessKey" accesskey0_ where ( accesskey0_."IsDeleted" = false) and accesskey0_."DataChange_LastTime">'1970-01-01T08:00:00.000+0800' order by accesskey0_."DataChange_LastTime" asc limit 500
2024-03-08 11:00:47.567 INFO 28024 --- [viceWithCache-1] p6spy : 1709866847567|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:47.582 INFO 28024 --- [viceWithCache-1] p6spy : 1709866847582|0|statement|select appnamespa0_."Id" as id1_2_, appnamespa0_."DataChange_CreatedBy" as datachan2_2_, appnamespa0_."DataChange_CreatedTime" as datachan3_2_, appnamespa0_."DataChange_LastModifiedBy" as datachan4_2_, appnamespa0_."DataChange_LastTime" as datachan5_2_, appnamespa0_."DeletedAt" as deleteda6_2_, appnamespa0_."IsDeleted" as isdelete7_2_, appnamespa0_."AppId" as appid8_2_, appnamespa0_."Comment" as comment9_2_, appnamespa0_."Format" as format10_2_, appnamespa0_."IsPublic" as ispubli11_2_, appnamespa0_."Name" as name12_2_ from "AppNamespace" appnamespa0_ where ( appnamespa0_."IsDeleted" = false) and appnamespa0_."Id">45 order by appnamespa0_."Id" asc limit 500
2024-03-08 11:00:47.874 INFO 28024 --- [essageScanner-1] p6spy : 1709866847874|0|commit|
2024-03-08 11:00:47.875 INFO 28024 --- [essageScanner-1] p6spy : 1709866847875|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:47.890 INFO 28024 --- [viceWithCache-1] p6spy : 1709866847890|0|statement|select accesskey0_."Id" as id1_0_, accesskey0_."DataChange_CreatedBy" as datachan2_0_, accesskey0_."DataChange_CreatedTime" as datachan3_0_, accesskey0_."DataChange_LastModifiedBy" as datachan4_0_, accesskey0_."DataChange_LastTime" as datachan5_0_, accesskey0_."DeletedAt" as deleteda6_0_, accesskey0_."IsDeleted" as isdelete7_0_, accesskey0_."AppId" as appid8_0_, accesskey0_."IsEnabled" as isenable9_0_, accesskey0_."Secret" as secret10_0_ from "AccessKey" accesskey0_ where ( accesskey0_."IsDeleted" = false) and accesskey0_."DataChange_LastTime">'1970-01-01T08:00:00.000+0800' order by accesskey0_."DataChange_LastTime" asc limit 500
2024-03-08 11:00:48.585 INFO 28024 --- [viceWithCache-1] p6spy : 1709866848585|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:48.599 INFO 28024 --- [viceWithCache-1] p6spy : 1709866848599|0|statement|select appnamespa0_."Id" as id1_2_, appnamespa0_."DataChange_CreatedBy" as datachan2_2_, appnamespa0_."DataChange_CreatedTime" as datachan3_2_, appnamespa0_."DataChange_LastModifiedBy" as datachan4_2_, appnamespa0_."DataChange_LastTime" as datachan5_2_, appnamespa0_."DeletedAt" as deleteda6_2_, appnamespa0_."IsDeleted" as isdelete7_2_, appnamespa0_."AppId" as appid8_2_, appnamespa0_."Comment" as comment9_2_, appnamespa0_."Format" as format10_2_, appnamespa0_."IsPublic" as ispubli11_2_, appnamespa0_."Name" as name12_2_ from "AppNamespace" appnamespa0_ where ( appnamespa0_."IsDeleted" = false) and appnamespa0_."Id">45 order by appnamespa0_."Id" asc limit 500
2024-03-08 11:00:48.890 INFO 28024 --- [essageScanner-1] p6spy : 1709866848890|0|commit|
2024-03-08 11:00:48.892 INFO 28024 --- [essageScanner-1] p6spy : 1709866848892|0|statement|select releasemes0_."Id" as id1_15_, releasemes0_."DataChange_LastTime" as datachan2_15_, releasemes0_."Message" as message3_15_ from "ReleaseMessage" releasemes0_ where releasemes0_."Id">0 order by releasemes0_."Id" asc limit 500
2024-03-08 11:00:48.907 INFO 28024 --- [viceWithCache-1] p6spy : 1709866848907|1|statement|select accesskey0_."Id" as id1_0_, accesskey0_."DataChange_CreatedBy" as datachan2_0_, accesskey0_."DataChange_CreatedTime" as datachan3_0_, accesskey0_."DataChange_LastModifiedBy" as datachan4_0_, accesskey0_."DataChange_LastTime" as datachan5_0_, accesskey0_."DeletedAt" as deleteda6_0_, accesskey0_."IsDeleted" as isdelete7_0_, accesskey0_."AppId" as appid8_0_, accesskey0_."IsEnabled" as isenable9_0_, accesskey0_."Secret" as secret10_0_ from "AccessKey" accesskey0_ where ( accesskey0_."IsDeleted" = false) and accesskey0_."DataChange_LastTime">'1970-01-01T08:00:00.000+0800' order by accesskey0_."DataChange_LastTime" asc limit 500
当前p6spy也可以监控慢sql等,当前需求仅是打印sql方便调试,故不做深入研究