What's the Most Efficient Way to Convert a ResultSet to JSON in Java?

Most Efficient Conversion of ResultSet to JSON?

The following code employs JSON arrays and objects to convert a result set to a JSON string:

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

        // Determine the SQL data type of the column
        switch(rsmd.getColumnType(i)) {
          case java.sql.Types.ARRAY:
            obj.put(column_name, rs.getArray(column_name));
            break;
          case java.sql.Types.BIGINT:
            obj.put(column_name, rs.getInt(column_name));
            break;
          case java.sql.Types.BOOLEAN:
            obj.put(column_name, rs.getBoolean(column_name));
            break;
          case java.sql.Types.BLOB:
            obj.put(column_name, rs.getBlob(column_name));
            break;
          case java.sql.Types.DOUBLE:
            obj.put(column_name, rs.getDouble(column_name));
            break;
          case java.sql.Types.FLOAT:
            obj.put(column_name, rs.getFloat(column_name));
            break;
          case java.sql.Types.INTEGER:
            obj.put(column_name, rs.getInt(column_name));
            break;
          case java.sql.Types.NVARCHAR:
            obj.put(column_name, rs.getNString(column_name));
            break;
          case java.sql.Types.VARCHAR:
            obj.put(column_name, rs.getString(column_name));
            break;
          case java.sql.Types.TINYINT:
            obj.put(column_name, rs.getInt(column_name));
            break;
          case java.sql.Types.SMALLINT:
            obj.put(column_name, rs.getInt(column_name));
            break;
          case java.sql.Types.DATE:
            obj.put(column_name, rs.getDate(column_name));
            break;
          case java.sql.Types.TIMESTAMP:
            obj.put(column_name, rs.getTimestamp(column_name));
            break;
          default:
            obj.put(column_name, rs.getObject(column_name));
            break;
        }
      }

      json.put(obj);
    }

    return json;
  }
}

Alternative Solution:

An alternative approach, which may be simpler and more memory-efficient, involves using the getObject() method directly:

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; i++) {
    String column_name = rsmd.getColumnName(i);
    obj.put(column_name, rs.getObject(column_name));
  }
  json.put(obj);
}
return json;

The above is the detailed content of What's the Most Efficient Way to Convert a ResultSet to JSON in Java?. For more information, please follow other related articles on the PHP Chinese website!