what is the best way to convert resultset to json

Question

I am getting the Resultset from mySQL server, and want to send it as JSON back to the client.. the server is writen in Java EE..

I have been looking up a lot for this.. but nothing simple.. is that elementary process really has to be that hard? or is there anything wrong in my understanding?

wassgren · Accepted Answer · 2014-12-21T20:06:55.873

Use Jackson for JSON-processing. If you convert your results to a POJO simply make the POJO Jackson compatible (getters will be serialized automatically for instance or use @JsonProperty.

Example for converting a pojo to JSON:

ObjectMapper mapper = new ObjectMapper();
mapper.writeValueAsString(somePojo);

If you do not convert your results to a POJO the JsonNode subclass called ObjectNode can be used.

Example:

public String convert(ResultSet rs) {
    ObjectNode node = new ObjectMapper().createObjectNode();
    node.put("fieldName", rs.getString("columnName"));
    return node.toString(); // this is proper JSON
}

However, the most common and clean approach is to return a POJO from your function (whether it is an EJB or a REST service or similar) and then let the framework convert it to JSON for you (typically the framework uses Jackson). This means that your method simply returns some kind of model object that is Jackson compatible.

score 1 · Answer 2 · answered Nov 02 '17 at 10:27

1

https://gist.github.com/mreynolds/603526

public String convertResultSetToJson(ResultSet resultSet) throws SQLException {
    Joiner commaJoiner = Joiner.on(", \n");

    StringBuilder builder = new StringBuilder();
    builder.append("{ \"results\": [ ");

    List<String> results = new ArrayList<String>();

    while (resultSet.next()) {
        List<String> resultBits = new ArrayList<String>();

        ResultSetMetaData metaData = resultSet.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            StringBuilder resultBit = new StringBuilder();
            String columnName = metaData.getColumnName(i);
            resultBit.append("\"").append(columnName).append("\": \"").append(resultSet.getString(i)).append("\"");
            resultBits.add(resultBit.toString());
        }

        results.add(" { " + commaJoiner.join(resultBits) + " } ");
    }

    builder.append(commaJoiner.join(results));
    builder.append("] }");
    return builder.toString();
}

answered Nov 02 '17 at 10:27

nagaraj v

19
1

Posting a link and bare code, even if correct, is not really an adequate answer. An explanation of _why_ the code and the material at the link adequately answer the question should be included. Without that information, your answer will get flagged as _low-quality_, which this has. – Jeff Zeitlin Nov 02 '17 at 12:11
Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Nov 02 '17 at 13:35
This code is only capable of handling string values. It won't handle dates, numbers, times or booleans correctly for JSON. – Rodney P. Barbati Feb 13 '19 at 19:53

score 0 · Answer 3 · answered Jan 20 '16 at 11:33

You may use JSONObject provided by org.json. Import org.json.JSONObject into your file. Then you can convert the resultset as follows:

jsonObject = new JSONObject();
jsonObject.put(key,resultSet.getInt(resultSet.findColumn(columname)));
return jsonObject.toString();

So if you wanted to return a column with name NO_OF_DAYS having value 3 into a json object such as this {"days" : "3"}, you write the code as:

jsonObject.put("days",resultSet.getInt(resultSet.findColumn("NO_OF_DAYS")));

OhadR · Answer 4 · 2017-04-03T08:06:17.147

@SuppressWarnings("unchecked")      //we use 3rd-party non-type-safe types...
public static String convertResultSetToJson(ResultSet resultSet) throws SQLException
{
    JSONArray json = new JSONArray();
    ResultSetMetaData metadata = resultSet.getMetaData();
    int numColumns = metadata.getColumnCount();

    while(resultSet.next())             //iterate rows
    {
        JSONObject obj = new JSONObject();      //extends HashMap
        for (int i = 1; i <= numColumns; ++i)           //iterate columns
        {
            String column_name = metadata.getColumnName(i);
            obj.put(column_name, resultSet.getObject(column_name));
        }
        json.add(obj);
    }
    return json.toJSONString();
}

source: https://github.com/OhadR/ohadr.common/blob/master/src/main/java/com/ohadr/common/utils/JsonUtils.java

you can use easily: JsonUtils.convertResultSetToJson(...)

Grab the JAR from Maven Central,

<!-- https://mvnrepository.com/artifact/com.ohadr/ohadr.commons -->
<dependency>
    <groupId>com.ohadr</groupId>
    <artifactId>ohadr.commons</artifactId>
    <version>0.3</version>
</dependency>

what is the best way to convert resultset to json

4 Answers4