CODEWITHSUNDEEP
javaoracle-databasebackendresultset
Chris_Z
Chris_Z

Reputation: 31

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

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

            }

Upvotes: 0

Views: 406

Answers (2)

Francisco Sitja
Francisco Sitja

Reputation: 1003

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

Upvotes: 2

Igor Urisman
Igor Urisman

Reputation: 798

I'd do the following test: F := the time it takes to fetch from the database; J := time it takes to serialize JSON from pre-fetched. I suspect that your F >> J, which would indicate that what you need is not a different JSON lib, but a faster read from the database. This may or may not be soluble. However, if F << J, consider parallelizing your serialization to take advantage of extra cores, when available.

Upvotes: 1

Related Questions