6.9.1.4.5 PGQL問合せのSQL変換の取得

PgqlStatementメソッドとPgqlPreparedStatementメソッドを介してPGQL問合せのSQL変換を取得できます。PGQL問合せの未加工のSQLは、次のいくつかの理由で役立ちます。

• 他のSQLベースのツールまたはインタフェース(たとえば、SQL*PlusやSQL Developer)を使用してデータベースに対してSQLを直接実行できます。

• 生成されたSQLをカスタマイズおよびチューニングして、パフォーマンスの最適化やアプリケーションの特定要件の達成を実行できます。

• PGQL副問合せを、Oracle Databaseに格納されている他のデータ(リレーショナル表、空間データ、およびJSONデータなど)と結合する大規模なSQL問合せを作成できます。

例6-15 PgqlExample8.java

PgqlExample8.javaは、PGQL問合せの未加工のSQL変換の取得方法を示しています。PgqlStatementのtranslateQueryメソッドは、問合せからの戻り列とSQL変換自体に関する情報を含むPgqlSqlQueryTransオブジェクトを返します。

変換されたSQLは、PGQL問合せから射影される「論理」オブジェクトまたは値の型に応じて異なる列を返します。PGQLに射影される頂点またはエッジには、変換されたSQLに射影される、2つの対応する列があります。

• $IT : ID型– NVARCHAR(1): 「V」は頂点、「E」はエッジ。

• $ID :頂点IDまたはエッジID – NUMBER: VT$表とGE$表内のVID列またはEID例と同じ内容

PGQLに射影されているプロパティ値または定数スカラー値には、変換されたSQLに射影される4つの対応する列があります。

• $T :値の型– NUMBER: VT$表とGE$表内のT列と同じ内容

• $V:値– NVARCHAR2(15000): VT$表とGE$表内のV列と同じ内容

• $VN:数値の値– NUMBER: VT$表とGE$表内のVN列と同じ内容

• $VT: Temporal値– TIMESTAMP WITH TIME ZONE: VT$表とGE$表内のVT列と同じ内容

import java.sql.Connection;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain the SQL translation for a PGQL query.
 */
public class PgqlExample8
{

  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;

    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);

      // PGQL query to be translated
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, e, e.\"since\" AS since "+
        "FROM MATCH (v1)-[e:\"knows\"]->(v2)";

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

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = ps.translateQuery(pgql,"");

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlTrans.getSqlTranslation());
    }
    finally {
      // close the statement
      if (ps != null) {
        ps.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample8.javaには、test_graph (GraphLoaderExample.javaコードを使用してロード可能)に対する次の出力があります。

-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[e] colType=[EDGE] offset=[7]
colName=[since] colType=[VALUE] offset=[9]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.SVID AS "V1$ID",
T0$1.T AS "FNAME1$T",
T0$1.V AS "FNAME1$V",
T0$1.VN AS "FNAME1$VN",
T0$1.VT AS "FNAME1$VT",
n'E' AS "E$IT",
T0$0.EID AS "E$ID",
T0$0.T AS "SINCE$T",
T0$0.V AS "SINCE$V",
T0$0.VN AS "SINCE$VN",
T0$0.VT AS "SINCE$VT"
FROM ( SELECT L.EID, L.SVID, L.DVID, L.EL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHGT$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHGE$ WHERE K=n'since' ) R
  WHERE L.EID = R.EID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.SVID=T0$1.VID AND
(T0$0.EL = n'knows' AND T0$0.EL IS NOT NULL)

例6-16 PgqlExample9.java

また、バインド変数を含むPGQL問合せのSQL変換も取得できます。この場合、対応するSQL変換にもバインド変数が含まれます。PgqlSqlQueryTransインタフェースには、SQL問合せにバインドされている必要があるJavaオブジェクトの順序付きリスト(リストの最初のオブジェクトは1の位置に設定され、2番目は2の位置に設定されている必要がある)を返すgetSqlBvListメソッドがあります。

PgqlExample9.javaは、バインド変数を使用したPGQL問合せのSQLの取得および実行方法を示しています。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.List;

import oracle.pg.rdbms.pgql.PgqlColumnDescriptor;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlPreparedStatement;
import oracle.pg.rdbms.pgql.PgqlSqlQueryTrans;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

/**
 * This example shows how to obtain and execute the SQL translation for a 
 * PGQL query that uses bind variables.
 */
public class PgqlExample9
{

  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 pgqlPs = null;

    PreparedStatement sqlPs = 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);

      // Execute query to get a ResultSet object
      String pgql = 
        "SELECT v1, v1.\"fname\" AS fname1, v1.\"age\" AS age, ? as constVal "+
        "FROM MATCH (v1) "+
        "WHERE  v1.\"fname\" = ? OR v1.\"age\" < ?";


      // Create a PgqlStatement
      pgqlPs = pgqlConn.prepareStatement(pgql);

      // set bind values
      pgqlPs.setDouble(1, 2.05d);
      pgqlPs.setString(2, "Bill");
      pgqlPs.setInt(3, 35);

      // Get the SQL translation
      PgqlSqlQueryTrans sqlTrans = pgqlPs.translateQuery("");

      // Get the SQL String
      String sqlStr = sqlTrans.getSqlTranslation();

      // Get the return column descriptions
      PgqlColumnDescriptor[] cols = sqlTrans.getReturnTypes();

      // Get the bind values
      List<Object> bindVals = sqlTrans.getSqlBvList();

      // Print column descriptions
      System.out.println("-- Return Columns -----------------------");
      printReturnCols(cols);

      // Print SQL translation
      System.out.println("-- SQL Translation ----------------------");
      System.out.println(sqlStr);

      // Print Bind Values
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute Query
      // Get PreparedStatement
      sqlPs = conn.prepareStatement("SELECT COUNT(*) FROM ("+sqlStr+")");
      // Set bind values and execute the PreparedStatement
      executePs(sqlPs, bindVals);

      // Set new bind values in the PGQL PreparedStatement 
      pgqlPs.setDouble(1, 3.02d);
      pgqlPs.setString(2, "Ray");
      pgqlPs.setInt(3, 30);

      // Print Bind Values
      bindVals = sqlTrans.getSqlBvList();
      System.out.println("\n-- Bind Values --------------------------");
      for (Object obj : bindVals) {
        System.out.println(obj.toString());
      }

      // Execute the PreparedStatement with new bind values
      executePs(sqlPs, bindVals);
    }
    finally {
      // close the SQL statement
      if (sqlPs != null) {
        sqlPs.close();
      }
      // close the statement
      if (pgqlPs != null) {
        pgqlPs.close();
      }
      // close the connection
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * Executes a SQL PreparedStatement with the input bind values
   */
  static void executePs(PreparedStatement ps, List<Object> bindVals) throws Exception
  {
    ResultSet rs = null;
    try {
      // Set bind values
      for (int idx = 0; idx < bindVals.size(); idx++) {
        Object o = bindVals.get(idx);
        // String
        if (o instanceof java.lang.String) {
          ps.setNString(idx + 1, (String)o);
        }
        // Int
        else if (o instanceof java.lang.Integer) {
          ps.setInt(idx + 1, ((Integer)o).intValue());
        }
        // Long
        else if (o instanceof java.lang.Long) {
          ps.setLong(idx + 1, ((Long)o).longValue());
        }
        // Float
        else if (o instanceof java.lang.Float) {
          ps.setFloat(idx + 1, ((Float)o).floatValue());
        }
        // Double
        else if (o instanceof java.lang.Double) {
          ps.setDouble(idx + 1, ((Double)o).doubleValue());
        }
       // Timestamp
       else if (o instanceof java.sql.Timestamp) {
         ps.setTimestamp(idx + 1, (Timestamp)o);
       }
       else {
         ps.setString(idx + 1, bindVals.get(idx).toString());
       }
     }

      // Execute query
      rs = ps.executeQuery();
      if (rs.next()) {
        System.out.println("\n-- Execute Query: Result has "+rs.getInt(1)+" rows --");
      }
    }
    finally {
      // close the SQL ResultSet
      if (rs != null) {
        rs.close();
      }
    }
  }

  /**
   * Prints return columns for a SQL translation
   */
  static void printReturnCols(PgqlColumnDescriptor[] cols) throws Exception
  {
    StringBuffer buff = new StringBuffer("");

    for (int i = 0; i < cols.length; i++) {

      String colName = cols[i].getColName();
      PgqlColumnDescriptor.Type colType = cols[i].getColType();
      int offset = cols[i].getSqlOffset();

      String readableType = "";
      switch(colType) {
        case VERTEX:
          readableType = "VERTEX";
          break;
        case EDGE:
          readableType = "EDGE";
          break;
        case VALUE:
          readableType = "VALUE";
          break;
      }

      buff.append("colName=["+colName+"] colType=["+readableType+"] offset=["+offset+"]\n");
    }
    System.out.println(buff.toString());
  }
}

PgqlExample9.javaには、test_graph (GraphLoaderExample.javaコードを使用してロード可能)に対する次の出力があります。

–-- Return Columns -----------------------
colName=[v1] colType=[VERTEX] offset=[1]
colName=[fname1] colType=[VALUE] offset=[3]
colName=[age] colType=[VALUE] offset=[7]
colName=[constVal] colType=[VALUE] offset=[11]
-- SQL Translation ----------------------
SELECT n'V' AS "V1$IT",
T0$0.VID AS "V1$ID",
T0$0.T AS "FNAME1$T",
T0$0.V AS "FNAME1$V",
T0$0.VN AS "FNAME1$VN",
T0$0.VT AS "FNAME1$VT",
T0$1.T AS "AGE$T",
T0$1.V AS "AGE$V",
T0$1.VN AS "AGE$VN",
T0$1.VT AS "AGE$VT",
4 AS "CONSTVAL$T",
to_nchar(?,'TM9','NLS_Numeric_Characters=''.,''') AS "CONSTVAL$V",
? AS "CONSTVAL$VN",
to_timestamp_tz(null) AS "CONSTVAL$VT"
FROM ( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'fname' ) R
  WHERE L.VID = R.VID(+)
) T0$0,
( SELECT L.VID, L.VL, R.K, R.T, R.V, R.VN, R.VT
  FROM "SCOTT".TEST_GRAPHVD$ L,
       (SELECT * FROM "SCOTT".TEST_GRAPHVT$ WHERE K=n'age' ) R
  WHERE L.VID = R.VID(+)
) T0$1
WHERE T0$0.VID=T0$1.VID AND
((T0$0.T = 1 AND T0$0.V = ?) OR T0$1.VN < ?)

-- Bind Values --------------------------
2.05
2.05
Bill
35
-- Execute Query: Result has 2 rows --

-- Bind Values --------------------------
3.02
3.02
Ray
30
-- Execute Query: Result has 1 rows --