Java - Convert a ResultSet to a List of Maps, where each Map is a row of data

ResultSet to List

/**
 * Convert the ResultSet to a List of Maps, where each Map represents a row with columnNames and columValues
 * @param rs
 * @return
 * @throws SQLException
 */
private List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
    while (rs.next()){
        Map<String, Object> row = new HashMap<String, Object>(columns);
        for(int i = 1; i <= columns; ++i){
            row.put(md.getColumnName(i), rs.getObject(i));
        }
        rows.add(row);
    }
    return rows;
}

public class JDBCUtils {

    private static final Calendar UTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

    public static Calendar utc() {
        return (Calendar) UTC.clone();
    }

    public static List<String> columns(final ResultSet r) throws SQLException {
        ResultSetMetaData resultSetMetaData = r.getMetaData();
        int count = resultSetMetaData.getColumnCount();

        ImmutableList.Builder<String> columns = ImmutableList.builder();

        for (int i = 1; i <= count; i++) {
            columns.add(resultSetMetaData.getColumnName(i));
        }
        return columns.build();
    }

    public static List<Map<String, Object>> result(final ResultSet r) throws SQLException {
        ImmutableList.Builder<Map<String, Object>> list = ImmutableList.builder();
        ResultSetMetaData meta = r.getMetaData();
        while (r.next()) {
            Map<String, Object> row = row(meta, r);
            if (row.size() > 0) {
                list.add(row);
            }
        }
        return list.build();
    }


    private static Object pgArrayToArray(String columnName, Object value, int jdbcType) throws SQLException {
        if (value == null || jdbcType != Types.ARRAY) {
            return value;
        }


        if (value instanceof List) {
            return value;
        } else {
            // BIGINT is the base type for Long pgArray that we store in crate db.
            PgArray pgArray = (PgArray) value;
            if (pgArray.getBaseType() != BIGINT) {
                return StaticHelper.pgArrayToStringList(value);
            } else {
                return StaticHelper.pgArrayToLongList(value);
            }
        }
    }


    private static Map<String, Object> row(ResultSetMetaData metaData, ResultSet r) throws SQLException {
        Map<String, Object> map = Maps.newHashMap();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            String columnName = metaData.getColumnName(i);
            String dataType = metaData.getColumnTypeName(i);
            int jdbcType = metaData.getColumnType(i);


            if (dataType.equalsIgnoreCase("timestamptz")) {
                Timestamp ts = r.getTimestamp(i, utc());
                //TODO remove this when crate supports zoned time
                //since cratedb right now assumes that time stored in db is always UTC,so it doesn't store zone with it, so right now we are appending it manually
                map.put(columnName, ts != null ? new LocalDateTime(ts.getTime(), DateTimeZone.UTC) + "Z" : null);
            } else {
                map.put(columnName, pgArrayToArray(columnName, r.getObject(i), jdbcType));
            }
        }
        return Collections.unmodifiableMap(map);
    }

    //TODO when converted to streams as below it gave null pointers, we can check why
// return result.stream().map(m -> m.entrySet().stream().filter(e -> columns.contains(e.getKey())).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue))).collect(Collectors.toList());
    public static List<Map<String, Object>> result(final ResultSet r, Collection<String> columns) throws SQLException {
        List<Map<String, Object>> result = result(r);

        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> m : result) {
            Map<String, Object> collect = new HashMap<>();
            for (Map.Entry<String, Object> e : m.entrySet()) {
                if (columns.contains(e.getKey())) {
                    if (collect.put(e.getKey(), e.getValue()) != null) {
                        throw new IllegalStateException("Duplicate key");
                    }
                }
            }
            list.add(collect);
        }
        return list;
    }

}```
I use above class for `cratedb` which has pretty similar protocols to postgres
Use `result` method. It has some custom modifications e.g
 - I want timestamp in zulu format
 - I want List in place of pg array
 so there are changes accordingly. But you can easily modify it as per your needs

Hope it helps !!!