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

Question

Currently I have the following code that converts my resultset to a JSONObject and then appended to a JSONArry, but the the processing time is way too long for large data sets. I am looking for an alternative way or libraries to improve my current computing time.

Currently it takes 16 to 17 seconds to process 14k records from Oracle DB

            int total_rows = rs.getMetaData().getColumnCount();

            while (rs.next()) {
                JSONObject obj = new JSONObject();
                for (int i = 0; i < total_rows; i++) {

                    obj.put(rs.getMetaData().getColumnLabel(i + 1)
                            .toLowerCase(), rs.getObject(i + 1));

                }
                jsonArray.put(obj);

            }

Answer 1

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