6.9.1.4.5 Obtaining the SQL Translation for a PGQL Query
You can obtain the SQL translation for a PGQL query through methods in PgqlStatement and PgqlPreparedStatement. The raw SQL for a PGQL query can be useful for several reasons:
-
You can execute the SQL directly against the database with other SQL-based tools or interfaces (for example, SQL*Plus or SQL Developer).
-
You can customize and tune the generated SQL to optimize performance or to satisfy a particular requirement of your application.
-
You can build a larger SQL query that joins a PGQL subquery with other data stored in Oracle Database (such as relational tables, spatial data, and JSON data).
Example 6-15 PgqlExample8.java
PgqlExample8.java shows how to obtain the raw SQL translation for a PGQL query. The translateQuery method of PgqlStatement returns an PgqlSqlQueryTrans object that contains information about return columns from the query and the SQL translation itself.
The translated SQL returns different columns depending on the type of "logical" object or value projected from the PGQL query. A vertex or edge projected in PGQL has two corresponding columns projected in the translated SQL:
-
$IT : id type – NVARCHAR(1): 'V' for vertex or 'E' for edge
-
$ID : vertex or edge identifier – NUMBER: same content as VID or EID columns in VT$ and GE$ tables
A property value or constant scalar value projected in PGQL has four corresponding columns projected in the translated SQL:
-
$T : value type – NUMBER: same content as T column in VT$ and GE$ tables
-
$V: value – NVARCHAR2(15000): same content as V column in VT$ and GE$ tables
-
$VN: number value – NUMBER: same content as VN column in VT$ and GE$ tables
-
$VT: temporal value – TIMESTAMP WITH TIME ZONE: same content as VT column in VT$ and GE$ tables
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 has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).
-- 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)
Example 6-16 PgqlExample9.java
You can also obtain the SQL translation for PGQL queries with bind variables. In this case, the corresponding SQL translation will also contain bind variables. The PgqlSqlQueryTrans interface has a getSqlBvList method that returns an ordered List of Java Objects that should be bound to the SQL query (the first Object on the list should be set at position 1, and the second should be set at position 2, and so on).
PgqlExample9.java shows how to get and execute the SQL for a PGQL query with bind variables.
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 has the following output for test_graph (which can be loaded using GraphLoaderExample.java code).
–-- 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 --