java resultset转json,Java:将ResultSet查询为JSON

Query to Oracle DB being sent via following code and supposed to return the query result as 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();

}

However, the value of String s is always null.

I've been to the solution here but it doesn't work for selecting * from the table.

解决方案

To output JSON, you want to accumulate your data into a List> first.

Use ResultSetMetaData to get the column count and column name.

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

To use Jackson ObjectMapper, add dependency to your project:

com.fasterxml.jackson.core

jackson-databind

2.9.4