Handling null value while dynamically converting resultset to JSON

Question

I am converting the resultset to JSON using the following approach and trying to figure out how to handle null value based on the scenario described below:

When I run the following SQL query (which is used in the code below) in Oracle SQL developer :

SELECT SQLQUERY FROM EMP WHERE id = 6, I get the following result:

  Employee State of Residence     Employee Count
1  (null)                         1400
2  AL                             1200
3  MS                             6700
4  WT                             4

As seen above,for the above data returned by the above SQL query, the Java code below is converting it into the following JSON :

[{
        "Employee Count": "           1400"
    },
    {
        "Employee Count": "           1200",
        "Employee State of Residence": "AL"
    },
    {
        "Employee Count": "              6700",
        "Employee State of Residence": "MS"
    },
    {
        "Employee Count": "              4",
        "Employee State of Residence": "WT"
    }
]

So basically, it didn't display the Employee State of Residence column name in the JSON response for Employee Count 1400 above because it's null.How can I make sure that in case of null value, it displays the column name with maybe an empty string? OR should I ask the database person to return something if it's NULL?

@Override
    public String getData(Integer id) throws DaoException {

        DataSource ds = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmtNew = null;
        ResultSet rs = null;    
        ResultSet rsNew = null;

        JSONArray json = new JSONArray();

        try {
            ds = jdbcTemplate.getDataSource();
            conn = ds.getConnection();          
            pstmt = conn.prepareStatement("SELECT SQLQUERY FROM EMP WHERE id = ?");
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();  
            rs.next();

            String sqlQuery = rs.getString("SQLQUERY");
            pstmtNew = conn.prepareStatement(sqlQuery);
            rsNew = pstmtNew.executeQuery();

            ResultSetMetaData rsmd = rsNew.getMetaData();
            int cols = rsmd.getColumnCount();
            logger.info("Total Column Count "+rsmd.getColumnCount());
            logger.info("The query fetched %d columns\n",cols);
            logger.info("These columns are: ");

             for (int i=1;i<=cols;i++) {
                 String colName = rsmd.getColumnName(i);
                 String colType = rsmd.getColumnTypeName(i);                 
                 logger.info(colName+" of type "+colType);
              }

             while(rsNew.next()) {

                JSONObject obj = new JSONObject();

                for (int i=1;i<=cols;i++) {

                     String column_name = rsmd.getColumnName(i);


                     if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                         obj.put(column_name, rsNew.getArray(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                         obj.put(column_name, rsNew.getInt(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                         obj.put(column_name, rsNew.getBoolean(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                         obj.put(column_name, rsNew.getBlob(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                         obj.put(column_name, rsNew.getDouble(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                         obj.put(column_name, rsNew.getFloat(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                         obj.put(column_name, rsNew.getInt(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                         obj.put(column_name, rsNew.getNString(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                         obj.put(column_name, rsNew.getString(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                         obj.put(column_name, rsNew.getInt(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                         obj.put(column_name, rsNew.getInt(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                         obj.put(column_name, rsNew.getDate(column_name));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                         obj.put(column_name, rsNew.getTimestamp(column_name));
                        } else {
                         obj.put(column_name, rsNew.getObject(column_name));
                        }

             }
                 json.put(obj);
            }



        }
        catch(Throwable th) {
            throw new DaoException(th.getMessage(), th);
        }
        finally {
            if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }}
            if (rsNew != null) { try { rsNew.close(); } catch (SQLException e) { e.printStackTrace(); }}
            if (pstmt != null) { try { pstmt.close(); } catch(SQLException sqe) { sqe.printStackTrace(); }}
            if (pstmtNew != null) { try { pstmtNew.close(); } catch(SQLException sqe) { sqe.printStackTrace(); }}
            if (conn != null) { try { conn.close(); } catch (SQLException sqle) { sqle.printStackTrace(); }}
        }
        return json.toString(); 

    }

The following log statements are printing the following:

logger.info("Total Column Count "+rsmd.getColumnCount());
            logger.info("The query fetched %d columns\n",cols);
            logger.info("These columns are: ");

            Total Column Count 2
            The query fetched %d columns
            These columns are:
            Employee State of Residence of type VARCHAR2
            Employee Count of type VARCHAR2

Answer 1

This is a result of a limitation in JSONObject. You need to place a JSONObject.NULL in order to see an entry in your JSON object. I would try modifying your code to the following:

} else {
    Object object = rsNew.getObject(column_name);
    if (object != null) {
      obj.put(column_name, rsNew.getObject(column_name));
    } else {
      obj.put(column_name, JSONObject.NULL);
    }
}

Now this might be a little different depending on which JSON library and version you are using. Feel free to include those details in your question.