时间戳

首页 > 解决方案 > Java：查询结果集到 JSON

java - Java：查询结果集到 JSON

问题描述

查询通过以下代码发送到 Oracle DB，并应将查询结果返回为JSON：

    Connection conn ;
    try {

        Class.forName("oracle.jdbc.driver.OracleDriver"); 
        String url = "jdbc:oracle:thin:@localhost:1521:dbname";     
        conn = DriverManager.getConnection(url,"username","pwd");  

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM table4 where ID = '5'");


        while (rs.next()) {
            String s = rs.getString("*");
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(s);

        }

        conn.close();


    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

但是， 的值String s始终为空。

我去过这里的解决方案，但它不适用于*从表中选择。

标签： javajsonresultset

解决方案

要输出 JSON，您希望将数据累积到List<Map<String, Object>>第一个。

用于ResultSetMetaData获取列数和列名。

List<Map<String, Object>> rows = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

while (rs.next()) {
      // Represent a row in DB. Key: Column name, Value: Column value
      Map<String, Object> row = new HashMap<>();
      for (int i = 1; i <= columnCount; i++) {
           // Note that the index is 1-based
           String colName = rsmd.getColumnName(i);
           Object colVal = rs.getObject(i);
           row.put(colName, colVal);
      }
      rows.add(row);
}

// Write the list of rows to output
// Recommend to use jackson-ObjectMapper to streaming json directly to outputstream:
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.writeValue(response.getOutputStream(), rows);

要使用 Jackson ObjectMapper，请将依赖项添加到您的项目中：

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.9.4</version>
</dependency>

推荐阅读