Using the PGX JDBC Driver with the AdbGraphClient API

You can use the PGX JDBC driver with the AdbGraphClient API to query graphs stored in the memory of the graph server in Graph Studio on Autonomous AI Database.

To use the PGX JDBC driver to connect to your Autonomous AI Database instance, note the following:

• Register the PGX JDBC driver with the DriverManager:

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

• Use one of the following two ways to establish the connection using the PGX JDBC Driver:

  • Using Properties

    properties = new Properties();
    properties.put("tenancy_ocid", "<tenancy_OCID>");
    properties.put("database_ocid", "<database_OCID>");
    properties.put("database", "<database_name>");
    properties.put("username", "<username>");
    properties.put("password", "<password>");
    Connection connection = DriverManager.getConnection("jdbc:oracle:pgx:https://<hostname-prefix>.adb.<region>.oraclecloudapps.com", properties);

  • Using a Wallet

    Connection connection = DriverManager.getConnection("jdbc:oracle:pgx:@<db_TNS_name>?TNS_ADMIN=<path_to_wallet>", "<ADB_username>", "<ADB_password>")

  Note that the JDBC URL in the preceding code samples, use jdbc:oracle:pgx: as the prefix.

The following example establishes a connection using the PGX JDBC driver to connect to an Autonomous AI Database instance, starts the compute environment in Graph Studio, loads a 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.pg.rdbms.*;
import oracle.pgx.api.*;

public class AdbPgxJdbc {

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

    DriverManager.registerDriver(new PgxJdbcDriver());


    try (Connection conn = DriverManager.getConnection("jdbc:oracle:pgx:@<db_TNS_name>?TNS_ADMIN=<path_to_wallet>","ADB_username","<ADB_password>")) {
      AdbGraphClient client = conn.unwrap(AdbGraphClient.class);
      if (!client.isAttached()) {
        var job = client.startEnvironment(10);
         job.get();
         System.out.println("job details: name=" + job.getName() + "type= " + job.getType() +"created_by= " + job.getCreatedBy());
        }
      PgxSession session = conn.unwrap(PgxSession.class);
      PgxGraph graph = session.readGraphByName("BANK_PGQL_GRAPH", GraphSource.PG_PGQL);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * "+
                                         "FROM GRAPH_TABLE ( BANK_PGQL_GRAPH "+
                                         "MATCH (a IS ACCOUNTS) -[e IS TRANSFERS]-> (b IS ACCOUNTS) "+
                                         "WHERE a.ID = 179 AND b.ID = 688 "+
                                         "COLUMNS (e.AMOUNT AS AMOUNT ))");
      while(rs.next()){
        System.out.println("AMOUNT = " + rs.getLong("AMOUNT"));
      }

    }
  }
}

The resulting output of the preceding code is as shown:

AMOUNT = 7562