Select data

As we have seen in the earlier section, you can use a Statement to execute a query. There are two ways to execute a query on a Statement.

The first is to use the executeQuery(String) method which returns an ResultSet directly. The second is to use execute(String) which assigns the resultset to the statement so that it can be retrieved later using stmt.getResultSet().

Once you have a reference to a resultset, we can query it. Recall the next() method from the quick-start. Internally the resultset maintains a cursor which points to the current row (Kafka record). Each time next() is invoked, this cursor moves to a single position. If the cursor then points to a valid row, next() returns true. If the cursor has moved past the end of the results, it then returns false.

If you are not familiar with JDBC previously, then it is worth knowing that the resultset acts as both the iterator, and the current element.

It is possible to move the cursor to any offset in the resultset. This is possible because the resultset is stored in memory once it has been loaded from the store. For example, we can move the cursor to the last row, the first row, a specific row, or a relative offset from the current position.

Here is an example of accessing the last record first, and then the first record second.

Connection conn = DriverManager.getConnection(
        "jdbc:lenses:kafka:http://localhost:3030",
        "username",
        "password");

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytopic");

rs.last();
System.out.println(rs.getString("name"));

rs.first();
System.out.println(rs.getString("name"));

It is also possible to re-use a statement to execute another query, but any methods called on a given statement always refer to the last executed query. Therefore you should not use the same statement instances in a concurrent context.

Nested fields 

Lenses SQL supports nested fields using dot-delimited syntax. So if you wish to select the field postcode from a structure called address, alongside the name at the top level, then the query would be as the following:

ResultSet rs = 
    stmt.executeQuery("SELECT name, address.postcode FROM mytopic");
while (rs.next()) {
    System.out.println(rs.getString("name"));
    System.out.println(rs.getString("address.postcode"));
}

Notice the dot-separated field name used when accessing nested fields.

Metadata 

It is common to execute SQL queries which have been generated programmatically. In this case, the number of columns, their order, and types can not be known upfront. In this situation, the JDBC specification allows us to provide metadata for a resultset.

By using the metadata, it is possible to retrieve the number of columns, the column types - both JDBC internal type and JVM type - the names of the columns, precision and scale information, whether a numerical type is signed, and finally if the column allows for null values.

Here is a program that prints out all the metadata for a given result set.

Connection conn = 
    DriverManager.getConnection("jdbc:lenses:kafka:https://localhost:3030", "user", "pass");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytopic");
ResultSetMetaData meta = rs.getMetaData();

for (int k = 1; k <= meta.getColumnCount(); k++) {
    System.out.println("ColumnName=" + meta.getColumnName(k));
    System.out.println("ColumnType=" + meta.getColumnTypeName(k));
    System.out.println("Nullability=" + meta.isNullable(k));
    System.out.println("Signed=" + meta.isSigned(k));
    System.out.println("Precision=" + meta.getPrecision(k));
    System.out.println("Scale=" + meta.getScale(k));
}