Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
|
Using XML-SQL Utility (XSU), 10 of 26
The following two XSU classes are useful for the Java API programmer:
oracle.xml.sql.query.OracleXMLQuery
: Generates a XML document given a SQL query.
oracle.xml.sql.dml.OracleXMLSave
: Puts a XML document into tables and views
The OracleXMLQuery
class makes up the XML generation part of the XSU's Java API.
Figure 4-3 illustrates the basic steps in the usage of OracleXMLQuery.
Perform these steps when generating XML:
OracleXMLQuery
instance by supplying a SQL string or a ResultSet
object
The following example, shows how a simple XML document can be generated.
These examples illustrate how using the XSU you can get a XML document in its DOM or string representation given a SQL query. See Figure 4-4.
The first step before getting the XML is to create a connection to the database. The connection can be obtained by supplying the JDBC connect string. You have to first register the Oracle JDBC class and then create the connection.
// import the Oracle driver.. import oracle.jdbc.driver.*; // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Create the connection. Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@","scott","tiger");
Here, the connection is done using the OCI8 JDBC driver. You can connect to the scott schema supplying the password tiger. It connects to the current database (identified by the ORA_SID environment variable). You can also use the JDBC thin driver to connect to the database. The thin driver is written in pure Java and can be called from within applets or any other Java program.
Here's an example of connecting using the thin driver.
// Create the connection. Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dlsun489:1521:ORCL", "scott","tiger");
The thin driver requires the specification of the host name (dlsun489), port number (1521) and the oracle SID (ORCL) which identifies a specific Oracle instance on the machine.
In the case of writing server side Java code, i.e., code that will run inside the server, you need not establish a connection using a username and password, since the server-side internal driver runs within a default session. You are already "connected". In that case you call the defaultConnection() on the oracle.jdbc.driver.OracleDriver() class to get the current connection.
import oracle.jdbc.driver.*; // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = new oracle.jdbc.driver.OracleDriver ().defaultConnection ();
The rest of the notes will either assume the OCI8 connection from the client or that you already have a connection object created. Use the appropriate connection creation based on your needs.
Once you have registered your connection, create an OracleXMLQuery class instance by supplying a SQL query to execute,
// import the query class in to your class import oracle.xml.sql.query.OracleXMLQuery; OracleXMLQuery qry = new OracleXMLQuery (conn, "select * from emp");
You are now ready to use the query class.
You can get a XML string for the result by:
String xmlString = qry.getXMLString();
If, instead of a string, you wanted a DOM object instead, you can simply ask for a DOM output,
org.w3c.DOM.Document domDoc = qry.getXMLDOM();
and use the DOM traversals.
Here's a complete listing of the program to extract the XML string. This program gets the string and prints it out to the standard output.
Import oracle.jdbc.driver.*; import oracle.xml.sql.query.OracleXMLQuery; import java.lang.*; import java.sql.*; // class to test the String generation! class testXMLSQL { public static void main(String[] argv) { try{ // create the connection Connection conn = getConnection("scott","tiger"); // Create the query class. OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp"); // Get the XML string String str = qry.getXMLString(); // Print the XML output System.out.println(" The XML output is:\n"+str); // Always close the query to get rid of any resources.. qry.close(); }catch(SQLException e){ System.out.println(e.toString()); } } // Get the connection given the user name and password..! private static Connection getConnection(String username, String password) throws SQLException { // register the JDBC driver.. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Create the connection using the OCI8 driver Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@",username,password); return conn; } }
To run this program, carry out the following:
You must have the CLASSPATH pointing to this directory for the java executable to find the class. Alternatively use various visual Java tools including Oracle's JDeveloper to compile and run this program.
When run, this program prints out the XML file to the screen.
A DOM (Document Object Model) is a standard defined by the W3C committee which represents an XML document in a parsed-tree like form. Each XML entity becomes a DOM node. Thus XML elements, attributes become DOM nodes and their children become child nodes.
To generate a DOM tree from the XML generated by the utility, it is efficient to directly ask for a DOM Document from the utility, as it saves the overhead of creating a string representation of the document and then parse it to generate the DOM tree.
XSU calls the parser to directly construct the DOM tree from the data values. The example is shown below to get the DOM tree. The example "walks" through the DOM tree and prints all the nodes one by one.
import org.w3c.dom.*; import oracle.xml.parser.v2.*; import java.sql.*; import oracle.xml.sql.query.OracleXMLQuery; import java.io.*; class domTest{ public static void main(String[] argv) { try{ // create the connection Connection conn = getConnection("scott","tiger"); // Create the query class. OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp"); // Get the XML DOM object. The actual type is the Oracle Parser's DOM // representation. (XMLDocument) XMLDocument domDoc = (XMLDocument)qry.getXMLDOM(); // Print the XML output directly from the DOM domDoc.print(System.out); // If you instead want to print it to a string buffer you can do this..! StringWriter s = new StringWriter(10000); domDoc.print(new PrintWriter(s)); System.out.println(" The string version ---> "+s.toString()); qry.close(); // You should always close the query!! }catch(Exception e){ System.out.println(e.toString()); } } // Get the connection given the user name and password..! private static Connection getConnection(String user, String passwd) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd); return conn; } }
|
Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|