Retrieving JSON documents from a ResultSet

You can query a relational table, and then convert the ResultSet that you retrieve to a com.ibm.db2.jcc.json.DB2JSONResultSet object, which holds the data in JSON format. After you create the DB2JSONResultSet object, you can use methods on that object to retrieve the JSON documents or JSON snippets.

Procedure

To retrieve JSON documents or snippets from ResultSet objects, follow this procedure.

Execute a query against a relational table whose data you want to convert to JSON format. Retrieve the contents of the table into a ResultSet object.

Create a DB2JSONResultSet object from the ResultSet object in one of the following ways:

For a stand-alone application, cast the ResultSet object to a DB2ResultSet object, and execute the DB2ResultSet.toJSONResultSet method to obtain a DB2JSONResultSet object. For example:
```
DB2JSONResultSet jsonrs = ((DB2ResultSet) rs).toJSONResultSet();
```
For a web application, unwrap the ResultSet object to a DB2ResultSet object. Then execute the DB2ResultSet.toJSONResultSet method to obtain a DB2JSONResultSet object. For example:
```
DB2ResultSet db2rs = rs.unwrap ( DB2ResultSet.class);
DB2JSONResultSet jsonrs = db2rs.toJSONResultSet();
```

The data type mappings for conversion of the columns in the ResultSet object to the fields in the DB2JSONResultSet object are as follows:

DB2® data type of a ResultSet column	JSON data type of a DB2JSONResultSet field
BIGINT DECIMAL DOUBLE FLOAT NUMERIC SMALLINT	Number
CHAR VARCHAR	String

Restrictions:

After you obtain the DB2JSONResultSet object, you can no longer retrieve data directly from the ResultSet object.
You cannot create a DB2JSONResultSet object if the ResultSet object contains columns of the BLOB, CLOB, XML, VARBINARY, or BINARY data types.

Use the appropriate DB2JSONResultSet method to retrieve the data from the DB2JSONResultSet object.

Method name	Function of the method
getAsciiStream	Returns the contents of the DB2JSONResultSet object as a java.io.InputStream object
getCharacterStream	Returns a java.io.Reader object on which applications can call methods to read the JSON document incrementally
getCurrentRow	Returns the current row of the DB2JSONResultSet as a JSON snippet
next	Moves the cursor to the next row in the DB2JSONResultSet object
toJSONString	Returns the rest of the DB2JSONResultSet object, starting with the current row, as a String

Close the DB2JSONResultSet object.

Examples

The numbers to the right of statements in the following examples correspond to the previously described steps.

Example: Use DB2JSONResultSet.getCurrentRow to retrieve one row of a JSON document at a time.

sql = "SELECT EMPNO, SALARY FROM EMPLOYEE";
ResultSet rs = 
 stmt.executeQuery(sql);                                               1 
                                        // ResultSet contains a CHAR
                                        // and a DECIMAL column
DB2JSONResultSet jsonrs = ((DB2ResultSet) rs).toJSONResultSet();       2 
                                        // DB2JSONResultSet contains
                                        // String and Number fields
while(jsonrs.next()) {
 String row = json.getCurrentRow();                                    3 
 …
 // Process row
}
jsonrs.close();                         // Close the DB2JSONResultSet  4

Example: Use DB2JSON.toJSONString to retrieve an entire JSON document into a String.

sql = "SELECT EMPNO, PHONENO FROM EMPLOYEE";
ResultSet rs = 
 stmt.executeQuery(sql);                                               1 
                                         // ResultSet contains a CHAR
                                         // and a DECIMAL column
DB2JSONResultSet jsonrs = ((DB2ResultSet) rs).toJSONResultSet();       2 
                                         // DB2JSONResultSet contains
                                         // String and Number fields
String jsondoc = json.toJSONString();                                  3 
 …
// Process contents of string 
jsonrs.close();                         // Close the DB2JSONResultSet  4

Example: Use DB2JSONResultSet.getAsciiStream to read the JSON document into an AsciiStream.

sql = "SELECT EMPNO, PHONENO FROM EMPLOYEE";
ResultSet rs = 
 stmt.executeQuery(sql);                                               1 
                                        // ResultSet contains a CHAR
                                        // and a DECIMAL column
DB2JSONResultSet jsonrs = ((DB2ResultSet) rs).toJSONResultSet();       2 
                                        // DB2JSONResultSet contains
                                        // String and Number fields
InputStream is = jsonrs.getAsciiStream();                              3 
char  [] buff = new char [1024]; 
int  b = 0;
do  {                                   // Read and print AsciiStream contents
 b = is.read ();
 if  (b > 0) {
  System.out.print ((char)b);
 }
} while (b > 0);                             
is.close();
jsonrs.close();                         // Close the DB2JSONResultSet  4

Example: Use DB2JSONResultSet.getCharacterStream to retrieve an entire JSON document into a character stream. Then read the document contents into a buffer and print them.

sql = "SELECT EMPNO, PHONENO FROM EMPLOYEE";
ResultSet rs = 
 stmt.executeQuery(sql);                                               1 
                                        // ResultSet contains a CHAR
                                        // and a DECIMAL column
DB2JSONResultSet jsonrs = ((DB2ResultSet) rs).toJSONResultSet();       2 
                                        // DB2JSONResultSet contains
                                        // String and Number fields
Stream reader = jsonrs.getCharacterStream ();                          3 
char [] buff = new char[1024];
int len = 0;
do {                                    // Read and print CharacterStream contents
 len = reader.read(buff);
 if (len > 0) {
  System.out.println(String.copyValueOf(buff,0,len));
 }
}
reader.close();
jsonrs.close();                         // Close the DB2JSONResultSet  4