6.8.2.5 Executing PGQL SELECT Queries

You can execute PGQL SELECT queries using the Java API in the oracle.pg.rdbms.pgql package. The Java interfaces PgqlStatement and PgqlResultSet are used in query execution.

SELECT Query Execution on a Property Graph View Using JShell

1. Launch the JShell to work with the database as shown:

   ./bin/opg4j --no_connect

2. Execute the following commands to connect to the database and to run any SELECT query on a property graph view:

   opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
   opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
   opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
   opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
   opg4j> String s = "SELECT n.id FROM MATCH (n:Accounts) ON <pgview> LIMIT 3"
   opg4j> var rs = pgqlStmt.executeQuery(s)
   opg4j> rs.print() //Prints the query result set
   +-----+
   | ID  |
   +-----+
   | 434 |
   | 435 |
   | 436 |
   +-----+
   

SELECT Query Execution on a Property Graph View Using Java

The following example shows how to execute a SELECT query on a property graph view.

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 execute a SELECT quey on a property graph view.
 */
public class ExecuteQueryOnPgView
{

  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 pgview             = args[idx++];
  
    
    Connection conn = 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);

   
      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
	  
      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL Query
      String query = "SELECT a,b,c,e1,e2,e3 FROM MATCH (a)-[e1]->(b)-[e2]->(c)-[e3]->(a) ON " +pgview + " LIMIT 3";
      rs = pgqlStmt.executeQuery(query);
      
      // Print the results
      rs.print();
    }
    finally {
      // 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 resulting output is as shown:

+--------------------------------------------------------------------------------------------------------------------------+
| A             | B             | C            | E1                      | E2                     | E3                     |
+--------------------------------------------------------------------------------------------------------------------------+
| ACCOUNTS(998) | ACCOUNTS(781) | ACCOUNTS(71) | TRANSFERS(998,781,1000) | TRANSFERS(781,71,1000) | TRANSFERS(71,998,1000) |
| ACCOUNTS(359) | ACCOUNTS(579) | ACCOUNTS(76) | TRANSFERS(359,579,1000) | TRANSFERS(579,76,1000) | TRANSFERS(76,359,1000) |
| ACCOUNTS(6)   | ACCOUNTS(580) | ACCOUNTS(82) | TRANSFERS(6,580,1000)   | TRANSFERS(580,82,1000) | TRANSFERS(82,6,1000)   |
+--------------------------------------------------------------------------------------------------------------------------+