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

Couldn't you simply check for null in the code you've written above and output an empty string? — Robert Harvey, Jan 22 '19 at 16:00
Could you tell me where and how? I am little confused. Thanks — John, Jan 22 '19 at 16:01
Wouldn't [this](https://stackoverflow.com/a/16607559/102937) be easier? It has a [Serialize Null Fields](http://tutorialtous.com/gson/serializingNullFields.php) option. — Robert Harvey, Jan 22 '19 at 16:02
@RobertHarvey I used these lines of code inside `else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR)` to explicitly check the null value `if(rsNew.getString(column_name) == null) { obj.put(column_name, ""); } else {obj.put(column_name, rsNew.getString(column_name));}` Is this what you were referring to in your first comment? It works after adding this explicit check. I might have to do this explicit check for others as well. — John, Jan 22 '19 at 16:39

score 1 · Accepted Answer · answered Jan 22 '19 at 16:34

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.

score 0 · Answer 2 · answered Jan 22 '19 at 16:11

0

See if this helps you:

else if(rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
   //Handle the column "Employee State of Residence" differently
   if(column_name.equals("Employee State of Residence")){
      String stateValue = rsNew.getString(column_name);
      obj.put(column_name, stateValue==null?"":stateValue );
    }else{
   //Else do it the regular way
     obj.put(column_name, rsNew.getString(column_name));
   }
}

answered Jan 22 '19 at 16:11

Prashant Zombade

482
3
15

Thanks. But I might not want to hardcode the column name here because for different SQL queries, I may run into similar issue – John Jan 22 '19 at 16:14

score 0 · Answer 3 · answered Mar 09 '22 at 14:13

Using standard SQL/JSON

In a lot of SQL dialects, you'll find native SQL/JSON support, so there's no need to write all of this SQL/JSON glue code in your Java client application. I'm assuming you're using Oracle, so you could use pretty much standard SQL/JSON like this:

SELECT JSON_ARRAYAGG(JSON_OBJECT(
  KEY 'Employee State of Residence' VALUE "Employee State of Residence",
  KEY 'Employee Count' VALUE "Employee Count"
  NULL ON NULL -- Use this instead of ABSENT ON NULL to retain null values
  RETURNING CLOB -- In case the data sizes are big
) RETURNING CLOB)
FROM t

Looks much better than your complex loop? If your resulting documents become big, just add RETURNING CLOB to your query, though that's optional.

If you're not using Oracle after all, other database products support SQL/JSON to some extent, or have their own non-standard syntax that can do the same thing.

Using third party libraries

If you're open to using a third party library for your SQL/JSON glue code, you could use jOOQ, which has extensive support for these SQL/JSON features. It can help with the tricky edge cases, e.g. when you need to embed BOOLEAN values, nested JSON data structures, or add vendor agnosticity to the query, etc.

(Disclaimer: I work for the company behind jOOQ)

Handling null value while dynamically converting resultset to JSON

3 Answers3

Using standard SQL/JSON

Using third party libraries