6.9.1.4.3.1 Using Bind Variables in PGQL Queries
Bind variables can be used in PGQL queries for better performance and increased security. Constant scalar values in PGQL queries can be replaced with bind variables. Bind variables are denoted by a '?' (question mark). Consider the following two queries that select people who are older than a constant age value.
// people older than 30
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > 30
// people older than 40
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > 40
The SQL translations for these queries would use the constants 30 and 40 in a similar way for the age filter. The database would perform a hard parse for each of these queries. This hard parse time can often exceed the execution time for simple queries.
You could replace the constant in each query with a bind variable as follows.
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > ?
This will allow the SQL engine to create a generic cursor for this query, which can be reused for different age values. As a result, a hard parse is no longer required to execute this query for different age values, and the parse time for each query will be drastically reduced.
In addition, applications that use bind variables in PGQL queries are less vulnerable to injection attacks than those that use string concatenation to embed constant values in PGQL queries.
See also Oracle Database SQL Tuning Guide for more information on cursor sharing and bind variables.
The PgqlPreparedStatement interface can be used to execute queries with bind variables as shown in PgqlExample5.java. PgqlPreparedStatement provides several set methods for different value types that can be used to set values for query execution.
There are a few limitations with bind variables in PGQL. Bind variables can only be used for constant property values. That is, vertices and edges cannot be replaced with bind variables. Also, once a particular bind variable has been set to a type, it cannot be set to a different type. For example, if setInt(1, 30) is executed for an PgqlPreparedStatement, you cannot call setString(1, "abc") on that same PgqlPreparedStatement.
Example 6-12 PgqlExample5.java
PgqlExample5.java shows how to use bind variables with a PGQL query.
import java.sql.Connection;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
/**
* This example shows how to use bind variables with a PGQL query.
*/
public class PgqlExample5
{
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;
PgqlPreparedStatement pps = 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);
// Query string with a bind variable (denoted by ?)
String pgql =
"SELECT v.\"fname\" AS fname, v.\"lname\" AS lname, v.\"age\" AS age "+
"FROM MATCH (v) "+
"WHERE v.\"age\" > ?";
// Create a PgqlPreparedStatement
pps = pgqlConn.prepareStatement(pgql);
// Set filter value to 30
pps.setInt(1, 30);
// execute query
rs = pps.executeQuery();
// Print query results
System.out.println("-- Values for v.\"age\" > 30 --");
rs.print();
// close result set
rs.close();
// set filter value to 40
pps.setInt(1, 40);
// execute query
rs = pps.executeQuery();
// Print query results
System.out.println("-- Values for v.\"age\" > 40 --");
rs.print();
// close result set
rs.close();
}
finally {
// close the result set
if (rs != null) {
rs.close();
}
// close the statement
if (pps != null) {
pps.close();
}
// close the connection
if (conn != null) {
conn.close();
}
}
}
}
PgqlExample5.java has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).
-- Values for v.age > 30 -- +---------------------+ | fname | lname | age | +---------------------+ | Susan | Blue | 35 | | Bill | Brown | 40 | | Ray | Green | 41 | +---------------------+ -- Values for v.age > 40 -- +---------------------+ | fname | lname | age | +---------------------+ | Ray | Green | 41 | +---------------------+
Example 6-13 PgqlExample6.java
PgqlExample6.java shows a query with two bind variables: one String variable and one Timestamp variable.
import java.sql.Connection;
import java.sql.Timestamp;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
/**
* This example shows how to use multiple bind variables with a PGQL query.
*/
public class PgqlExample6
{
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;
PgqlPreparedStatement pps = 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);
// Query string with multiple bind variables
String pgql =
"SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2 "+
"FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
"WHERE e.\"since\" < ? AND e.\"firstMetIn\" = ?";
// Create a PgqlPreparedStatement
pps = pgqlConn.prepareStatement(pgql);
// Set e.since < 2006-01-01T12:00:00.00Z
Timestamp t = Timestamp.valueOf(OffsetDateTime.parse("2006-01-01T12:00:01.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
pps.setTimestamp(1, t);
// Set e.firstMetIn = 'CA'
pps.setString(2, "CA");
// execute query
rs = pps.executeQuery();
// Print query results
System.out.println("-- Values for e.\"since\" < 2006-01-01T12:00:01.00Z AND e.\"firstMetIn\" = 'CA' --");
rs.print();
// close result set
rs.close();
// Set e.since < 2000-01-01T12:00:00.00Z
t = Timestamp.valueOf(OffsetDateTime.parse("2000-01-01T12:00:00.00Z").atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime());
pps.setTimestamp(1, t);
// Set e.firstMetIn = 'TX'
pps.setString(2, "TX");
// execute query
rs = pps.executeQuery();
// Print query results
System.out.println("-- Values for e.\"since\" < 2000-01-01T12:00:00.00Z AND e.\"firstMetIn\" = 'TX' --");
rs.print();
// close result set
rs.close();
}
finally {
// close the result set
if (rs != null) {
rs.close();
}
// close the statement
if (pps != null) {
pps.close();
}
// close the connection
if (conn != null) {
conn.close();
}
}
}
}
PgqlExample6.java gives the following output for test_graph (which can be loaded using GraphLoaderExample.java code).
-- Values for e."since" < 2006-01-01T12:00:01.00Z AND e."firstMetIn" = 'CA' -- +-----------------+ | FNAME1 | FNAME2 | +-----------------+ | Susan | Bill | | Susan | Ray | +-----------------+ -- Values for e."since" < 2000-01-01T12:00:00.00Z AND e."firstMetIn" = 'TX' -- +-----------------+ | FNAME1 | FNAME2 | +-----------------+ | Ray | Bill | +-----------------+
Parent topic: Security Techniques for PGQL Queries