Proper display converted SQL resultset into JSON

Question

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.

*but it displays in wrong format so far* this means absolutely nothing. — , Jul 18 '16 at 23:34
also that is the default output of `.toString()` on `Object` and not the correct thing to call on `ResultSet`, just look at the source code for `ResultSet`, it just does the default on `Object`. I think you need to read the documentation on what you are trying to do, or again if you already have. — , Jul 18 '16 at 23:37

score 2 · Accepted Answer · answered Jul 19 '16 at 05:43

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:

Proper display converted SQL resultset into JSON

1 Answers1