Class OracleXMLQuery makes up the XML generation part of the XSU Java API.

Figure 21-1 shows the basic process for generating XML with XSU.

The basic steps in Figure 21-1 are:

Figure 21-1 Generating XML with XSU

Description of "Figure 21-1 Generating XML with XSU"

1. Create a JDBC connection to the database. Normally, you establish a connection with the DriverManager class, which manages a set of JDBC drivers. After the JDBC drivers are loaded, invoke getConnection(). When it finds the right driver, this method returns a Connection object that represents a database session. All SQL statements are executed within the context of this session.

   You have these options:

   • Create the connection with the JDBC Oracle Call Interface (OCI) driver. This code fragment shows this technique:

     // import the Oracle driver class
     import oracle.jdbc.*;
     // load the Oracle JDBC driver
     DriverManager.registerDriver(new oracle.jdbc.OracleDriver());     
     // create the connection
     Connection conn =
        DriverManager.getConnection("jdbc:oracle:oci:@","hr","password");
     

     The preceding example uses the default connection for the JDBC OCI driver.

   • Create the connection with the JDBC thin driver. The thin driver is written in pure Java and can be called from any Java program. This code fragment shows this technique:

     Connection conn =        
        DriverManager.getConnection("jdbc:oracle:thin:@dlsun489:1521:ORCL",
          "hr","password");
     

     The thin driver requires the host name (dlsun489), port number (1521), and the Oracle system identifier (SID), ORCL. The database must have an active Transmission Control Protocol/Internet Protocol (TCP/IP) listener.

   • Use default connection used by the server-side internal JDBC driver. This driver runs within a default session and default transaction context. You are already connected to the database; your SQL operations are part of the default transaction. Thus, you do not have to register the driver. Create the Connection object:

     Connection conn = new oracle.jdbc.OracleDriver().defaultConnection ();

   Note:

   OracleXMLDataSetExtJdbc is used only for Oracle JDBC, whereas OracleXMLDataSetGenJdbc is used for non-Oracle JDBC. These classes are in the oracle.xml.sql.dataset package.

2. Create an XML query object and assign it a SQL query. You create an OracleXMLQuery Class instance by passing a SQL query to the constructor, as shown in this example:

   OracleXMLQuery qry = new OracleXMLQuery (conn, "SELECT * from EMPLOYEES");
   

3. Configure the XML query object by invoking OracleXMLQuery methods. This example specifies that only 20 rows are to be included in the result set:

   xmlQry.setMaxRows(20); 
   

4. Return a DOM object or string by invoking OracleXMLQuery methods. For example, get a DOM object:

   XMLDocument domDoc = (XMLDocument)qry.getXMLDOM();
   

   Get a string object:

   String xmlString = qry.getXMLString();
   

5. Perform additional processing on the string or DOM as needed.

   See Also:

   • Oracle Database Java Developer’s Guide to learn about Oracle JDBC

   • Oracle Database XML Java API Reference to learn about OracleXMLQuery methods

Use the OracleXMLSave class to insert, update, and delete XML in the database.

Figure 21-2 shows the basic process.

Figure 21-2 Storing XML in the Database Using XSU

Description of "Figure 21-2 Storing XML in the Database Using XSU"

The basic steps in Figure 21-2 are:

1. Create a JDBC connection to the database. This step is identical to the first step described in Generating XML with the XSU Java API: Basic Process.

2. Create an XML save object and assign it a table on which to perform DML. Pass a table or view name to the constructor, as shown in this example:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Specify the primary key columns. For example, this code specifies that employee_id is the key column:

   String [] keyColNames = new String[1];
   keyColNames[0] = "EMPLOYEE_ID";
   sav.setKeyColumnList(keyColNames);
   

4. Configure the XML save object by invoking OracleXMLSave methods. This example specifies an update of the salary and job_id columns:

   String[] updateColNames = new String[2];
   updateColNames[0] = "SALARY";
   updateColNames[1] = "JOB_ID";
   sav.setUpdateColumnList(updateColNames); // set the columns to update
   

5. Invoke the insertXML(), updateXML(), or deleteXML() methods on the OracleXMLSave object. This example shows an update:

   // Assume that the user passes in this XML document as the first argument
   sav.updateXML(sav.getURL(argv[0]));
   

   When performing the DML, XSU performs these tasks:

   1. Parses the input XML document.

   2. Matches element names to column names in the target table or view.

   3. Converts the elements to SQL types and binds them to the appropriate statement.

6. Close the OracleXMLSave object and deallocate all contexts associated with it, as shown in this example:

   sav.close();

   See Also:

   • Oracle Database Java Developer’s Guide to learn about JDBC

   • Oracle Database XML Java API Reference to learn about OracleXMLSave

The typical architecture is shown for applications that use the XSU libraries installed in the database.

Figure 21-3 illustrates this typical architecture. XML generated from XSU running in the database can be placed in advanced queues in the database to be queued to other systems or clients. You deliver the XML internally through stored procedures in the database or externally through web servers or application servers.

In Figure 21-3 all lines are bidirectional. Because XSU can generate and save data, resources can deliver XML to XSU running inside the database, which can then insert it in the appropriate database tables.

Figure 21-3 Running XSU in the Database

Description of "Figure 21-3 Running XSU in the Database"

You can run XSU in an application server.

Your application architecture may require an application server in the middle tier. The application tier can be a database or an application server that supports Java programs.

You can generate XML in the middle tier from SQL queries or ResultSets for various reasons, for example, to integrate different JDBC data sources in the middle tier. In this case, you can install XSU in your middle tier, thereby enabling your Java programs to make use of XSU through its Java API.

Figure 21-4 shows a typical architecture for running XSU in a middle tier. In the middle tier, data from JDBC sources is converted by XSU into XML and then sent to web servers or other systems. Again, the process is bidirectional, which means that the data can be put back into the JDBC sources (database tables or views) with XSU. If a database is used as the application server, then you can use the PL/SQL front end instead of Java.

Figure 21-4 Running XSU in the Middle Tier

Description of "Figure 21-4 Running XSU in the Middle Tier"

You can run XSU in a web server because the web server supports Java servlets.

Figure 21-5 shows XSU running in a web server.

Figure 21-5 Running XSU in a Web Server

Description of "Figure 21-5 Running XSU in a Web Server"

You can write Java servlets that use XSU. XSQL Servlet is a standard servlet provided by Oracle. It is built on top of XSU and provides a template-like interface to XSU functionality. To perform XML processing in the web server and avoid intricate servlet programming, you can use the XSQL Servlet.

To generate XML from the database schema use the getXML parameter.

java OracleXML getXML -user "hr/password" "SELECT * FROM employees"

1. Connects to the current default database
2. Executes the specified SELECT query
3. Converts the SQL result set to XML
4. Prints the XML to standard output

You can use XSU to generate XML from tables with XMLType columns.

java OracleXML getXML -user "hr/password" -rowTag "Part" "SELECT * FROM parts"

<?xml version = '1.0'?>
<ROWSET>
   <Part num="1">
      <PARTNO>1735</PARTNO>
      <PARTNAME>Gizmo</PARTNAME>
      <PARTDESC>
         <Description>
           <Title>Description of the Gizmo</Title>
           <Author>John Smith</Author>
           <Body>
             The <b>Gizmo</b> is <i>grand</i>.
           </Body>
         </Description>
      </PARTDESC>
   </Part>
</ROWSET>

An example shows how to insert an XML document into a database table.

java OracleXML putXML -user "hr/password" -fileName "new_employees.xml" employees

1. Connects to the current database as hr
2. Reads the XML document named new_emp.xml
3. Parses the XML document, matching the tags with column names
4. Inserts the values appropriately into the employees table

The testXMLSQL program is described.

1. Compile testXMLSQL.java with javac.
2. Execute java testXMLSQL on the command line.

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPLOYEE_ID>100</EMPLOYEE_ID>
      <FIRST_NAME>Steven</FIRST_NAME>
      <LAST_NAME>King</LAST_NAME>
      <EMAIL>SKING</EMAIL>
      <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
      <HIRE_DATE>6/17/1987 0:0:0</HIRE_DATE>
      <JOB_ID>AD_PRES</JOB_ID>
      <SALARY>24000</SALARY>
      <DEPARTMENT_ID>90</DEPARTMENT_ID>
   </ROW>
<!-- ROW num="2" through num="107" ... -->
</ROWSET>

Different ways to limit the number of rows in a result set are described.

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="6">
      <EMPLOYEE_ID>105</EMPLOYEE_ID>
      <FIRST_NAME>David</FIRST_NAME>
      <LAST_NAME>Austin</LAST_NAME>
      <EMAIL>DAUSTIN</EMAIL>
      <PHONE_NUMBER>590.423.4569</PHONE_NUMBER>
      <HIRE_DATE>6/25/1997 0:0:0</HIRE_DATE>
      <JOB_ID>IT_PROG</JOB_ID>
      <SALARY>4800</SALARY>
      <MANAGER_ID>103</MANAGER_ID>
      <DEPARTMENT_ID>60</DEPARTMENT_ID>
   </ROW>
</ROWSET>

java OracleXML getXML -user "hr/password" -skipRows 5 -maxRows 1 \
  "SELECT * FROM employees"

In some situations, you might want to keep a query object open for the duration of the user session. You can handle such cases with the maxRows() method and the keepObjectOpen() method.

The paginateResults.java program shows how you can generate an XML page that paginates results. The output XML displays only 20 rows of the hr table.

1. Create a SQL statement object and initialize it with a SQL query. The following code fragment sets two options in java.sql.ResultSet:

   Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                                         ResultSet.CONCUR_READ_ONLY);
   

2. Create the query as a string and execute it by invoking Statement.executeQuery(). The return object is of type ResultSet. The following example shows this technique:

   String sCmd = "SELECT first_name, last_name FROM hr.employees"; 
   ResultSet rs = stmt.executeQuery(sCmd); 
   

3. Create the query object, as shown in this code:

   OracleXMLQuery xmlQry = new OracleXMLQuery(conn, rs);
   

4. Configure the query object. The following code specifies that the query object is open for the duration of the session. It also limits the number of rows returned to 20:

   xmlQry.keepObjectOpen(true); 
   xmlQry.setRowsetTag("ROWSET"); 
   xmlQry.setRowTag("ROW"); 
   xmlQry.setMaxRows(20); 
   

5. Retrieve the result as a String and print:

   String sXML = xmlQry.getXMLString(); 
   System.out.println(sXML);
   

java paginateResults

The testInsert.java demo program inserts XML values into all columns of the hr.employees table.

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Insert the data in an input XML document into the hr.employees table. The following code fragment creates a URL from the document file name specified on the command line:

   sav.insertXML(sav.getURL(argv[0]));
   

4. Close the XML save object:

   sav.close();

In some situations, you might not want to insert values into all columns. For example, the group of values that you get might not be the complete set, requiring you to use triggers or default values for the remaining columns.

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. Initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of strings. Each element of the array must contain the name of a column in which values are inserted. The following code fragment specifies the names of five columns:

   String [] colNames = new String[5];
   colNames[0] = "EMPLOYEE_ID";
   colNames[1] = "LAST_NAME";
   colNames[2] = "EMAIL";
   colNames[3] = "JOB_ID";
   colNames[4] = "HIRE_DATE";
   

4. Configure the XML save object to update the specified columns. The following statement passes a reference to the array to the OracleXMLSave.setUpdateColumnList() method:

   sav.setUpdateColumnList(colNames);
   

5. Insert the data in an input XML document into the hr.employees table. The following code fragment creates a URL from the document file name specified on the command line:

   sav.insertXML(sav.getURL(argv[0]));
   

6. Close the XML save object:

   sav.close();

Demo program testUpdate.java invokes method OracleXMLSave.setKeyColumnList() to update table hr.employees.

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create a single-element String array to hold the name of the primary key column in the table to be updated. The following code fragment specifies the name of the employee_id column:

   String [] keyColNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the keyColNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Update the rows specified in the input XML document. The following statement creates a URL from the file name specified on the command line:

   sav.updateXML(sav.getURL(argv[0]));
   

6. Close the XML save object:

   sav.close();

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of type String to hold the name of the primary key column in the table to be updated. The array contains only one element, which is the name of the primary key column in the table to be updated. The following code fragment specifies the name of the employee_id column:

   String [] colNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the colNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Create an array of type String to hold the name of the columns to be updated. The following code fragment specifies the name of the employee_id column:

   String[] updateColNames = new String[2];
   updateColNames[0] = "SALARY";
   updateColNames[1] = "JOB_ID";
   

6. Set the XML save object to the list of columns to be updated. The following statement performs this task:

   sav.setUpdateColumnList(updateColNames);
   

7. Update the rows specified in the input XML document. The following code fragment creates a URL from the file name specified on the command line:

   sav.updateXML(sav.getURL(argv[0]));
   

8. Close the XML save object:

   sav.close();

The testDeleteRow.java demo program accepts an XML document file name as input and deletes the rows corresponding to the elements in the document.

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Delete the rows specified in the input XML document. The following code fragment creates a URL from the file name specified on the command line:

   sav.deleteXML(sav.getURL(argv[0]));
   

4. Close the XML save object:

   sav.close();

To use only the key values as predicates on the DELETE statement, invoke the OracleXMLSave.setKeyColumnList() method. This approach limits the number of elements used to identify a row, which has the benefit of improving performance by caching the DELETE statement and batching transactions. The testDeleteKey.java program shows this technique.

1. Create a JDBC OCI connection. The program invokes the same getConnection() method used by the previous examples in this chapter:

   Connection conn = getConnection("hr","password");
   

2. Create an XML save object. You initialize the object by passing it the Connection reference and the name of the table on which you want to perform DML. The following example shows this technique:

   OracleXMLSave sav = new OracleXMLSave(conn, "employees");
   

3. Create an array of type String to hold the name of the primary key column in the table. The array contains only one element. The following code fragment specifies the name of the employee_id column:

   String [] colNames = new String[1];
   colNames[0] = "EMPLOYEE_ID";
   

4. Set the XML save object to the primary key specified in the array. The following statement passes a reference to the colNames array to the OracleXMLSave.setKeyColumnList() method:

   sav.setKeyColumnList(keyColNames);
   

5. Delete the rows specified in the input XML document. The following code fragment creates a URL from the file name specified on the command line:

   sav.deleteXML(sav.getURL(argv[0]));
   

6. Close the XML save object:

   sav.close();

The testException.java demo program throws a runtime exception and then gets the parent exception by invoking Exception.getParentException().

Running the program generates this error message:

Caught SQL Exception:ORA-00904: "SD": invalid identifier

When there are no rows to process, XSU returns a null string. You can throw an exception each time there are no more rows, however, so that a program can process this exception using exception handlers.

1. Configure the query object or raise a no rows exception. The following code fragment shows this technique:

   pageTest test = new pageTest("SELECT * from employees");
   ...
   test.qry.setRaiseNoRowsException(true);
   

2. Loop through the result set infinitely, retrieving ten rows at a time. When no rows are available, the program throws an exception. The following code fragment invokes pageTest.nextPage(), which scrolls through the result set ten rows at a time:

   try
   {
     while(true)
       System.out.println(test.nextPage());
   }
   

3. Catch the no rows exception and print "END OF OUTPUT". The following code shows this technique:

   catch(oracle.xml.sql.OracleXMLSQLNoRowsException e)
   {
     System.out.println(" END OF OUTPUT "); 
     try
     {
       test.close();
     }
     catch ( Exception ae )
     {
       ae.printStackTrace(System.out);
     }
   }
   

java noRowsTest