Using the PGX JDBC Driver with the AdbGraphClient API
Starting from Graph Server and Client Release 24.1.0, 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 Database.
To use the PGX JDBC driver to connect to your Autonomous 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. - Using
Properties
Example 5-1 Using the PGX JDBC Driver to run graph queries in Autonomous Database
The following example establishes a connection using the PGX JDBC driver to connect to an Autonomous 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