SqlRowSet throws Invalid SQL type for column with dateTimeOffset(Type associated with java Instant)

Question

I have upgraded my application to Springboot 3(Hibernate 6) and now instant is stored as datetimeoffset in sql server. I have several queries which returns last modified date and created date using SqlRowSet. Problem is with this approach a method(checkColType ) is called internally which uses java.sql.Types.class which doesn't have any field for datetimeoffset(-155 sqlType) because of this checkColType throws an exception. Is there any way it can be solved or should i return back to older datatype(datetime) for java Instant.

public class RowSetMetaDataImpl implements RowSetMetaData,  Serializable {
private void checkColType(int SQLType) throws SQLException {
    try {
        Class<?> c = java.sql.Types.class;
        Field[] publicFields = c.getFields();
        int fieldValue = 0;
        for (int i = 0; i < publicFields.length; i++) {
            fieldValue = publicFields[i].getInt(c);
            if (fieldValue == SQLType) {
                return;
             }
        }
    } catch (Exception e) {
        throw new SQLException(e.getMessage());
    }
    throw new SQLException("Invalid SQL type for column");
}
}

Even in com.microsoft.sqlserver.jdbc.SSType DATETIMEOFFSET is mapped with microsoft.sql.Types.DATETIMEOFFSET which is absent in java.sql.Types and RowSetMetaDataImpl uses only java.sql.Types.

Codes Intent: Our team has written various adhoc native sql queries, whose result set we write in excel. Using SqlRowSet we get column meta data and write header even if data is not present. If data is present then data is also written on excel. It is impossible for our team to re-write queries on so many clients. I am also attaching the method which we use to write data.

public Path exportDefaultView(List<QueryExcelSheetConfig> queryExcelSheetConfigs) throws Exception {
    Workbook wb = new Workbook();
    Path temp = Files.createTempFile("temp", ".xlsx");
    try (InputStream is = Files.newInputStream(temp)) {
        wb.getWorksheets().removeAt(0);
        for (QueryExcelSheetConfig queryConfig : queryExcelSheetConfigs) {
            String sheetName = queryConfig.getSheetName().replaceAll("[^a-zA-Z0-9._]+", "_");
            Worksheet ws = wb.getWorksheets().add(sheetName);
            SqlRowSet sqlRowSet = namedParameterJdbcTemplate.queryForRowSet("dynamic query from db",
                    queryConfig.getArguments());
            SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
            Cells cells = ws.getCells();
            IntStream.range(0, metaData.getColumnCount()).forEach(colNum -> {
                Cell cell = cells.get(0, colNum);
                cell.setValue(metaData.getColumnName(colNum + 1));
            });
            while (sqlRowSet.next()) {
                IntStream.range(0, metaData.getColumnCount()).forEach(colNum -> {
                    Cell cell = cells.get(sqlRowSet.getRow(), colNum);
                    setCellValue(cell, colNum + 1, sqlRowSet, metaData);
                });
            }
            wb.getWorksheets().get(sheetName).autoFitColumns();
        }
        wb.save(temp.toString());
    } finally {
        wb.dispose();
    }
    return temp;
}

Answer 1

I used this method to resolve my issue.

public ResultRecord executeSelectSqlQueryForResultRecord(String query, Map<String, Object> data) {

NamedParameterJdbcTemplate loadBalancedDB = getLoadBalancedDB();
loadBalancedDB.getJdbcTemplate().setQueryTimeout(SELECT_QUERY_TIMEOUT);
loadBalancedDB.getJdbcTemplate().setFetchSize(1000);

boolean enableAllResultSet = data != null && data.containsKey("enableAllResultSet");
final ResultRecord result1 = loadBalancedDB.query(query, data, rs -> {
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    Map<String, Object> values = null;
    List<String> columns = new ArrayList<>(columnCount);
    for (int i = 1; i <= columnCount; i++) {
        columns.add(JdbcUtils.lookupColumnName(rsmd, i));
    }
    
    List<Map<String, Object>> result = new LinkedList<>();
    int count = 0;
    while (rs.next()) {
        values = new LinkedCaseInsensitiveMap<>(columnCount);
        count++;
        for (int i = 1; i <= columnCount; i++) {
            values.putIfAbsent(columns.get(i-1), JdbcUtils.getResultSetValue(rs, i));
        }
        result.add(values);
        if (count > 50000 && !enableAllResultSet)
            break;
    }
    rs.close();
    return new ResultRecord(result, columns);
});

if (result1.result().size() > 50000 && !enableAllResultSet) {
    throw new DataException("Result set size cannot be greater than 50000. " + "Please pass \"enableAllResultSet\" as one of the argument to disable this check.");
}

return result1;
}