DEV Community

Raghwendra Sonu
Raghwendra Sonu

Posted on

How to get data from Postgres DB as Java Map?

In this post i will talk about how we get retrieve data from Postgres DB as map of list as well as list of maps in Java.

To fetch data from RDS as List of Map:

public List<Map<String, Object>> readResultSetAsList(String query) throws SQLException {
    System.out.println("query(" + query + ")");
    Statement queryStatement = connection.createStatement();
    ResultSet resultSet = queryStatement.executeQuery(query);
    ResultSetMetaData md = resultSet.getMetaData();
    int columns = md.getColumnCount();
    List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
    while (resultSet.next()) {
      Map<String, Object> row = new HashMap<String, Object>(columns);
      for (int i = 1; i <= columns; ++i) {
        row.put(md.getColumnName(i), resultSet.getObject(i));
      }
      rows.add(row);
    }
    System.out.println("rows=====" + rows);
    return rows;
  }
Enter fullscreen mode Exit fullscreen mode

To fetch data from RDS as Map of List:

public Map<String, List<Object>> readResultSet(String query) {
    System.out.println("query(" + query + ")");
    Map<String, List<Object>> resultList = null;
    try {
      Statement queryStatement = connection.createStatement();
      ResultSet resultSet = queryStatement.executeQuery(query);
      ResultSetMetaData md = resultSet.getMetaData();
      int columnCount = resultSet.getMetaData().getColumnCount();

      resultList = new HashMap<>(columnCount);
      for (int i = 1; i <= columnCount; ++i) {
        resultList.put(md.getColumnName(i), new ArrayList<>());
      }
      while (resultSet.next()) {
        for (int i = 1; i <= columnCount; ++i) {
          resultList.get(md.getColumnName(i)).add(resultSet.getObject(i));
        }
      }
      System.out.println("resultList=====" + resultList);

      resultSet.close();
      queryStatement.close();
    } catch (SQLException e) {
      Assert.fail(
          "Failed to execute query("
              + query
              + ")! Reason - "
              + e.getSQLState()
              + " "
              + e.getMessage());
    }
    return resultList;
  }

Enter fullscreen mode Exit fullscreen mode

Let me know in comments if this was helpful.

Top comments (0)