6.9.4.3.1 PGQL問合せでのバインド変数の使用
パフォーマンス改善とセキュリティ向上のためにPGQL問合せでバインド変数を使用できます。PGQL問合せの定数スカラー値は、バインド変数に置き換えることができます。バインド変数は、'?' (疑問符)で示されます。一定の年齢値よりも上の年齢の人を選択する、次の2つの問合せについて検討します。
// 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
これらの問合せのSQL変換は、年齢フィルタに同様の方法で定数30および40を使用します。データベースは、これらの各問合せのハード解析を実行します。このハード解析時間は、単純な問合せの実行時間を超過することがよくあります。
次のように、各問合せ内の定数をバインド変数に置き換えることができます。
SELECT v.fname AS fname, v.lname AS lname, v.age AS age
FROM MATCH (v)
WHERE v.age > ?
これにより、SQLエンジンは、様々な年齢値に再利用できる、この問合せの汎用カーソルを作成できます。その結果、ハード解析が様々な年齢値に対してこの問合せを実行する必要がなくなり、各問合せの解析時間が大幅に短縮されます。
さらに、PGQL問合せでバインド変数を使用するアプリケーションでは、文字列連結を使用して定数値をPGQL問合せに埋め込むものに比べ、インジェクション攻撃に対する脆弱性が軽減されます。
カーソル共有とバインド変数の詳細は、『Oracle Database SQLチューニング・ガイド』も参照してください。
PgqlExample5.javaに示すように、PgqlPreparedStatementインタフェースは、バインド変数を使用する問合せを実行するために使用できます。PgqlPreparedStatementには、問合せ実行のための値の設定に使用できる様々な値タイプ用の複数の設定メソッドが用意されています。
PGQLではバインド変数にいくつかの制限があります。バインド変数は、定数プロパティ値にのみを使用できます。つまり、頂点とエッジをバインド変数に置き換えることはできません。また、一旦特定のバインド変数をある型に設定したら、それを別の型に設定することはできません。たとえば、PgqlPreparedStatementに対してsetInt(1, 30)が実行される場合、その同じPgqlPreparedStatementでsetString(1, "abc")をコールすることはできません。
例6-12 PgqlExample5.java
PgqlExample5.javaは、PGQL問合せでのバインド変数の使用方法を示しています。
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には、test_graph (GraphLoaderExample.javaコードを使用してロード可能)に対する次の出力があります。
-- 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 | +---------------------+
例6-13 PgqlExample6.java
PgqlExample6.javaは、2つのバインド変数、1つのString変数と1つのTimestamp変数を含む問合せを示しています。
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は、test_graph (GraphLoaderExample.javaコードを使用してロード可能)に対して次の出力を提供します。
-- 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 | +-----------------+
親トピック: PGQL問合せのセキュリティ手法