Returning Query Results as Java Streams with BSON

The HCL OneDB™ JDBC Driver can simplify converting trasitional ResultSet objects into JSON/BSON structures using Java Streams via Statement or PreparedStatement objects. After creating a Statement or PreparedStatement you can use the extended classes to access a set of methods for streaming the rows of the database as BSON records.

Java applications can access the stream(…) methods by casting the Statement or PreparedStatement to the HCL OneDB JDBC Driver implementation classes. By default each row is streamed from the Driver (using the normal fetch buffers), but you can override this to prefetch all rows BEFORE the streams API starts processing.

Once you have a java.util.Stream object (Seehttps://docs.oracle.com/javase/8/docs/api/java/util/stream/Stream.html), you can use the Java Streams API to process each row.

Each row of a database table is represented by a BSON object represented by the IfxBSONObject class. See the Javadocs for the list of methods for IfxBSONObject.

The example below shows how to cast the Statement object from the connection and issue a query that returns a Stream.

try(IfxStatement s = connection.createStatement().unwrap(IfxStatement.class)) {
   String sql = "SELECT tabname, tabid from systables where tabid < 10";
   System.out.println(s.stream(sql)
      .count()); // Prints 9
   System.out.println(s.stream(sql, IfxStatement.STREAMS_FETCHING.PREFETCH) //Prefetch all rows. Optional
      .count()); // Prints 9
   s.stream(sql).forEach(System.out::println);  //prints each row as a JSON object
}

Data types supported

The streaming of results only works with a subset of datatypes that can be mapped into a BSON object. See the documentation on BSON objects to understand the mappings.