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

Answer 1

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. :)