Most efficient conversion of ResultSet to JSON?

Question

The following code converts a ResultSet to a JSON string using JSONArray and JSONObject.

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;

  }

}

• Is there a faster way?
• Is there a way that uses less memory?

Answer 1

Following two things will make the conversion of Resulset to JSON faster are:

Try moving your call to rsmd.getColumnCount() out of the while loop. But, the column count should not vary across the rows.

1. For each column type, you can use the below code:

obj.put(column_name, rs.getInt(column_name));

2. The below code will be slightly faster to use the column index to retrieve the column value:

obj.put(column_name, rs.getInt(i));