A.12.4.4 Using a Text Index with PGQL Queries
PGQL queries executed against Oracle Database can use Oracle Text indexes created for vertex and edge properties. After creating a text index, you can use the CONTAINS operator to perform a full text search. CONTAINS has two arguments: a vertex or edge property, and an Oracle Text search string. Any valid Oracle Text search string can be used, including advanced features such as wildcards, stemming, and soundex.
Example A-35 PgqlExample7.java
PgqlExample7.java
shows how to execute a CONTAINS query.
import java.sql.CallableStatement;
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 use an Oracle Text index with a PGQL query.
*/
public class PgqlExample7
{
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();
// Create text index with SQL API
CallableStatement cs = null;
// text index on vertices
cs = conn.prepareCall(
"begin opg_apis.create_vertices_text_idx(:1,:2); end;"
);
cs.setString(1,user);
cs.setString(2,graph);
cs.execute();
cs.close();
// text index on edges
cs = conn.prepareCall(
"begin opg_apis.create_edges_text_idx(:1,:2); end;"
);
cs.setString(1,user);
cs.setString(2,graph);
cs.execute();
cs.close();
// Get a PGQL connection
PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
pgqlConn.setGraph(graph);
// Create a PgqlStatement
ps = pgqlConn.createStatement();
// Query using CONTAINS text search operator on vertex property
// Find all vertices with an lname property value that starts with 'B'
String pgql =
"SELECT v.\"fname\" AS fname, v.\"lname\" AS lname "+
"FROM MATCH (v) "+
"WHERE CONTAINS(v.\"lname\",'B%')";
// execute query
rs = ps.executeQuery(pgql, "");
// print results
System.out.println("-- Vertex Property Query --");
rs.print();
// close result set
rs.close();
// Query using CONTAINS text search operator on edge property
// Find all knows edges with a firstMetIn property value that ends with 'A'
pgql =
"SELECT v1.\"fname\" AS fname1, v2.\"fname\" AS fname2, e.\"firstMetIn\" AS loc "+
"FROM MATCH (v1)-[e:\"knows\"]->(v2) "+
"WHERE CONTAINS(e.\"firstMetIn\",'%A')";
// execute query
rs = ps.executeQuery(pgql, "");
// print results
System.out.println("-- Edge Property Query --");
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();
}
}
}
}
PgqlExample7.java
has the following output for test_graph
(which can be loaded using GraphLoaderExample.java
code).
-- Vertex Property Query -- +---------------+ | FNAME | LNAME | +---------------+ | Susan | Blue | | Bill | Brown | | John | Black | +---------------+ -- Edge Property Query -- +-----------------------+ | FNAME1 | FNAME1 | LOC | +-----------------------+ | Susan | Bill | CA | | John | Bill | GA | | Susan | John | CA | | Susan | Ray | CA | +-----------------------+