6.8.6.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.