1. Graph Developer's Guide for Property Graph
  2. Using the Graph Server (PGX)
  3. Executing PGQL Queries Against the Graph Server (PGX)
  4. Java APIs for Executing SELECT Queries

18.7 Java APIs for Executing SELECT Queries

This section describes the APIs to execute SELECT queries in the graph server (PGX).

Parent topic: Executing PGQL Queries Against the Graph Server (PGX)

18.7.1 Executing SELECT Queries Against a Graph in the Graph Server (PGX)

The PgxGraph.queryPgql(String query) method executes the query in the current session. The method returns a PgqlResultSet.

The ON clauses inside the MATCH clauses can be omitted since the query is executed directly against a PGX graph. For the same reason, the INTO clauses inside the INSERT clauses can be omitted. However, if you want to explicitly specify graph names in the ON and INTO clauses, then those graph names have to match the actual name of the graph (PgxGraph.getName()).

Parent topic: Java APIs for Executing SELECT Queries

18.7.2 Executing SELECT Queries Against a PGX Session

The PgxSession.queryPgql(String query) method executes the given query in the session and returns a PgqlResultSet.

The ON clauses inside the MATCH clauses, and the INTO clauses inside the INSERT clauses, must be specified and cannot be omitted. At this moment, all the ON and INTO clauses of a query need to reference the same graph since joining data from multiple graphs in a single query is not yet supported.

Parent topic: Java APIs for Executing SELECT Queries

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

See the Java Documentation for more details.

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

18.7.4 Printing a Result Set

The following methods of PgqlResultSet (package oracle.pgx.api) are used to print a result set:

  • print() : PgqlResultSet
  • print(long numResults) : PgqlResultSet
  • print(long numResults, int from) : PgqlResultSet
  • print(PrintStream printStream, long numResults, int from) : PgqlResultSet

For example:

g.queryPgql("SELECT COUNT(*) AS numPersons FROM MATCH (n:Person)").print().close()
+------------+
| numPersons |
+------------+
| 3          |
+------------+

Another example:

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")

resultSet.print().close()
+-----------------------------------------------+
| account_holder | total_transacted_with_Nikita |
+-----------------------------------------------+
| Camille        | 1000.0                       |
| Oracle         | 4501.0                       |
+-----------------------------------------------+

Parent topic: Java APIs for Executing SELECT Queries