Spring Boot下基于JPA抓取SQL运行时的参数值
来源:互联网 发布:天球仪软件 编辑:程序博客网 时间:2024/06/11 03:51
引言: 在系统开发过程中,涉及到数据库操作都是SQL的天下,基于ORM的各类框架来简化开发;在JPA/Hibernate方案中,只提供了SQL的打印输出,但并未输入SQL的参数值是多少,这里将解决这个问题,告知具体如何来实现这个操作。
1. 技术栈的介绍
JDK 1.8, Spring Boot 1.4.0, Mysql 5.7, 默认Tomcat JDBC Connection Pool.
2. 依赖包
Spring Boot中的JPA依赖包:
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency>Mysql的驱动程序:
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency>log4jdbc的依赖包:
<dependency><groupId>com.googlecode.log4jdbc</groupId><artifactId>log4jdbc</artifactId><version>1.2</version></dependency>3. log4jdbc的介绍
log4jdbc使用SLF4J(Simple Logging Facade For Java)作为日志系统。特性:
- 支持JDBC3和JDBC4,其中JDBC3对应于JDK 1.4 or 1.5,JDBC4要求JDK 1.6或以上。
- 支持现有大部分JDBC驱动。
- 易于配置(在大部分情况下,只需要改变驱动类名并在jdbc url前加上”jdbc:log4“,设置好日志输出级别)。
- 能够自动把SQL变量值加到SQL输出日志中,改进易读性和方便调试。
- 能够快速标识出应用程序中执行比较慢的SQL语句。
- 能够生成SQL连接数信息帮助识别连接池/线程问题。
综合一句话,log4jdbc可以帮我们实现一个抓取SQL中实际使用值的功能。
项目地址【需要翻墙,翻墙问题自行解决】: https://code.google.com/p/log4jdbc/
4. Spring Boot示例
application.properties内容:
spring.datasource.url = jdbc:log4jdbc:mysql://localhost:3306/mealsystem?useUnicode=true&characterEncoding=utf-8spring.datasource.username = rootspring.datasource.password = 123456spring.datasource.driverClassName = net.sf.log4jdbc.DriverSpy这里需要注意的是mysql的原来的驱动程序Class Name:
#spring.datasource.driverClassName = com.mysql.jdbc.Driver将被 net.sf.log4jdbc.DriverSpy来替代,可以理解为DriverSpy做为mysql驱动的代理来捕捉SQL的信息。
数据库连接的URL需要进行修改为:
原来的: jdbc:mysql://localhost:3306/mealsystem
现在的: jdbc:log4jdbc:mysql://localhost:3306/mealsystem
初次之外,无需额外的变化需求,其余代码都一致即可获得运行中的SQL信息。
5. SQL捕捉示例
2016-08-30 20:38:37.362 INFO 13288 --- [ main] com.rain.wx.meal.dao.TestEntityTest : Started TestEntityTest in 19.879 seconds (JVM running for 21.522)2016-08-30 20:38:37.432 INFO 13288 --- [ main] jdbc.audit : 4. Connection.isClosed() returned false2016-08-30 20:38:37.436 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned true2016-08-30 20:38:37.457 INFO 13288 --- [ main] jdbc.audit : 4. Connection.setAutoCommit(false) returned Hibernate: insert into test (created_time, updated_time, version, name) values (?, ?, ?, ?)2016-08-30 20:38:37.495 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getCatalog() returned mealsystem2016-08-30 20:38:37.535 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.new PreparedStatement returned 2016-08-30 20:38:37.541 INFO 13288 --- [ main] jdbc.audit : 4. Connection.prepareStatement(insert into test (created_time, updated_time, version, name) values (?, ?, ?, ?), 1) returned net.sf.log4jdbc.PreparedStatementSpy@3e0704e22016-08-30 20:38:37.549 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getMaxFieldSize() returned 41943042016-08-30 20:38:37.550 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getMaxRows() returned 02016-08-30 20:38:37.550 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getQueryTimeout() returned 02016-08-30 20:38:37.551 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getFetchDirection() returned 10002016-08-30 20:38:37.551 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getFetchSize() returned 02016-08-30 20:38:37.554 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.setNull(1, 93) returned 2016-08-30 20:38:37.554 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.setNull(2, 93) returned 2016-08-30 20:38:37.556 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.setInt(3, 0) returned 2016-08-30 20:38:37.557 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.setString(4, "我的天空") returned 2016-08-30 20:38:37.560 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned false2016-08-30 20:38:37.561 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned false2016-08-30 20:38:37.561 INFO 13288 --- [ main] jdbc.sqlonly : insert into test (created_time, updated_time, version, name) values (NULL, NULL, 0, '我的天空') 2016-08-30 20:38:37.585 INFO 13288 --- [ main] jdbc.sqltiming : insert into test (created_time, updated_time, version, name) values (NULL, NULL, 0, '我的天空') {executed in 24 msec}2016-08-30 20:38:37.585 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.executeUpdate() returned 12016-08-30 20:38:37.586 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.new ResultSet returned 2016-08-30 20:38:37.586 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getGeneratedKeys() returned net.sf.log4jdbc.ResultSetSpy@402770772016-08-30 20:38:37.586 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.getMetaData() returned com.mysql.jdbc.ResultSetMetaData@4cb773c5 - Field level information: com.mysql.jdbc.Field@2c4eae94[catalog=null,tableName=,originalTableName=null,columnName=GENERATED_KEY,originalColumnName=null,mysqlType=-1( Unknown MySQL Type # -1),flags=, charsetIndex=0, charsetName=UTF-8]2016-08-30 20:38:37.591 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.next() returned true2016-08-30 20:38:37.593 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.getMetaData() returned com.mysql.jdbc.ResultSetMetaData@4c114687 - Field level information: com.mysql.jdbc.Field@2c4eae94[catalog=null,tableName=,originalTableName=null,columnName=GENERATED_KEY,originalColumnName=null,mysqlType=-1( Unknown MySQL Type # -1),flags=, charsetIndex=0, charsetName=UTF-8]2016-08-30 20:38:37.598 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.getLong(1) returned 62016-08-30 20:38:37.598 INFO 13288 --- [ main] jdbc.resultset : 4. ResultSet.close() returned 2016-08-30 20:38:37.599 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getMaxRows() returned 02016-08-30 20:38:37.599 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getQueryTimeout() returned 02016-08-30 20:38:37.599 INFO 13288 --- [ main] jdbc.audit : 4. Connection.isClosed() returned false2016-08-30 20:38:37.599 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.clearParameters() returned 2016-08-30 20:38:37.648 INFO 13288 --- [ main] jdbc.audit : 4. Connection.commit() returned 2016-08-30 20:38:37.674 INFO 13288 --- [ main] jdbc.audit : 4. Connection.setAutoCommit(true) returned 2016-08-30 20:38:37.675 INFO 13288 --- [ main] jdbc.audit : 4. Connection.isClosed() returned false2016-08-30 20:38:37.676 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getWarnings() returned null2016-08-30 20:38:37.676 INFO 13288 --- [ main] jdbc.audit : 4. Connection.clearWarnings() returned 2016-08-30 20:38:37.676 INFO 13288 --- [ main] jdbc.audit : 4. Connection.clearWarnings() returned 2016-08-30 20:38:37.681 INFO 13288 --- [ main] jdbc.audit : 4. Connection.isClosed() returned false2016-08-30 20:38:37.706 INFO 13288 --- [ main] jdbc.audit : 4. Connection.setReadOnly(true) returned 2016-08-30 20:38:37.706 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned true2016-08-30 20:38:37.744 INFO 13288 --- [ main] jdbc.audit : 4. Connection.setAutoCommit(false) returned Hibernate: select testentity0_.id as id1_5_0_, testentity0_.created_time as created_2_5_0_, testentity0_.updated_time as updated_3_5_0_, testentity0_.version as version4_5_0_, testentity0_.name as name5_5_0_ from test testentity0_ where testentity0_.id=?2016-08-30 20:38:37.751 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getCatalog() returned mealsystem2016-08-30 20:38:37.793 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.new PreparedStatement returned 2016-08-30 20:38:37.793 INFO 13288 --- [ main] jdbc.audit : 4. Connection.prepareStatement(select testentity0_.id as id1_5_0_, testentity0_.created_time as created_2_5_0_, testentity0_.updated_time as updated_3_5_0_, testentity0_.version as version4_5_0_, testentity0_.name as name5_5_0_ from test testentity0_ where testentity0_.id=?) returned net.sf.log4jdbc.PreparedStatementSpy@5b32e0b12016-08-30 20:38:37.793 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getMaxFieldSize() returned 41943042016-08-30 20:38:37.794 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getMaxRows() returned 02016-08-30 20:38:37.794 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getQueryTimeout() returned 02016-08-30 20:38:37.794 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getFetchDirection() returned 10002016-08-30 20:38:37.794 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.getFetchSize() returned 02016-08-30 20:38:37.796 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.setLong(1, 6) returned 2016-08-30 20:38:37.797 INFO 13288 --- [ main] jdbc.audit : 4. PreparedStatement.isWrapperFor(java.sql.CallableStatement) returned false2016-08-30 20:38:37.798 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned false2016-08-30 20:38:37.798 INFO 13288 --- [ main] jdbc.audit : 4. Connection.getAutoCommit() returned false2016-08-30 20:38:37.798 INFO 13288 --- [ main] jdbc.sqlonly : select testentity0_.id as id1_5_0_, testentity0_.created_time as created_2_5_0_, testentity0_.updated_time as updated_3_5_0_, testentity0_.version as version4_5_0_, testentity0_.name as name5_5_0_ from test testentity0_ where testentity0_.id=6 2016-08-30 20:38:37.833 INFO 13288 --- [ main] jdbc.sqltiming : select testentity0_.id as id1_5_0_, testentity0_.created_time as created_2_5_0_, testentity0_.updated_time as updated_3_5_0_, testentity0_.version as version4_5_0_, testentity0_.name as name5_5_0_ from test testentity0_ where testentity0_.id=6 {executed in 35 msec}大家可以分析其中的Log内容,可以发现,Hibernate的SQL输出是没有具体的参数值的:
Hibernate: insert into test (created_time, updated_time, version, name) values (?, ?, ?, ?)
log4jdbc中的日志输出如下:
2016-08-30 20:38:37.561 INFO 13288 --- [ main] jdbc.sqlonly : insert into test (created_time, updated_time, version, name) values (NULL, NULL, 0, '我的天空') 2016-08-30 20:38:37.585 INFO 13288 --- [ main] jdbc.sqltiming : insert into test (created_time, updated_time, version, name) values (NULL, NULL, 0, '我的天空') {executed in 24 msec}可以发现其Insert语句是有具体的值存在的,额外还有执行所使用的时间。
6. 参考资料
- http://www.cnblogs.com/xdp-gacl/p/4081848.html
- http://my.oschina.net/kzhou/blog/149532
0 0
- Spring Boot下基于JPA抓取SQL运行时的参数值
- Spring Boot 中使用log4jdbc记录SQL的运行时参数
- 使用spring-boot-starter-data-jpa 怎么配置使运行时输出SQL语句
- 基于Spring boot的Spring data jpa连接MySQL数据库
- (5)spring boot下 spring Data JPA的使用
- Spring Boot下的Spring Data JPA实战详解
- spring-boot入门整合jpa基于maven
- 基于Spring Boot与Spring Data JPA的多数据源配置
- Spring Data JPA例子[基于Spring Boot、Mysql]
- Spring Data JPA例子[基于Spring Boot、Mysql]
- Spring Data JPA例子[基于Spring Boot、Mysql]
- spring boot+JPA+easyUI 实现基于浏览器语言的国际化配置
- 基于Spring Boot+jsoup+redis抓取CSDN每周干货的RESTFul爬虫
- Spring Boot JPA-Hibernate
- spring boot 配置 JPA
- Spring Boot JPA总结
- spring-boot-jpa
- spring boot jpa配置
- Oracle中用一个表的数据更新另一个表的数据
- wordpress统一添加archives路径
- Windows Diskpart命令详解
- BZOJ2214 [Poi2011]Shift
- 设计模式学习之路-代理模式
- Spring Boot下基于JPA抓取SQL运行时的参数值
- CAD数据集导出DWG,风格对照表的使用
- Java加密技术(三)——PBE算法
- Packet for query is too large(Packet>1048576)
- SOAP协议详解-转载
- 1004. 成绩排名 (20)
- No write since last change
- JqGrid之实际应用
- 【leetcode】389. Find the Difference【E】