'Handling null value while dynamically converting resultset to JSON
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
Solution 1:[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.
Solution 2:[2]
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));
}
}
Solution 3:[3]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | M. Rizzo |
| Solution 2 | Prashant Zombade |
| Solution 3 | Lukas Eder |