0

I am using the postgres database and my actual queries are now returned in Json format and I do not know how to display that to the screen. For instance before I would do this

    org.json.JSONObject jo = null;
    org.json.JSONArray ja = new org.json.JSONArray();

String myquery = "select DISTINCT ON(city,state)latitudes,longitudes,thirtylatmin,thirtylatmax,thirtylonmin,thirtylonmax,city,state from zips where city ilike 'Orl%'  order by city,state,ziptype asc limit 10";
            conn = db.getConnection();


            ps = conn.prepareStatement(myquery);
            rs = ps.executeQuery();

            while (rs.next()) {
                jo = new org.json.JSONObject();
                jo.put("city", rs.getString("city"));
                jo.put("state", rs.getString("state").trim());
                jo.put("latitudes", rs.getFloat("latitudes"));
                jo.put("longitudes", rs.getFloat("longitudes"));

                jo.put("thirtylatmin", rs.getFloat("thirtylatmin"));

                jo.put("thirtylatmax", rs.getFloat("thirtylatmax"));
                jo.put("thirtylonmin", rs.getFloat("thirtylonmin"));
                jo.put("thirtylonmax", rs.getFloat("thirtylonmax"));
                ja.put(jo);
            }
            org.json.JSONObject mainObj = new org.json.JSONObject();
            mainObj.put("location_update", ja);


            String total = mainObj.toString();

            if (ps != null) {
                ps.close();
            }
            conn.close();
            return ok(total);

As you can see I would do my query then convert it to Json format and show it on the browser. My code now is like this

String myquery = "select json_build_object('Profile', array_to_json(array_agg(t))) from (select DISTINCT ON(city,state)latitudes,longitudes,thirtylatmin,thirtylatmax,thirtylonmin,thirtylonmax,city,state from zips where city ilike 'Orl%' order by city,state,ziptype asc limit 10)t";

                conn = db.getConnection();


                ps = conn.prepareStatement(myquery);

                rs = ps.executeQuery();
String results=null;
                while (rs.next()) {

                }



                String total = results.toString();
                // System.err.println(total);
                return ok(total);

My query now returns in Json format so there is no need to convert to Json, how can I get the results of that query to return to the browser . I am new to JDBC and my other question is that I know that JDBC is blocking but is there a limit to the number of request per second that it can handle ? Assuming that I keep adding more CPU's ?

user1591668
  • 2,591
  • 5
  • 41
  • 84
  • JDBC is going to return an object to the caller. I'm guessing that's a servlet or controller, not a browser. The class that's responsible for querying the database should create the HTTP response, set the type to JSON, and return it to the browser. The HTML page can render the JSON as it sees fit. – duffymo Jun 19 '16 at 23:30
  • http://stackoverflow.com/questions/11874919/parsing-json-string-in-java – Benjamin M Jun 19 '16 at 23:39

1 Answers1

0

JDBC does not support JSON datatype, just get it as string.

rs = ps.executeQuery();
String results=null;
if (rs.next()) {
    result = rs.getString(1);
}
String total = results.toString();
Beck Yang
  • 3,004
  • 2
  • 21
  • 26