Iterating Through a Result Set

6.8.5.3 Iterating Through a Result Set

There are two ways to iterate through a result set: in a JDBC-like manner or using the Java Iterator interface.

For JDBC-like iterations, the methods in PgqlResultSet (package oracle.pgx.api) are similar to the ones in java.sql.ResultSet. A noteworthy difference is that PGQL's result set interface is based on the new date and time library that was introduced in Java 8, while java.sql.ResultSet is based on the legacy java.util.Date. To bridge the gap, PGQL's result set provides getLegacyDate(..) for applications that still use java.util.Date.

A PgqlResultSet has a cursor that is initially set before the first row. Then, the following methods are available to reposition the cursor:

next() : boolean
previous() : boolean
beforeFirst()
afterLast()
first() : boolean
last() : boolean
absolute(long row) : boolean
relative(long rows) : boolean

Above methods are documented in more detail here.

After the cursor is positioned at the desired row, the following getters are used to obtain values:

getObject(int columnIdx) : Object
getObject(String columnName) : Object
getString(int columnIdx) : String
getString(String columnName) : String
getInteger(int columnIdx) : Integer
getInteger(String columnName) : Integer
getLong(int columnIdx) : Long
getLong(String columnName) : Long
getFloat(int columnIdx) : Float
getFloat(String columnName) : Float
getDouble(int columnIdx) : Double
getDouble(String columnName) : Double
getBoolean(int columnIdx) : Boolean
getBoolean(String columnName) : Boolean
getVertexLabels(int columnIdx) : Set<String>
getVertexLabels(String columnName) : Set<String>
getDate(int columnIdx) : LocalDate
getDate(String columnName) : LocalDate
getTime(int columnIdx) : LocalTime
getTime(String columnName) : LocalTime
getTimestamp(int columnIdx) : LocalDateTime
getTimestamp(String columnName) : LocalDateTime
getTimeWithTimezone(int columnIdx) : OffsetTime
getTimeWithTimezone(String columnName) : OffsetTime
getTimestampWithTimezone(int columnIdx) : OffsetDateTime
getTimestampWithTimezone(String columnName) : OffsetDateTime
getLegacyDate(int columnIdx) : java.util.Date
getLegacyDate(String columnName) : java.util.Date
getVertex(int columnIdx) : PgxVertex<ID>
getVertex(String columnName) : PgxVertex<ID>
getEdge(int columnIdx) : PgxEdge
getEdge(String columnName) : PgxEdge

Above methods are documented in more detail here.

Finally, there is a PgqlResultSet.close() which releases the result set’s resources, and there is a PgqlResultSet.getMetaData() through which the column names and column count can be retrieved.

An example for result set iteration is as follows:

PgqlResultSet resultSet = g.queryPgql(
    "   SELECT owner.name AS account_holder, SUM(t.amount) AS total_transacted_with_Nikita "
  + "     FROM MATCH (p:Person) -[:ownerOf]-> (account1:Account) " 
  + "        , MATCH (account1) -[t:transaction]- (account2) "
  + "        , MATCH (account2:Account) <-[:ownerOf]- (owner:Person|Company) "
  + "    WHERE p.name = 'Nikita' "
  + " GROUP BY owner");

while (resultSet.next()) {
  String accountHolder = resultSet.getString(1);
  long totalTransacted = resultSet.getLong(2);
  System.out.println(accountHolder + ": " + totalTransacted);
}

resultSet.close();

The output of the above example will look like:

Oracle: 4501
Camille: 1000

In addition, the PgqlResultSet is also iterable via the Java Iterator interface. An example of a “for each loop” over the result set is as follows:

for (PgxResult result : resultSet) {
  String accountHolder = result.getString(1);
  long totalTransacted = result.getLong(2);
  System.out.println(accountHolder + ": " + totalTransacted);
}

The output of the above example will look like:

Oracle: 4501
Camille: 1000

Note that the same getters that are available for PgqlResultSet are also available for PgxResult.

Parent topic: Java APIs for Executing SELECT Queries