8 Using XQuery API for Java to Access Oracle XML DB

Introduction to Oracle XML DB Support for XQJ

XQuery API for Java (XQJ), also known as JSR-225, provides an industry-standard way for Java programs to access Extensible Markup Language (XML) data using XQuery. It lets you evaluate XQuery expressions against XML data sources and process the results as XML data.

Oracle provides two XQuery engines for evaluating XQuery expressions: one in Oracle XML DB, for use with XML data in the database, and one in Oracle XML Developer's Kit (XDK), for use with XML data outside the database. (See Chapter 7 for information about the XQuery engine for XDK).

Oracle provides two different XQJ implementations for accessing these two XQuery engines. Both implementations are part of XDK, enabling you to use XDK to access XML data with a standard XQJ API whether that data resides in the database or elsewhere.

The queries executed by XQJ are written in standard World Wide Web Consortium (W3C) XQuery 1.0 language, as supported by Oracle XML DB. A typical use case for this feature is to access XML data stored in remote databases (in Oracle XML DB) from a local Java program.

General information about XQuery and XQJ is documented outside of this document.

Examples: Using XQJ to Query XML DB

You can use XQJ to query and retrieve data in Oracle XML DB, as shown by the examples in this section.

Example 8-1 shows how to use XQJ to query data from a table in Oracle XML DB. It uses the WAREHOUSES table in the Order Entry (OE) database sample schema. The OE sample schema contains XML documents with warehouse information in the WAREHOUSES table. The WAREHOUSES table contains an XMLType column warehouse_spec and other columns. (See the discussion about standard database schemas in Oracle XML DB Developer's Guide for more information about the data used in this example.)

Specifically, Example 8-1 shows how to perform these steps:

1. Get an XQJ connection to Oracle XML DB

   Every program using XQJ to connect to Oracle XML DB must first create an OXQDDataSource object. Then, OXQDDataSource must be initialized with the required property values before getting an XQJ connection to the Oracle XML DB instance.

2. Prepare an XQuery expression

3. Submit the XQuery expression for evaluation

4. Print each item in the resulting XQuery sequence

import oracle.xml.xquery.xqjdb.OXQDDataSource;
 
import javax.xml.xquery.XQItemType;
import javax.xml.xquery.XQResultSequence;
import javax.xml.xquery.XQConnection;
import javax.xml.xquery.XQPreparedExpression;
 
import javax.xml.namespace.QName;
 
public class example1
{
  public static void main(String argv[])
  {
 
    try
    {
      // Create a new OXQDDataSource for connecting to Oracle XML DB
      OXQDDataSource oxqDS = new OXQDDataSource();
 
      // Set appropriate connection information for the database instance.
      // Must use the thin driver
      oxqDS.setProperty("driver", "jdbc:oracle:thin");
 
      oxqDS.setProperty("dbusername", "oe");
      oxqDS.setProperty("dbpassword", "oe");
 
      // Machine hostname
      oxqDS.setProperty("dbserver", "myserver");
 
      // Database instance port number
      oxqDS.setProperty("dbport", "6479");
 
      // Database instance port number
      oxqDS.setProperty("serviceName", "mydbinstance");
      XQConnection conn = oxqDS.getConnection();
 
      XQItemType  itemTypeInt = conn.createAtomicType(XQItemType.XQBASETYPE_INT);
 
      XQPreparedExpression expr = conn.prepareExpression("declare variable $x as
        xs:int external; for $i in fn:collection('oradb:/OE/WAREHOUSES') where
          $i/ROW/WAREHOUSE_ID < $x return $i/ROW/WAREHOUSE_SPEC/Warehouse");
 
      expr.bindInt(new QName("x"), 3, itemTypeInt);
 
      XQResultSequence xqSeq = expr.executeQuery();
 
      while (xqSeq.next())
         System.out.println (xqSeq.getItemAsString(null));
 
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
  }
}

In Example 8-1, the XQuery expression accesses the WAREHOUSES table through the use of the Universal Resource Identifier (URI) scheme oradb. (See the discussion about the URI scheme oradb in Oracle XML DB Developer's Guide for more information about using XQuery with XML DB to query table or view data.)

Example 8-1 also shows how to bind external variable values in XQJ. The query has an external variable $x, which is used to filter the returned rows from the WAREHOUSES table, by WAREHOUSE_ID.

Example 8-1 generates this output (reformatted for better readability):

<Warehouse><Building>Owned</Building><Area>25000</Area><Docks>2</Docks>
 <DockType>Rearload</DockType><WaterAccess>Y</WaterAccess>
 <RailAccess>N</RailAccess><Parking>Street</Parking>
 <VClearance>10 ft</VClearance></Warehouse>

<Warehouse><Building>Rented</Building><Area>50000</Area><Docks>1</Docks>
 <DockType>Sideload</DockType><WaterAccess>Y</WaterAccess>
 <RailAccess>N</RailAccess><Parking>Lot</Parking>
 <VClearance>12 ft</VClearance></Warehouse>

Example 8-2 shows how to use XQJ to retrieve data from the Oracle XML DB repository. This example assumes that two files, depts.xml and emps.xml, have been uploaded into the XML DB repository under the folder /public. For example, you can use FTP to upload the two files into the Oracle XML DB repository. (See the discussion about using the Oracle XML DB repository in Oracle XML DB Developer's Guide for more information about storing data in and using the Oracle XML DB repository.)

The content of depts.xml is:

depts.xml:
 
<?xml version="1.0"?>
     <depts>
       <dept deptno="10" dname="Administration"/>
       <dept deptno="20" dname="Marketing"/>
       <dept deptno="30" dname="Purchasing"/>
     </depts>

The content of emps.xml is:

emps.xml:
 
<?xml version="1.0"?>
   <emps>
    <emp empno="1" deptno="10" ename="John"  salary="21000"/>
       <emp empno="2" deptno="10" ename="Jack" salary="310000"/>
       <emp empno="3" deptno="20" ename="Jill" salary="100001"/>
     </emps>

You can use the fn:doc and fn:collection functions to query the data in the Oracle XML DB repository with XQuery. Example 8-2 shows how to use the fn:doc function within XQuery to access the repository. (See the discussion about querying XML data in the Oracle XML DB repository in Oracle XML DB Developer's Guide for more information about using these XQuery functions.)

import oracle.xml.xquery.xqjdb.OXQDDataSource;
 
import javax.xml.xquery.XQItemType;
import javax.xml.xquery.XQResultSequence;
import javax.xml.xquery.XQConnection;
import javax.xml.xquery.XQPreparedExpression;
 
import javax.xml.namespace.QName;
 
public class example2
{
  public static void main(String argv[])
  {
 
    try
    {
      // Create a new OXQDDataSource for connecting to Oracle XML DB
      OXQDDataSource oxqDS = new OXQDDataSource();
 
      // Set appropriate connection information for the database instance.
      // Must use the thin driver
      oxqDS.setProperty("driver", "jdbc:oracle:thin");
 
      oxqDS.setProperty("dbusername", "oe");
      oxqDS.setProperty("dbpassword", "oe");
 
      // Machine hostname
      oxqDS.setProperty("dbserver", "myserver");
 
      // Database instance port number
      oxqDS.setProperty("dbport", "6479");
 
      // Database instance port number
      oxqDS.setProperty("serviceName", "mydbinstance");
 
      XQConnection conn = oxqDS.getConnection();
 
      XQPreparedExpression expr = conn.prepareExpression("for $e in
        doc(\"/public/emps.xml\")/emps/emp let $d :=
        doc(\"/public/depts.xml\")//dept[@deptno = $e/@deptno]/@dname where
        $e/@salary > 100000 order by $e/@empno return 
        <emp ename=\"{$e/@ename}\" dept=\"{$d}\"/>");
 
      XQResultSequence xqSeq = expr.executeQuery();
 
      while (xqSeq.next())
         System.out.println (xqSeq.getItemAsString(null));
 
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
  }
}

Example 8-2 generates this output:

<emp ename="Jack" dept="Administration"/>
<emp ename="Jill" dept="Marketing"/>

XQJ Support for Oracle XML DB

The two Oracle XQJ implementations differ in some respects. Oracle XML DB support for XQJ is described in this section. Chapter 7 provides information about using XQJ to access the mid-tier XQuery engine.

Table 8-1 describes the OXQDDataSource properties to be used for connection to Oracle XML DB. To create an XQJ connection to Oracle XML DB, you must set the values for these properties. You must set either the dbname or the serviceName property value, and all the other OXQDDataSource property values listed in Table 8-1.

^Footnote 1 You can identify the database using either the service id or the service name.

Table 8-2 describes the Oracle XML DB support for optional XQJ features.

XQDataSource.getConnection(
  java.sql.Connection)

XQDataSource.getConnection(
  java.lang.String,
  java.lang.String)

XQJ Performance Considerations for Use with Oracle XML DB

To fetch a sequence of items from the database, use XQResultSequence method next() to retrieve a single item at a time; then use an XQItemAccessor method to fetch all data corresponding to that item.

This provides better performance than using these whole-sequence fetch methods, which each materialize the entire sequence before returning any data.

• getSequenceAsStream()

• getSequenceAsString(java.util.Properties props)

• writeSequence(java.io.OutputStream os, java.util.Properties props)

• writeSequence(java.io.Writer ow, java.util.Properties props)

• writeSequenceToResult(javax.xml.transform.Result result)

• writeSequenceToSAX(org.xml.sax.ContentHandler saxhdlr)

For example, if you invoke getSequenceAsStream(), all of the XQuery result sequence data is fetched from the database before the XMLStreamReader instance that is built from it is returned to your program.

Be aware also that items themselves are not streamable: the item accessor methods always materialize an entire item before outputting any part of it.

For inputting, all bind methods defined on XQDynamicContext fully materialize the input data before passing it to the database.

For example, when you invoke bindDocument(javax.xml.namespace.QName varName, javax.xml.stream.XMLStreamReader value, XQItemType type), all the data that is referenced by the input XMLStreamReader instance is processed before the external XQuery variable is bound to it.