Hibernate 打印 sql
Published on: | Views: 241对于某些项目而言，我更喜欢使用spring data jpa（底层为hibernate），在开发和测试环境，经常需要看到sql日志，但是spring data jpa的日志打印就有点坑，总得来说就是不太好看，为了看到最终执行的sql， 只好自己动手修改了一把。 示例sql为：
@Modifying
@Query("update Article a set a.salesArticleCategoryId=:salesArticleCategoryId where a.tenantId=:tenantId and a.id in :articleList")
Integer setArticleCategoryId(Long tenantId, Long salesArticleCategoryId, List<Long> articleList);
普通打印
方法一
spring.jpa.show-sql=true
打印结果是：
update article set sales_article_category_id=? where tenant_id=? and (id in (?))
方法二
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
打印结果是
Hibernate:
update
article
set
sales_article_category_id=?
where
tenant_id=?
and (
id in (
?
)
)
打印参数
以上方法均看不到参数，需要打印参数，可以打开：
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=trace
打印结果是
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [BIGINT] - [1048849785215520768]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [BIGINT] - [474606483645009920]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BIGINT] - [1052093050802933760]
增强打印
以上参数基本上可以看到sql了， 但是距离原始sql还是有段距离， 在复制执行的时候非常不爽，要自己一个个设置参数，所以我需要更强大的打印功能。框架没有这个东西，那就自己做。 这里用到一个知识，对于Java来说，普通的类都是通过AppClassLoader加载的，而AppClassLoader会在属性"java.class.path"指定的目录中搜索类文件，然后加载到内存处理。而因为默认情况下，当前源码目录在classpath中的位置早于第三方jar包，所以可以创建类名完全相同的类，让其早于第三方库中的类加载，达到覆盖第三方库中的类，修改其逻辑的目的，嗯 ，这种方法，在关闭安全检查的情况下，可以为所欲为，但毕竟是hack手法，要注意第三方库升级的问题。
通过调试，发现最终hibernate执行函数在org.hibernate.engine.jdbc.internal.ResultSetReturnImpl类中， 于是我在当前源码目录中新建一个完全限定名相同的类，并在其中执行自己的打印逻辑，具体代码如下：
public class ResultSetReturnImpl implements ResultSetReturn {
// ......
@Override
public ResultSet execute(PreparedStatement statement) {
SqlLogger.logPreparedStatement(statement);
//...
}
@Override
public ResultSet execute(Statement statement, String sql) {
SqlLogger.logSql(sql);
//...
}
@Override
public int executeUpdate(PreparedStatement statement) {
SqlLogger.logPreparedStatement(statement);
//...
}
@Override
public int executeUpdate(Statement statement, String sql) {
SqlLogger.logSql(sql);
// ...
}
}
日志打印类SqlLogger是放在自己包中的类：
@Slf4j
public class SqlLogger {
public static void logPreparedStatement(Statement statement) {
try {
if (!(statement instanceof PreparedStatement)) {
return;
}
if (statement instanceof ProxyStatement) {
statement = statement.unwrap(Statement.class);
}
ClientPreparedStatement clientPreparedStatement = null;
if (statement instanceof ClientPreparedStatement) {
clientPreparedStatement = (ClientPreparedStatement) statement;
}
if (clientPreparedStatement != null) {
log.debug("{}", clientPreparedStatement.asSql());
}
} catch (SQLException e) {
log.debug("logPreparedStatement",e);
}
}
public static void logSql(String sql) {
log.debug("{}", sql);
}
}
输出结果如下：
update article set sales_article_category_id=1048849785215520768 where tenant_id=474606483645009920 and (id in (1052093050802933760))
打印结果
进一步的，可以在ResultSetReturnImpl.postExtract中打印返回结果：
public static void logResultSet(ResultSet rs) {
if (rs == null) {
log.debug("return 0 line");
return;
}
try {
ResultSetMetaData metaData = rs.getMetaData();
int columnsNumber = metaData.getColumnCount();
StringBuilder stringBuilder = new StringBuilder();
while (rs.next()) {
for (int i = 1; i <= columnsNumber; i++) {
if (i > 1) {
stringBuilder.append(",");
}
stringBuilder
.append(metaData.getColumnName(i))
.append("=")
.append(rs.getString(i));
}
log.debug("{}", stringBuilder.toString());
}
} catch (Exception e) {
log.debug("logResultSet", e);
}
}
拿来主义
两个类的代码如下(hibernate-core:5.3.7.Final)：
/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* License: GNU Lesser General Public License (LGPL), version 2.1 or later.
* See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
*/
package org.hibernate.engine.jdbc.internal;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.com.sjfx.log.SqlLogger;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.jdbc.spi.JdbcCoordinator;
import org.hibernate.engine.jdbc.spi.JdbcServices;
import org.hibernate.engine.jdbc.spi.ResultSetReturn;
import org.hibernate.engine.jdbc.spi.SqlExceptionHelper;
import org.hibernate.engine.jdbc.spi.SqlStatementLogger;
/**
* Standard implementation of the ResultSetReturn contract
*
* @author Brett Meyer
*/
public class ResultSetReturnImpl implements ResultSetReturn {
private final JdbcCoordinator jdbcCoordinator;
private final Dialect dialect;
private final SqlStatementLogger sqlStatementLogger;
private final SqlExceptionHelper sqlExceptionHelper;
/**
* Constructs a ResultSetReturnImpl
*
* @param jdbcCoordinator The JdbcCoordinator
*/
public ResultSetReturnImpl(JdbcCoordinator jdbcCoordinator) {
this.jdbcCoordinator = jdbcCoordinator;
final JdbcServices jdbcServices = jdbcCoordinator.getJdbcSessionOwner()
.getJdbcSessionContext()
.getServiceRegistry()
.getService(JdbcServices.class);
this.dialect = jdbcServices.getDialect();
this.sqlStatementLogger = jdbcServices.getSqlStatementLogger();
this.sqlExceptionHelper = jdbcServices.getSqlExceptionHelper();
}
@Override
public ResultSet extract(PreparedStatement statement) {
SqlLogger.logPreparedStatement(statement);
// IMPL NOTE : SQL logged by caller
try {
final ResultSet rs;
try {
jdbcExecuteStatementStart();
rs = statement.executeQuery();
} finally {
jdbcExecuteStatementEnd();
}
postExtract(rs, statement);
return rs;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not extract ResultSet");
}
}
private void jdbcExecuteStatementEnd() {
jdbcCoordinator.getJdbcSessionOwner().getJdbcSessionContext().getObserver().jdbcExecuteStatementEnd();
}
private void jdbcExecuteStatementStart() {
jdbcCoordinator.getJdbcSessionOwner().getJdbcSessionContext().getObserver().jdbcExecuteStatementStart();
}
@Override
public ResultSet extract(CallableStatement callableStatement) {
SqlLogger.logPreparedStatement(callableStatement);
// IMPL NOTE : SQL logged by caller
try {
final ResultSet rs;
try {
jdbcExecuteStatementStart();
rs = dialect.getResultSet(callableStatement);
} finally {
jdbcExecuteStatementEnd();
}
postExtract(rs, callableStatement);
return rs;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not extract ResultSet");
}
}
@Override
public ResultSet extract(Statement statement, String sql) {
SqlLogger.log(sql);
// sqlStatementLogger.logStatement(sql);
try {
final ResultSet rs;
try {
jdbcExecuteStatementStart();
rs = statement.executeQuery(sql);
} finally {
jdbcExecuteStatementEnd();
}
postExtract(rs, statement);
return rs;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not extract ResultSet");
}
}
@Override
public ResultSet execute(PreparedStatement statement) {
SqlLogger.logPreparedStatement(statement);
// sql logged by StatementPreparerImpl
try {
final ResultSet rs;
try {
jdbcExecuteStatementStart();
if (!statement.execute()) {
while (!statement.getMoreResults() && statement.getUpdateCount() != -1) {
// do nothing until we hit the resultset
}
}
rs = statement.getResultSet();
} finally {
jdbcExecuteStatementEnd();
}
postExtract(rs, statement);
return rs;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not execute statement");
}
}
@Override
public ResultSet execute(Statement statement, String sql) {
SqlLogger.log(sql);
// sqlStatementLogger.logStatement( sql );
try {
final ResultSet rs;
try {
jdbcExecuteStatementStart();
if (!statement.execute(sql)) {
while (!statement.getMoreResults() && statement.getUpdateCount() != -1) {
// do nothing until we hit the resultset
}
}
rs = statement.getResultSet();
} finally {
jdbcExecuteStatementEnd();
}
postExtract(rs, statement);
return rs;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not execute statement");
}
}
@Override
public int executeUpdate(PreparedStatement statement) {
SqlLogger.logPreparedStatement(statement);
try {
jdbcExecuteStatementStart();
int result = statement.executeUpdate();
SqlLogger.log("affect rows: " + result);
return result;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not execute statement");
} finally {
jdbcExecuteStatementEnd();
}
}
@Override
public int executeUpdate(Statement statement, String sql) {
SqlLogger.log(sql);
sqlStatementLogger.logStatement(sql);
try {
jdbcExecuteStatementStart();
int result = statement.executeUpdate(sql);
SqlLogger.log("affect rows: " + result);
return result;
} catch (SQLException e) {
throw sqlExceptionHelper.convert(e, "could not execute statement");
} finally {
jdbcExecuteStatementEnd();
}
}
private void postExtract(ResultSet rs, Statement st) {
SqlLogger.logResultSet(rs);
if (rs != null) {
jdbcCoordinator.getResourceRegistry().register(rs, st);
}
}
}
package cn.com.demo.log;
import com.mysql.cj.jdbc.ClientPreparedStatement;
import com.zaxxer.hikari.pool.ProxyStatement;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
@Slf4j
public class SqlLogger {
public static void logPreparedStatement(Statement statement) {
try {
if (!(statement instanceof PreparedStatement)) {
return;
}
if (statement instanceof ProxyStatement) {
statement = statement.unwrap(Statement.class);
}
ClientPreparedStatement clientPreparedStatement = null;
if (statement instanceof ClientPreparedStatement) {
clientPreparedStatement = (ClientPreparedStatement) statement;
}
if (clientPreparedStatement != null) {
log.debug("{}", clientPreparedStatement.asSql());
}
} catch (SQLException e) {
log.debug("logPreparedStatement", e);
}
}
public static void logResultSet(ResultSet rs) {
if (rs == null) {
log.debug("return 0 line");
return;
}
try {
ResultSetMetaData metaData = rs.getMetaData();
int columnsNumber = metaData.getColumnCount();
StringBuilder stringBuilder = new StringBuilder();
while (rs.next()) {
for (int i = 1; i <= columnsNumber; i++) {
if (i > 1) {
stringBuilder.append(",");
}
stringBuilder
.append(metaData.getColumnName(i))
.append("=")
.append(rs.getString(i));
}
log.debug("{}", stringBuilder.toString());
}
rs.beforeFirst();
} catch (Exception e) {
log.debug("logResultSet", e);
}
}
public static void log(String sql) {
log.debug("{}", sql);
}
}