springboot关于jpa实现sql显示

Love The Way You Lie 2022-09-16 01:22 412阅读 0赞

## 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\]