Proper display converted SQL resultset into JSON

I'm trying to implement java method for web service that should retrieve data from table and return the result in json format. For this I use json_agg() function that converts query result into json which I want to display, but it displays in wrong format so far. Here is the method itself:

public String GetRowsFromTable() throws SQLException {

    Connection connection = null;
    String result = "";
    try {
        connection = Connection.getConnection();
        PreparedStatement prepStmt = conn.prepareStatement("SELECT json_agg(table1) FROM table");
        ResultSet rs = prepStmt.executeQuery();
        result += rs;
        System.out.println(rs);
    } catch (SQLException sqlex) {
        sqlex.printStackTrace();
    } finally {
        connection.close();
    }

    return result;
}

The console displays the following result:

org.postgresql.jdbc.PgResultSet@5179f609

Any help would be appreciated.

Solution

You need to iterate through the ResultSet and retrieve each column. The ResultSet instance is not your actual result it's a container for the result. Your query returns one column, so you can access the content of that through the index 1 (JDBC column indexes start at 1, not 0 as usual in Java). To move the result set to the next row you need to use next()

ResultSet rs = prepStmt.executeQuery();
if (rs.next()) {
    System.out.println(rs.getString(1));
}

Typically you would use a while() loop to iterate over all rows in the ResultSet but your query will only return a single row due to the aggregation so that's not necessary. Are you sure you really want all rows of the table as a single huge JSON document. What if the table contains a million rows?

This is all explained in the Java JDBC tutorial: