动态转换resultset到JSON时处理空值

Question

我正在使用以下方法将resultset转换为JSON，并试图根据下面描述的场景找出如何处理null值：

当我在Oracle SQL developer中运行以下SQL查询（在下面的代码中使用）时：

从EMP WHERE id=6中选择SQLQUERY，得到以下结果：

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

如上所述，对于由上述SQL查询返回的上述数据，下面的Java代码将其转换为以下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"
    }
]

因此，基本上，它没有在上面的雇员计数1400的JSON响应中显示雇员居住状态列名，因为它是null。如何确保在null值的情况下，它显示的列名可能是一个空字符串？或者，如果它是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(); 

    }

以下日志语句正在打印以下内容：

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

这是JSONObject中一个限制的结果。您需要放置一个JSONObject.null来查看JSON对象中的一个条目。我将尝试将您的代码修改为以下内容：

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

现在，根据您使用的JSON库和版本，这可能会有一些不同。请在你的问题中包括这些细节。