5 Property Graph Query Language (PGQL)

PGQL is a SQL-like query language for property graph data structures that consist of vertices that are connected to other vertices by edges, each of which can have key-value pairs (properties) associated with them.

The language is based on the concept of graph pattern matching, which allows you to specify patterns that are matched against vertices and edges in a data graph.

The property graph support provides two ways to execute Property Graph Query Language (PGQL) queries through Java APIs:

For more information about PGQL, see https://pgql-lang.org.

5.1 Creating a Property Graph using PGQL

CREATE PROPERTY GRAPH is a PGQL DDL statement to create a graph from database tables. The graph is stored in the property graph schema.

The CREATE PROPERTY GRAPH statement starts with the name you give the graph, followed by a set of vertex tables and edge tables. The graph can have no vertex tables or edge tables (an empty graph), or vertex tables and no edge tables (a graph with only vertices and no edges), or both vertex tables and edge tables (a graph with vertices and edges). However, a graph cannot be specified with only edge tables and no vertex tables.

Consider the following example:

  • PERSONS is a table with columns ID, NAME, and ACCOUNT_NUMBER. A row is added to this table for every person who has an account.
  • TRANSACTIONS is a table with columns FROM_ACCOUNT, TO_ACCOUNT, DATE, and AMOUNT. A row is added into this table in the database every time money is transferred from a FROM_ACCOUNT to a TO_ACCOUNT.

A straightforward mapping of tables to graphs is as follows. The graph concepts mapped are: vertices, edges, labels, properties.

  • Vertex tables: A table that contains data entities is a vertex table.
    • Each row in the vertex table is a vertex.
    • The columns in the vertex table are properties of the vertex.
    • The name of the vertex table is the default label for this set of vertices. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
  • Edge tables: An edge table can be any table that links two vertex tables, or a table that has data that indicates an action from a source entity to a target entity. For example, a transfer of money from FROM_ACCOUNT to TO_ACCOUNT is a natural edge.
    • Foreign key relationships can give guidance on what links are relevant in your data. CREATE PROPERTY GRAPH will default to using foreign key relationships to identify edges.
    • Some of the properties of an edge table can be the properties of the edge. For example, an edge from FROM_ACCOUNT to TO_ACCOUNT can have properties DATE and AMOUNT.
    • The name of an edge table is the default label for this set of edges. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
  • Keys:
    • Keys in a vertex table: The key of a vertex table identifies a unique vertex in the graph. The key can be specified in the CREATE PROPERTY GRAPH statement; otherwise, it defaults to the primary key of the table. If there are duplicate rows in the table, the CREATE PROPERTY GRAPH statement will return an error.
    • Key in an edge table: The key of an edge table uniquely identifies an edge in the graph. The KEY clause when specifying source and destination vertices uniquely identifies the source and destination vertices.

    The following is an example CREATE PROPERTY GRAPH statement for the tables PERSONS and TRANSACTIONS.

    CREATE PROPERTY GRAPH bank_transfers
         VERTEX TABLES (persons KEY(account_number))
         EDGE TABLES(
                      transactions KEY (from_acct, to_acct, date, amount)
                      SOURCE KEY (from_account) REFERENCES persons
                      DESTINATION KEY (to_account) REFERENCES persons
                      PROPERTIES (date, amount)
           )
    
  • Table aliases: Vertex and edge tables must have unique names. If you need to identify multiple vertex tables from the same relational table, or multiple edge tables from the same relational table, you must use aliases. For example, you can create two vertex tables PERSONS and PERSONS_ID from one table PERSONS, as in the following example.
    CREATE PROPERTY GRAPH bank_transfers
         VERTEX TABLES (persons KEY(account_number)
                        persons_id AS persons KEY(id))
    
  • REFERENCES clause: This connects the source and destination vertices of an edge to the corresponding vertex tables.

For more details, see: https://pgql-lang.org/spec/latest/#creating-a-property-graph.

5.2 Pattern Matching with PGQL

Pattern matching is done by specifying one or more path patterns in the MATCH clause. A single path pattern matches a linear path of vertices and edges, while more complex patterns can be matched by combining multiple path patterns, separated by comma. Value expressions (similar to their SQL equivalents) are specified in the WHERE clause and let you filter out matches, typically by specifying constraints on the properties of the vertices and edges

For example, assume a graph of TCP/IP connections on a computer network, and you want to detect cases where someone logged into one machine, from there into another, and from there into yet another. You would query for that pattern like this:

SELECT id(host1) AS id1, id(host2) AS id2, id(host3) AS id3         /* choose what to return */
FROM MATCH
    (host1) -[connection1]-> (host2) -[connection2]-> (host3)       /* single linear path pattern to match */
WHERE
    connection1.toPort = 22 AND connection1.opened = true AND
    connection2.toPort = 22 AND connection2.opened = true AND
    connection1.bytes > 300 AND                                     /* meaningful amount of data was exchanged */
    connection2.bytes > 300 AND
    connection1.start < connection2.start AND                       /* second connection within time-frame of first */
    connection2.start + connection2.duration < connection1.start + connection1.duration
GROUP BY id1, id2, id3                                              /* aggregate multiple matching connections */ 

For more examples of pattern matching, see the relevant section of the PGQL specification.

5.3 Edge Patterns Have a Direction with PGQL

An edge pattern has a direction, as edges in graphs do. Thus, (a) <-[]- (b) specifies a case where b has an edge pointing at a, whereas (a) -[]-> (b) looks for an edge in the opposite direction.

The following example finds common friends of April and Chris who are older than both of them.

SELECT friend.name, friend.dob
FROM MATCH                  /* note the arrow directions below */
  (p1:person) -[:likes]-> (friend) <-[:likes]- (p2:person)
WHERE
  p1.name = 'April' AND p2.name ='Chris' AND
  friend.dob > p1.dob AND friend.dob > p2.dob
ORDER BY friend.dob DESC 

For more examples of edge patterns, see the relevant section of the PGQL specification here.

5.4 Vertex and Edge Labels with PGQL

Labels are a way of attaching type information to edges and nodes in a graph, and can be used in constraints in graphs where not all nodes represent the same thing. For example:

SELECT p.name
FROM MATCH (p:person) -[e1:likes]-> (m1:movie),
     MATCH (p) -[e2:likes]-> (m2:movie)
WHERE m1.title = 'Star Wars'
  AND m2.title = 'Avatar'

For more examples of label expressions, see the relevant section of the PGQL specification here.

5.5 Variable-Length Paths with PGQL

Variable-length path patterns have a quantifier like * to match a variable number of vertices and edges. Using a PATH macro, you can specify a named path pattern at the start of a query that can be embedded into the MATCH clause any number of times, by referencing its name. The following example finds all of the common ancestors of Mario and Luigi.

PATH has_parent AS () -[:has_father|has_mother]-> ()
SELECT ancestor.name
FROM MATCH (p1:Person) -/:has_parent*/-> (ancestor:Person)
   , MATCH (p2:Person) -/:has_parent*/-> (ancestor)
WHERE
  p1.name = 'Mario' AND
  p2.name = 'Luigi'

The preceding path specification also shows the use of anonymous constraints, because there is no need to define names for intermediate edges or nodes that will not be used in additional constraints or query results. Anonymous elements can have constraints, such as [:has_father|has_mother] -- the edge does not get a variable name (because it will not be referenced elsewhere), but it is constrained.

For more examples of variable-length path pattern matching, see the relevant section of the PGQL specification here.

5.6 Aggregation and Sorting with PGQL

Like SQL, PGQL has support for the following:

  • GROUP BY to create groups of solutions

  • MIN, MAX, SUM, and AVG aggregations

  • ORDER BY to sort results

And for many other familiar SQL constructs.

For GROUP BY and aggregation, see the relevant section of the PGQL specification here. For ORDER BY, see the relevant section of the PGQL specification here.

5.7 Executing PGQL Queries Against the In-Memory Graph Server (PGX)

This section describes the Java APIs that are used to execute PGQL queries in the In-Memory graph server (PGX).

5.7.1 Getting Started with PGQL

This section provides an example on how to get started with PGQL. It assumes a database realm that has been previously set up (follow the steps in 3.1.1 Prepare the Graph Server for Database Authentication). It also assumes that the user has read access to the HR schema.

First, create a graph with employees, departments, and employee works at department, by executing a CREATE PROPERTY GRAPH statement.

Example 5-1 Creating a graph in the in-memory graph server (PGX)

The following statement creates a graph in the in-memory graph server (PGX)

String statement =
      "CREATE PROPERTY GRAPH hr_simplified "
    + "  VERTEX TABLES ( "
    + "    hr.employees LABEL employee "
    + "      PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "
    + "    hr.departments LABEL department "
    + "      PROPERTIES ( department_id, department_name ) "
    + "  ) "
    + "  EDGE TABLES ( "
    + "    hr.employees AS works_at "
    + "      SOURCE KEY ( employee_id ) REFERENCES employees "
    + "      DESTINATION departments "
    + "      PROPERTIES ( employee_id ) "
    + "  )";
session.executePgql(statement);

/**
 * To get a handle to the graph, execute:
 */
PgxGraph g = session.getGraph("HR_SIMPLIFIED");

/**
 * You can use this handle to run PGQL queries on this graph.
 * For example, to find the department that “Nandita Sarchand” works for, execute:
 */
String query =
    "SELECT dep.department_name "
  + "FROM MATCH (emp:Employee) -[:works_at]-> (dep:Department) "
  + "WHERE emp.first_name = 'Nandita' AND emp.last_name = 'Sarchand' "
  + "ORDER BY 1";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();
+-----------------+
| department_name |
+-----------------+
| Shipping        |
+-----------------+

/**
 * To get an overview of the types of vertices and their frequencies, execute:
 */
String query =
      "SELECT label(n), COUNT(*) "
    + "FROM MATCH (n) "
    + "GROUP BY label(n) "
    + "ORDER BY COUNT(*) DESC";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();

+-----------------------+
| label(n)   | COUNT(*) |
+-----------------------+
| EMPLOYEE   | 107      |
| DEPARTMENT | 27       |
+-----------------------+

/**
  *To get an overview of the types of edges and their frequencies, execute:
  */
 String query =
    "SELECT label(n) AS srcLbl, label(e) AS edgeLbl, label(m) AS dstLbl, COUNT(*) "
  + "FROM MATCH (n) -[e]-> (m) "
  + "GROUP BY srcLbl, edgeLbl, dstLbl "
  + "ORDER BY COUNT(*) DESC";
PgqlResultSet resultSet = g.queryPgql(query);
resultSet.print();

+---------------------------------------------+
| srcLbl   | edgeLbl  | dstLbl     | COUNT(*) |
+---------------------------------------------+
| EMPLOYEE | WORKS_AT | DEPARTMENT | 106      |
+---------------------------------------------+

5.7.2 Supported PGQL Features

The In-Memory graph server (PGX) supports all PGQL features except DROP PROPERTY GRAPH.

Few features have certain limitations that are described below.

5.7.2.1 Limitations on Quantifiers

Although all quantifiers such as *, +, and {1,4} are supported for reachability patterns, the only quantifier that is supported for shortest and cheapest path patterns is * (zero or more).

5.7.2.2 Limitations on WHERE and COST Clauses in Quantified Patterns

The WHERE and COST clauses in quantified patterns, such as reachability patterns or shortest and cheapest path patterns, are limited to referencing a single variable only.

The following are examples of queries that are not supported because the WHERE or COST clauses reference two variables e and x instead of zero or one:

... PATH p AS (n) –[e]-> (m) WHERE e.prop > m.prop ...
... SHORTEST ( (n) (-[e]-> (x) WHERE e.prop + x.prop > 10)* (m) ) ...
... CHEAPEST ( (n) (-[e]-> (x) COST e.prop + x.prop )* (m) ) ...

The following query is supported because the subquery only references a single variable a from the outer scope, while the variable c does not count since it is newly introduced in the subquery:

... PATH p AS (a) -> (b)
      WHERE EXISTS ( SELECT * FROM MATCH (a) -> (c) ) ...

5.7.3 Java APIs for Executing CREATE PROPERTY GRAPH Statements

The easiest way to execute a CREATE PROPERTY GRAPH statement is through the PgxSession.executePgql(String statement) method.

Example 5-2 Executing a CREATE PROPERTY GRAPH statement

String statement =
      "CREATE PROPERTY GRAPH hr_simplified "
    + "  VERTEX TABLES ( "
    + "    hr.employees LABEL employee "
    + "      PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "
    + "    hr.departments LABEL department "
    + "      PROPERTIES ( department_id, department_name ) "
    + "  ) "
    + "  EDGE TABLES ( "
    + "    hr.employees AS works_at "
    + "      SOURCE KEY ( employee_id ) REFERENCES employees "
    + "      DESTINATION departments "
    + "      PROPERTIES ( employee_id ) "
    + "  )";
session.executePgql(statement);
PgxGraph g = session.getGraph("HR_SIMPLIFIED");

/**
 * Alternatively, one can use the prepared statement API, for example:
 */

PgxPreparedStatement stmnt = session.preparePgql(statement);
stmnt.execute();
stmnt.close();
PgxGraph g = session.getGraph("HR_SIMPLIFIED");

5.7.4 Java APIs for Executing SELECT Queries

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

5.7.4.1 Executing SELECT Queries Against a Graph in the In-memory Graph Server (PGX)

The PgxGraph.queryPgql(String query) method executes the query in the session that was used to create the PgxGraph. 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()).

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

5.7.4.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
  • getList(int columnIdx) : List<T>
  • getList(String columnName) : List<T>

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.

5.7.4.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                       |
+-----------------------------------------------+

5.7.5 Java APIs for Executing UPDATE Queries

The UPDATE queries make changes to existing graphs using the INSERT, UPDATE, and DELETE operations as detailed in the section Graph Modification of the PGQL 1.3 specification.

Note that INSERT allows you to insert new vertices and edges into a graph, UPDATE allows you to update existing vertices and edges by setting their properties to new values, and DELETE allows you to delete vertices and edges from a graph.

5.7.5.1 Executing UPDATE Queries against a Graph in the in-memory Graph Server (PGX)

To execute UPDATE queries against a graph, use the PgxGraph.executePgql(String query) method.

The following is an example of INSERT query:

g.executePgql("INSERT VERTEX v " +
              "         LABELS ( Person ) " +
              "         PROPERTIES ( v.firstName = 'Camille', " +
              "                      v.lastName = ' Mullins' ) "); 

Note that the INTO clause of the INSERT can be omitted. If you use an INTO clause, the graph name in the INTO clause must correspond to the name of the PGX graph (PgxGraph.getName()) that the query is executed against.

The following is an example of UPDATE query:

// set the date of birth of Camille to 2014-11-15
g.executePgql("UPDATE v SET ( v.dob = DATE '2014-11-14' ) " +
              "FROM MATCH (v:Person) " +
              "WHERE v.firstName = 'Camille' AND v.lastName = ' Mullins' "); 

The following is an example of DELETE query:

// delete Camille from the graph
g.executePgql("DELETE v " +
              "FROM MATCH (v:Person) " +
              "WHERE v.firstName = 'Camille' AND v.lastName = 'Mullins' "); 
5.7.5.2 Executing UPDATE Queries Against a PGX Session

For now, there is no support for executing UPDATE queries against a PgxSession and therefore, updates always have to be executed against a PgxGraph. To obtain a graph from a session, use the PgxSession.getGraph(String graphName) method.

5.7.5.3 Updatability of Graphs Through PGQL

Graph data that is loaded from the Oracle RDBMS or from CSV files into the PGX is not updatable through PGQL right away.

First, you need to create a copy of the data through the PgxGraph.clone() method. The resulting graph is fully updatable.

Consider the following example:

// load a graph from the RDBMS or from CSV
PgxGraph g1 = session.readGraphWithProperties("path/to/graph_config.json");

// create an updatable copy of the graph
PgxGraph g2 = g1.clone("new_graph_name");

// insert an additional vertex into the graph
g2.executePgql("INSERT VERTEX v " +
               "         LABELS ( Person ) " +
               "         PROPERTIES ( v.firstName = 'Camille', " +
               "                      v.lastName = ' Mullins')"); 

Additionally, there is also a PgxGraph.cloneAndExecutePgql(String query, String graphName) method that combines the last two steps from above example into a single step:

// create an updatable copy of the graph while inserting a new vertex
PgxGraph g2_copy = g1.cloneAndExecutePgql(
                     "INSERT VERTEX v " +
                     "         LABELS ( Person ) " +
                     "         PROPERTIES ( v.firstName = 'Camille', " +
                     "                      v.lastName = ' Mullins') "
                   , "new_graph_name");

Note that graphs that are created through PgxGraph.clone() are local to the session. However, they can be shared with other sessions through the PgxGraph.publish(..) methods but then they are no longer updatable through PGQL. Only session-local graphs are updatable but persistent graphs are not.

5.7.5.4 Altering the Underlying Schema of a Graph

The INSERT operations can only insert vertices and edges with known labels and properties. Similarly, UPDATE operations can only set values of known properties. Thus, new data must always conform to the existing schema of the graph.

However, some PGX APIs exist for updating the schema of a graph: while no APIs exist for adding new labels, new properties can be added through the PgxGraph.createVertexProperty(PropertyType type, String name) and PgxGraph.createEdgeProperty(PropertyType type, String name) methods. The new properties are attached to each vertex/edge in the graph, irrespective of their labels. Initially the properties are assigned a default value but then the values can be updated through the UPDATE statements.

Consider the following example:

// load a graph from the RDBMS or from CSV
PgxGraph g = session.readGraphWithProperties("path/to/graph_config.json");

// add a new property to the graph
g.createVertexProperty(PropertyType.LOCAL_DATE, "dob");

// set the date of birth of Camille to 2014-11-15
g.executePgql("UPDATE v SET ( v.dob = DATE '2014-11-14' ) " +
              "FROM MATCH (v:Person) " +
              "WHERE v.firstName = 'Camille' AND v.lastName = ' Mullins' ");

5.7.6 Security Tools for Executing PGQL Queries

To safeguard against query injection, bind variables can be used in place of literals while printIdentifier(String identifier) can be used in place of identifiers like graph names, labels, and property names.

5.7.6.1 Using Bind Variables

There are two reasons for using bind variables:

  • It protects against query injection.
  • It speeds up query execution because the same bind variables can be set multiple times without requiring recompilation of the query.

To create a prepared statement, use one of the following two methods:

  • PgxGraph.preparePgql(String query) : PgxPreparedStatement
  • PgxSession.preparePgql(String query) : PgxPreparedStatement

The PgxPreparedStatement (package oracle.pgx.api) returned from these methods have setter methods for binding the bind variables to values of the designated data type.

Consider the following example:
PreparedStatement stmnt = g.preparePgql(
  "SELECT v.id, v.dob " +
  "FROM MATCH (v) " +
  "WHERE v.firstName = ? AND v.lastName = ?");
stmnt.setString(1, "Camille");
stmnt.setString(2, "Mullins");
ResultSet rs = stmnt.executeQuery();

Each bind variable in the query needs to be set to a value using one of the following setters of PgxPreparedStatement:

  • setBoolean(int parameterIndex, boolean x)
  • setDouble(int parameterIndex, double x)
  • setFloat(int parameterIndex, float x)
  • setInt(int parameterIndex, int x)
  • setLong(int parameterIndex, long x)
  • setDate(int parameterIndex, LocalDate x)
  • setTime(int parameterIndex, LocalTime x)
  • setTimestamp(int parameterIndex, LocalDateTime x)
  • setTimeWithTimezone(int parameterIndex, OffsetTime x)
  • setTimestampWithTimezone(int parameterIndex, OffsetDateTime x)
  • setArray(int parameterIndex, List<?> x)

Once all the bind variables are set, the statement can be executed through:

  • PgxPreparedStatement.executeQuery()
    • For SELECT queries only
    • Returns a ResultSet
  • PgxPreparedStatement.execute()
    • For any type of statement
    • Returns a Boolean to indicate the form of the result: true in case of a SELECT query, false otherwise
    • In case of SELECT, the ResultSet can afterwards be accessed through PgxPreparedStatement.getResultSet()

In PGQL, bind variables can be used in place of literals of any data type, including array literals. An example query with a bind variable to is set to an instance of a String array is:

List<String> countryNames = new ArrayList<String>();
countryNames.add("Scotland");
countryNames.add("Tanzania");
countryNames.add("Serbia");

PreparedStatement stmnt = g.preparePgql(
  "SELECT n.name, n.population " +
  "FROM MATCH (c:Country) " +
  "WHERE c.name IN ?");

ResultSet rs = stmnt.executeQuery();

Finally, if a prepared statement is no longer needed, it is closed through PgxPreparedStatement.close() to free up resources.

5.7.6.2 Using Identifiers in a Safe Manner

When you create a query through string concatenation, not only literals in queries pose a security risk, but also identifiers like graph names, labels, and property names do. The only problem is that bind variables are not supported for such identifier. Therefore, if these identifiers are variable from the application's perspective, then it is recommended to protect against query injection by passing the identifier through the oracle.pgql.lang.ir.PgqlUtils.printIdentifier(String identifier) method.

Given an identifier string, the method automatically adds double quotes to the start and end of the identifier and escapes the characters in the identifier appropriately.

Consider the following example:

String graphNamePrinted = printIdentifier("my graph name with \" special % characters ");
PreparedStatement stmnt = g.preparePgql(
  "SELECT COUNT(*) AS numVertices FROM MATCH (v) ON " + graphNamePrinted);

5.7.7 Best Practices for Tuning PGQL Queries

This section describes best practices regarding memory allocation, parallelism, and query planning.

5.7.7.1 Memory Allocation

The In-Memory Analyst (PGX) has on-heap and off-heap memory, the earlier being the standard JVM heap while the latter being a separate heap that is managed by PGX. Just like graph data, intermediate and final results of PGQL queries are partially stored on-heap and partially off-heap. Therefore, both heaps are needed.

In case of the on-heap memory, the default maximum is chosen upon startup of the JVM, but it can be overwritten through the -Xmx option.

In case of the off-heap, there is no maximum set by default and the off-heap memory usage, therefore, keeps increasing automatically until it depletes the system resources, in which case the operation is cancelled, it's memory is released, and an appropriate exception is passed to the user. If needed, a maximum off-heap size can be configured through the max_off_heap_size option of PGX.

A ratio of 1:1 for on-heap vs. off-heap is recommended as a good starting point to allow for the largest possible graphs to be loaded and queried. For example, if you have 256 GB of memory available on your machine, then setting the maximum on-heap to 125 GB will make sure that there is a similar amount of memory available for off-heap:

export JAVA_OPTS="-Xmx125g"
5.7.7.2 Parallelism

By default, all available processor threads are used to process PGQL queries. However, if needed, the number of threads can be limited by setting the parallelism option of the In-Memory Analyst (PGX).

5.7.7.3 Query Plan Explaining

The PgxGraph.explainPgql(String query) method is used to get insight into the query plan of the query. The method returns an instance of Operation (package oracle.pgx.api) which has the following methods:

  • print(): for printing the operation and its child operations
  • getOperationType(): for getting the type of the operation
  • getPatternInfo(): for getting a string representation of the operation
  • getCostEstimate(): for getting the cost of the operation
  • getTotalCostEstimate(): for getting the cost of the operations and its child operations
  • getCardinatlityEstimate(): for getting the expected number of result rows
  • getChildren(): for accessing the child operations

Consider the following example:

g.explainPgql("SELECT COUNT(*) FROM MATCH (n) -[e1]-> (m) -[e2]-> (o)").print()
\--- GROUP BY  GroupBy {"cardinality":"42", "cost":"42", "accumulatedCost":"58.1"}
     \--- (m) -[e2]-> (o) NeighborMatch {"cardinality":"3.12", "cost":"3.12", "accumulatedCost":"16.1"}
          \--- (n) -[e1]-> (m) NeighborMatch {"cardinality":"5", "cost":"5", "accumulatedCost":"13"}
               \--- (n) RootVertexMatch {"cardinality":"8", "cost":"8", "accumulatedCost":"8"}

In the above example, the print() method is used to print the query plan.

If a query plan is not optimal, it is often possible to rewrite the query to improve its performance. For example, a SELECT query may be split into an UPDATE and a SELECT query as a way to improve the total runtime.

Note that the In-Memory Analyst (PGX) does not provide a hint mechanism.

5.8 Executing PGQL Queries Directly Against Oracle Database

This topic explains how you can execute PGQL queries directly against the graph in Oracle Database (as opposed to in-memory).

Property Graph Query Language (PGQL) queries can be executed against disk-resident property graph data stored in Oracle Database. PGQL on Oracle Database (RDBMS) provides a Java API for executing PGQL queries. Logic in PGQL on RDBMS translates a submitted PGQL query into an equivalent SQL query, and the resulting SQL is executed on the database server. PGQL on RDBMS then wraps the SQL query results with a convenient PGQL result set API.

This PGQL query execution flow is shown in the following figure.

Figure 5-1 PGQL on Oracle Database (RDBMS)

Description of Figure 5-1 follows
Description of "Figure 5-1 PGQL on Oracle Database (RDBMS)"

The basic execution flow is:

  1. The PGQL query is submitted to PGQL on RDBMS through a Java API.

  2. The PGQL query is translated to SQL.

  3. The translated SQL is submitted to Oracle Database by JDBC.

  4. The SQL result set is wrapped as a PGQL result set and returned to the caller.

The ability to execute PGQL queries directly against property graph data stored in Oracle Database provides several benefits.

  • PGQL provides a more natural way to express graph queries than SQL manually written to query schema tables, including VT$, VD$, GE$, and GT$.

  • PGQL queries can be executed without the need to load a snapshot of your graph data into PGX, so there is no need to worry about staleness of frequently updated graph data.

  • PGQL queries can be executed against graph data that is too large to fit in memory.

  • The robust and scalable Oracle SQL engine can be used to execute PGQL queries.

  • Mature tools for management, monitoring and tuning of Oracle Database can be used to tune and monitor PGQL queries.

5.8.1 PGQL Features Supported

PGQL is a SQL-like query language for querying property graph data. It is based on the concept of graph pattern matching and allows you to specify, among other things, topology constraints, paths, filters, sorting and aggregation.

The Java API for PGQL defined in the oracle.pg.rdbms.pgql package supports the PGQL specification with a few exceptions. (The PGQL specification can be found at https://pgql-lang.org).

The following features of PGQL are not supported.

  • Shortest path
  • ARRAY_AGG aggregation
  • IN and NOT IN predicates
  • Single CHEAPEST path and TOP-K CHEAPEST path using COST functions
  • Case-insensitive matching of uppercased references to labels and properties

In addition, the following features of PGQL require special consideration.

5.8.1.1 Temporal Types

The temporal types DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE are supported in PGQL queries.

All of these value types are represented internally using the Oracle SQL TIMESTAMP WITH TIME ZONE type. DATE values are automatically converted to TIMESTAMP WITH TIME ZONE by assuming the earliest time in UTC+0 timezone (for example, 2000-01-01 becomes 2000-01-01 00:00:00.00+00:00). TIMESTAMP values are automatically converted to TIMESTAMP WITH TIME ZONE by assuming UTC+0 timezone (for example, 2000-01-01 12:00:00.00 becomes 2000-01-01 12:00:00.00+00:00).

Temporal constants are written in PGQL queries as follows.

  • DATE 'YYYY-MM-DD'

  • TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

  • TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM'

Some examples are DATE '2000-01-01', TIMESTAMP '2000-01-01 14:01:45.23', TIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00-05:00', and TIMESTAMP WITH TIMEZONE '2000-01-01 13:00:00.00+01:00'.

In addition, temporal values can be obtained by casting string values to a temporal type. The supported string formats are:

  • DATE 'YYYY-MM-DD'

  • TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' and 'YYYY-MM-DD"T"HH24:MI:SS.FF'

  • TIMESTAMP WITH TIMEZONE 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM' and 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'.

Some examples are CAST ('2005-02-04' AS DATE), CAST ('1990-01-01 12:00:00.00' AS TIMESTAMP), CAST ('1985-01-01T14:05:05.00-08:00' AS TIMESTAMP WITH TIMEZONE).

When consuming results from a PgqlResultSet object, getObject returns a java.sql.Timestamp object for temporal types.

Bind variables can only be used for the TIMESTAMP WITH TIMEZONE temporal type in PGQL, and a setTimestamp method that takes a java.sql.Timestamp object as input is used to set the bind value. As a simpler alternative, you can use a string bind variable in a CAST statement to bind temporal values (for example, CAST (? AS TIMESTAMP WITH TIMEZONE) followed by setString(1, "1985-01-01T14:05:05.00-08:00")). See also Using Bind Variables in PGQL Queries for more information about bind variables.

5.8.1.2 Type Casting

Type casting is supported in PGQL with a SQL-style CAST (VALUE AS DATATYPE) syntax, for example CAST('25' AS INT), CAST (10 AS STRING), CAST ('2005-02-04' AS DATE), CAST(e.weight AS STRING). Supported casting operations are summarized in the following table. Y indicates that the conversion is supported, and N indicates that it is not supported. Casting operations on invalid values (for example, CAST('xyz' AS INT)) or unsupported conversions (for example, CAST (10 AS TIMESTAMP)) return NULL instead of raising a SQL exception.

Table 5-1 Type Casting Support in PGQL (From and To Types)

“to” type from STRING from INT from LONG from FLOAT from DOUBLE from BOOLEAN from DATE from TIMESTAMP from TIMESTAMP WITH TIMEZONE

to STRING

Y

Y

Y

Y

Y

Y

Y

Y

Y

to INT

Y

Y

Y

Y

Y

Y

N

N

N

to LONG

Y

Y

Y

Y

Y

Y

N

N

N

to FLOAT

Y

Y

Y

Y

Y

Y

N

N

N

to DOUBLE

Y

Y

Y

Y

Y

Y

N

N

N

to BOOLEAN

Y

Y

Y

Y

Y

Y

N

N

N

to DATE

Y

N

N

N

N

N

Y

Y

Y

to TIMESTAMP

Y

N

N

N

N

N

Y

Y

Y

to TIMESTAMP WITH TIMEZONE

Y

N

N

N

N

N

Y

Y

Y

An example query that uses type casting is:

SELECT e.name, CAST (e.birthDate AS STRING) AS dob
FROM MATCH (e)
WHERE e.birthDate < CAST ('1980-01-01' AS DATE)
5.8.1.3 CONTAINS Built-in Function

A CONTAINS built-in function is supported. It is used in conjunction with an Oracle Text index on vertex and edge properties. CONTAINS returns true if a value matches an Oracle Text search string and false if it does not match.

An example query is:

SELECT v.name
FROM MATCH (v)
WHERE CONTAINS(v.abstract, 'Oracle')

See also Using a Text Index with PGQL Queries for more information about using full text indexes with PGQL.

5.8.2 Creating Property Graphs through CREATE PROPERTY GRAPH Statements

You can use PGQL to create property graphs from relational database tables. A CREATE PROPERTY GRAPH statement defines a set of vertex tables that are transformed into vertices and a set of edge tables that are transformed into edges. For each table a key, a label and a set of column properties can be specified. The column types CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 , NUMBER, LONG, FLOAT, DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE are supported for CREATE PROPERTY GRAPH column properties.

When a CREATE PROPERTY GRAPH statement is called, a property graph schema for the graph is created, and the data is copied from the source tables into the property graph schema tables. The graph is created as a one-time copy and is not automatically kept in sync with the source data.

Example 5-3 PgqlCreateExample1.java

This example shows how to create a property graph from a set of relational tables. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example. The example also shows how to execute a query against a property graph.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to create a Property Graph from relational
 * data stored in Oracle Database executing a PGQL create statement.
 */
public class PgqlCreateExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Statement stmt = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Create relational data
      stmt = conn.createStatement();

      //Table Person
      stmt.executeUpdate(
        "create table Person(  " +
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  dob   TIMESTAMP     " +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
      stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
      stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");

      // Table Hobby
      stmt.executeUpdate(
        "create table Hobby(   " + 
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20)  " +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
      stmt.executeUpdate("insert into Hobby values(2, 'Music')");

      // Table Hobbies
      stmt.executeUpdate(
        "create table Hobbies( "+
        "  person    NUMBER, "+
        "  hobby     NUMBER, "+
        "  strength  NUMBER  "+
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
      stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
      stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
      stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");

      //Commit changes
      conn.commit();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to create property graph
      String pgql = 
        "Create Property Graph " + graph + " " +
        "VERTEX TABLES ( " +
        "  Person " +
        "    Key(id) " +
        "    Label \"people\" +
        "    PROPERTIES(name AS \"first_name\", dob AS \"birthday\")," +
        "  Hobby  " +
        "    Key(id) Label \"hobby\"  PROPERTIES(name AS \"name\")" +
        ")" +
        "EDGE TABLES (" +
        "  Hobbies" +
        "    SOURCE KEY(person) REFERENCES Person " +
        "    DESTINATION KEY(hobby) REFERENCES Hobby " +
        "    LABEL \"likes\" PROPERTIES (strength AS \"score\")" +
        ")";
      pgqlStmt.execute(pgql);

      // Execute a PGQL query to verify Graph creation
      pgql =
        "SELECT p.\"first_name\", p.\"birthday\", h.\"name\", e.\"score\" " +
        "FROM MATCH (p:\"people\")-[e:\"likes\"]->(h:\"hobby\") ON " + graph;
      rs = pgqlStmt.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the sql statment
      if (stmt != null) {
        stmt.close();
      }
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlCreateExample1.java is:

+---------------------------------------------------------+
|   first_name |   birthday            |   name |   score |
+---------------------------------------------------------+
| Alan         | 1995-05-25 17:00:00.0 | Music  | 30.0    |
| Claire       | 1967-11-29 16:00:00.0 | Music  | 20.0    |
| Ben          | 2007-02-14 16:00:00.0 | Sports | 10.0    |
| Alan         | 1995-05-25 17:00:00.0 | Sports | 20.0    |
+---------------------------------------------------------+

Example 5-4 PgqlCreateExample2.java

This example shows how a create property graph statement without specifying any keys. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to create a Property Graph from relational
 * data stored in Oracle Database executing a PGQL create statement.
 */
public class PgqlCreateExample2
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Statement stmt = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Create relational data
      stmt = conn.createStatement();

      //Table Person
      stmt.executeUpdate(
        "create table Person(  " +
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  dob   TIMESTAMP,    " +
        "  CONSTRAINT pk_person PRIMARY KEY(id)" +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
      stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
      stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");

      // Table Hobby
      stmt.executeUpdate(
        "create table Hobby(   " + 
        "  id    NUMBER,       " +
        "  name  VARCHAR2(20), " +
        "  CONSTRAINT pk_hobby PRIMARY KEY(id)" +
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
      stmt.executeUpdate("insert into Hobby values(2, 'Music')");

      // Table Hobbies
      stmt.executeUpdate(
        "create table Hobbies( "+
        "  person    NUMBER, "+
        "  hobby     NUMBER, "+
        "  strength  NUMBER, "+
        "  CONSTRAINT fk_hobbies1 FOREIGN KEY (person) REFERENCES Person(id), "+
        "  CONSTRAINT fk_hobbies2 FOREIGN KEY (hobby)  REFERENCES Hobby(id)"+
        ")");

      // Insert some data
      stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
      stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
      stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
      stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");

      //Commit changes
      conn.commit();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to create property graph
      String pgql = 
        "Create Property Graph " + graph + " " +
        "VERTEX TABLES ( " +
        "  Person " +
        "    Label people +
        "    PROPERTIES ALL COLUMNS," +
        "  Hobby  " +
        "    Label hobby PROPERTIES ALL COLUMNS EXCEPT(id)" +
        ")" +
        "EDGE TABLES (" +
        "  Hobbies" +
        "    SOURCE Person DESTINATION Hobby " +
        "    LABEL likes NO PROPERTIES" +
        ")";
      pgqlStmt.execute(pgql);

      // Execute a PGQL query to verify Graph creation
      pgql =
        "SELECT p.NAME AS person, p.DOB, h.NAME AS hobby " +
        "FROM MATCH (p:people)-[e:likes]->(h:hobby) ON " + graph;
      rs = pgqlStmt.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the sql statment
      if (stmt != null) {
        stmt.close();
      }
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlCreateExample2.java is:

+-----------------------------------------+
| PERSON |   DOB                 |  HOBBY |
+-----------------------------------------+
| Alan   | 1995-05-25 17:00:00.0 | Music  |
| Claire | 1967-11-29 16:00:00.0 | Music  |
| Ben    | 2007-02-14 16:00:00.0 | Sports |
| Alan   | 1995-05-25 17:00:00.0 | Sports |
+-----------------------------------------+

5.8.3 Dropping Property Graphs through DROP PROPERTY GRAPH Statements

You can use PGQL to drop property graphs. When a DROP PROPERTY GRAPH statement is called, all the property graph schema tables of the graph are dropped.

Example 5-5 PgqlDropExample1.java

This example shows how to drop a property graph.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to drop a Property executing a PGQL drop statement.
 */
public class PgqlDropExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PgqlStatement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL to drop property graph
      String pgql = "Drop Property Graph " + graph;
      pgqlStmt.execute(pgql);

    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
        pgqlStmt.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

5.8.4 Using the oracle.pg.rdbms.pgql Java Package to Execute PGQL Queries

The Java API in the oracle.pg.rdbms.pgql package provides support for executing PGQL queries against Oracle Database. This topic explains how to use the Java API through a series of examples.

Note:

Effective with Release 20c, the following classes in the oracle.pg.rdbms package are deprecated:

oracle.pg.rdbms.OraclePgqlColumnDescriptorImpl
oracle.pg.rdbms.OraclePgqlColumnDescriptor
oracle.pg.rdbms.OraclePgqlExecutionFactory
oracle.pg.rdbms.OraclePgqlExecution
oracle.pg.rdbms.PgqlPreparedStatement
oracle.pg.rdbms.OraclePgqlResultElementImpl
oracle.pg.rdbms.OraclePgqlResultElement
oracle.pg.rdbms.OraclePgqlResultImpl
oracle.pg.rdbms.OraclePgqlResultIterable
oracle.pg.rdbms.OraclePgqlResultIteratorImpl
oracle.pg.rdbms.OraclePgqlResult
oracle.pg.rdbms.OraclePgqlResultSetImpl
oracle.pg.rdbms.OraclePgqlResultSet
oracle.pg.rdbms.OraclePgqlResultSetMetaDataImpl
oracle.pg.rdbms.OraclePgqlResultSetMetaData
oracle.pg.rdbms.PgqlSqlQueryTransImpl
oracle.pg.rdbms.PgqlSqlQueryTrans
oracle.pg.rdbms.PgqlStatement

You should instead use equivalent classes in oracle.pg.rdbms.pgql:

oracle.pg.rdbms.pgql.PgqlColumnDescriptorImpl
oracle.pg.rdbms.pgql.PgqlColumnDescriptor
oracle.pg.rdbms.pgql.PgqlConnection
oracle.pg.rdbms.pgql.PgqlExecution
oracle.pg.rdbms.pgql.PgqlPreparedStatement
oracle.pg.rdbms.pgql.PgqlResultElementImpl
oracle.pg.rdbms.pgql.PgqlResultElement
oracle.pg.rdbms.pgql.PgqlResultSetImpl
oracle.pg.rdbms.pgql.PgqlResultSet
oracle.pg.rdbms.pgql.PgqlResultSetMetaDataImpl
oracle.pg.rdbms.pgql.PgqlSqlTransImpl
oracle.pg.rdbms.pgql.PgqlSqlTrans
oracle.pg.rdbms.pgql.PgqlStatement

One difference between oracle.pg.rdbms.OraclePgqlResultSet and oracle.pg.rdbms.pgql.PgqlResultSet is that oracle.pg.rdbms.pgql.PgqlResultSet does not provide APIs to retrieve vertex and edge objects. Existing code using those interfaces should be changed to project IDs rather than OracleVertex and OracleEdge objects. You can obtain an OracleVertex or OracleEdge object from the projected ID values by calling OracleVertex.getInstance() or OracleEdge.getInstance(). (For an example, see Example 5-20.)

The following test_graph data set in Oracle flat file format will be used in the examples in subtopics that follow. The data set includes a vertex file (test_graph.opv) and an edge file (test_graph.ope).

test_graph.opv:

2,fname,1,Ray,,,person
2,lname,1,Green,,,person
2,mval,5,,,1985-01-01T12:00:00.000Z,person
2,age,2,,41,,person
0,bval,6,Y,,,person
0,fname,1,Bill,,,person
0,lname,1,Brown,,,person
0,mval,1,y,,,person
0,age,2,,40,,person
1,bval,6,Y,,,person
1,fname,1,John,,,person
1,lname,1,Black,,,person
1,mval,2,,27,,person
1,age,2,,30,,person
3,bval,6,N,,,person
3,fname,1,Susan,,,person
3,lname,1,Blue,,,person
3,mval,6,N,,,person
3,age,2,,35,,person

test_graph.ope:

4,0,1,knows,mval,1,Y,,
4,0,1,knows,firstMetIn,1,MI,,
4,0,1,knows,since,5,,,1990-01-01T12:00:00.000Z
16,0,1,friendOf,strength,2,,6,
7,1,0,knows,mval,5,,,2003-01-01T12:00:00.000Z
7,1,0,knows,firstMetIn,1,GA,,
7,1,0,knows,since,5,,,2000-01-01T12:00:00.000Z
17,1,0,friendOf,strength,2,,7,
9,1,3,knows,mval,6,N,,
9,1,3,knows,firstMetIn,1,SC,,
9,1,3,knows,since,5,,,2005-01-01T12:00:00.000Z
10,2,0,knows,mval,1,N,,
10,2,0,knows,firstMetIn,1,TX,,
10,2,0,knows,since,5,,,1997-01-01T12:00:00.000Z
12,2,3,knows,mval,3,,342.5,
12,2,3,knows,firstMetIn,1,TX,,
12,2,3,knows,since,5,,,2011-01-01T12:00:00.000Z
19,2,3,friendOf,strength,2,,4,
14,3,1,knows,mval,1,a,,
14,3,1,knows,firstMetIn,1,CA,,
14,3,1,knows,since,5,,,2010-01-01T12:00:00.000Z
15,3,2,knows,mval,1,z,,
15,3,2,knows,firstMetIn,1,CA,,
15,3,2,knows,since,5,,,2004-01-01T12:00:00.000Z
5,0,2,knows,mval,2,,23,
5,0,2,knows,firstMetIn,1,OH,,
5,0,2,knows,since,5,,,2002-01-01T12:00:00.000Z
6,0,3,knows,mval,3,,159.7,
6,0,3,knows,firstMetIn,1,IN,,
6,0,3,knows,since,5,,,1994-01-01T12:00:00.000Z
8,1,2,knows,mval,6,Y,,
8,1,2,knows,firstMetIn,1,FL,,
8,1,2,knows,since,5,,,1999-01-01T12:00:00.000Z
18,1,3,friendOf,strength,2,,5,
11,2,1,knows,mval,2,,1001,
11,2,1,knows,firstMetIn,1,OK,,
11,2,1,knows,since,5,,,2003-01-01T12:00:00.000Z
13,3,0,knows,mval,5,,,2001-01-01T12:00:00.000Z
13,3,0,knows,firstMetIn,1,CA,,
13,3,0,knows,since,5,,,2006-01-01T12:00:00.000Z
20,3,1,friendOf,strength,2,,3,
5.8.4.1 Basic Query Execution

Two main Java Interfaces, PgqlStatement and PgqlResultSet, are used for PGQL execution. This topic includes several examples of basic query execution.

Example 5-6 GraphLoaderExample.java

GraphLoaderExample.java loads some Oracle property graph data that will be used in subsequent examples in this topic.

import oracle.pg.rdbms.Oracle; 
import oracle.pg.rdbms.OraclePropertyGraph; 
import oracle.pg.rdbms.OraclePropertyGraphDataLoader;

/**
 * This example shows how to create an Oracle Property Graph 
 * and load data into it from vertex and edge flat files.
 */
public class GraphLoaderExample
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];
    String vertexFile         = args[idx++];
    String edgeFile           = args[idx++];

    Oracle oracle = null;
    OraclePropertyGraph opg = null;

    try {
      // Create a connection to Oracle
      oracle = new Oracle("jdbc:oracle:thin:@"+host+":"+port +":"+sid, user, password);

      // Create a property graph
      opg = OraclePropertyGraph.getInstance(oracle, graph);

      // Clear any existing data
      opg.clearRepository();

      // Load data from opv and ope files
      OraclePropertyGraphDataLoader opgLoader = OraclePropertyGraphDataLoader.getInstance();
      opgLoader.loadData(opg, vertexFile, edgeFile, 1);

      System.out.println("Vertices loaded:" + opg.countVertices());
      System.out.println("Edges loaded:" + opg.countEdges());

    }
    finally {
      // close the property graph
      if (opg != null) {
        opg.close();
      }
      // close oracle
      if (oracle != null) {
        oracle.dispose();
      }
    }
  }
}

GraphLoaderExample.java gives the following output for test_graph.

Vertices loaded:4
Edges loaded:17

Example 5-7 PgqlExample1.java

PgqlExample1.java executes a PGQL query and prints the query result. PgqlConnection is used to obtain a PgqlStatement. Next, it calls the executeQuery method of PgqlStatement, which returns a PgqlResultSet object. PgqlResultSet provides a print() method, which shows results in a tabular mode.

The PgqlResultSet and PgqlStatement objects should be closed after consuming the query result.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a basic PGQL query against disk-resident 
 * PG data stored in Oracle Database and iterate through the result.
 */
public class PgqlExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a PgqlResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname, v.\"mval\" AS mval "+
        "FROM MATCH (v)";
      rs = ps.executeQuery(pgql, /* query string */ 
                           ""    /* options */);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample1.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+---------------------------------------+
| FNAME | LNAME | MVAL                  |
+---------------------------------------+
| Susan | Blue  | false                 |
| Bill  | Brown | y                     |
| Ray   | Green | 1985-01-01 04:00:00.0 |
| John  | Black | 27                    |
+---------------------------------------+

Example 5-8 PgqlExample2.java

PgqlExample2.java shows a PGQL query with a temporal filter on an edge property.

  • PgqlResultSet provides an interface for consuming the query result that is very similar to the java.sql.ResultSet interface.
  • A next() method allows moving through the query result, and a close() method allows releasing resources after the application is fiished reading the query result.
  • In addition, PgqlResultSet provides getters for String, Integer, Long, Float, Double, Boolean, LocalDateTime, and OffsetDateTime, and it provides a generic getObject() method for values of any type.
import java.sql.Connection;

import java.text.SimpleDateFormat;

import java.util.Date;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.pgql.lang.ResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with a temporal edge 
 * property filter against disk-resident PG data stored in Oracle Database 
 * and iterate through the result.
 */
public class PgqlExample2
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    ResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS n1, v2.\"fname\" AS n2, e.\"firstMetIn\" AS loc "+
        "FROM MATCH (v)-[e:\"knows\"]->(v2) "+
        "WHERE e.\"since\" > TIMESTAMP '2000-01-01 00:00:00.00+00:00'";
      rs = ps.executeQuery(pgql, "");

      // Print results
      printResults(rs);
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Prints a PGQL ResultSet
   */
  static void printResults(ResultSet rs) throws Exception
  {
    StringBuffer buff = new StringBuffer("");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
    while (rs.next()) {
      buff.append("[");
      for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
        // use generic getObject to handle all types
        Object mval = rs.getObject(i);
        String mStr = "";
        if (mval instanceof java.lang.String) {
          mStr = "STRING: "+mval.toString();
        }
        else if (mval instanceof java.lang.Integer) {
          mStr = "INTEGER: "+mval.toString();
        }
        else if (mval instanceof java.lang.Long) {
          mStr = "LONG: "+mval.toString();
        }
        else if (mval instanceof java.lang.Float) {
          mStr = "FLOAT: "+mval.toString();
        }
        else if (mval instanceof java.lang.Double) {
          mStr = "DOUBLE: "+mval.toString();
        }
        else if (mval instanceof java.sql.Timestamp) {
          mStr = "DATE: "+sdf.format((Date)mval);
        }
        else if (mval instanceof java.lang.Boolean) {
          mStr = "BOOLEAN: "+mval.toString();
        }
        if (i > 1) {
          buff.append(",\t");
        }
        buff.append(mStr);
      }
      buff.append("]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample2.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

[STRING: Susan, STRING: Bill,   STRING: CA]
[STRING: Susan, STRING: John,   STRING: CA]
[STRING: Susan, STRING: Ray,    STRING: CA]
[STRING: Bill,  STRING: Ray,    STRING: OH]
[STRING: Ray,   STRING: John,   STRING: OK]
[STRING: Ray,   STRING: Susan,  STRING: TX]
[STRING: John,  STRING: Susan,  STRING: SC]
[STRING: John,  STRING: Bill,   STRING: GA]

Example 5-9 PgqlExample3.java

PgqlExample3.java shows a PGQL query with grouping and aggregation.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with aggregation 
 * against disk-resident PG data stored in Oracle Database and iterate 
 * through the result.
 */
public class PgqlExample3
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS \"fname\", COUNT(v2) AS \"friendCnt\" "+
        "FROM MATCH (v)-[e:\"friendOf\"]->(v2) "+
        "GROUP BY v "+
        "ORDER BY \"friendCnt\" DESC";
      rs = ps.executeQuery(pgql, "");

      // Print results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample3.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+-------------------+
| fname | friendCnt |
+-------------------+
| John  | 2         |
| Bill  | 1         |
| Ray   | 1         |
| Susan | 1         |
+-------------------+

Example 5-10 PgqlExample4.java

PgqlExample4.java shows a PGQL path query.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a path query in PGQL against 
 * disk-resident PG data stored in Oracle Database and iterate 
 * through the result.
 */
public class PgqlExample4
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

  // Execute query to get a ResultSet object
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"|\"knows\"]->() "+
        "SELECT v2.\"fname\" AS friend "+
        "FROM MATCH (v)-/:fof*/->(v2) "+
        "WHERE v.\"fname\" = 'John' AND v != v2";
      rs = ps.executeQuery(pgql, "");

      // Print results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample4.java gives the following output for test_graph(which can be loaded using GraphLoaderExample.java code).

+--------+
| FRIEND |
+--------+
| Susan  |
| Bill   |
| Ray    |
+--------+
5.8.4.2 Security Techniques for PGQL Queries

Programs executing dynamic queries might be subject to injection attacks that could compromise integrity and functioning of the applications.

This topic presents some techniques that can be used to prevent injection attacks when building PGQL queries using string concatenation.

5.8.4.2.1 Using Bind Variables in PGQL Queries

Bind variables can be used in PGQL queries for better performance and increased security. Constant scalar values in PGQL queries can be replaced with bind variables. Bind variables are denoted by a '?' (question mark). Consider the following two queries that select people who are older than a constant age value.

// people older than 30
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > 30

// people older than 40
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v) 
WHERE v.age > 40

The SQL translations for these queries would use the constants 30 and 40 in a similar way for the age filter. The database would perform a hard parse for each of these queries. This hard parse time can often exceed the execution time for simple queries.

You could replace the constant in each query with a bind variable as follows.

SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > ?

This will allow the SQL engine to create a generic cursor for this query, which can be reused for different age values. As a result, a hard parse is no longer required to execute this query for different age values, and the parse time for each query will be drastically reduced.

In addition, applications that use bind variables in PGQL queries are less vulnerable to injection attacks than those that use string concatenation to embed constant values in PGQL queries.

See also Oracle Database SQL Tuning Guide for more information on cursor sharing and bind variables.

The PgqlPreparedStatement interface can be used to execute queries with bind variables as shown in PgqlExample5.java. PgqlPreparedStatement provides several set methods for different value types that can be used to set values for query execution.

There are a few limitations with bind variables in PGQL. Bind variables can only be used for constant property values. That is, vertices and edges cannot be replaced with bind variables. Also, once a particular bind variable has been set to a type, it cannot be set to a different type. For example, if setInt(1, 30) is executed for an PgqlPreparedStatement, you cannot call setString(1, "abc") on that same PgqlPreparedStatement.

Example 5-11 PgqlExample5.java

PgqlExample5.java shows how to use bind variables with a PGQL query.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use bind variables with a PGQL query.
 */
public class PgqlExample5
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Query string with a bind variable (denoted by ?)
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname, v.\"age\" AS age "+
        "FROM MATCH (v) "+
        "WHERE v.\"age\" > ?";

      // Create a PgqlPreparedStatement
      pps = pgqlConn.prepareStatement(pgql);

      // Set filter value to 30
      pps.setInt(1, 30);

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for v.\"age\" > 30 --");
      rs.print();
      // close result set
      rs.close();

      // set filter value to 40
      pps.setInt(1, 40);

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for v.\"age\" > 40 --");
      rs.print();
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pps != null) {
        pps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample5.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Values for v.age > 30 --
+---------------------+
| fname | lname | age |
+---------------------+
| Susan | Blue  | 35  |
| Bill  | Brown | 40  |
| Ray   | Green | 41  |
+---------------------+
-- Values for v.age > 40 --
+---------------------+
| fname | lname | age |
+---------------------+
| Ray   | Green | 41  |
+---------------------+

Example 5-12 PgqlExample6.java

PgqlExample6.java shows a query with two bind variables: one String variable and one Timestamp variable.

import java.sql.Connection;
import java.sql.Timestamp;

import java.time.OffsetDateTime;
import java.time.ZoneOffset;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use multiple bind variables with a PGQL query.
 */
public class PgqlExample6
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Query string with multiple bind variables
      String pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
        "WHERE e.\"since\" < ? AND e.\"firstMetIn\" = ?";

      // Create a PgqlPreparedStatement
      pps = pgqlConn.prepareStatement(pgql);

      // Set e.since < 2006-01-01T12:00:00.00Z
      Timestamp t = Timestamp.valueOf(OffsetDateTime.parse("2006-01-01T12:00:01.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
      pps.setTimestamp(1, t);
      // Set e.firstMetIn = 'CA'
      pps.setString(2, "CA");

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for e.\"since\" <  2006-01-01T12:00:01.00Z AND e.\"firstMetIn\" = 'CA' --");
      rs.print();
      // close result set
      rs.close();

      // Set e.since < 2000-01-01T12:00:00.00Z
      t = Timestamp.valueOf(OffsetDateTime.parse("2000-01-01T12:00:00.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
      pps.setTimestamp(1, t);
      // Set e.firstMetIn = 'TX'
      pps.setString(2, "TX");

      // execute query
      rs = pps.executeQuery();

      // Print query results
      System.out.println("-- Values for e.\"since\" <  2000-01-01T12:00:00.00Z AND e.\"firstMetIn\" = 'TX' --");
      rs.print();
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (pps != null) {
        pps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample6.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Values for e."since" <  2006-01-01T12:00:01.00Z AND e."firstMetIn" = 'CA' --
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Susan  | Bill   |
| Susan  | Ray    |
+-----------------+
-- Values for e."since" <  2000-01-01T12:00:00.00Z AND e."firstMetIn" = 'TX' --
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Bill   |
+-----------------+
5.8.4.2.2 Verifying PGQL Identifiers

For some parts of a PGQL query the parser does not allow use of bind variables. In such cases, the input can be verified using the printIdentifier method in package oracle.pgql.lang.ir.PgqlUtils.

Consider the following query execution that concatenates the graph against which the graph pattern will be matched:

stmt.executeQuery("SELECT n.name FROM MATCH (n) ON " + graphName, "");

In order to avoid injection, the identifier graphName should be verified as follows:

stmt.executeQuery("SELECT n.name FROM MATCH (n) ON " + PgqlUtils.printIdentifier(graphName), "");
5.8.4.3 Using a Text Index with PGQL Queries

PGQL queries executed against Oracle Database can use Oracle Text indexes created for vertex and edge properties. After creating a text index, you can use the CONTAINS operator to perform a full text search. CONTAINS has two arguments: a vertex or edge property, and an Oracle Text search string. Any valid Oracle Text search string can be used, including advanced features such as wildcards, stemming, and soundex.

Example 5-13 PgqlExample7.java

PgqlExample7.java shows how to execute a CONTAINS query.

import java.sql.CallableStatement;
import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use an Oracle Text index with a PGQL query.
 */
public class PgqlExample7
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create text index with SQL API
      CallableStatement cs = null;
      // text index on vertices
      cs = conn.prepareCall(
        "begin opg_apis.create_vertices_text_idx(:1,:2); end;"
      );
      cs.setString(1,user);
      cs.setString(2,graph);
      cs.execute();
      cs.close();
      // text index on edges
      cs = conn.prepareCall(
        "begin opg_apis.create_edges_text_idx(:1,:2); end;"
      );
      cs.setString(1,user);
      cs.setString(2,graph);
      cs.execute();
      cs.close(); 

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Query using CONTAINS text search operator on vertex property
      // Find all vertices with an lname property value that starts with 'B'
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
        "FROM MATCH (v) "+
        "WHERE CONTAINS(v.\"lname\",'B%')";

      // execute query
      rs = ps.executeQuery(pgql, "");

      // print results
      System.out.println("-- Vertex Property Query --");
      rs.print();

      // close result set
      rs.close();

      // Query using CONTAINS text search operator on edge property
      // Find all knows edges with a firstMetIn property value that ends with 'A'
      pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2, e.\"firstMetIn\" AS loc "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
        "WHERE CONTAINS(e.\"firstMetIn\",'%A')";

      // execute query
      rs = ps.executeQuery(pgql, "");

      // print results
      System.out.println("-- Edge Property Query --");
      rs.print();

    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample7.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Vertex Property Query --
+---------------+
| FNAME | LNAME |
+---------------+
| Susan | Blue  |
| Bill  | Brown |
| John  | Black |
+---------------+
-- Edge Property Query --
+-----------------------+
| FNAME1 | FNAME1 | LOC |
+-----------------------+
| Susan  | Bill   | CA  |
| John   | Bill   | GA  |
| Susan  | John   | CA  |
| Susan  | Ray    | CA  |
+-----------------------+
5.8.4.4 Obtaining the SQL Translation for a PGQL Query

You can obtain the SQL translation for a PGQL query through methods in PgqlStatement and PgqlPreparedStatement. The raw SQL for a PGQL query can be useful for several reasons:

  • You can execute the SQL directly against the database with other SQL-based tools or interfaces (for example, SQL*Plus or SQL Developer).

  • You can customize and tune the generated SQL to optimize performance or to satisfy a particular requirement of your application.

  • You can build a larger SQL query that joins a PGQL subquery with other data stored in Oracle Database (such as relational tables, spatial data, and JSON data).

Example 5-14 PgqlExample8.java

PgqlExample8.java shows how to obtain the raw SQL translation for a PGQL query. The translateQuery method of PgqlStatement returns an PgqlSqlQueryTrans object that contains information about return columns from the query and the SQL translation itself.

The translated SQL returns different columns depending on the type of "logical" object or value projected from the PGQL query. A vertex or edge projected in PGQL has two corresponding columns projected in the translated SQL:

  • $IT : id type – NVARCHAR(1): 'V' for vertex or 'E' for edge

  • $ID : vertex or edge identifier – NUMBER: same content as VID or EID columns in VT$ and GE$ tables

A property value or constant scalar value projected in PGQL has four corresponding columns projected in the translated SQL:

  • $T : value type – NUMBER: same content as T column in VT$ and GE$ tables

  • $V: value – NVARCHAR2(15000): same content as V column in VT$ and GE$ tables

  • $VN: number value – NUMBER: same content as VN column in VT$ and GE$ tables

  • $VT: temporal value – TIMESTAMP WITH TIME ZONE: same content as VT column in VT$ and GE$ tables

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain the SQL translation for a PGQL query.
 */
public class PgqlExample8
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // PGQL query to be translated
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, e, e.\"since\" AS since "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2)";

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = ps.translateQuery(pgql,"");

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlTrans.getSqlTranslation());
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample8.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[e] colType=[EDGE] offset=[7]
colName=[since] colType=[VALUE] offset=[9]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.SVID AS "V1$ID",
T0$1.T AS "FNAME1$T",
T0$1.V AS "FNAME1$V",
T0$1.VN AS "FNAME1$VN",
T0$1.VT AS "FNAME1$VT",
n'E' AS "E$IT",
T0$0.EID AS "E$ID",
T0$0.T AS "SINCE$T",
T0$0.V AS "SINCE$V",
T0$0.VN AS "SINCE$VN",
T0$0.VT AS "SINCE$VT"
FROM ( SELECT L.EID, L.SVID, L.DVID, L.EL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHGT$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHGE$ WHERE K=n'since' ) R
  WHERE L.EID = R.EID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.SVID=T0$1.VID AND
(T0$0.EL = n'knows' AND T0$0.EL IS NOT NULL)

Example 5-15 PgqlExample9.java

You can also obtain the SQL translation for PGQL queries with bind variables. In this case, the corresponding SQL translation will also contain bind variables. The PgqlSqlQueryTrans interface has a getSqlBvList method that returns an ordered List of Java Objects that should be bound to the SQL query (the first Object on the list should be set at position 1, and the second should be set at position 2, and so on).

PgqlExample9.java shows how to get and execute the SQL for a PGQL query with bind variables.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.List;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain and execute the SQL translation for a 
 * PGQL query that uses bind variables.
 */
public class PgqlExample9
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlPreparedStatement pgqlPs = null;

    PreparedStatement sqlPs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Create a Pgql connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, v1.\"age\" AS age, ? as constVal "+
        "FROM MATCH (v1) "+
        "WHERE  v1.\"fname\" = ? OR v1.\"age\" < ?";


      // Create a PgqlStatement
      pgqlPs = pgqlConn.prepareStatement(pgql);

      // set bind values
      pgqlPs.setDouble(1, 2.05d);
      pgqlPs.setString(2, "Bill");
      pgqlPs.setInt(3, 35);

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = pgqlPs.translateQuery("");

      // Get the SQL String
      String sqlStr = sqlTrans.getSqlTranslation();

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Get the bind values
      List<Object> bindVals = sqlTrans.getSqlBvList();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlStr);

      // Print Bind Values
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute Query
      // Get PreparedStatement
      sqlPs = conn.prepareStatement("SELECT COUNT(*) FROM ("+sqlStr+")");
      // Set bind values and execute the PreparedStatement
      executePs(sqlPs, bindVals);

      // Set new bind values in the PGQL PreparedStatement 
      pgqlPs.setDouble(1, 3.02d);
      pgqlPs.setString(2, "Ray");
      pgqlPs.setInt(3, 30);

      // Print Bind Values
      bindVals = sqlTrans.getSqlBvList();
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute the PreparedStatement with new bind values
      executePs(sqlPs, bindVals);
    }
    finally {
      // close the SQL statement
      if (sqlPs != null) {
        sqlPs.close();
      }
      // close the statement
      if (pgqlPs != null) {
        pgqlPs.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Executes a SQL PreparedStatement with the input bind values
   */
  static void executePs(PreparedStatement ps, List<Object> bindVals) throws Exception
  {
    ResultSet rs = null;
    try {
      // Set bind values
      for (int idx = 0; idx < bindVals.size(); idx++) {
        Object o = bindVals.get(idx);
        // String
        if (o instanceof java.lang.String) {
          ps.setNString(idx + 1, (String)o);
        }
        // Int
        else if (o instanceof java.lang.Integer) {
          ps.setInt(idx + 1, ((Integer)o).intValue());
        }
        // Long
        else if (o instanceof java.lang.Long) {
          ps.setLong(idx + 1, ((Long)o).longValue());
        }
        // Float
        else if (o instanceof java.lang.Float) {
          ps.setFloat(idx + 1, ((Float)o).floatValue());
        }
        // Double
        else if (o instanceof java.lang.Double) {
          ps.setDouble(idx + 1, ((Double)o).doubleValue());
        }
       // Timestamp
       else if (o instanceof java.sql.Timestamp) {
         ps.setTimestamp(idx + 1, (Timestamp)o);
       }
       else {
         ps.setString(idx + 1, bindVals.get(idx).toString());
       }
     }

      // Execute query
      rs = ps.executeQuery();
      if (rs.next()) {
        System.out.println("\n-- Execute Query: Result has "+rs.getInt(1)+" rows --");
      }
    }
    finally {
      // close the SQL ResultSet
      if (rs != null) {
        rs.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample9.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

–-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[age] colType=[VALUE] offset=[7]
colName=[constVal] colType=[VALUE] offset=[11]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.VID AS "V1$ID",
T0$0.T AS "FNAME1$T",
T0$0.V AS "FNAME1$V",
T0$0.VN AS "FNAME1$VN",
T0$0.VT AS "FNAME1$VT",
T0$1.T AS "AGE$T",
T0$1.V AS "AGE$V",
T0$1.VN AS "AGE$VN",
T0$1.VT AS "AGE$VT",
4 AS "CONSTVAL$T",
to_nchar(?,'TM9','NLS_Numeric_Characters=''.,''') AS "CONSTVAL$V",
? AS "CONSTVAL$VN",
to_timestamp_tz(null) AS "CONSTVAL$VT"
FROM ( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'age' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.VID=T0$1.VID AND
((T0$0.T = 1 AND T0$0.V = ?) OR T0$1.VN < ?)

-- Bind Values --------------------------
2.05
2.05
Bill
35
-- Execute Query: Result has 2 rows --

-- Bind Values --------------------------
3.02
3.02
Ray
30
-- Execute Query: Result has 1 rows --
5.8.4.5 Additional Options for PGQL Translation and Execution

Several options are available to influence PGQL query translation and execution. The following are the main ways to set query options:

  • Through explicit arguments to executeQuery and translateQuery

  • Through flags in the options string argument of executeQuery and translateQuery

  • Through Java JVM arguments.

The following table summarizes the available query arguments for PGQL translation and execution.

Table 5-2 PGQL Translation and Execution Options

Option Default Explict Argument Options Flag JVM Argument

Degree of parallelism

0

parallel

none

none

Timeout

unlimited

timeout

none

none

Dynamic sampling

2

dynamicSampling

none

none

Maximum number of results

unlimited

maxResults

none

none

GT$ table usage

on

none

USE_GT_TAB=F

-Doracle.pg.rdbms.pgql.useGtTab=false

CONNECT BY usage

off

none

USE_RW=F

-Doracle.pg.rdbms.pgql.useRW=false

Distinct recursive WITH usage

off

none

USE_DIST_RW=T

-Doracle.pg.rdbms.pgql.useDistRW=true

Maximum path length

unlimited

none

MAX_PATH_LEN=n

-Doracle.pg.rdbms.pgql.maxPathLen=n

Set partial

false

none

EDGE_SET_PARTIAL=T

-Doracle.pg.rdbms.pgql.edgeSetPartial=true

Project null properties

true

none

PROJ_NULL_PROPS=F

-Doracle.pg.rdbms.pgql.projNullProps=false

VT$ VL column usage

on

none

USE_VL_COL=F

-Doracle.pg.rdbms.pgql.useVLCol=false

5.8.4.5.1 Query Options Controlled by Explicit Arguments

Some query options are controlled by explicit arguments to methods in the Java API.

  • The executeQuery method of PgqlStatement has explicit arguments for timeout in seconds, degree of parallelism, optimizer dynamic sampling, and maximum number of results.

  • The translateQuery method has explicit arguments for degree of parallelism, optimizer dynamic sampling, and maximum number of results. PgqlPreparedStatement also provides those same additional arguments for executeQuery and translateQuery.

Example 5-16 PgqlExample10.java

PgqlExample10.java shows PGQL query execution with additional options controlled by explicit arguments.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL query with various options.
 */
public class PgqlExample10
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-[:\"friendOf\"]->(v2)";
      rs = ps.executeQuery(pgql /* query string */, 
                           100  /* timeout (sec): 0 is default and implies no timeout */,
                           2    /* parallel: 1 is default */,
                           6    /* dynamic sampling: 2 is default */,
                           50   /* max results: -1 is default and implies no limit */,
                           ""   /* options */);

      // Print query results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample10.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| John   | Susan  |
| Bill   | John   |
| Susan  | John   |
| John   | Bill   |
+-----------------+
5.8.4.5.2 Using the GT$ Skeleton Table

The property graph relational schema defines a GT$ skeleton table that stores a single row for each edge in the graph, no matter how many properties an edge has. This skeleton table is populated by default so that PGQL query execution can take advantage of the GT$ table and avoid sorting operations on the GE$ table in many cases, which gives a significant performance improvement.

You can add "USE_GT_TAB=F" to the options argument of executeQuery and translateQuery or use -Doracle.pg.rdbms.pgql.useGtTab=false in the Java command line to turn off GT$ table usage.

Example 5-17 PgqlExample11.java

PgqlExample11.java shows a query that uses the GT$ skeleton table.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to avoid using the GT$ skeleton table for
 * PGQL query execution.
 */
public class PgqlExample11
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT id(v1), id(v2) "+
        "FROM MATCH (v1)-[knows]->(v2)";

      // Get the SQL translation with GT table
      PgqlSqlQueryTrans sqlTrans = ps.translateQuery(pgql,"");

      // Print SQL translation
      System.out.println("-- SQL Translation with GT Table ----------------------");
      System.out.println(sqlTrans.getSqlTranslation());

      // Get the SQL translation without GT table
      sqlTrans = ps.translateQuery(pgql,"USE_GT_TAB=F");

      // Print SQL translation
      System.out.println("-- SQL Translation without GT Table -------------------------");
      System.out.println(sqlTrans.getSqlTranslation());

    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample11.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- SQL Translation with GT Table ----------------------
SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0
-- SQL Translation without GT Table -------------------------
SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (SELECT DISTINCT EID, SVID, DVID,EL FROM "SCOTT".TEST_GRAPHGE$) T0$0
5.8.4.5.3 Path Query Options

A few options are available for executing path queries in PGQL. There are two basic evaluation methods available in Oracle SQL: CONNECT BY or recursive WITH clauses. Recursive WITH is the default evaluation method. In addition, you can further modify the recursive WITH evaluation method to include a DISTINCT modifier during the recursive step of query evaluation. Computing distinct vertices at each step helps prevent a combinatorial explosion in highly connected graphs. The DISTINCT modifier is not added by default because it requires a specific parameter setting in the database ("_recursive_with_control"=8).

You can also control the maximum length of paths searched. Path length in this case is defined as the number of repetitions allowed when evaluating the * and + operators. The default maximum length is unlimited.

Path evaluation options are summarized as follows.

  • CONNECT BY: To use CONNECT BY, specify 'USE_RW=F' in the options argument or specify -Doracle.pg.rdbms.pgql.useRW=false in the Java command line.

  • Distinct Modifier in Recursive WITH: To use the DISTINCT modifier in the recursive step, first set "_recursive_with_control"=8 in your database session, then specify 'USE_DIST_RW=T' in the options argument or specify -Doracle.pg.rdbms.pgql.useDistRW=true in the Java command line. You will encounter ORA-32486: unsupported operation in recursive branch of recursive WITH clause if "_recursive_with_control" has not been set to 8 in your session.

  • Path Length Restriction: To limit maximum number of repetitions when evaluating * and + to n, specify 'MAX_PATH_LEN=n' in the query options argument or specify -Doracle.pg.rdbms.pgql.maxPathLen=n in the Java command line.

Example 5-18 PgqlExample12.java

PgqlExample12.java shows path query translations under various options.

import java.sql.Connection;
import java.sql.Statement;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use various options with PGQL path queries.
 */
public class PgqlExample12
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Set "_recursive_with_control"=8 to enable distinct optimization
      // optimization for recursive with
      Statement stmt = conn.createStatement();
      stmt.executeUpdate("alter session set \"_recursive_with_control\"=8");
      stmt.close();

      // Path Query to illustrate options      
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"]->() "+
        "SELECT id(v1), id(v2) "+
        "FROM MATCH (v1)-/:fof*/->(v2) "+
        "WHERE id(v1) = 2";


      // get SQL translation with defaults - Non-distinct Recursive WITH
      PgqlSqlQueryTrans sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          ""   /* options */);
      System.out.println("-- Default Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with DISTINCT reachability optimization
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          " USE_DIST_RW=T " /* options */);
      System.out.println("-- DISTINCT RW Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with CONNECT BY
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          2    /* parallel: default is 1 */,
                          2    /* dynamic sampling: default is 2 */,
                          -1   /* max results: -1 implies no limit */,
                          " USE_RW=F " /* options */);
      System.out.println("-- CONNECT BY Path Translation --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample12.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Default Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID,DVID FROM
(WITH RW (ROOT, DVID) AS
( SELECT ROOT, DVID FROM
(SELECT SVID ROOT, DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE T0$0.SVID = 2 AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL))
) UNION ALL
SELECT RW.ROOT, R.DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) R, RW
WHERE RW.DVID = R.SVID )
CYCLE DVID SET cycle_col TO 1 DEFAULT 0
SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

-- DISTINCT RW Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID,DVID FROM
(WITH RW (ROOT, DVID) AS
( SELECT ROOT, DVID FROM
(SELECT SVID ROOT, DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE T0$0.SVID = 2 AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL))
) UNION ALL
SELECT DISTINCT RW.ROOT, R.DVID
FROM (SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) R, RW
WHERE RW.DVID = R.SVID )
CYCLE DVID SET cycle_col TO 1 DEFAULT 0
SELECT ROOT SVID, DVID FROM RW))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

-- CONNECT BY Path Translation --------------------
SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v2)$T",
to_nchar(T0$0.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v2)$V",
T0$0.DVID AS "id(v2)$VN",
to_timestamp_tz(null) AS "id(v2)$VT"
FROM (/*Path[*/SELECT DISTINCT SVID, DVID
FROM (
SELECT 2 AS SVID, 2 AS DVID
FROM SYS.DUAL
WHERE EXISTS(
SELECT 1
FROM "SCOTT".TEST_GRAPHVT$
WHERE VID = 2)
UNION ALL
SELECT SVID, DVID
FROM
(SELECT CONNECT_BY_ROOT T0$0.SVID AS SVID, T0$0.DVID AS DVID
FROM(
SELECT T0$0.SVID AS SVID,
T0$0.DVID AS DVID
FROM "SCOTT".TEST_GRAPHGT$ T0$0
WHERE (T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL)) T0$0
START WITH T0$0.SVID = 2
CONNECT BY NOCYCLE PRIOR DVID = SVID))/*]Path*/) T0$0
WHERE T0$0.SVID = 2)

The query plan for the first query with the default recursive WITH strategy should look similar to the following.

-- default RW
---------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                      |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                           |
|   1 |  TEMP TABLE TRANSFORMATION                        |                           |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)         | SYS_TEMP_0FD9D6662_37AA44 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST       |                           |
|   4 |     PX COORDINATOR                                |                           |
|   5 |      PX SEND QC (RANDOM)                          | :TQ20000                  |
|   6 |       LOAD AS SELECT (CURSOR DURATION MEMORY)     | SYS_TEMP_0FD9D6662_37AA44 |
|   7 |        PX PARTITION HASH ALL                      |                           |
|*  8 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED     | TEST_GRAPHGT$             |
|*  9 |      INDEX RANGE SCAN                             | TEST_GRAPHXSG$            |
|  10 |     PX COORDINATOR                                |                           |
|  11 |      PX SEND QC (RANDOM)                          | :TQ10000                  |
|  12 |       LOAD AS SELECT (CURSOR DURATION MEMORY)     | SYS_TEMP_0FD9D6662_37AA44 |
|  13 |        NESTED LOOPS                               |                           |
|  14 |     PX BLOCK ITERATOR                             |                           |
|* 15 |      TABLE ACCESS FULL                            | SYS_TEMP_0FD9D6662_37AA44 |
|  16 |     PARTITION HASH ALL                            |                           |
|* 17 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED    | TEST_GRAPHGT$             |
|* 18 |       INDEX RANGE SCAN                            | TEST_GRAPHXSG$            |
|  19 |   PX COORDINATOR                                  |                           |
|  20 |    PX SEND QC (RANDOM)                            | :TQ30001                  |
|  21 |     VIEW                                          |                           |
|  22 |      HASH UNIQUE                                  |                           |
|  23 |       PX RECEIVE                                  |                           |
|  24 |        PX SEND HASH                               | :TQ30000                  |
|  25 |     HASH UNIQUE                                   |                           |
|  26 |      VIEW                                         |                           |
|  27 |       UNION-ALL                                   |                           |
|  28 |        PX SELECTOR                                |                           |
|* 29 |         FILTER                                    |                           |
|  30 |          FAST DUAL                                |                           |
|  31 |          PARTITION HASH SINGLE                    |                           |
|* 32 |           INDEX SKIP SCAN                         | TEST_GRAPHXQV$            |
|  33 |        VIEW                                       |                           |
|* 34 |         VIEW                                      |                           |
|  35 |          PX BLOCK ITERATOR                        |                           |
|  36 |           TABLE ACCESS FULL                       | SYS_TEMP_0FD9D6662_37AA44 |
---------------------------------------------------------------------------------------

The query plan for the second query that adds a DISTINCT modifier in the recursive step should look similar to the following.

--------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name                      |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |                           |
|   1 |  TEMP TABLE TRANSFORMATION                             |                           |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)              | SYS_TEMP_0FD9D6669_37AA44 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST            |                           |
|   4 |     PX COORDINATOR                                     |                           |
|   5 |      PX SEND QC (RANDOM)                               | :TQ20000                  |
|   6 |       LOAD AS SELECT (CURSOR DURATION MEMORY)          | SYS_TEMP_0FD9D6669_37AA44 |
|   7 |        PX PARTITION HASH ALL                           |                           |
|*  8 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED          | TEST_GRAPHGT$             |
|*  9 |      INDEX RANGE SCAN                                  | TEST_GRAPHXSG$            |
|  10 |     PX COORDINATOR                                     |                           |
|  11 |      PX SEND QC (RANDOM)                               | :TQ10001                  |
|  12 |       LOAD AS SELECT (CURSOR DURATION MEMORY)          | SYS_TEMP_0FD9D6669_37AA44 |
|  13 |        SORT GROUP BY                                   |                           |
|  14 |     PX RECEIVE                                         |                           |
|  15 |      PX SEND HASH                                      | :TQ10000                  |
|  16 |       SORT GROUP BY                                    |                           |
|  17 |        NESTED LOOPS                                    |                           |
|  18 |         PX BLOCK ITERATOR                              |                           |
|* 19 |          TABLE ACCESS FULL                             | SYS_TEMP_0FD9D6669_37AA44 |
|  20 |         PARTITION HASH ALL                             |                           |
|* 21 |          TABLE ACCESS BY LOCAL INDEX ROWID BATCHED     | TEST_GRAPHGT$             |
|* 22 |           INDEX RANGE SCAN                             | TEST_GRAPHXSG$            |
|  23 |   PX COORDINATOR                                       |                           |
|  24 |    PX SEND QC (RANDOM)                                 | :TQ30001                  |
|  25 |     VIEW                                               |                           |
|  26 |      HASH UNIQUE                                       |                           |
|  27 |       PX RECEIVE                                       |                           |
|  28 |        PX SEND HASH                                    | :TQ30000                  |
|  29 |     HASH UNIQUE                                        |                           |
|  30 |      VIEW                                              |                           |
|  31 |       UNION-ALL                                        |                           |
|  32 |        PX SELECTOR                                     |                           |
|* 33 |         FILTER                                         |                           |
|  34 |          FAST DUAL                                     |                           |
|  35 |          PARTITION HASH SINGLE                         |                           |
|* 36 |           INDEX SKIP SCAN                              | TEST_GRAPHXQV$            |
|  37 |        VIEW                                            |                           |
|* 38 |         VIEW                                           |                           |
|  39 |          PX BLOCK ITERATOR                             |                           |
|  40 |           TABLE ACCESS FULL                            | SYS_TEMP_0FD9D6669_37AA44 |
--------------------------------------------------------------------------------------------

The query plan for the third query that uses CONNECTY BY should look similar to the following.

-----------------------------------------------------------------------------
| Id  | Operation                                      | Name               |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                    |
|   1 |  VIEW                                          |                    |
|   2 |   HASH UNIQUE                                  |                    |
|   3 |    VIEW                                        |                    |
|   4 |     UNION-ALL                                  |                    |
|*  5 |      FILTER                                    |                    |
|   6 |       FAST DUAL                                |                    |
|   7 |       PARTITION HASH SINGLE                    |                    |
|*  8 |        INDEX SKIP SCAN                         | TEST_GRAPHXQV$     |
|*  9 |      VIEW                                      |                    |
|* 10 |       CONNECT BY WITH FILTERING                |                    |
|  11 |        PX COORDINATOR                          |                    |
|  12 |     PX SEND QC (RANDOM)                        | :TQ10000           |
|  13 |      PX PARTITION HASH ALL                     |                    |
|* 14 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_GRAPHGT$      |
|* 15 |        INDEX RANGE SCAN                        | TEST_GRAPHXSG$     |
|  16 |        NESTED LOOPS                            |                    |
|  17 |     CONNECT BY PUMP                            |                    |
|  18 |     PARTITION HASH ALL                         |                    |
|* 19 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TEST_GRAPHGT$      |
|* 20 |       INDEX RANGE SCAN                         | TEST_GRAPHXSG$     |
-----------------------------------------------------------------------------

Example 5-19 PgqlExample13.java

PgqlExample13.java shows how to set length restrictions during path query evaluation.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use the maximum path length option for 
 * PGQL path queries.
 */
public class PgqlExample13
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Path Query to illustrate options
      String pgql = 
        "PATH fof AS ()-[:\"friendOf\"]->() "+
        "SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
        "FROM MATCH (v1)-/:fof*/->(v2) "+
        "WHERE v1.\"fname\" = 'Ray'";


      // execute query for 1-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=1 ");

      // print results
      System.out.println("-- Results for 1-hop ----------------");
      rs.print();

      // close result set
      rs.close();

      // execute query for 2-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=2 ");

      // print results
      System.out.println("-- Results for 2-hop ----------------");
      rs.print();

      // close result set
      rs.close();

      // execute query for 3-hop
      rs = ps.executeQuery(pgql, " MAX_PATH_LEN=3 ");

      // print results
      System.out.println("-- Results for 3-hop ----------------");
      rs.print();

      // close result set
      rs.close();

    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample13.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).

-- Results for 1-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Ray    |
| Ray    | Susan  |
+-----------------+
-- Results for 2-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| Ray    | Ray    |
| Ray    | John   |
+-----------------+
-- Results for 3-hop ----------------
+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Ray    | Susan  |
| Ray    | Bill   |
| Ray    | Ray    |
| Ray    | John   |
+-----------------+
5.8.4.5.4 Options for Partial Object Construction

When reading edges from a query result, there are two possible behaviors when adding the start and end vertex to any local caches:

  • Add only the vertex ID, which is available from the edge itself. This option is the default, for efficiency.

  • Add the vertex ID, and retrieve all properties for the start and end vertex. For this behavior, you can call setPartial(true) on each OracleVertex object constructed from your PGQL query result set.

Example 5-20 PgqlExample14.java

PgqlExample14.java illustrates this difference in behavior. This program first executes a query to retrieve all edges, which causes the incident vertices to be added to a local cache. The second query retrieves all vertices. The program then prints each OracleVertex object to show which properties have been loaded.

import java.sql.Connection;

import oracle.pg.rdbms.Oracle;
import oracle.pg.rdbms.OraclePropertyGraph;
import oracle.pg.rdbms.OracleVertex;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows the behavior of setPartial(true) for OracleVertex objects
 * created from PGQL query results.
 */
public class PgqlExample14
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    Oracle oracle = null;
    OraclePropertyGraph opg = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Query to illustrate set partial
      String pgql =
        "SELECT id(e), label(e) "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2)";

      // execute query
      rs = ps.executeQuery(pgql, " ");

      // print results
      System.out.println("-- Results for edge query -----------------");
      rs.print();

      // close result set
      rs.close();

      // Create an Oracle Property Graph instance
      oracle = new Oracle(conn);
      opg = OraclePropertyGraph.getInstance(oracle,graph);

      // Query to retrieve vertices
      pgql =
        "SELECT id(v) "+
        "FROM MATCH (v)";

      // Get each vertex object in result and print with toString()
      rs = ps.executeQuery(pgql, " ");

      // iterate through result
      System.out.println("-- Vertex objects retrieved from vertex query --");
      while (rs.next()) {
        Long vid = rs.getLong(1);
        OracleVertex v = OracleVertex.getInstance(opg, vid);
        System.out.println(v.toString());
      }
      // close result set
      rs.close();

      // Execute the same query but call setPartial(true) for each vertex
      rs = ps.executeQuery(pgql, " ");
      System.out.println("-- Vertex objects retrieved from vertex query with setPartial(true) --");
      while (rs.next()) {
        Long vid = rs.getLong(1);
        OracleVertex v = OracleVertex.getInstance(opg, vid);
        v.setPartial(true);
        System.out.println(v.toString());
      }
      // close result set
      rs.close();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
      // close the property graph
      if (opg != null) {
        opg.close();
      }
      // close oracle
      if (oracle != null) {
        oracle.dispose();
      }
    }
  }
}

The output for PgqlExample14.java (which can be loaded using GraphLoaderExample.java code) is:

-- Results for edge query -----------------
+------------------+
| id(e) | label(e) |
+------------------+
| 6     | knows    |
| 11    | knows    |
| 10    | knows    |
| 5     | knows    |
| 4     | knows    |
| 13    | knows    |
| 9     | knows    |
| 12    | knows    |
| 8     | knows    |
| 7     | knows    |
| 14    | knows    |
| 15    | knows    |
+------------------+
-- Vertex objects retrieved from vertex query --
Vertex ID 3 [NULL] {}
Vertex ID 0 [NULL] {}
Vertex ID 2 [NULL] {}
Vertex ID 1 [NULL] {}
-- Vertex objects retrieved from vertex query with setPartial(true) --
Vertex ID 3 [NULL] {bval:bol:false, fname:str:Susan, lname:str:Blue, mval:bol:false, age:int:35}
Vertex ID 0 [NULL] {bval:bol:true, fname:str:Bill, lname:str:Brown, mval:str:y, age:int:40}
Vertex ID 2 [NULL] {fname:str:Ray, lname:str:Green, mval:dat:1985-01-01 04:00:00.0, age:int:41}
Vertex ID 1 [NULL] {bval:bol:true, fname:str:John, lname:str:Black, mval:int:27, age:int:30}
5.8.4.6 Querying Another User’s Property Graph

You can query another user’s property graph data if you have been granted the appropriate privileges in the database. For example, to query GRAPH1 in SCOTT’s schema, you must have READ privilege on SCOTT.GRAPH1GE$, SCOTT.GRAPH1VT$, SCOTT.GRAPH1GT$, and SCOTT.GRAPH1VD$.

Example 5-21 PgqlExample15.java

PgqlExample15.java shows how another user can query a graph in SCOTT’s schema.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to query a property graph located in another user's
 * schema. READ privilege on GE$, VT$, GT$ and VD$ tables for the other user's
 * property graph are required to avoid ORA-00942: table or view does not exist.
 */
public class PgqlExample15
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Set schema so that we can query Scott's graph
      pgqlConn.setSchema("SCOTT");

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
        "FROM MATCH (v)";
      rs = ps.executeQuery(pgql, "");


      // Print query results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The following SQL statements create database user USER2 and grant the necessary privileges. You can also use the OraclePropertyGraph.grantAccess Java API to achieve the same effect.

SQL> grant connect, resource, unlimited tablespace to user2 identified by user2;

Grant succeeded.

SQL> grant read on scott.test_graphvt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphge$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphgt$ to user2;

Grant succeeded.

SQL> grant read on scott.test_graphvd$ to user2;

Grant succeeded.

The output for PgqlExample15.java for the test_graph data set when connected to the database as USER2 is as follows. Note that test_graph should have already been loaded (using GraphLoaderExample.java code) as GRAPH1 by user SCOTT before running PgqlExample15.

+---------------+
| FNAME | LNAME |
+---------------+
| Susan | Blue  |
| Bill  | Brown |
| Ray   | Green |
| John  | Black |
+---------------+
5.8.4.7 Using Query Optimizer Hints with PGQL

The Java API allows query optimizer hints that influence the join type when executing PGQL queries. The executeQuery and translateQuery methods in PgqlStatement and PgqlPreparedStatement accept the following strings in the options argument to influence the query plan for the corresponding SQL query.

  • ALL_EDGE_NL – Use Nested Loop join for all joins that involve the $GE and $GT tables.

  • ALL_EDGE_HASH – Use HASH join for all joins that involve the $GE and $GT tables.

  • ALL_VERTEX_NL – Use Nested Loop join for all joins that involve the $VT table.

  • ALL_VERTEX_HASH – Use HASH join for all joins that involve the $VT table.

Example 5-22 PgqlExample16.java

PgqlExample16.java shows how to use optimizer hints to influence the joins used for a graph traversal.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to use query optimizer hints with PGQL queries.
 */
public class PgqlExample16
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();
      // Query to illustrate join hints
      String pgql = 
        "SELECT id(v1), id(v4) "+
        "FROM MATCH (v1)-[:\"friendOf\"]->(v2)-[:\"friendOf\"]->(v3)-[:\"friendOf\"]->(v4)";

      // get SQL translation with hash join hint
      PgqlSqlQueryTrans sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          " ALL_EDGE_HASH " /* options */);
      // print SQL translation
      System.out.println("-- Query with ALL_EDGE_HASH --------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");

      // get SQL translation with nested loop join hint
      sqlTrans = 
        ps.translateQuery(pgql /* query string */, 
                          " ALL_EDGE_NL " /* options */);
      // print SQL translation
      System.out.println("-- Query with ALL_EDGE_NL ---------------------");
      System.out.println(sqlTrans.getSqlTranslation()+"\n");
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample16.java for test_graph (which can be loaded using GraphLoaderExample.java code) is:

-- Query with ALL_EDGE_HASH --------------------
SELECT /*+ USE_HASH(T0$0 T0$1 T0$2) */ 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v4)$T",
to_nchar(T0$2.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v4)$V",
T0$2.DVID AS "id(v4)$VN",
to_timestamp_tz(null) AS "id(v4)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0,
"SCOTT".TEST_GRAPHGT$ T0$1,
"SCOTT".TEST_GRAPHGT$ T0$2
WHERE T0$0.DVID=T0$1.SVID AND
T0$1.DVID=T0$2.SVID AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL) AND
(T0$1.EL = n'friendOf' AND T0$1.EL IS NOT NULL) AND
(T0$2.EL = n'friendOf' AND T0$2.EL IS NOT NULL)

-- Query with ALL_EDGE_NL ---------------------
SELECT /*+ USE_NL(T0$0 T0$1 T0$2) */ 7 AS "id(v1)$T",
to_nchar(T0$0.SVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v1)$V",
T0$0.SVID AS "id(v1)$VN",
to_timestamp_tz(null) AS "id(v1)$VT",
7 AS "id(v4)$T",
to_nchar(T0$2.DVID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(v4)$V",
T0$2.DVID AS "id(v4)$VN",
to_timestamp_tz(null) AS "id(v4)$VT"
FROM "SCOTT".TEST_GRAPHGT$ T0$0,
"SCOTT".TEST_GRAPHGT$ T0$1,
"SCOTT".TEST_GRAPHGT$ T0$2
WHERE T0$0.DVID=T0$1.SVID AND
T0$1.DVID=T0$2.SVID AND
(T0$0.EL = n'friendOf' AND T0$0.EL IS NOT NULL) AND
(T0$1.EL = n'friendOf' AND T0$1.EL IS NOT NULL) AND
(T0$2.EL = n'friendOf' AND T0$2.EL IS NOT NULL)

The query plan for the first query that uses ALL_EDGE_HASH should look similar to the following.

-----------------------------------------------
| Id  | Operation             | Name          |
-----------------------------------------------
|   0 | SELECT STATEMENT      |               |
|*  1 |  HASH JOIN            |               |
|*  2 |   HASH JOIN           |               |
|   3 |    PARTITION HASH ALL |               |
|*  4 |     TABLE ACCESS FULL | TEST_GRAPHGT$ |
|   5 |    PARTITION HASH ALL |               |
|*  6 |     TABLE ACCESS FULL | TEST_GRAPHGT$ |
|   7 |   PARTITION HASH ALL  |               |
|*  8 |    TABLE ACCESS FULL  | TEST_GRAPHGT$ |
-----------------------------------------------

The query plan for the second query that uses ALL_EDGE_NL should look similar to the following.

-----------------------------------------------------------------------
| Id  | Operation                                    | Name           |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                |
|   1 |  NESTED LOOPS                                |                |
|   2 |   NESTED LOOPS                               |                |
|   3 |    PARTITION HASH ALL                        |                |
|*  4 |     TABLE ACCESS FULL                        | TEST_GRAPHGT$  |
|   5 |    PARTITION HASH ALL                        |                |
|*  6 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_GRAPHGT$  |
|*  7 |      INDEX RANGE SCAN                        | TEST_GRAPHXSG$ |
|   8 |   PARTITION HASH ALL                         |                |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TEST_GRAPHGT$  |
|* 10 |     INDEX RANGE SCAN                         | TEST_GRAPHXSG$ |
-----------------------------------------------------------------------

5.8.5 Modifying Property Graphs through INSERT, UPDATE, and DELETE Statements

PGQL supports INSERT, UPDATE, and DELETE operations on Property Graphs. The method execute in PgqlStatement lets you execute such DML operations. This topic provides several examples of such operations.

Note:

JDBC connection autocommit must be off in order to be able to execute INSERT, UPDATE, and DELETE statements.

Example 5-23 PgqlExample17.java (Insert)

PgqlExample17.java inserts several vertices and edges into a graph. Notice that the special property _ora_id is used to define ID values of vertices and edges. If the property _ora_id is omitted, a unique ID is generated for each new vertex or edge that is inserted into the graph.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL INSERT operation.
 */
public class PgqlExample17
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute insert statement
      String pgql = 
        "INSERT VERTEX p1 LABELS (person) PROPERTIES (p1.\"_ora_id\" = 1, p1.fname = 'Jake') "+
        "     , VERTEX p2 LABELS (person) PROPERTIES (p2.\"_ora_id\" = 2, p2.fname = 'Amy')  "+
        "     , VERTEX p3 LABELS (person) PROPERTIES (p3.\"_ora_id\" = 3, p3.fname = 'Erik') "+
        "     , VERTEX p4 LABELS (person) PROPERTIES (p4.\"_ora_id\" = 4, p4.fname = 'Jane') "+
        "     , EDGE e1 BETWEEN p1 AND p2 LABELS (knows) PROPERTIES (e1.\"_ora_id\" = 1, e1.since = DATE '2003-04-21') "+
        "     , EDGE e2 BETWEEN p1 AND p3 LABELS (knows) PROPERTIES (e2.\"_ora_id\" = 2, e2.since = DATE '2010-02-10') "+
        "     , EDGE e3 BETWEEN p3 AND p4 LABELS (knows) PROPERTIES (e3.\"_ora_id\" = 3, e3.since = DATE '1999-01-03') ";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify insertion
      pgql = 
          "  SELECT id(p1) AS id1, p1.fname AS person1, id(p2) as id2, p2.fname AS person2, id(e) as e, e.since "+
          "    FROM MATCH (p1)-[e:knows]->(p2) "+
          "ORDER BY id1, id2";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample17.java is:

+-----------------------------------------------------------+
| ID1 | PERSON1 | ID2 | PERSON2 | E | SINCE                 |
+-----------------------------------------------------------+
| 1   | Jake    | 2   | Amy     | 1 | 2003-04-20 17:00:00.0 |
| 1   | Jake    | 3   | Erik    | 2 | 2010-02-09 16:00:00.0 |
| 3   | Erik    | 4   | Jane    | 3 | 1999-01-02 16:00:00.0 |
+-----------------------------------------------------------+

For more examples of INSERT statement, see the relevant section of the PGQL specification here.

Example 5-24 PgqlExample18.java (Update)

PgqlExample18.java updates several properties of vertices and edges that are matched in the FROM clause of an UPDATE statement.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL UPDATE operation.
 */
public class PgqlExample18
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute update statement
      String pgql = 
        "UPDATE p1 SET (p1.age = 47, p1.lname = 'Red'), "+
        "       p2 SET (p2.age = 29, p2.lname = 'White'), "+
        "        e SET (e.strength = 100) "+
        "FROM MATCH (p1) -[e:knows]-> (p2) "+
        "WHERE p1.fname = 'Jake' AND p2.fname = 'Amy'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify update
      pgql = 
          "SELECT p1.fname AS fname1, p1.lname AS lname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.lname AS lname2, p2.age AS age2, e.strength "+
          "FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample18.java applied on a graph where PgqlExample17.java has been previously executed is:

+----------------------------------------------------------------+
| FNAME1 | LNAME1 | AGE1   | FNAME2 | LNAME2 | AGE2   | STRENGTH |
+----------------------------------------------------------------+
| Jake   | Red    | 47     | Amy    | White  | 29     | 100      |
| Jake   | Red    | 47     | Erik   | <null> | <null> | <null>   |
| Erik   | <null> | <null> | Jane   | <null> | <null> | <null>   |
+----------------------------------------------------------------+

For more examples of UPDATE statement, see the relevant section of the PGQL specification here.

Example 5-25 PgqlExample19.java (Delete)

PgqlExample19.java deletes edges that are matched in the FROM clause of a DELETE statement.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL DELETE operation.
 */
public class PgqlExample19
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute delete statement
      String pgql = 
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Jake'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify delete
      pgql = 
          "SELECT p1.fname AS fname1, p2.fname AS fname2 "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

The output for PgqlExample19.java applied on a graph where PgqlExample18.java has been previously executed is:

+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Erik   | Jane   |
+-----------------+

For more examples of DELETE statement, see the relevant section of the PGQL specification here.

Example 5-26 PgqlExample20.java (Multiple Modifications)

PgqlExample20.java executes multiple modifications in the same statement: an edge is inserted, vertex properties are updated, and another edge is deleted.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to execute a PGQL 
 * INSERT/UPDATE/DELETE operation.
 */
public class PgqlExample20
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute INSERT/UPDATE/DELETE statement
      String pgql = 
        "INSERT EDGE f BETWEEN p2 AND p1 LABELS (knows) PROPERTIES (f.since = e.since) "+
        "UPDATE p1 SET (p1.age = 30) "+
        "     , p2 SET (p2.age = 25) "+
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Erik'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify INSERT/UPDATE/DELETE
      pgql = 
          "SELECT p1.fname AS fname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.age AS age2, e.since "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

The output for PgqlExample20.java applied on a graph where PgqlExample19.java has been previously executed is:

+-------------------------------------------------------+
| FNAME1 | AGE1 | FNAME2 | AGE2 | SINCE                 |
+-------------------------------------------------------+
| Jane   | 25   | Erik   | 30   | 1999-01-02 16:00:00.0 |
+-------------------------------------------------------+

For more examples of INSERT/UPDATE/DELETE statements, see the relevant section of the PGQL specification here.

5.8.5.1 Additional Options for PGQL Statement Execution

Several options are available to influence PGQL statement execution. The following are the main ways to set query options:

  • Through flags in the modify options string argument of execute
  • Through Java JVM arguments.

The following table summarizes the main options for modifying PGQL statement execution.

Table 5-3 PGQL Statement Modification Options

Option Default Options Flag JVM Argument
Auto commit true if JDBC auto commit is off, false if JDBC auto commit is on AUTO_COMMIT=F -Doracle.pg.rdbms.pgql.autoCommit=false
Delete cascade true DELETE_CASCADE=F -Doracle.pg.rdbms.pgql.deleteCascade=false
5.8.5.1.1 Turning Off PGQL Auto Commit

When an INSERT, UPDATE, or DELETE operation is executed, a commit is performed automatically at the end of the PGQL execution so that changes are persisted on the RDBMS side.

The flag AUTO_COMMIT=F can be added to the options argument of execute or the flag Doracle.pg.rdbms.pgql.autoCommit=false can be set in the Java command line to turn off auto commit. Notice that when auto commit is off, you must perform any necessary commits or rollbacks on the JDBC connection in order to persist or cancel graph modifications.

Example 5-27 Turn Off Auto Commit and Roll Back Changes

PgqlExample21.java turns off auto commit and performs a rollback of the changes.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to modify a PGQL graph
 * with auto commit off.
 */
public class PgqlExample21
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;
    PgqlResultSet rs = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Delete all the edges in the graph
      String pgql = 
        "DELETE e "+
        "  FROM MATCH () -[e]-> ()";
      ps.execute(pgql, /*  query string  */ 
                 "",   /* query options  */
                 "AUTO_COMMIT=F"  /* modify options */);

      // Execute a query to verify deletion
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after deletion:");
      rs.print();
      rs.close();

      // Rollback the changes. This is possible because
      // AUTO_COMMIT=F flag was used in execute
      conn.rollback();

      // Execute a query to verify rollback
      pgql = 
          "SELECT COUNT(e) "+
          "  FROM MATCH () -[e]-> ()";
      rs = ps.executeQuery(pgql, "");

      // Print the results
      System.out.println("Number of edges after rollback:");
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
        rs.close();
      }
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample21.java gives the following output for a graph with one edge:

Number of edges after deletion:
+----------+
| COUNT(e) |
+----------+
| 0        |
+----------+
Number of edges after rollback:
+----------+
| COUNT(e) |
+----------+
| 1        |
+----------+
5.8.5.1.2 Turning Off Cascading Deletion

When a vertex is deleted from a graph, all its input and output edges are also deleted automatically.

Using the flag DELETE_CASCADE=F in the options argument of execute of setting the flag or setting the flag Doracle.pg.rdbms.pgql.autoCommit=false in the Java command line lets you turn off cascading deletion. When a vertex with input or output edges is deleted and cascading deletion is off, an error is thrown to warn about the unsafe operation that you are trying to perform.

Example 5-28 Turn Off Cascading Deletion

PgqlExample22.java attempts to delete a vertex with an output edge when cascading deletion is off.

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlToSqlException;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows the use of DELETE_CASCADE flag.
 */
public class PgqlExample22
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String host               = args[idx++]; 
    String port               = args[idx++]; 
    String sid                = args[idx++]; 
    String user               = args[idx++]; 
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement ps = null;

    try {

      //Get a jdbc connection
      PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();
      pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
      pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
      pds.setUser(user);
      pds.setPassword(password);     
      conn = pds.getConnection();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Delete all the vertices with output edges
      // This will throw an error
      String pgql = 
        "DELETE v "+
        "  FROM MATCH (v) -[e]-> ()";
      ps.execute(pgql, /*  query string  */ 
                 "",   /* query options  */
                 "DELETE_CASCADE=F"  /* modify options */);
    }
    catch (PgqlToSqlException ex){
      System.out.println("Error in execution: " + ex.getMessage());
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }
}

PgqlExample22.java gives the following output for a graph with at least one edge:

Error in execution: Attempting to delete vertices with incoming/outgoing edges. Drop edges first or turn on DELETE_CASCADE option

5.8.6 Performance Considerations for PGQL Queries

Many factors affect the performance of PGQL queries in Oracle Database. The following are some recommended practices for query performance.

Query Optimizer Statistics

Good, up-to-date query optimizer statistics are critical for query performance. Ensure that you run OPG_APIS.ANALYZE_PG after any significant updates to your property graph data.

Parallel Query Execution

Use parallel query execution to take advantage of Oracle’s parallel SQL engine. Parallel execution often gives a significant speedup versus serial execution. Parallel execution is especially critical for path queries evaluated using the recursive WITH strategy.

See also the Oracle Database VLDB and Partitioning Guide for more information about parallel query execution.

Optimizer Dynamic Sampling

Due to the inherent flexibility of the graph data model, static information may not always produce optimal query plans. In such cases, dynamic sampling can be used by the query optimizer to sample data at run time for better query plans. The amount of data sampled is controlled by the dynamic sampling level used. Dynamic sampling levels range from 0 to 11. The best level to use depends on a particular dataset and workload, but levels of 2 (default), 6, or 11 often give good results.

See also Supplemental Dynamic Statistics in the Oracle Database SQL Tuning Guide.

Bind Variables

Use bind variables for constants whenever possible. The use of bind variables gives a very large reduction in query compilation time, which dramatically increases throughput for query workloads with queries that differ only in the constant values used. In addition, queries with bind variables are less vulnerable to injection attacks.

Path Queries

Path queries in PGQL that use the + (plus sign) or * (asterisk) operator to search for arbitrary length paths require special consideration because of their high computational complexity. You should use parallel execution and use the DISTINCT option for Recursive WITH (USE_DIST_RW=T) for the best performance. Also, for large, highly connected graphs, it is a good idea to use MAX_PATH_LEN=n to limit the number of repetitions of the recursive step to a reasonable number. A good strategy can be to start with a small repetition limit, and iteratively increase the limit to find more and more results.