A.12.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 auto commit must be off in order to be able to execute INSERT, UPDATE, and DELETE statements.
Example A-45 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 INSERT section in the PGQL specification.
Example A-46 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 UPDATE section in the PGQL specification.
Example A-47 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 DELETE section in the PGQL specification.
Example A-48 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 Combining INSERT, UPDATE and DELETE section in the PGQL specification.