springboot关于jpa实现sql显示

1. sql语句打印

  jpa:
    # 显示sql语句
    show-sql: true
    properties:
      hibernate:
        # 格式化sql语句
        format_sql: true

2. 显示传入参数

通过上面的配置，我们只能在日志中查看执行的sql语句，至于传入的参数我们无法获取。所以需要增加如下配置：

logging:
  level:
    # 基本类型绑定
    org.hibernate.type.descriptor.sql.BasicBinder: trace
    # 枚举类型绑定
    org.hibernate.metamodel.model.convert.internal.NamedEnumValueConverter: trace

这里为什么是trace呢？我们可以通过如下两段代码可知：

BasicBinder

public final void bind(PreparedStatement st, J value, int index, WrapperOptions options) throws SQLException {
    boolean traceEnabled = log.isTraceEnabled();
    if (value == null) {
        if (traceEnabled) {
            log.trace(String.format("binding parameter [%s] as [%s] - [null]", index, JdbcTypeNameMapper.getTypeName(this.getSqlDescriptor().getSqlType())));
        }

        st.setNull(index, this.sqlDescriptor.getSqlType());
    } else {
        if (traceEnabled) {
            log.trace(String.format("binding parameter [%s] as [%s] - [%s]", index, JdbcTypeNameMapper.getTypeName(this.sqlDescriptor.getSqlType()), this.getJavaDescriptor().extractLoggableRepresentation(value)));
        }

        this.doBind(st, value, index, options);
    }

}

public final void bind(CallableStatement st, J value, String name, WrapperOptions options) throws SQLException {
    boolean traceEnabled = log.isTraceEnabled();
    if (value == null) {
        if (traceEnabled) {
            log.trace(String.format("binding parameter [%s] as [%s] - [null]", name, JdbcTypeNameMapper.getTypeName(this.getSqlDescriptor().getSqlType())));
        }

        st.setNull(name, this.sqlDescriptor.getSqlType());
    } else {
        if (traceEnabled) {
            log.trace(String.format("binding parameter [%s] as [%s] - [%s]", name, JdbcTypeNameMapper.getTypeName(this.sqlDescriptor.getSqlType()), this.getJavaDescriptor().extractLoggableRepresentation(value)));
        }

        this.doBind(st, value, name, options);
    }

}

NamedEnumValueConverter

 public void writeValue(PreparedStatement statement, E value, int position) throws SQLException {
 	String jdbcValue = value == null ? null : this.toRelationalValue(value);
 	boolean traceEnabled = log.isTraceEnabled();
 	if (jdbcValue == null) {
 		if (traceEnabled) {
 			log.tracef("Binding null to parameter: [%s]", position);
 		}

 		statement.setNull(position, this.getJdbcTypeCode());
 	} else {
 	if (traceEnabled) {
 		log.tracef("Binding [%s] to parameter: [%s]", jdbcValue, position);
 	}
 	statement.setString(position, jdbcValue);
 }

3. 显示效果

由上诉代码可以看出基本类型和枚举类型的日志形式是不一样的，注意1号参数和10号参数和其他的参数格式形式。

Hibernate:
insert
into
streetview
(auth_state, create_time, data_entity_id, description, maxx, maxy, minx, miny, progress, status, sv_name, table_name, update_time, url, user_id, id)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-10-14 12:00:41.892 [http-nio-8270-exec-7] TRACE o.h.m.m.convert.internal.NamedEnumValueConverter - Binding [PUBLIC] to parameter: [1]
2021-10-14 12:00:41.893 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Thu Oct 14 12:00:41 GMT+08:00 2021]
2021-10-14 12:00:41.895 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BIGINT] - [null]
2021-10-14 12:00:41.895 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [null]
2021-10-14 12:00:41.896 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [DOUBLE] - [113.6]
2021-10-14 12:00:41.896 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [6] as [DOUBLE] - [22.225]
2021-10-14 12:00:41.896 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [7] as [DOUBLE] - [113.525]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [8] as [DOUBLE] - [22.1]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [9] as [DOUBLE] - [0.0]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE o.h.m.m.convert.internal.NamedEnumValueConverter - Binding [CREATE] to parameter: [10]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [11] as [VARCHAR] - [Hongkong]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [12] as [VARCHAR] - [5ce0a28e33ff1d218efdc9ec0d77d8ea]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [13] as [TIMESTAMP] - [Thu Oct 14 12:00:41 GMT+08:00 2021]
2021-10-14 12:00:41.897 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [14] as [VARCHAR] - [/home/test]
2021-10-14 12:00:41.898 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [15] as [INTEGER] - [0]
2021-10-14 12:00:41.898 [http-nio-8270-exec-7] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [16] as [BIGINT] - [60]