6.9.1.4.9 Modifying Property Graphs through INSERT, UPDATE, and DELETE Statements

PGQL supports INSERT, UPDATE, and DELETE operations on Property Graphs. The method execute in PgqlStatement lets you execute such DML operations. This topic provides several examples of such operations.

Note:

JDBC connection autocommit must be off in order to be able to execute INSERT, UPDATE, and DELETE statements.

Example 6-24 PgqlExample17.java (Insert)

PgqlExample17.java inserts several vertices and edges into a graph. Notice that the special property _ora_id is used to define ID values of vertices and edges. If the property _ora_id is omitted, a unique ID is generated for each new vertex or edge that is inserted into the graph.

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 INSERT operation.
 */
public class PgqlExample17
{

  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();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute insert statement
      String pgql = 
        "INSERT VERTEX p1 LABELS (person) PROPERTIES (p1.\"_ora_id\" = 1, p1.fname = 'Jake') "+
        "     , VERTEX p2 LABELS (person) PROPERTIES (p2.\"_ora_id\" = 2, p2.fname = 'Amy')  "+
        "     , VERTEX p3 LABELS (person) PROPERTIES (p3.\"_ora_id\" = 3, p3.fname = 'Erik') "+
        "     , VERTEX p4 LABELS (person) PROPERTIES (p4.\"_ora_id\" = 4, p4.fname = 'Jane') "+
        "     , EDGE e1 BETWEEN p1 AND p2 LABELS (knows) PROPERTIES (e1.\"_ora_id\" = 1, e1.since = DATE '2003-04-21') "+
        "     , EDGE e2 BETWEEN p1 AND p3 LABELS (knows) PROPERTIES (e2.\"_ora_id\" = 2, e2.since = DATE '2010-02-10') "+
        "     , EDGE e3 BETWEEN p3 AND p4 LABELS (knows) PROPERTIES (e3.\"_ora_id\" = 3, e3.since = DATE '1999-01-03') ";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify insertion
      pgql = 
          "  SELECT id(p1) AS id1, p1.fname AS person1, id(p2) as id2, p2.fname AS person2, id(e) as e, e.since "+
          "    FROM MATCH (p1)-[e:knows]->(p2) "+
          "ORDER BY id1, id2";
      rs = ps.executeQuery(pgql, "");

      // 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();
      }
    }
  }
}

The output for PgqlExample17.java is:

+-----------------------------------------------------------+
| ID1 | PERSON1 | ID2 | PERSON2 | E | SINCE                 |
+-----------------------------------------------------------+
| 1   | Jake    | 2   | Amy     | 1 | 2003-04-20 17:00:00.0 |
| 1   | Jake    | 3   | Erik    | 2 | 2010-02-09 16:00:00.0 |
| 3   | Erik    | 4   | Jane    | 3 | 1999-01-02 16:00:00.0 |
+-----------------------------------------------------------+

For more examples of INSERT statement, see the relevant section of the PGQL specification here.

Example 6-25 PgqlExample18.java (Update)

PgqlExample18.java updates several properties of vertices and edges that are matched in the FROM clause of an UPDATE statement.

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 UPDATE operation.
 */
public class PgqlExample18
{

  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();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute update statement
      String pgql = 
        "UPDATE p1 SET (p1.age = 47, p1.lname = 'Red'), "+
        "       p2 SET (p2.age = 29, p2.lname = 'White'), "+
        "        e SET (e.strength = 100) "+
        "FROM MATCH (p1) -[e:knows]-> (p2) "+
        "WHERE p1.fname = 'Jake' AND p2.fname = 'Amy'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify update
      pgql = 
          "SELECT p1.fname AS fname1, p1.lname AS lname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.lname AS lname2, p2.age AS age2, e.strength "+
          "FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // 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();
      }
    }
  }
}

The output for PgqlExample18.java applied on a graph where PgqlExample17.java has been previously executed is:

+----------------------------------------------------------------+
| FNAME1 | LNAME1 | AGE1   | FNAME2 | LNAME2 | AGE2   | STRENGTH |
+----------------------------------------------------------------+
| Jake   | Red    | 47     | Amy    | White  | 29     | 100      |
| Jake   | Red    | 47     | Erik   | <null> | <null> | <null>   |
| Erik   | <null> | <null> | Jane   | <null> | <null> | <null>   |
+----------------------------------------------------------------+

For more examples of UPDATE statement, see the relevant section of the PGQL specification here.

Example 6-26 PgqlExample19.java (Delete)

PgqlExample19.java deletes edges that are matched in the FROM clause of a DELETE statement.

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 DELETE operation.
 */
public class PgqlExample19
{

  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();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute delete statement
      String pgql = 
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Jake'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify delete
      pgql = 
          "SELECT p1.fname AS fname1, p2.fname AS fname2 "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // 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();
      }
    }
  }
}

The output for PgqlExample19.java applied on a graph where PgqlExample18.java has been previously executed is:

+-----------------+
| FNAME1 | FNAME2 |
+-----------------+
| Erik   | Jane   |
+-----------------+

For more examples of DELETE statement, see the relevant section of the PGQL specification here.

Example 6-27 PgqlExample20.java (Multiple Modifications)

PgqlExample20.java executes multiple modifications in the same statement: an edge is inserted, vertex properties are updated, and another edge is deleted.

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 
 * INSERT/UPDATE/DELETE operation.
 */
public class PgqlExample20
{

  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();
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PgqlStatement
      ps = pgqlConn.createStatement();

      // Execute INSERT/UPDATE/DELETE statement
      String pgql = 
        "INSERT EDGE f BETWEEN p2 AND p1 LABELS (knows) PROPERTIES (f.since = e.since) "+
        "UPDATE p1 SET (p1.age = 30) "+
        "     , p2 SET (p2.age = 25) "+
        "DELETE e "+
        "  FROM MATCH (p1) -[e:knows]-> (p2) "+
        " WHERE p1.fname = 'Erik'";
      ps.execute(pgql, /*  query string  */ 
                   "", /* query options  */
                   ""  /* modify options */);

      // Execute a query to verify INSERT/UPDATE/DELETE
      pgql = 
          "SELECT p1.fname AS fname1, p1.age AS age1, "+
          "       p2.fname AS fname2, p2.age AS age2, e.since "+
          "  FROM MATCH (p1) -[e:knows]-> (p2)";
      rs = ps.executeQuery(pgql, "");

      // 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();
      }
    }
  }

The output for PgqlExample20.java applied on a graph where PgqlExample19.java has been previously executed is:

+-------------------------------------------------------+
| FNAME1 | AGE1 | FNAME2 | AGE2 | SINCE                 |
+-------------------------------------------------------+
| Jane   | 25   | Erik   | 30   | 1999-01-02 16:00:00.0 |
+-------------------------------------------------------+

For more examples of INSERT/UPDATE/DELETE statements, see the relevant section of the PGQL specification here.

• Additional Options for PGQL Statement Execution