Hibernate通过p6spy 打印完整SQL日志

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方便调试，故不做深入研究