Retrieving User-Friendly Java Objects from SEM_MATCH or SQL-Based Query Results

You can query an RDF graph using any of the following approaches.

SPARQL (through Java methods or web service end point)
SEM_MATCH (table function that has SPARQL queries embedded)
SQL ( by querying the <user>.<network_name>#RDFM<model> view and joining with <user>.<network_name>#RDF_VALUE$ and/or other tables)

For Java developers, the results from the first approach are easy to consume. The results from the second and third approaches, however, can be difficult for Java developers because you must parse various columns to get properly typed Java objects that are mapped from typed RDF literals. RDF graph support for Apache Jena supports several methods and helper functions to simplify the task of getting properly typed Java objects from a JDBC result set. These methods and helper functions are shown in the following examples:

These examples use an RDF graph TGRAPH into which a set of typed literals is added through inserts into the RDF graph’s RDFT view, as in the following code:

exec sem_apis.create_rdf_graph('tgraph',null,null,network_owner=>'RDFUSR',network_name=>'LOCALNET');
exec sem_apis.truncate_rdf_graph('tgraph', network_owner=>'RDFUSR',network_name=>'LOCALNET');

-- Add some triples
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s1>','<urn:p1>', '<urn:o1>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s2>','<urn:p2>', '"hello world"','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s3>','<urn:p3>', '"hello world"@en','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s4>','<urn:p4>', '" o1o "^^<http://www.w3.org/2001/XMLSchema#string>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s4>','<urn:p4>', '"xyz"^^<http://mytype>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s5>','<urn:p5>', '"123"^^<http://www.w3.org/2001/XMLSchema#integer>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s5>','<urn:p5>', '"123.456"^^<http://www.w3.org/2001/XMLSchema#double>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph','<urn:s6>','<urn:p6>', '_:bn1','RDFUSR','LOCALNET'));

-- Add some quads
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g1>','<urn:s1>','<urn:p1>', '<urn:o1>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s1>','<urn:p1>', '<urn:o1>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s2>','<urn:p2>', '"hello world"','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s3>','<urn:p3>', '"hello world"@en','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s4>','<urn:p4>', '" o1o "^^<http://www.w3.org/2001/XMLSchema#string>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s4>','<urn:p4>', '"xyz"^^<http://mytype>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s5>','<urn:p5>', '"123"^^<http://www.w3.org/2001/XMLSchema#integer>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s5>','<urn:p5>', '"123.456"^^<http://www.w3.org/2001/XMLSchema#double>','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s6>','<urn:p6>', '_:bn1','RDFUSR','LOCALNET'));
insert into LOCALNET#RDFT_TGRAPH(TRIPLE) values(sdo_rdf_triple_s('tgraph:<urn:g2>','<urn:s7>','<urn:p7>', '"2002-10-10T12:00:00-05:00"^^<http://www.w3.org/2001/XMLSchema#dateTime>','RDFUSR','LOCALNET'));
commit;

Example: SQL-Based Graph Query Example: SQL-Based Graph Query runs a pure SQL-based graph query and constructs Jena objects.

iTimeout = 0; // no time out
iDOP = 1;     // degree of parallelism
iStartColPos = 2;

queryString = "select 'hello'||rownum as extra, o.VALUE_TYPE,o.LITERAL_TYPE,o.LANGUAGE_TYPE,o.LONG_VALUE,o.VALUE_NAME "
            + "  from rdfusr.localnet#rdfm_tgraph g, rdfusr.localnet#rdf_value$ o where g.canon_end_node_id = o.value_id";

rs = oracle.executeQuery(queryString, iTimeout, iDOP, bindValues);

while (rs.next()) {
  node = OracleSemIterator.retrieveNodeFromRS(rs, iStartColPos, OracleSemQueryPlan.CONST_FIVE_COL, translator);
  System.out.println("Result " + node.getClass().getName() + " = " + node + " " + rs.getString(1));
}

Example: SQL-Based Graph Query might generate the following output:

Result org.apache.jena.graph.Node_Literal = "123"^^http://www.w3.org/2001/XMLSchema#decimal hello1
Result org.apache.jena.graph.Node_Literal = "123"^^http://www.w3.org/2001/XMLSchema#decimal hello2
Result org.apache.jena.graph.Node_URI = urn:o1 hello3
Result org.apache.jena.graph.Node_URI = urn:o1 hello4
Result org.apache.jena.graph.Node_URI = urn:o1 hello5
Result org.apache.jena.graph.Node_Literal = "hello world" hello6
Result org.apache.jena.graph.Node_Literal = "hello world" hello7
Result org.apache.jena.graph.Node_Literal = "hello world"@en hello8
Result org.apache.jena.graph.Node_Literal = "hello world"@en hello9
Result org.apache.jena.graph.Node_Literal = " o1o " hello10
Result org.apache.jena.graph.Node_Literal = " o1o " hello11
Result org.apache.jena.graph.Node_Literal = "xyz"^^http://mytype hello12
Result org.apache.jena.graph.Node_Literal = "xyz"^^http://mytype hello13
Result org.apache.jena.graph.Node_Literal = "1.23456E2"^^http://www.w3.org/2001/XMLSchema#double hello14
Result org.apache.jena.graph.Node_Literal = "1.23456E2"^^http://www.w3.org/2001/XMLSchema#double hello15
Result org.apache.jena.graph.Node_Blank = m15mbn1 hello16
Result org.apache.jena.graph.Node_Blank = m15g3C75726E3A67323Egmbn1 hello17
Result org.apache.jena.graph.Node_Literal = "2002-10-10T17:00:00Z"^^http://www.w3.org/2001/XMLSchema#dateTime hello18

Example: Hybrid Query Mixing SEM_MATCH with Regular SQL Constructs Example: Hybrid Query Mixing SEM_MATCH with Regular SQL Constructs uses the OracleSemIterator.retrieveNodeFromRS API to construct a Jena object by reading the five consecutive columns (in the exact order of value type, literal type, language type, long value, and value name), and by performing the necessary unescaping and object instantiations.

iStartColPos = 1;
queryString = "select  g$RDFVTYP, g, count(1) as cnt "
            + "  from table(sem_match('{ GRAPH ?g { ?s ?p ?o . } }',sem_models('tgraph'),null,null,null,null,null,null,null,'RDFUSR','LOCALNET')) "
            + " group by g$RDFVTYP, g";

rs = oracle.executeQuery(queryString, iTimeout, iDOP, bindValues);
while (rs.next()) {
  node = OracleSemIterator.retrieveNodeFromRS(rs, iStartColPos, OracleSemQueryPlan.CONST_TWO_COL, translator);
  System.out.println("Result " + node.getClass().getName() + " = " + node + " " + rs.getInt(iStartColPos + 2));
}

Example: Hybrid Query Mixing SEM_MATCH with Regular SQL Constructs might generate the following output:

Result org.apache.jena.graph.Node_URI = urn:g2 9
Result org.apache.jena.graph.Node_URI = urn:g1 1

In Example: Hybrid Query Mixing SEM_MATCH with Regular SQL Constructs:

The helper function executeQuery in the Oracle class is used to run the SQL statement, and the OracleSemIterator.retrieveNodeFromRS API (also used in Example: SQL-Based Graph Query) is used to construct Jena objects.
Only two columns are used in the output: value type (g$RDFVTYP) and value name (g), it is known that this g variable can never be a literal RDF resource.
The column order is significant. For a two-column variable, the first column must be the value type and the second column must be the value name.

Example: SEM_MATCH Query Example: SEM_MATCH Query runs a SEM_MATCH query and constructs an iterator (instance of OracleSemIterator) that returns a list of Jena objects.

queryString = "select  g$RDFVTYP, g, s$RDFVTYP, s, p$RDFVTYP, p, o$RDFVTYP,o$RDFLTYP,o$RDFLANG,o$RDFCLOB,o "
            + "  from table(sem_match('{ GRAPH ?g { ?s ?p ?o . } }',sem_models('tgraph'),null,null,null,null,null,null,null,'RDFUSR','LOCALNET'))";
guide = new ArrayList<String>();
guide.add(OracleSemQueryPlan.CONST_TWO_COL);
guide.add(OracleSemQueryPlan.CONST_TWO_COL);
guide.add(OracleSemQueryPlan.CONST_TWO_COL);
guide.add(OracleSemQueryPlan.CONST_FIVE_COL);

rs = oracle.executeQuery(queryString, iTimeout, iDOP, bindValues);
osi = new OracleSemIterator(rs);
osi.setGuide(guide);
osi.setTranslator(translator);

while (osi.hasNext()) {
  result = osi.next();
  System.out.println("Result " + result.getClass().getName() + " = " + result);
}

Example: SEM_MATCH Query might generate the following output:

Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s5 2:urn:p5 3:"123"^^http://www.w3.org/2001/XMLSchema#decimal>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s5 2:urn:p5 3:"1.23456E2"^^http://www.w3.org/2001/XMLSchema#double>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s7 2:urn:p7 3:"2002-10-10T17:00:00Z"^^http://www.w3.org/2001/XMLSchema#dateTime>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s2 2:urn:p2 3:"hello world">
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s4 2:urn:p4 3:" o1o ">
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s4 2:urn:p4 3:"xyz"^^http://mytype>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s6 2:urn:p6 3:m15g3C75726E3A67323Egmbn1>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s1 2:urn:p1 3:urn:o1>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g1 1:urn:s1 2:urn:p1 3:urn:o1>
Result oracle.spatial.rdf.client.jena.Domain = <domain 0:urn:g2 1:urn:s3 2:urn:p3 3:"hello world"@en>

In Example: SEM_MATCH Query:

OracleSemIterator takes in a JDBC result set. OracleSemIterator needs guidance on parsing all the columns that represent the bind values of SPARQL variables. A guide is simply a list of string values. Two constants have been defined to differentiate a 2-column variable (for subject or predicate position) from a 5-column variable (for object position). A translator is also required.
Four variables are used in the output. The first three variables are not RDF literal resources, so CONST_TWO_COL is used as their guide. The last variable can be an RDF literal resource, so CONST_FIVE_COL is used as its guide.
The column order is significant, and it must be as shown in the example.