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;
}

How to read this list?

There are multiple ways to read the list. Following few ways for reference:
#1 first way -
for (Map<String, Object> row:dataList) {
for (Map.Entry<String, Object> rowEntry : row.entrySet()) {
System.out.print(rowEntry.getKey() + " = " + rowEntry.getValue() + ", ");
}
}*/ // OUTER FOR-LOOP ENDs

#2 second way -
for (int i=0; i<dataList.size(); i++) {
System.out.print(" " + dataList.get(i).get("REPORT_SECTION"));
}

@ALL, Can you please help me 👍
How to push back from HashMap<String , String> to tuple in mysql?

why dont you try something @jsroyal, I don't know what you meant by in mysql. in Java try

map.entrySet().stream().map((e) -> new Pair<>(e.getKey(),e.getValue())).collect(Collectors.toList());

What if you have multiple result sets?

Hi ,

Using the above Resultset to list function is omitting the very first row. Any idea how to resolve this? Means if my total row count is 8 then resultset is giving only 7.

I got it fixed by changing the while(rs.next()) to do {} while. Line 13 is related to increase the column count

How can we write a test for this extractor ?

I would recommend to use md.getColumnLabel(i) at line 14
getColumnName ignores sql "as" labeling in select.
So if you join two nearly same tables and use the "as" to label them different getColumnName overwrites the existing same column.

Thanks

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 !!!