Java: Query ResultSet to JSON

Question

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.

A ResultSet is a collection of columns, so [getString(...)](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getString-java.lang.String-) will act only upon one column. — Paul Benn, Jun 12 '18 at 10:30
@PaulBenn so how do I return all the columns without mentioning them one by one? — Eng7, Jun 12 '18 at 10:31
Question [24943894](https://stackoverflow.com/questions/24943894) already addresses this. It's always good to look through the [documentation](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html) before asking questions like this too - see the [getMetadata()](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getMetaData--) method. — Paul Benn, Jun 12 '18 at 10:34
String s = rs.getString("*"); this won't work,You have to set variebles as much of database table fields otherwise use java POJO classes — Sumesh TG, Jun 21 '18 at 09:58

Mạnh Quyết Nguyễn · Accepted Answer · 2019-08-23T01:45:30.300

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

Use ResultSetMetaData to get the column count and column name.

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

To use Jackson ObjectMapper, add dependency to your project:

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

In this case, what are the that I should use in `List> rows = new ArrayList<>();` ? — Eng7, Jun 19 '18 at 07:35
Read my comment in my code. Each map represents a row in DB. Map key is column name and Map value is column value — Mạnh Quyết Nguyễn, Jun 19 '18 at 07:45
Please replace "Object colVal = rsmd.getObject(i);" with "Object colVal = rs.getObject(i);" — Michael Belyakov, Jul 04 '19 at 12:37

score 2 · Answer 2 · answered Jun 12 '18 at 10:41

2

The problem here is not the conversion to JSON (which is already addressed in this question you link in your post) but the way you are calling the ResultSet. Looking through its documentation, we can see the getString method can take either a column index or a column label, but mentions nothing about wildcard searches such as *. If you don't know anything about the columns in the database, you can use the getMetadata() method to obtain information about the columns (including the column names).

answered Jun 12 '18 at 10:41

Paul Benn

1,696
10
26

1

Could you propose a code based on the one above? after changed to `getMetaData` I still have null values. – Eng7 Jun 12 '18 at 11:40
it depends heavily on how you want the row formatted. Would you like your column values as an `Object[]`? Would you like their `toString` representations? I'm assuming your question arises because it wouldn't be enough to turn it into JSON directly? – Paul Benn Jun 12 '18 at 12:57
1

`toString` will be used to turn the values into JSON. – Eng7 Jun 13 '18 at 07:03

Java: Query ResultSet to JSON

2 Answers2

Linked