记-jackson oracle.sql.TIMESTAMP 序列化失败

记-jackson oracle.sql.TIMESTAMP 序列化失败

一、错误信息

jdbc原生查询final PreparedStatement statement = connection.prepareStatement(sql);返回map结果，无法序列化到前端页面。

com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class java.io.ByteArrayInputStream and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.example.common.response.bean.Result["data"]->java.util.HashMap["data"]->java.util.ArrayList[0]->java.util.HashMap["CREATE_TIME"]->oracle.sql.TIMESTAMP["stream"])

二、数据预览代码

 public Map<String, Object> previewData(String sql) throws SQLException {
        Map<String, Object> result = new HashMap(16);
        List<Map<String, Object>> list = new ArrayList<>();
        try (final Connection connection = dataSource.getConnection()) {
            final PreparedStatement statement = connection.prepareStatement(sql);
            final ResultSetMetaData metaData = statement.getMetaData();
            final int columnCount = metaData.getColumnCount();
            final String[] columns = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columns[i] = metaData.getColumnLabel(i+1);
            }
            result.put("column", columns);
            try (final ResultSet rs = statement.executeQuery()) {
                int rowIndex = 0;
                int limit = 10;
                while (rs.next() &&  rowIndex < limit) {
                    final Map<String, Object> map = new HashMap<>(16);
                    for (int j = 0; j < columnCount; j++) {
                        final String columnLabel = columns[j];
                        final Object object = rs.getObject(columnLabel);
                        map.put(columnLabel, object);
                    }
                    list.add(map);
                    rowIndex++;
                }
            }
            result.put("data", list);
        } catch (SQLException e) {
            LOGGER.error("SQL执行异常=>{}", e);
        }

        return result;
    }

三、解决办法

1.使用datum.toJdbc()转换为jdbc兼容的class类型

// fix oracle serializer bug
if (object instanceof Datum){
     final Datum datum = (Datum) object;
     map.put(columnLabel, datum.toJdbc());
 } else {
     map.put(columnLabel, object);
 }

2.oracle.jdbc.J2EE13Compliant设置为true（推荐）

HikariDataSource dataSource = new HikariDataSource();
dataSource.addDataSourceProperty("oracle.jdbc.J2EE13Compliant", true);

If the value of this property is "true", JDBC uses strict compliance for some edge cases. In general Oracle's JDBC drivers will allow some operations that are not permitted in the strict interpretation of J2EE 1.3. Setting this property to true will cause those cases to throw SQLExceptions. There are some other edge cases where Oracle's JDBC drivers have slightly different behavior than defined in J2EE 1.3. This results from Oracle having defined the behavior prior to the J2EE 1.3 specification and the resultant need for compatibility with existing customer code. Setting this property will result in full J2EE 1.3 compliance at the cost of incompatibility with some customer code. Can be either a system property or a connection property.

OracleDataSource配置说明