Most Efficient Conversion of Resultset to JSON

Most efficient conversion of ResultSet to JSON?

The JIT Compiler is probably going to make this pretty fast since it's just branches and basic tests. You could probably make it more elegant with a HashMap lookup to a callback but I doubt it would be any faster. As to memory, this is pretty slim as is.

Somehow I doubt this code is actually a critical bottle neck for memory or performance. Do you have any real reason to try to optimize it?

what is the best way to convert resultset to json

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.

Java: Query ResultSet to JSON

To output JSON, you want to accumulate your data into a List<Map<String, Object>> first.

Use ResultSetMetaData to get the column count and column name.

List<Map<String, Object>> rows = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

while (rs.next()) {
      // Represent a row in DB. Key: Column name, Value: Column value
      Map<String, Object> row = new HashMap<>();
      for (int i = 1; i <= columnCount; i++) {
           // Note that the index is 1-based
           String colName = rsmd.getColumnName(i);
           Object colVal = rs.getObject(i);
           row.put(colName, colVal);
      }
      rows.add(row);
}

// Write the list of rows to output
// Recommend to use jackson-ObjectMapper to streaming json directly to outputstream:
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(response.getOutputStream(), rows);

To use Jackson ObjectMapper, add dependency to your project:

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.9.4</version>
</dependency>

Need a more efficient way to convert JDBC resultset to a JSON array

If you are using Oracle 12cR2 you can utilize Oracle supplied functions (JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG and so on) to generate JSON directly from the database, ready to read. It will be substantially faster and easier to code.

You have not posted additional details about your data model so we can help with something more specific, but do feel free to explore the documentation below.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html

I created one quick example below to show a bit about how it works:

FSITJA@db01 2019-06-26 14:15:02> select json_object('name'         value username,
  2                     'default_ts'   value default_tablespace,
  3                     'temp_ts'      value temporary_tablespace,
  4                     'common'       value case when common = 'Y' then 'YES' else 'NO' end,
  5                     'oracle_maint' value case when oracle_maintained = 'Y' then 'YES' else 'NO' end
  6                     format json) as json_obj
  7    from dba_users u
  8   where oracle_maintained = 'Y'
  9         and rownum <= 5;

JSON_OBJ
-----------------------------------------------------------------------------------------------------------
{"name":"SYS","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"SYSTEM","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"GSMCATUSER","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"XS$NULL","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"MDDATA","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}

Working With Java to transform result of ResultSet, by which I do not know the number of columns in the query

1)firs set the class path to gson-2.1.jar

2)code to conver the ResultSet object information to JSON .

ResultSet rs_Tbl_CONTROL_SYNCHRONISM = con.query("Select * From DB_EGLISE.Tbl_CONTROL_synchronism;");

JsonObject jsonResponse = new JsonObject(); 
JsonArray data = new JsonArray();
while(rs_Tbl_CONTROL_SYNCHRONISM.next() ) {
JsonArray row = new JsonArray();
row.add(new JsonPrimitive(rs_Tbl_CONTROL_SYNCHRONISM.getString("columnName1")));
row.add(new JsonPrimitive(rs_Tbl_CONTROL_SYNCHRONISM.getString("columnName2")));
row.add(new JsonPrimitive(rs_Tbl_CONTROL_SYNCHRONISM.getString("columnName3")));

data.add(row);
}
jsonResponse.add("aaData", data);

Convert from ResultSet to JSON Object

I just managed two lines of your function in the question. Please tell me if it works. then i can tell you what was the problem!

    public static JSONArray convertToJSONArray(ResultSet resultSet)
            throws Exception {
        JSONArray jsonArray = new JSONArray();
        while (resultSet.next()) {
            JSONObject obj = new JSONObject();
            int total_rows = resultSet.getMetaData().getColumnCount();
            for (int i = 0; i < total_rows; i++) {
                obj.put(resultSet.getMetaData().getColumnLabel(i + 1)
                        .toLowerCase(), resultSet.getObject(i + 1));

            }
            jsonArray.put(obj);
        }
        return jsonArray;
    }

resultset into JSON. How can I print the json Object?

Your program cannot reach to the print statement as there is a return statement before that. If you had been using an IDE like Netbeans or Eclipse, that would have given you a clear warning.

---

Related Topics