what is the best way to convert resultset to json [duplicate]

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.

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

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