Get in to JSON all the rows from sql query in JAVA

Question

I got the next code. I think I just need to make a little change but I don't know what. The code only returns the last row but I want to see all the rows in response. If I declare a LIMIT 10 then I want to get 10 results in response.

What should I change?

Thank you.

    @RequestMapping(value="/url/results/", method=RequestMethod.GET)
    public JsonResponse getResults() throws SQLException {

        Connection connection = ConnSingle.getInstance().getConnection(env);
        String selectSql = "SELECT * FROM Lib.Table WHERE YEARS=10 LIMIT 10";

        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(selectSql);


        JSONArray json = new JSONArray();
        ResultSetMetaData rsmd = rs.getMetaData();
        JSONObject obj = new JSONObject();

        System.out.println(rsmd);

        while(rs.next()) {
            int numColumns = rsmd.getColumnCount();
            for (int i=1; i<=numColumns; i++) {
                String column_name = rsmd.getColumnName(i);
                obj.put(column_name, rs.getObject(column_name));
            }
        }

        rs.close();
        statement.close();
        connection.close();

        return new JsonResponse(obj);
    }

gmcontessa · Accepted Answer · 2018-09-06 09:11:01Z

1

@jaydip is right (he's beaten me by few minutes). Here is an example... You are pushing all the results in the same obj object. To display all the results you have to push "obj" into an array like this:

JSONArray objects = new JSONArray();
while(rs.next()) {
            int numColumns = rsmd.getColumnCount();
            for (int i=1; i<=numColumns; i++) {
                String column_name = rsmd.getColumnName(i);
                obj.put(column_name, rs.getObject(column_name));
                objects.add(obj);
            }
        }
rs.close();
        statement.close();
        connection.close();

        return new JsonResponse(objects);

edited Sep 6, 2018 at 9:11

answered Sep 5, 2018 at 18:07

gmcontessa

5584 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FranzSif Over a year ago

Oh... I understand... I need to push all the objects inside the array, but when I want to push objects.push(obj); the push method doesnt work: error: cannot find symbol object.<push>(obj); D; !!! I just know that in javascript its more easy...

FranzSif Over a year ago

Yes, I have the solution, is not push, is add :D !!! json.add(obj); before the for loop

FranzSif Over a year ago

No problem @jgmcontessa , but the objects.add(obj); must go after the key of the for loop for to add the object when iterating. Thank you very much!!!

Lukas Eder · Accepted Answer · 2020-11-27 09:26:34Z

Doing it natively with Db2's SQL/JSON support

You might not need to have to implement any logic in Java to get this sort of JSON document out of your Db2 database, just use the Db2 native SQL/JSON capabilities, as I've described in this article.

Instead of:

SELECT * FROM Lib.Table WHERE YEARS=10 LIMIT 10

Write:

SELECT cast(('[' || listagg(
  json_object(
    KEY 'id' VALUE t.id,
    KEY 'years' VALUE t.years,
    ...
  ), ',') || ']') AS varchar(32672))
FROM lib.table t
WHERE t.years = 10
FETCH FIRST 10 ROWS ONLY

Unfortunately, Db2 doesn't yet support the standard SQL/JSON JSON_ARRAYAGG() function, which is why you have to do the string concatenation workaround yourself.

Using a third party library

If using a third party library is an option for you in this case, jOOQ can handle that emulation of JSON_ARRAYAGG for you. With jOOQ in a simple case like this, you could also just export the JSON from the JDBC ResultSet like this:

String json = ctx.fetch(rs).formatJSON();

(Disclaimer: I work for the company behind jOOQ)

Jaydip Rakholiya · Accepted Answer · 2018-09-05 18:00:34Z

0

You are iterating over resultset and adding each row into JSONObject but not adding JSONObject into the JSONArray.

Just add JSONObject (obj) into JSONArray (json) and you will get all rows/records in the response. :)

answered Sep 5, 2018 at 18:00

Jaydip Rakholiya

80211 silver badges20 bronze badges

Collectives™ on Stack Overflow

Get in to JSON all the rows from sql query in JAVA

3 Answers 3

3 Comments

Doing it natively with Db2's SQL/JSON support

Using a third party library

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Doing it natively with Db2's SQL/JSON support

Using a third party library

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related