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 lt 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

User · Answer

If anyone plan to use this implementation, You might wanna check this out and this

This is my version of that convertion code:

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

        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.getLong(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.REAL) {
                obj.put(column_name, rs.getFloat(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.getDouble(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.CHAR) {
                obj.put(column_name, rs.getString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.NCHAR) {
                obj.put(column_name, rs.getNString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.LONGNVARCHAR) {
                obj.put(column_name, rs.getNString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARCHAR) {
                obj.put(column_name, rs.getString(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                obj.put(column_name, rs.getByte(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                obj.put(column_name, rs.getShort(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.TIME) {
                obj.put(column_name, rs.getTime(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                obj.put(column_name, rs.getTimestamp(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.BINARY) {
                obj.put(column_name, rs.getBytes(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.VARBINARY) {
                obj.put(column_name, rs.getBytes(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY) {
                obj.put(column_name, rs.getBinaryStream(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) {
                obj.put(column_name, rs.getBoolean(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.CLOB) {
                obj.put(column_name, rs.getClob(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) {
                obj.put(column_name, rs.getBigDecimal(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.DECIMAL) {
                obj.put(column_name, rs.getBigDecimal(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.DATALINK) {
                obj.put(column_name, rs.getURL(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.REF) {
                obj.put(column_name, rs.getRef(column_name));
            } else if (rsmd.getColumnType(i) == java.sql.Types.STRUCT) {
                obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
            } else if (rsmd.getColumnType(i) == java.sql.Types.DISTINCT) {
                obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
            } else if (rsmd.getColumnType(i) == java.sql.Types.JAVA_OBJECT) {
                obj.put(column_name, rs.getObject(column_name));
            } else {
                obj.put(column_name, rs.getString(i));
            }
        }

        json.put(obj);
    }

    return json;
}
}

User · Answer

Use a third party library for the JSON export You could use jOOQ for the job  You don t have to use all of jOOQ s features to take advantage of some useful JDBC extensions  In this case  simply write  String json   DSL using connection  fetch resultSet  formatJSON     Relevant API methods used are   DSLContext fetch ResultSet  to convert a JDBC ResultSet into a jOOQ Result  Result formatJSON   to format the jOOQ Result into a JSON String   The resulting formatting will look like this    quot fields quot     quot name quot   quot field-1 quot   quot type quot   quot type-1 quot                 quot name quot   quot field-2 quot   quot type quot   quot type-2 quot                                 quot name quot   quot field-n quot   quot type quot   quot type-n quot       quot records quot    value-1-1 value-1-2     value-1-n                value-2-1 value-2-2     value-2-n     You could also create your own formatting rather easily  through Result map RecordMapper  This essentially does the same as your code  circumventing the generation of JSON objects   quot streaming quot  directly into a StringBuilder  I d say that the performance overhead should be negligible in both cases  though   Disclaimer  I work for the company behind jOOQ  Use SQL JSON features instead Of course  you don t have to use your middleware to map JDBC ResultSets to JSON  The question doesn t mention for which SQL dialect this needs to be done  but many support standard SQL JSON syntax  or something similar  e g  Oracle SELECT json arrayagg json object     FROM t  SQL Server SELECT   FROM t FOR JSON AUTO  PostgreSQL SELECT to jsonb array agg t   FROM t

User · Answer

package com idal cib   import java io FileWriter  import java io IOException  import java sql Connection  import java sql PreparedStatement  import java sql ResultSet  import java sql ResultSetMetaData  import java sql SQLException  import java util ArrayList   import org json simple JSONArray  import org json simple JSONObject   public class DBJsonConverter        static ArrayList lt String gt  data   new ArrayList lt String gt         static Connection conn   null      static PreparedStatement ps   null      static ResultSet rs   null      static String path           static String driver         static String url         static String username         static String password         static String query           SuppressWarnings    unchecked         public static void dataLoad String path            JSONObject obj1   new JSONObject            JSONArray jsonArray   new JSONArray            conn   DatabaseConnector getDbConnection driver  url  username                  password           try               ps   conn prepareStatement query               rs   ps executeQuery                ArrayList lt String gt  columnNames   new ArrayList lt String gt                 if  rs    null                    ResultSetMetaData columns   rs getMetaData                    int i   0                  while  i  lt  columns getColumnCount                          i                        columnNames add columns getColumnName i                                      while  rs next                          JSONObject obj   new JSONObject                        for  i   0  i  lt  columnNames size    i                              data add rs getString columnNames get i                                                           for  int j   0  j  lt  data size    j                                      if  data get j     null                                        obj put columnNames get i   data get j                                     else                                       obj put columnNames get i                                                                                                                                             jsonArray add obj                       obj1 put  header   jsonArray                       FileWriter file   new FileWriter path                       file write obj1 toJSONString                         file flush                        file close                                      ps close                  else                   JSONObject obj2   new JSONObject                    obj2 put null  null                   jsonArray add obj2                   obj1 put  header   jsonArray                           catch  SQLException e                   TODO Auto-generated catch block             e printStackTrace              catch  IOException e                   TODO Auto-generated catch block             e printStackTrace              finally               if  conn    null                    try                       conn close                        rs close                        ps close                      catch  SQLException e                           TODO Auto-generated catch block                     e printStackTrace                                                          SuppressWarnings  static-access       public static void main String   args               TODO Auto-generated method stub         driver    oracle jdbc driver OracleDriver           url    jdbc oracle thin  localhost 1521 database           username    user           password    password           path    path of file           query    select   from temp employee            DatabaseConnector dc   new DatabaseConnector            dc getDbConnection driver url username password           DBJsonConverter formatter   new DBJsonConverter            formatter dataLoad path                 package com idal cib   import java sql Connection  import java sql DriverManager  import java sql SQLException   public class DatabaseConnector        static Connection conn1   null       public static Connection getDbConnection String driver  String url              String username  String password               TODO Auto-generated constructor stub         try                Class forName driver                conn1   DriverManager getConnection url  username  password             catch  ClassNotFoundException e                   TODO Auto-generated catch block             e printStackTrace              catch  SQLException e                   TODO Auto-generated catch block             e printStackTrace                      return conn1

User · Answer

In addition to suggestions made by  Jim Cook  One other thought is to use a switch instead of if-elses   while rs next        int numColumns   rsmd getColumnCount      JSONObject obj   new JSONObject       for  int i 1  i lt numColumns 1  i          String column name   rsmd getColumnName i        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

User · Answer

Just as a heads up  the if then loop is more efficient than the switch for enums   If you have the switch against the raw enum integer  then it s more efficient  but against the variable  if then is more efficient  at least for Java 5  6  and 7   I e   for some reason  after some performance tests   if  ordinalValue    1             else  ordinalValue    2               is faster than  switch  myEnum ordinal           case 1                    break      case 2                    break      I see that a few people are doubting me  so I ll post code here that you can run yourself to see the difference  along with output I have from Java 7   The results of the following code with 10 enum values are as follows   Note the key here is the if then using an integer value comparing against ordinal constants of the enum  vs  the switch with an enum s ordinal value against the raw int ordinal values  vs  a switch with the enum against each enum name   The if then with an integer value beat out both other switches  although the last switch was a little faster than the first switch  it was not faster than the if else   If   else took 23 ms Switch took 45 ms Switch 2 took 30 ms Total matches  3000000  package testing   import java util Random   enum TestEnum       FIRST      SECOND      THIRD      FOURTH      FIFTH      SIXTH      SEVENTH      EIGHTH      NINTH      TENTH    public class SwitchTest       private static int LOOP   1000000      private static Random r   new Random        private static int SIZE   TestEnum values   length       public static void main String   args            long time   System currentTimeMillis            int matches   0          for  int i   0  i  lt  LOOP  i                  int j   r nextInt SIZE               if  j    TestEnum FIRST ordinal                      matches                  else if  j    TestEnum SECOND ordinal                      matches                  else if  j    TestEnum THIRD ordinal                      matches                  else if  j    TestEnum FOURTH ordinal                      matches                  else if  j    TestEnum FIFTH ordinal                      matches                  else if  j    TestEnum SIXTH ordinal                      matches                  else if  j    TestEnum SEVENTH ordinal                      matches                  else if  j    TestEnum EIGHTH ordinal                      matches                  else if  j    TestEnum NINTH ordinal                      matches                  else                   matches                                    System out println  If   else took    System currentTimeMillis   - time    ms            time   System currentTimeMillis            for  int i   0  i  lt  LOOP  i                  TestEnum te   TestEnum values   r nextInt SIZE                switch  te ordinal                      case 0                      matches                        break                  case 1                      matches                        break                  case 2                      matches                        break                  case 3                      matches                        break                  case 4                      matches                        break                  case 5                      matches                        break                  case 6                      matches                        break                  case 7                      matches                        break                  case 8                      matches                        break                  case 9                      matches                        break                  default                      matches                        break                                  System out println  Switch took    System currentTimeMillis   - time    ms            time   System currentTimeMillis            for  int i   0  i  lt  LOOP  i                  TestEnum te   TestEnum values   r nextInt SIZE                switch  te                    case FIRST                      matches                        break                  case SECOND                      matches                        break                  case THIRD                      matches                        break                  case FOURTH                      matches                        break                  case FIFTH                      matches                        break                  case SIXTH                      matches                        break                  case SEVENTH                      matches                        break                  case EIGHTH                      matches                        break                  case NINTH                      matches                        break                  default                      matches                        break                                  System out println  Switch 2 took    System currentTimeMillis   - time    ms                 System out println  Total matches    matches

User · Answer

public static JSONArray GetJSONDataFromResultSet ResultSet rs  throws SQLException       ResultSetMetaData metaData   rs getMetaData        int count   metaData getColumnCount        String   columnName   new String count       JSONArray jsonArray   new JSONArray        while rs next              JSONObject jsonObject   new JSONObject            for  int i   1  i  lt   count  i                    columnName i-1    metaData getColumnLabel i                  jsonObject put columnName i-1   rs getObject i                      jsonArray put jsonObject             return jsonArray

User · Answer

First pre-generate column names  second use rs getString i  instead of rs getString column name    The following is an implementation of this                 Convert ResultSet to a common JSON Object array        Result is like     ID   1   NAME   Tom   AGE   24      ID   2   NAME   Bob   AGE   26                     public static List lt JSONObject gt  getFormattedResult ResultSet rs            List lt JSONObject gt  resList   new ArrayList lt JSONObject gt             try                  get column names             ResultSetMetaData rsMeta   rs getMetaData                int columnCnt   rsMeta getColumnCount                List lt String gt  columnNames   new ArrayList lt String gt                 for int i 1 i lt  columnCnt i                      columnNames add rsMeta getColumnName i  toUpperCase                                while rs next         convert each object to an human readable JSON object                 JSONObject obj   new JSONObject                    for int i 1 i lt  columnCnt i                          String key   columnNames get i - 1                       String value   rs getString i                       obj put key  value                                     resList add obj                           catch Exception e                e printStackTrace              finally               try                   rs close                  catch  SQLException e                    e printStackTrace                                    return resList

User · Answer

This answer may not be the most efficient  but it sure is dynamic  Pairing native JDBC with Google s Gson library  I easily can convert from an SQL result to a JSON stream   I have included the converter  example DB properties file  SQL table generation  and a Gradle build file  with dependencies used    QueryApp java  import java io PrintWriter   import com oracle jdbc ResultSetConverter   public class QueryApp       public static void main String   args            PrintWriter writer   new PrintWriter System out           String dbProps     database properties           String indent                    writer println  Basic SELECT             ResultSetConverter queryToJson writer  dbProps   SELECT   FROM Beatles   indent  false            writer println   n nIntermediate SELECT             ResultSetConverter queryToJson writer  dbProps   SELECT first name  last name  getAge date of birth  as age FROM Beatles   indent  true             ResultSetConverter java  package com oracle jdbc   import java io    import java lang reflect Type  import java sql    import java util     import com google common reflect TypeToken  import com google gson GsonBuilder  import com google gson stream JsonWriter   public class ResultSetConverter       public static final Type RESULT TYPE   new TypeToken lt List lt Map lt String  Object gt  gt  gt              private static final long serialVersionUID   -3467016635635320150L        getType         public static void queryToJson Writer writer  String connectionProperties  String query  String indent  boolean closeWriter            Connection conn   null          Statement stmt   null          GsonBuilder gson   new GsonBuilder            JsonWriter jsonWriter   new JsonWriter writer            if  indent    null  jsonWriter setIndent indent            try               Properties props   readConnectionInfo connectionProperties               Class forName props getProperty  driver                  conn   openConnection props               stmt   conn createStatement                 gson create   toJson QueryHelper select stmt  query   RESULT TYPE  jsonWriter                if  closeWriter  jsonWriter close                 stmt close                conn close              catch  SQLException se                se printStackTrace              catch  Exception e                e printStackTrace                try                   if  stmt    null  stmt close                  catch  SQLException se2                              try                   if  conn    null  conn close                  catch  SQLException se                    se printStackTrace                              try                   if  closeWriter  amp  amp  jsonWriter    null  jsonWriter close                  catch  IOException ioe                    ioe printStackTrace                                       private static Properties readConnectionInfo String resource  throws IOException           Properties properties   new Properties            InputStream in   ResultSetConverter class getResourceAsStream resource           properties load in           in close             return properties             private static Connection openConnection Properties connectionProperties  throws IOException  SQLException           String database   connectionProperties getProperty  database            String username   connectionProperties getProperty  username            String password   connectionProperties getProperty  password             return DriverManager getConnection database  username  password             QueryHelper java  package com oracle jdbc   import java sql    import java text    import java util     import com google common base CaseFormat   public class QueryHelper       static DateFormat DATE FORMAT   new SimpleDateFormat  YYYY-MM-dd         public static List lt Map lt String  Object gt  gt  select Statement stmt  String query  throws SQLException           ResultSet resultSet   stmt executeQuery query           List lt Map lt String  Object gt  gt  records   mapRecords resultSet            resultSet close             return records             public static List lt Map lt String  Object gt  gt  mapRecords ResultSet resultSet  throws SQLException           List lt Map lt String  Object gt  gt  records   new ArrayList lt Map lt String  Object gt  gt             ResultSetMetaData metaData   resultSet getMetaData             while  resultSet next                  records add mapRecord resultSet  metaData                       return records             public static Map lt String  Object gt  mapRecord ResultSet resultSet  ResultSetMetaData metaData  throws SQLException           Map lt String  Object gt  record   new HashMap lt String  Object gt              for  int c   1  c  lt   metaData getColumnCount    c                  String columnType   metaData getColumnTypeName c               String columnName   formatPropertyName metaData getColumnName c                Object value   resultSet getObject c                if  columnType equals  DATE                      value   DATE FORMAT format value                              record put columnName  value                      return record             private static String formatPropertyName String property            return CaseFormat LOWER UNDERSCORE to CaseFormat LOWER CAMEL  property             database properties  driver com mysql jdbc Driver database jdbc mysql   localhost JDBC Tutorial username root password    JDBC Tutorial sql  -- phpMyAdmin SQL Dump -- version 4 5 1 -- http   www phpmyadmin net -- -- Host  127 0 0 1 -- Generation Time  Jan 12  2016 at 07 40 PM -- Server version  10 1 8-MariaDB -- PHP Version  5 6 14  SET SQL MODE    NO AUTO VALUE ON ZERO   SET time zone     00 00        40101 SET  OLD CHARACTER SET CLIENT   CHARACTER SET CLIENT        40101 SET  OLD CHARACTER SET RESULTS   CHARACTER SET RESULTS        40101 SET  OLD COLLATION CONNECTION   COLLATION CONNECTION        40101 SET NAMES utf8mb4      -- -- Database   jdbc tutorial  -- CREATE DATABASE IF NOT EXISTS  jdbc tutorial  DEFAULT CHARACTER SET latin1 COLLATE latin1 swedish ci  USE  jdbc tutorial    DELIMITER    -- -- Functions -- DROP FUNCTION IF EXISTS  getAge    CREATE DEFINER  root   localhost  FUNCTION  getAge    in dob  DATE  RETURNS INT 11  NO SQL BEGIN DECLARE l age INT     IF DATE FORMAT NOW    00- m- d    gt   DATE FORMAT in dob  00- m- d   THEN       -- This person has had a birthday this year       SET l age DATE FORMAT NOW     Y  -DATE FORMAT in dob   Y       ELSE       -- Yet to have a birthday this year       SET l age DATE FORMAT NOW     Y  -DATE FORMAT in dob   Y  -1     END IF        RETURN l age   END    DELIMITER    -- --------------------------------------------------------  -- -- Table structure for table  beatles  --  DROP TABLE IF EXISTS  beatles   CREATE TABLE IF NOT EXISTS  beatles       id  int 11  NOT NULL     first name  varchar 255  DEFAULT NULL     last name  varchar 255  DEFAULT NULL     date of birth  date DEFAULT NULL    PRIMARY KEY   id     ENGINE InnoDB DEFAULT CHARSET latin1   -- -- Truncate table before insert  beatles  --  TRUNCATE TABLE  beatles   -- -- Dumping data for table  beatles  --  INSERT INTO  beatles    id    first name    last name    date of birth   VALUES 100   John    Lennon    1940-10-09    INSERT INTO  beatles    id    first name    last name    date of birth   VALUES 101   Paul    McCartney    1942-06-18    INSERT INTO  beatles    id    first name    last name    date of birth   VALUES 102   George    Harrison    1943-02-25    INSERT INTO  beatles    id    first name    last name    date of birth   VALUES 103   Ringo    Starr    1940-07-07        40101 SET CHARACTER SET CLIENT  OLD CHARACTER SET CLIENT        40101 SET CHARACTER SET RESULTS  OLD CHARACTER SET RESULTS        40101 SET COLLATION CONNECTION  OLD COLLATION CONNECTION       build gradle  apply plugin   java  apply plugin   eclipse  apply plugin   application   mainClassName    com oracle jdbc QueryApp   repositories       maven            url  http   repo1 maven org maven2           jar       baseName    jdbc-tutorial      version     1 0 0     sourceCompatibility   1 7 targetCompatibility   1 7  dependencies       compile  mysql mysql-connector-java 5 1 16      compile  com google guava guava 18 0      compile  com google code gson gson 1 7 2     task wrapper type  Wrapper        gradleVersion    2 9        Results  Basic SELECT                   firstName    John            lastName    Lennon            dateOfBirth    1940-10-09            id   100                       firstName    Paul            lastName    McCartney            dateOfBirth    1942-06-18            id   101                       firstName    George            lastName    Harrison            dateOfBirth    1943-02-25            id   102                       firstName    Ringo            lastName    Starr            dateOfBirth    1940-07-07            id   103           Intermediate SELECT                   firstName    John            lastName    Lennon            age   75                       firstName    Paul            lastName    McCartney            age   73                       firstName    George            lastName    Harrison            age   72                       firstName    Ringo            lastName    Starr            age   75

User · Answer

For all who ve opted for the if-else mesh solution  please use   String columnName   metadata getColumnName  String displayName   metadata getColumnLabel i   switch  metadata getColumnType i     case Types ARRAY      obj put displayName  resultSet getArray columnName        break        Because in case of aliases in your query  the column name and column label are two different things  For example if you execute   select col1  col2 as my alias from table   You will get           col1   1   col2   2            col1   1   col2   2       Rather than            col1   1   my alias   2            col1   1   my alias   2

User · Answer

I think there s a way to use less memory  a fixed and not linear amount depending on data cardinality  but this imply to change the method signature  In fact we may print the Json data directly on an output stream as soon as we fetch them from the ResultSet  the already written data will be garbage collected since we don t need an array that keeps them in memory    I use GSON that accepts type adapters  I wrote a type adapter to convert ResultSet to JsonArray and it looks very like to your code  I m waiting the  Gson 2 1  Targeted Dec 31  2011  release which will have the  Support for user-defined streaming type adapters   Then I ll modify my adapter to be a streaming adapter     Update  As promised I m back but not with Gson  instead with Jackson 2  Sorry to be late  of 2 years    Preface  The key to use less memory of the result itsef is in the  server side  cursor  With this kind of cursors  a k a  resultset to Java devs  the DBMS sends data incrementally to client  a k a  driver  as the client goes forward with the reading  I think Oracle cursor are server side by default  For MySQL   5 0 2 look for useCursorFetch at connection url paramenter  Check about your favourite DBMS   1  So to use less memory we must    use server side cursor behind the scene use resultset open as read only and  of course  forward only  avoid to load all the cursor in a list  or a JSONArray  but write each row directly on an output line  where for output line I mean an output stream or a writer or also a json generator that wraps an output stream or a writer     2  As Jackson Documentation says       Streaming API is best performing  lowest overhead  fastest read write    other 2 methods build on it    3  I see you in your code use getInt  getBoolean  getFloat    of ResultSet without wasNull  I expect this can yield problems    4  I used arrays to cache thinks and to avoid to call getters each iteration  Although not a fan of the switch case construct  I used it for that int SQL Types   The answer  Not yet fully tested  it s based on Jackson 2 2    lt dependency gt       lt groupId gt com fasterxml jackson core lt  groupId gt       lt artifactId gt jackson-databind lt  artifactId gt       lt version gt 2 2 2 lt  version gt   lt  dependency gt    The ResultSetSerializer object instructs Jackson on how to serialize  tranform the object to JSON  a ResultSet  It uses the Jackson Streaming API inside  Here the code of a test   SimpleModule module   new SimpleModule    module addSerializer new ResultSetSerializer      ObjectMapper objectMapper   new ObjectMapper    objectMapper registerModule module            do the query         ResultSet resultset   statement executeQuery query       Use the DataBind Api here ObjectNode objectNode   objectMapper createObjectNode        put the resultset in a containing structure objectNode putPOJO  results   resultset       generate all objectMapper writeValue stringWriter  objectNode     And  of course  the code of the ResultSetSerializer class   public class ResultSetSerializer extends JsonSerializer lt ResultSet gt         public static class ResultSetSerializerException extends JsonProcessingException          private static final long serialVersionUID   -914957626413580734L           public ResultSetSerializerException Throwable cause               super cause                         Override     public Class lt ResultSet gt  handledType             return ResultSet class              Override     public void serialize ResultSet rs  JsonGenerator jgen  SerializerProvider provider  throws IOException  JsonProcessingException            try               ResultSetMetaData rsmd   rs getMetaData                int numColumns   rsmd getColumnCount                String   columnNames   new String numColumns               int   columnTypes   new int numColumns                for  int i   0  i  lt  columnNames length  i                      columnNames i    rsmd getColumnLabel i   1                   columnTypes i    rsmd getColumnType i   1                              jgen writeStartArray                 while  rs next                       boolean b                  long l                  double d                   jgen writeStartObject                     for  int i   0  i  lt  columnNames length  i                           jgen writeFieldName columnNames i                        switch  columnTypes i                          case Types INTEGER                          l   rs getInt i   1                           if  rs wasNull                                  jgen writeNull                              else                               jgen writeNumber l                                                     break                       case Types BIGINT                          l   rs getLong i   1                           if  rs wasNull                                  jgen writeNull                              else                               jgen writeNumber l                                                     break                       case Types DECIMAL                      case Types NUMERIC                          jgen writeNumber rs getBigDecimal i   1                            break                       case Types FLOAT                      case Types REAL                      case Types DOUBLE                          d   rs getDouble i   1                           if  rs wasNull                                  jgen writeNull                              else                               jgen writeNumber d                                                     break                       case Types NVARCHAR                      case Types VARCHAR                      case Types LONGNVARCHAR                      case Types LONGVARCHAR                          jgen writeString rs getString i   1                            break                       case Types BOOLEAN                      case Types BIT                          b   rs getBoolean i   1                           if  rs wasNull                                  jgen writeNull                              else                               jgen writeBoolean b                                                     break                       case Types BINARY                      case Types VARBINARY                      case Types LONGVARBINARY                          jgen writeBinary rs getBytes i   1                            break                       case Types TINYINT                      case Types SMALLINT                          l   rs getShort i   1                           if  rs wasNull                                  jgen writeNull                              else                               jgen writeNumber l                                                     break                       case Types DATE                          provider defaultSerializeDateValue rs getDate i   1   jgen                           break                       case Types TIMESTAMP                          provider defaultSerializeDateValue rs getTime i   1   jgen                           break                       case Types BLOB                          Blob blob   rs getBlob i                           provider defaultSerializeValue blob getBinaryStream    jgen                           blob free                            break                       case Types CLOB                          Clob clob   rs getClob i                           provider defaultSerializeValue clob getCharacterStream    jgen                           clob free                            break                       case Types ARRAY                          throw new RuntimeException  ResultSetSerializer not yet implemented for SQL type ARRAY                         case Types STRUCT                          throw new RuntimeException  ResultSetSerializer not yet implemented for SQL type STRUCT                         case Types DISTINCT                          throw new RuntimeException  ResultSetSerializer not yet implemented for SQL type DISTINCT                         case Types REF                          throw new RuntimeException  ResultSetSerializer not yet implemented for SQL type REF                         case Types JAVA OBJECT                      default                          provider defaultSerializeValue rs getObject i   1   jgen                           break                                                           jgen writeEndObject                               jgen writeEndArray               catch  SQLException e                throw new ResultSetSerializerException e

User · Answer

A simpler solution  based on code in question    JSONArray json   new JSONArray    ResultSetMetaData rsmd   rs getMetaData    while rs next        int numColumns   rsmd getColumnCount      JSONObject obj   new JSONObject      for  int i 1  i lt  numColumns  i          String column name   rsmd getColumnName i       obj put column name  rs getObject column name          json put obj     return json

User · Answer

The JIT Compiler is probably going to make this pretty fast since it s just branches and basic tests  You could probably make it more elegant with a HashMap lookup to a callback but I doubt it would be any faster  As to memory  this is pretty slim as is    Somehow I doubt this code is actually a critical bottle neck for memory or performance  Do you have any real reason to try to optimize it

User · Answer

the other way   here I have used ArrayList and Map  so its not  call json object row by row but after iteration of resultset finished     List lt Map lt String  String gt  gt  list   new ArrayList lt Map lt String  String gt  gt        ResultSetMetaData rsMetaData   rs getMetaData              while rs next                    Map map   new HashMap                  for  int i   1  i  lt   rsMetaData getColumnCount    i                       String key   rsMetaData getColumnName i                      String value   null                  if  rsmd getColumnType i     java sql Types VARCHAR                               value   rs getString key                    else if rsmd getColumnType i   java sql Types BIGINT                                                        value   rs getLong key                                                            map put key  value                                 list add map                  json put list

User · Answer

Two things that will make this faster are   Move your call to rsmd getColumnCount   out of the while loop  The column count should not vary across rows   For each column type  you end up calling something like this   obj put column name  rs getInt column name      It will be slightly faster to use the column index to retrieve the column value   obj put column name  rs getInt i

[java] Most efficient conversion of ResultSet to JSON?

Examples related to java

Examples related to sql

Examples related to json

Examples related to resultset