A.12.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 A-27 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 A-28 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 A-29 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 thejava.sql.ResultSet
interface.- A
next()
method allows moving through the query result, and aclose()
method allows releasing resources after the application is fiished reading the query result. - In addition,
PgqlResultSet
provides getters forString
,Integer
,Long
,Float
,Double
,Boolean
,LocalDateTime
, andOffsetDateTime
, and it provides a genericgetObject()
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 A-30 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 A-31 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 | +--------+