我们在使用orm框架时,一般默认提供的日志打印方式打印出来的sql中间的占位符并没有被替换掉:
例如mybatis:
使用日志打印
logging.level.com.wk.entity.dao=debug
2018-07-14 14:38:39.497 DEBUG 4088 --- [nio-8082-exec-1] c.k.e.e.d.A.selectByPrimaryKey : ==> Preparing: select id, score from a_innodb_test where id = ?
2018-07-14 14:38:39.520 DEBUG 4088 --- [nio-8082-exec-1] c.k.e.e.d.A.selectByPrimaryKey : ==> Parameters: 1(Integer)
打印出来的sql带有占位符,并不利于调试。
P6Spy 是针对数据库访问操作的动态监测框架(为开源项目,项目首 页:www.p6spy.com)它使得数据库数据可无缝截取和操纵,而不必对现有应用程序的代码作任何修改。P6Spy 分发包包括P6Log,它是一 个可记录任何 Java 应用程序的所有JDBC事务的应用程序。其配置完成使用时,可以进行数据访问性能的监测。
使用p6spy我们可以直接查看到数据库执行的sql而不是预编译带?的SQL。
首先我们导入p6spy的依赖:
// https://mvnrepository.com/artifact/p6spy/p6spy
compile group: 'p6spy', name: 'p6spy', version: '3.7.0'
然后修改数据库连接驱动和url:
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/test
数据库驱动将com.mysql.jdbc.Driver替换为com.p6spy.engine.spy.P6SpyDriver,然后在url中的按上面的格式加入p6spy即可。
再在资源目录下加入spy.properties配置文件,内容如下:
###
# #%L
# P6Spy
# %%
# Copyright (C) 2013 P6Spy
# %%
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# #L%
###
#################################################################
# P6Spy Options File #
# See documentation for detailed instructions #