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,911
11
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

score 0 · Answer 3 · answered Jul 06 '22 at 08:39

I had the same problem and I wrote a message body writer to solve it. Naively passing the result set to Jakarta JSON bind API doesn't work:

jakarta.json.bind.JsonbException: Unable to serialize property 'creator' from oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1ResultSetProxy$2oracle$1jdbc$1internal$1OracleResultSet$$$Proxy

It's database agnostic but extendable and tested with Oracle (>= 19) and PostgreSQL. There are many pitfalls to avoid, I don't expect the accepted solution to work flawlessly, especially with Oracle. ResultSet.getObject(int) and ResultSet.getObject(String) may return objects that can't be directly serialized because they aren't Java beans. Keep in mind that Paul Benn's answer is still valid.

N.B: The source code is under GPL. I use the metadata of the result set to get the columns, I loop on the rows, I convert each value into something that can be passed to Jakarta JSON Processing. It requires a recent JDBC driver to have a correct management of dates and times as some bug fixes and features implemented this year affects their conversion. If you have to (re)implement such a tool, stay away from ResultSet.getObject() when you can or your source code will end up depending a lot on changing and potentially buggy classes in Oracle JDBC driver (ResultSetMetaData.getColumnClassName() returns the wrong class name for FLOAT, "java.lang.Double" instead of "java.math.BigDecimal").

score 0 · Answer 4 · answered Aug 25 '22 at 14:20

Oracle / JDBC only solution

If you want to stick with JDBC, then you can let Oracle database do the work for you and just use the useful JSON_OBJECT(*) syntax:

SELECT JSON_OBJECT(*) FROM table4 WHERE id = '5'

For example:

SELECT JSON_OBJECT(*) FROM v$version;

Produces:

{
  "BANNER":"Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production",
  "BANNER_FULL":"Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production\nVersion 21.3.0.0.0",
  "BANNER_LEGACY":"Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production",
  "CON_ID":0
}

A solution using third parties

If using a third party library is an option for you, you could use jOOQ, which has a variety of JSON export features, including:

You can also use it only for the JDBC ResultSet to JSON translation, e.g.:

try (ResultSet rs = stmt.executeQuery("SELECT * FROM table4 where ID = '5'")) {
    String json = ctx.fetch(rs).formatJSON();
}

Disclaimer: I work for the company behind jOOQ

Java: Query ResultSet to JSON

4 Answers4

Oracle / JDBC only solution

A solution using third parties

Linked