18.7 Java APIs for Executing SELECT Queries
This section describes the APIs to execute SELECT
queries
in the graph server (PGX).
- Executing SELECT Queries Against a Graph in the Graph Server (PGX)
ThePgxGraph.queryPgql(String query)
method executes the query in the current session. The method returns aPgqlResultSet
. - Executing SELECT Queries Against a PGX Session
ThePgxSession.queryPgql(String query)
method executes the given query in the session and returns aPgqlResultSet
. - 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. - Printing a Result Set
The following methods ofPgqlResultSet (package oracle.pgx.api)
are used to print a result set:
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
.
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
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