Convert from ResultSet to JSON Object

convert resultset to json using jackson
how to convert database data to json in java
cassandra resultset to json
convert resultset to json python
how to convert sql query to json in java
json jdbc driver
jdbc json
json database java

I am trying to convert ResultSet to JSON object. I managed to get a JSON Array object already. Is there a way I can convert JSON object to JSON Array, or from ResultSet to JSON Object directly?

I tried to follow below suggestions in below link. How can I turn a JSONArray into a JSONObject?

But the JSON object I get now is like this:

{"data":[{"patient_key":80},{"eng_surname":"CAN"},{"eng_givename":"NOT"}....]}

I wish to have something like this for API calls:

{       "patientKey": 88,
        "hkid": " U0500574",
        "engSurname": "CAN",
        "engGivename": "NOT ", ....}

Is there any way I can get my desired result?

This is how I converted to JSON Array.

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

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

Most efficient conversion of ResultSet to JSON?, The following code converts a ResultSet to a JSON string using JSONArray and JSONObject. import org.json. Is there a way that uses less  Hi Sushant i have one solution for this problem. I think i will help you. 1)firs set the class path to gson-2.1.jar 2)code to conver the ResultSet object information to JSON .

Try this implementation -

import org.json.JSONArray;
import org.json.JSONObject;
import org.json.JSONException;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class ResultSetConverter {
  public static JSONArray convert( ResultSet rs )
    throws SQLException, JSONException
  {
    JSONArray json = new JSONArray();
    ResultSetMetaData rsmd = rs.getMetaData();

    while(rs.next()) {
      int numColumns = rsmd.getColumnCount();
      JSONObject obj = new JSONObject();

      for (int i=1; i<numColumns+1; i++) {
        String column_name = rsmd.getColumnName(i);

        if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
         obj.put(column_name, rs.getArray(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
         obj.put(column_name, rs.getInt(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
         obj.put(column_name, rs.getBoolean(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
         obj.put(column_name, rs.getBlob(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
         obj.put(column_name, rs.getDouble(column_name)); 
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
         obj.put(column_name, rs.getFloat(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
         obj.put(column_name, rs.getInt(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
         obj.put(column_name, rs.getNString(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
         obj.put(column_name, rs.getString(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
         obj.put(column_name, rs.getInt(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
         obj.put(column_name, rs.getInt(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
         obj.put(column_name, rs.getDate(column_name));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
        obj.put(column_name, rs.getTimestamp(column_name));   
        }
        else{
         obj.put(column_name, rs.getObject(column_name));
        }
      }

      json.put(obj);
    }

    return json;
  }
}

Java: Streaming a JDBC ResultSet as JSON, Interested to learn more about JDBC ResultSet? Then check out our article where we convert a java.sql.ResultSet to JSON and stream it back  Both libraries implement Streaming. I prefer to take a result set and convert it to a collection because it is easier to debug and, generally speaking, no result set being parsed into JSON should be very large, I also often have more complex objects that require additional data from multiple queries or other sources.

You can create a model for the result and convert it into a JSON string. using the GSON class.

Gson gson = new Gson();
String json = gson.toJson(user);

How to convert Java object to JSON String - Gson Java/JSON Serialization Example

take the above reference. you can parse it into any object.

If you are using java8 use streams for list manipulation

Stream In Java

Convert ResultSet to JSON object?, Hi Sushant i have one solution for this problem. I think i will help you. 1)firs set the class path to gson-2.1.jar 2)code to conver the ResultSet  After that we add this object to array and move to the next row. If we do like you suggest, then we go to first row, write it into the JSON object, then we move to second row and override data from first row. In the end we get JSONArray out of 1 object that corresponds to last row in ResultSet Sergio Santiago G. • 5 years ago

DB2 11 - Java - Retrieving JSON documents from a ResultSet, You can query a relational table, and then convert the ResultSet that you retrieve to a com.ibm.db2.jcc.json.DB2JSONResultSet object, which holds the data in  Hey Stefan, we are looking at adding a new activity in the future to convert result set data into json. For now you can use a Script activity to do this conversion. If you want to convert a single row of data to a json object, you can use the following script. import org.json.JSONObject; import workflow.types.data.*;

Nice and simple converter of Java ResultSet into JSONArray or XML, import org.json.JSONArray; import org.json.JSONObject; import java.sql. ResultSet; /** * Utility for converting ResultSets into some Output  I have modified a sample program in Connector/C++ 1.1.7 to put a JSON_OBJECT into a table called 'test' with one column called 'id' which is of JSON type. When I query the 'test' table and get the sql::ResultSet object, I'm unable to convert the string into a json::object, though it shows up as a properly formed json string - e.g. {"name": "Ram"}.

This class is designed to convert ResultSet data taken as a result of , import org.json.JSONObject;. import java.sql.ResultSet;. import java.text. public static JSONArray convertResultSetIntoJSON(ResultSet resultSet) throws  You can query a relational table, and then convert the ResultSetthat you retrieve to a com.ibm.db2.jcc.json.DB2JSONResultSetobject, which holds the data in JSON format. After you create the DB2JSONResultSetobject, you can use methods on that object to retrieve the JSON documents or JSON snippets.

Comments
  • Yes, this is working! Seems like simply just moving out the JSONObject from the for loop, and add that to the output array in the final step will help!
  • Yes. The problem was that when evere you try to add a new field to you json object, it creates a new objects. So i managed to create object outside the loop, and add it to array after all iterations for adding fields is completed.
  • Is this just converting to JSONArray?
  • I'm checking this method too. However, since there are chances that some fields might be null. Would that be an issue?
  • I managed to modify my current method to convert ResultSet to JSONObject. However, I'm hitting another issue. I need to add another ResultSet into a current JSONObject. I tried to use .put method, but it is giving me this: "patientPhones":[{"phone_id":42}, {"phone_type_cd":"O"},{"country_cd":"852"},... What I need is this: "patientPhones": [{"phoneId": 88, "phoneTypeCd": "H",... How should I do? Hope you can understand what I mean.