18.6 Executing PGQL Queries Using the PGX JDBC Driver

Starting from Graph Server and Client Release 24.1.0, you can use the PGX JDBC driver to access a PGX session and query graphs that are loaded in to the graph server (PGX).

To use the PGX JDBC driver, note the following:

• Register the PGX JDBC driver with the DriverManager:

  import java.sql.DriverManager;
  import oracle.pgx.jdbc.PgxJdbcDriver;
  ...
  DriverManager.registerDriver(new PgxJdbcDriver());

• Add the jdbc:oracle:pgx: prefix to the JDBC URL when obtaining a connection object as shown:

  Connection conn = DriverManager.getConnection("jdbc:oracle:pgx:@<graph_server_host>:<server_port>", "<username>", "<password>");

Example 18-5 Using the PGX JDBC Driver

The following example establishes a connection using the PGX JDBC driver, accesses the underlying PGX session to load the graph into the graph server (PGX), creates a statement, and runs a PGQL query on the graph.

import java.sql.*;
import oracle.pgx.jdbc.*;
import oracle.pgx.api.*;

public class PgxJdbcSample {

  public static void main(String[] args) throws Exception {


    String jdbcUrl = "jdbc:oracle:pgx:https://localhost:7007";
    String username = "graphuser";
    String password = "graph";
    
    DriverManager.registerDriver(new PgxJdbcDriver());
    try(Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
             if (conn.isWrapperFor(PgxSession.class)) {
                     PgxSession session = conn.unwrap(PgxSession.class);
                     session.readGraphByName("BANK_GRAPH_VIEW", GraphSource.PG_PGQL);
             }
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT e.AMOUNT AS AMOUNT FROM MATCH (a IS ACCOUNTS) -[e IS TRANSFERS]-> (b IS ACCOUNTS) ON BANK_GRAPH_VIEW "+
                                             " WHERE a.ID = 179 AND b.ID = 688");
            while(rs.next()){
                 System.out.println("AMOUNT = " + rs.getLong("AMOUNT"));
            }
    }

  }
}

The resulting output of the preceding code is as shown:

AMOUNT = 1000

Note that when running the preceding code, you must provide the PGX client JARs on the runtime classpath.

• Limitations of the PGX JDBC Driver
  Review the limiations of the PGX JDBC driver.
• PGX Data Type Compatibility and Casting
  You can configure a compatibility mode for the PGX JDBC driver to determine the data type returned when calling the ResultSet#getObject() method.