Building Oracle XML Applications phần 6 pptx
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (549.17 KB, 89 trang )
• You can execute the procedure from the SQL*Plus command line using the
EXEC command as follows:
EXEC QuotesForStocksInPortfolio(101)
After successful execution, you can see the resulting contents of the web
page buffer by issuing:
EXEC OWA_UTIL.SHOWPAGE
If you have previously issued SET SERVEROUTPUT ON from SQL*Plus, you
will see the contents of the page buffer in your SQL*Plus command console.
After requesting the page buffer, the OWA routines flush the buffer.
• If you are using one of the web servers listed earlier on a computer named
yourserver, and you have properly registered a virtual path location
named yourpath to point to the database schema where you created the
previous PL/SQL procedure, you can request the page through a web
browser (or any program capable of making an HTTP request) using the
URL:
http://yourserver/yourpath/QuotesForStocksInPortfolio?id=101
10.1.4 Formatting Database Data in XML Using PL/SQL
The process of generating dynamic XML documents in PL/SQL based on query
results is identical to the procedure used for generating HTML:
1. Identify the set of data to publish in XML by executing one or more SQL
queries.
2. Loop over the resulting data.
3. Output the data, surrounded by appropriate XML tags.
10.1.4.1 Returning an XML stock quote datagram
When you want to format database query results in XML instead of HTML, the
programming techniques remain the same; only the tags around the data change.
By making slight modifications to Example 10.7, we can generate dynamic XML
datagrams filled with stock quotes in no time.
Studying Example 10.8, you'll notice a few obvious changes:
• We're explicitly setting the MIME type of the returned document to
text/xml using the MIME_HEADER routine in the built-in OWA_UTIL package.
• We're outputting an XML declaration instead of an <HTML> tag as the very
first text in the page.
• The tags used to mark up the data have meaningful element names that
reflect the structure of the query result's information, unlike the fixed set of
HTML tags, like <TD>, which signify visual presentation of the data.
Example 10.8. Procedure to Return XML Stock Quote
Datagram
CREATE PROCEDURE XMLQuotesForStocksInPortfolio( id NUMBER ) IS
Select all stocks for the user with id passed in
CURSOR c_QuotesForUserid( cp_Userid NUMBER )
IS SELECT q.symbol, q.price, q.change
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = cp_Userid;
BEGIN
OWA_UTIL.MIME_HEADER('text/xml');
HTP.P('<?xml version="1.0"?>');
HTP.P('<Quotes>');
FOR curQuote IN c_QuotesForUserid( id )
LOOP
HTP.P('<Quote>');
HTP.P('<Symbol>' ||curQuote.symbol||'</Symbol>');
HTP.P('<Price>' ||curQuote.price ||'</Price>');
HTP.P('<Change>' ||curQuote.change||'</Change>');
HTP.P('</Quote>');
END LOOP;
HTP.P('</Quotes>');
END XMLQuotesForStocksInPortfolio;
We have to set the MIME type of the result to text/xml so that a requesting user
agent can make an informed decision about how it wants to handle the XML
document we're returning. Depending on the web server you're using, you may
also have to register text/xml as one of the valid MIME types the server
recognizes.
Web developers producing static pages with graphics and text never need to
think about MIME types, since most modern web servers automatically set the
MIME type of requested web resources like .html and .gif files based on their file
extension.
In addition, many developers producing dynamic HTML pages don't have to think
about MIME types, since most web servers default the MIME type of dynamically
generated pages to text/html. With dynamically generated XML, however, we
must take care to set the MIME type manually; otherwise, your data pages will
most likely be handled like HTML when they get to their destination.
If you begin to write lots of PL/SQL stored procedures that format XML, you'll
quickly get tired of printing out the tags manually. Just as the HTP and HTF
packages offer routines for more reliably creating common HTML tags, we can
write a quick helper routine to assist with the formatting of opening and closing
tags for our XML.
Example 10.9 creates a PL/SQL package named xmlhelper by providing its
PACKAGE specification and companion PACKAGE BODY, defining the
implementation of the routines in the package.
Example 10.9. Helper Package to Simplify XML Creation
CREATE OR REPLACE PACKAGE xmlhelper IS
PROCEDURE prolog;
PROCEDURE startTag( elementName VARCHAR2 );
PROCEDURE tag( elementName VARCHAR2,
content VARCHAR2 := NULL);
PROCEDURE endTag( elementName VARCHAR2 );
END xmlhelper;
CREATE OR REPLACE PACKAGE BODY xmlhelper IS
PROCEDURE prolog IS
BEGIN
OWA_UTIL.MIME_HEADER('text/xml');
HTP.P('<?xml version="1.0"?>');
END prolog;
PROCEDURE startTag( elementName VARCHAR2 ) IS
BEGIN
HTP.P('<'||elementName||'>');
END startTag;
PROCEDURE tag( elementName VARCHAR2,
content VARCHAR2 := NULL) IS
BEGIN
HTP.P( '<'||elementName||'>'
||content ||
'</'||elementName||'>');
END tag;
PROCEDURE endTag( elementName VARCHAR2 ) IS
BEGIN
HTP.P('</'||elementName||'>');
END endTag;
END xmlhelper;
The prolog routine encapsulates the setting of the text/xml MIME type as well
as the printing of the XML declaration. prolog should always be called before
calling any of the other routines to generate tags for the content of the page. The
startTag and endTag routines do what their names imply, while the tag routines
combine these to print the start tag, some text content, and the end tag in one
command.
If we were to rewrite Example 10.8 using our new xmlhelper package, our code
would look like Example 10.10.
Example 10.10. Improved Procedure Returning XML Stock
Quotes
CREATE PROCEDURE XMLQuotesForStocksInPortfolio( id NUMBER ) IS
Select all stocks for the user with id passed in
CURSOR c_QuotesForUserid( cp_Userid NUMBER )
IS SELECT q.symbol, q.price, q.change
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = cp_Userid;
BEGIN
xmlhelper.prolog;
xmlhelper.startTag('Quotes');
FOR curQuote IN c_QuotesForUserid( id )
LOOP
xmlhelper.startTag('Quote');
xmlhelper.tag('Symbol', curQuote.symbol);
xmlhelper.tag('Price', curQuote.price);
xmlhelper.tag('Change', curQuote.change);
xmlhelper.endTag('Quote');
END LOOP;
xmlhelper.endTag('Quotes');
END XMLQuotesForStocksInPortfolio;
As before with the HTML-producing stored procedure, we can test the output of
our XML-producing stored procedure using:
EXEC XmlQuotesForStocksInPortfolio(101)
followed by:
EXEC OWA_UTIL.SHOWPAGE
from the SQL*Plus command line, or by browsing the URL:
http://yourserver/yourpath/XmlQuotesForStocksInPortfolio?id=101
10.2 Automatic XML Generation with DBXML
We could certainly continue with PL/SQL examples, demonstrating how to write
stored procedures to:
• Format the query results from multiple SQL statements into a single
resulting XML page
• Generically process any SQL query, using the built-in DBMS_SQL package,
generating appropriate XML tags for their column names
• Automatically search for additional information in related tables by
checking the database dictionary views for metadata about foreign key and
primary key constraints
But luckily, we don't have to write this code ourselves, since Oracle provides all
this functionality in the freely downloadable set of XML Utilities for PL/SQL called
PLSXML which includes lots of demos and source code (see
The
readme.html file in the readme directory in the PLSXML distribution provides
setup instructions.
10.2.1 Letting DBXML Do the Work for You
The heart of the PLSXML suite of utilities is a PL/SQL package called DBXML. The
package offers a key procedure named Query that accepts a SQL query to be
processed and automatically produces the XML output in the OWA page buffer. As
Example 10.11
illustrates, it is practically no work at all to use Dbxml.Query.
Passing any query to it as a string causes the appropriately formatted XML
document representing its query results to be sent to the HTP page buffer.
Example 10.11. Automatically Producing Stock Quote XML
with DBXML
CREATE PROCEDURE StockQuotesDbxmlBasic( id NUMBER ) IS
BEGIN
Dbxml.Query('SELECT q.symbol as Symbol,
q.price as Price,
q.change as Change
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = ' || id);
END;
With a single line of PLSQL, any query can be published in XML over the Web! By
default, for a query whose leading table name is TABLENAME in the FROM clause,
Dbxml.Query creates:
• A <TABLENAMELIST> element to wrap the entire set of rows
• A <TABLENAME> element to wrap each row of data
• The database column names as element names for each column's data
The default output from Dbxml.Query for a simple query like Example 10.11
looks like this:
<?xml version="1.0" ?>
<! Oracle DBXML Version 1.1.10 Query Results at 20-JUN-2000 22:10:57
>
<!
SELECT SYMBOL,PRICE,CHANGE
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = 101
>
<QLIST>
<Q>
<SYMBOL>GE</SYMBOL>
<PRICE>103.5</PRICE>
<CHANGE>.8</CHANGE>
</Q>
<Q>
<SYMBOL>ORCL</SYMBOL>
<PRICE>27.33</PRICE>
<CHANGE>3.4</CHANGE>
</Q>
</QLIST>
By providing values for some of Dbxml.Query 's optional parameters, you can
control certain aspects of its automatic XML generation. A simple example of this
is to substitute the default tags with custom values, as in Example 10.12.
Example 10.12. XML Stock Portfolio Using DBXML
CREATE PROCEDURE StockQuotesDbxml( id NUMBER ) IS
BEGIN
Dbxml.Query('SELECT q.symbol as Symbol,
q.price as Price,
q.change as Change
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = ' || id,
theDocElement => 'PORTFOLIO',
tableElement => 'QUOTE' );
END;
This produces the requested effect on the output:
<?xml version="1.0" ?>
<! Oracle DBXML Version 1.1.11 Query Results at 05-JUL-1999 18:58:12
>
<!
SELECT SYMBOL,PRICE,CHANGE
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = 101
>
<PORTFOLIO>
<QUOTE>
<SYMBOL>GE</SYMBOL>
<PRICE>103.5</PRICE>
<CHANGE>.8</CHANGE>
</QUOTE>
<QUOTE>
<SYMBOL>ORCL</SYMBOL>
<PRICE>27.33</PRICE>
<CHANGE>3.4</CHANGE>
</QUOTE>
</PORTFOLIO>
After looking in the next section at using Dbxml.Query to work with information
for multiple tables in a single request, we'll cover all of the options available to
control Dbxml.Query 's output.
10.2.2 Returning XML Information for Multiple Tables
One of the key values of XML is its ability to elegantly represent trees of related
information. For example, if you imagine an XML document representing a
patient's medical history, you would assume such a document should include
information about the patient, the patient's medical visits with various doctors at
one or more medical facilities, as well as other details. In a relational database
like Oracle, the information for each of these different kinds of business entities
is stored in separate tables, and relationships between the entities are captured
by referential constraints between tables. As an example, Figure 10.1 shows a
simple schema for a Medical Visit Tracking application.
Figure 10.1. Sample schema to track medical visits
Depending on the information needs of the moment, the tables in Figure 10.1
provide the foundation for many different XML documents:
• A <PATIENT> profile, with related information about the patient's <VISIT>s
and which <DOCTOR>s the patient saw at what <FACILITY>s
• A <DOCTOR> profile, with related information about the list of the doctor's
<VISIT>s and <PATIENT>s visited
• A <STATE> profile, with related information about the list of <CITY>s in the
state, and what medical <FACILITY>s exist in each city
The list could clearly go on. The key point is that the tables in a relational
database, together with the referential constraints among them, have the
potential to represent many different XML documents, depending on your desired
point of view.
Dbxml.Query offers a built-in capability to "walk" relationships and include data
tables that are related to the table you provide as the starting point. By providing
Dbxml.Query an initial query based on the PATIENT table, for example, it will
include information about related medical visits from the VISIT table. The
process is recursive, so information from the VISIT table will be supplemented by
related details from the FACILITY table where the visit took place as well as
information from the DOCTOR table about the doctor who saw the patient. You
can provide a parameter upon invoking Dbxml.Query to indicate which tables you
wish to exclude from the set of details to control how far the "walking" goes.
Let's look at a couple of simple examples using this feature of Dbxml.Query and
the tables from Figure 10.1. Example 10.13 shows an example of a patient profile
datagram.
Example 10.13. Multi-Table Patient Profile with DBXML
CREATE PROCEDURE PatientProfile( id NUMBER ) IS
BEGIN
Dbxml.Query('SELECT * FROM patient
WHERE id = '|| id ,
includeDetails => 'Y',
detailExclusionList => 'FACILITY');
END;
Executing the PatientProfile procedure through the Oracle Web Agent
produces the XML document in Example 10.14.
Example 10.14. Nested DBXML Query Results for the Patient
Table
<?xml version="1.0" ?>
<! Oracle DBXML Version 1.1.10 Query Results at 20-JUN-2000 22:27:22
>
<!
SELECT *
FROM patient
WHERE id = 2099344
>
<PATIENTLIST>
<PATIENT>
<ID>2099344</ID>
<FIRSTNAME>Laurel</FIRSTNAME>
<LASTNAME>Birch</LASTNAME>
<VISITLIST>
<VISIT>
<ID>198554</ID>
<VISITDATE>05-JUL-99</VISITDATE>
<PATIENTID>2099344</PATIENTID>
<FACILITYID>Bay03</FACILITYID>
<DOCTORID>bproto</DOCTORID>
<DOCTOR>
<ID>bproto</ID>
<FIRSTNAME>Bryan</FIRSTNAME>
<LASTNAME>Proto</LASTNAME>
<HOMEOFFICE>French44</HOMEOFFICE>
</DOCTOR>
</VISIT>
<VISIT>
<ID>197111</ID>
<VISITDATE>24-JUN-99</VISITDATE>
<PATIENTID>2099344</PATIENTID>
<FACILITYID>French44</FACILITYID>
<DOCTORID>bproto</DOCTORID>
<DOCTOR>
<ID>bproto</ID>
<FIRSTNAME>Bryan</FIRSTNAME>
<LASTNAME>Proto</LASTNAME>
<HOMEOFFICE>French44</HOMEOFFICE>
</DOCTOR>
</VISIT>
<VISIT>
<ID>196223</ID>
<VISITDATE>11-JUN-99</VISITDATE>
<PATIENTID>2099344</PATIENTID>
<FACILITYID>Bay03</FACILITYID>
<DOCTORID>krhymes</DOCTORID>
<DOCTOR>
<ID>krhymes</ID>
<FIRSTNAME>Kathryn</FIRSTNAME>
<LASTNAME>Rhymes</LASTNAME>
<HOMEOFFICE>Bay03</HOMEOFFICE>
</DOCTOR>
</VISIT>
</VISITLIST>
</PATIENT>
</PATIENTLIST>
Passing a 'Y' for the includeDetails parameter tells Dbxml.Query to "walk" to
find related information in the initial table, PATIENT. The detailExclusionList
parameter is a list of table names to exclude from the set of related details. By
setting this value equal to 'FACILITY' we're asking that no additional details
from the related FACILITY table be included in the resulting XML document. This
effectively eliminates not only information from the FACILITY table, but also any
of its details.
Again using the tables from Figure 10.1, the stored procedure in Example 10.15
produces results by starting with the identified row in the STATE table, and by
proceeding to format related information from CITY and FACILITY, but excluding
the details that would have been included by default from COUNTRY, DOCTOR,
and VISIT.
Example 10.15. Multi-Table State Profile with DBXML
CREATE PROCEDURE StateProfile( abbrev VARCHAR2 ) IS
BEGIN
Dbxml.Query('SELECT * FROM state
WHERE name = '''|| abbrev ||'''' ,
includeDetails => 'Y',
detailExclusionList => 'COUNTRY,DOCTOR,VISIT');
END;
The results of this stored procedure appear in Example 10.16.
Example 10.16. Nested DBXML Query Results for the State
Table
<?xml version="1.0" ?>
<! Oracle DBXML Version 1.1.10 Query Results at 20-JUN-2000 22:29:23
>
<!
SELECT *
FROM state
WHERE name = 'CA'
>
<STATELIST>
<STATE>
<NAME>CA</NAME>
<COUNTRYNAME>USA</COUNTRYNAME>
<CITYLIST>
<CITY>
<NAME>San Francisco</NAME>
<STATENAME>CA</STATENAME>
<FACILITYLIST>
<FACILITY>
<CODE>Bay03</CODE>
<NAME>Bayside Medical</NAME>
<ADDRESS>201 Oceanview Rd.</ADDRESS>
<CITY>San Francisco</CITY>
</FACILITY>
<FACILITY>
<CODE>French44</CODE>
<NAME>Downtown French Campus</NAME>
<ADDRESS>11 Battery Street</ADDRESS>
<CITY>San Francisco</CITY>
</FACILITY>
</FACILITYLIST>
</CITY>
<CITY>
<NAME>Oakland</NAME>
<STATENAME>CA</STATENAME>
<FACILITYLIST>
</FACILITYLIST>
</CITY>
<CITY>
<NAME>Los Angeles</NAME>
<STATENAME>CA</STATENAME>
<FACILITYLIST>
</FACILITYLIST>
</CITY>
</CITYLIST>
</STATE>
</STATELIST>
10.2.3 Controlling How the XML Is Generated
Table 10.2 provides the full list of optional parameters to Dbxml.Query that
control how it generates XML documents.
Table 10.2. Dbxml.Query Parameters to Control Output
Parameter Name Description
theQuery
The SQL statement to execute. This is the only required parameter.
theDocElement
Name of the element to use for the entire set of rows in the query result.
(Default is 'ROWSET', which produces the <ROWSET> tag we've seen in the
example output.)
tableElement
Name of the element to use for each row of the query result. (Default is
'ROW', which produces the <ROW> tag we've seen in the example output.)
maximumRows
Maximum number of rows to fetch from the query. If combined with an
ORDER BY in the query statement, can be useful for retrieving the top N
rows from a query. (Default is to fetch up to 200 rows.)
includeDetails
If set to 'Y', causes DBXML to attempt its "relationship walking" for
including detail data. (Default is 'N'.)
detailExclusionList
Comma-separated list of table names to exclude from the detail table
traversal. If a table appears in the list, neither it nor any of its details will
appear in the result. Useful for defining the boundaries of what related
information to include from a complex schema with many related tables.
(Default is NULL, which includes all details.)
stylesheet
Provides the relative or absolute URL of the stylesheet to be included by
reference using an <?xml-stylesheet?> processing instruction at the
top of the resulting XML document. (Default is NULL, which omits any
stylesheet reference.)
NoRowsException
If set to 'Y', causes a PL/SQL NO_DATA_FOUND exception to be raised if no
rows are found by the query. (Default is 'N' which raises no exception
when no rows are found, and returns just an XML document with an
empty <ROWSET> document element.)
theMainTable
If the query is a join, or DBXML has trouble identifying the main table by
parsing the query statement, use this to indicate the name of the table to
use for the purpose of traversing to find related details. (Default is
PL/SQL NULL.)
singleRow
If set to 'Y', causes DBXML to fetch only a single row and causes it not to
use the top-level <ROWSET> element as the document element. (Default is
'N'.)
In your code, use PL/SQL's named parameter calling syntax to provide values for
any of the optional Dbxml.Query parameters you want to use. After specifying a
SQL query string as the first argument to Dbxml.Query, the optional named
parameters can appear in any order in your procedure call.
We've seen that writing PL/SQL to loop over database query results and produce
XML datagrams is quite straightforward, and the techniques presented in this
section work in virtually any version of the Oracle database server with any
release of the Oracle Internet Application Server or previous application server
products from Oracle. For many typical cases, Dbxml.Query can completely
automate the rendering of SQL query results as an XML document, optionally
including a set of detail information from related tables.
Chapter 11. Generating Datagrams with Java
Java programmers using Oracle8i have a myriad of options for outputting database information
as XML. This chapter covers all of the available approaches, proceeding in order from the most
manual to the most automatic, with examples every step of the way.
11.1 Generating XML Using Java
In this section, we'll learn the basics of using Java to generate XML datagrams containing
database query results. The two basic programmatic techniques for accessing SQL query results
from Java are the JDBC API and SQLJ.
11.1.1 Generating XML with JDBC
The most basic way in Java to produce XML from database information is to use a JDBC
ResultSet to execute a SQL statement and loop over its results. Developers familiar with
database programming using the JDBC interface will find this technique to be second nature.
Example 11.1
issues a query to retrieve current stock quotes for all the positions in a particular
customer's portfolio. Most interesting queries in an application depend on some kind of context
information being supplied. Example 11.1
shows how to use a bind variable in the SQL statement,
setting the value of the bind variable to the customer id passed in as a command-line argument.
This allows the same SQL statement to be used for retrieving the appropriate stock quotes in any
customer's portfolio.
Example 11.1. Using a JDBC Result Set to Produce XML
import java.sql.*;
class StockQuotesXmlJdbc
{
public static void main (String arg[]) throws Exception
{
// Connect to the database
Connection cn = Examples.getConnection( );
// Prepare the query statement containing a bind variable "?"
PreparedStatement ps =
cn.prepareStatement("SELECT q.symbol, q.price, q.change" +
" FROM quotes q, portfolio_stocks ps" +
" WHERE q.symbol = ps.symbol" +
" AND ps.owner = ?");
// Use first command line arg as customer id
int id = Integer.parseInt( arg[0] );
// Bind value of customer id to first (and only) bind variable
ps.setInt( 1, id );
// Execute the query
ResultSet rs = ps.executeQuery( );
// Generate the XML document
System.out.println("<?xml version=\"1.0\"?>");
System.out.println("<Quotes>");
// Loop over the rows in the query result
while (rs.next ( )) {
System.out.println("<Quote>");
System.out.println("<Symbol>" + rs.getString(1) + "</Symbol>");
System.out.println( "<Price>" + rs.getString(2) + "</Price>") ;
System.out.println("<Change>" + rs.getString(3) + "</Change>");
System.out.println("</Quote>");
}
System.out.println("</Quotes>");
rs.close(); ps.close( ); cn.close( );
}
}
For each row of stock quote information retrieved, we generate an appropriate <Quote> element
with nested elements wrapping the stock quote data. All of this data is nested inside the top-level
<Quotes> element. Note from the following example output that Example 11.1
does not go out of
its way to nicely indent the XML tags that structure the data:
<?xml version="1.0"?>
<Quotes>
<Quote>
<Symbol>GE</Symbol>
<Price>103.5</Price>
<Change>0.8</Change>
</Quote>
<Quote>
<Symbol>ORCL</Symbol>
<Price>27.33</Price>
<Change>3.4</Change>
</Quote>
</Quotes>
Of course, an XML parser is happy to read a document without the extra beautifying whitespace,
but having it in there can make your dynamically generated documents easier for humans to
understand. For now, we won't worry about the indenting in our examples. We'll see later in the
chapter that some of the more automated techniques of producing XML from database
information handle the "pretty-printing" for us.
With a technique similar to the one used in Example 11.1, we can create Java code to generate
XML for any query we like. While this technique provides full control over the resulting XML
document, writing code by hand to create the appropriate tags for many different SQL statements
will soon have you looking for a more automated approach. By exploiting the fact that any
ResultSet can provide information about its columns and their datatypes at runtime, we can
certainly improve on this basic example.
Example 11.2
shows the XMLForResultSet class, whose print() method produces a valid XML
document for the query results of any ResultSet. By calling the getResultSetMetaData()
method on the ResultSet passed in, we can reference a companion ResultSetMetaData
interface. We then can use it to determine interesting structural information about that
ResultSet. Specifically, here we make use of:
• getColumnCount( ) to find the number of columns in the ResultSet's SELECT list
• getColumnName( n ) to retrieve the name of the nth column
Example 11.2. Generating XML for Any ResultSet Using
ResultSetMetaData
import java.sql.*;
import java.io.*;
public class XMLForResultSet {
public static void print(ResultSet rs, String resultElt, String rowElt,
PrintWriter out) throws SQLException {
ResultSetMetaData rsMeta = rs.getMetaData( );
int colCount = rsMeta.getColumnCount( );
out.println("<?xml version=\"1.0\"?>");
// Document element for the result
out.println("<"+resultElt+">");
// Loop over the rows in the query result
while (rs.next ( )) {
// Element for each row
out.println("<"+rowElt+">");
// For each column in the result set
for (int curCol = 1; curCol <= colCount; curCol++) {
String curName = rsMeta.getColumnName(curCol);
out.println("<"+curName+">"+rs.getString(curCol)+"</"+curName+">");
}
out.println("</"+rowElt+">");
}
out.println("</"+resultElt+">");
}
}
XMLForResultSet.print( ) lets the caller pass in:
• The ResultSet providing the data
• The document element name to use for the query results
• The element name to generate for each row in the result
• The java.io.PrintWriter to use for output
With these four ingredients in hand, plus the information obtained from ResultSetMetaData, the
job of producing an XML document from the ResultSet's data is a straightforward task of looping
over the rows, and for each row, looping over the columns in the row.
Example 11.3
demonstrates how to use XMLForResultSet in a simple program. We've rewritten
Example 11.1
to call the XMLForResultSet.print() method in place of all the hand-coded XML
tag generation. When XML utilities are driven off runtime metadata, one line of code can be very
effective.
Example 11.3. Using XMLForResultSet.print( ) to Produce XML
import java.sql.*;
import java.io.*;
public class StockQuotesXml {
public static void main (String arg[]) throws Exception
{
// Use first command line arg as customer id
int id = Integer.parseInt( arg[0] );
print(id, System.out);
}
public static void print(int customerId, OutputStream out) throws Exception {
// Connect to the database
Connection cn = Examples.getConnection( );
// Prepare the query statement (Note column aliases!)
PreparedStatement ps =
cn.prepareStatement("SELECT q.symbol as \"Symbol\", " +
" q.price as \"Price\", " +
" q.change as \"Change\" " +
" FROM quotes q, portfolio_stocks ps" +
" WHERE q.symbol = ps.symbol" +
" AND ps.owner = ?");
// Bind value of the customerId parameter
ps.setInt( 1, customerId );
// Execute the Query
ResultSet rs = ps.executeQuery( );
PrintWriter pw = new PrintWriter(out);
// Generate the XML document for this ResultSet
XMLForResultSet.print(rs,"Quotes","Quote", pw);
pw.close(); rs.close(); ps.close( ); cn.close( );
}
}
Besides removing a lot of code, we've also slightly changed the SQL query statement in this
example. Example 11.3
introduces column aliases for each column in the SELECT list, which
provide precise control over the case of the column names. If we left the query as it was in
Example 11.1
, our generic XMLForResultSet routine would have generated an XML document
like this:
<?xml version="1.0"?>
<Quotes>
<Quote>
<SYMBOL>GE</SYMBOL>
<PRICE>103.50</PRICE>
<CHANGE>0.80</CHANGE>
</Quote>
<Quote>
<SYMBOL>ORCL</SYMBOL>
<PRICE>27.33</PRICE>
<CHANGE>3.40</CHANGE>
</Quote>
</Quotes>
The element names for <SYMBOL>, <PRICE>, and <CHANGE> are in uppercase, while the document
element <Quotes> and the <Quote> row elements are in mixed case. By default, tables created in
Oracle have uppercase column names, and column names in SQL statements are treated
case-insensitively. By default, column aliases are treated case-insensitively and returned in
uppercase as well, so we must use double quotes around the column alias if we want a
mixed-case name. The query statement in Example 11.3
adopts this technique to create column
aliases of Symbol, Price, and Change for the selected data. This causes the output to look like this
instead:
<?xml version="1.0"?>
<Quotes>
<Quote>
<Symbol>GE</Symbol>
<Price>103.5</Price>
<Change>0.8</Change>
</Quote>
<Quote>
<Symbol>ORCL</Symbol>
<Price>27.33</Price>
<Change>3.4</Change>
</Quote>
</Quotes>
11.1.2 Generating XML with SQLJ
Real-world database applications in Java typically require a significant amount of SQL to SELECT,
INSERT, UPDATE, and DELETE data to meet business processing needs. Even the few examples
above, which only perform SELECT statements to retrieve data, illustrate that working with SQL
in Java using JDBC can be quite tedious. Java developers using JDBC SQL in their code have to:
• Split long SQL statements into many line-sized chunks, making the SQL hard to read and
even harder to edit
• Invoke several APIs, typically, to accomplish each SQL operation
• Set each bind variable value by position through code, making SQL statements with many
bind variables hard to understand and more prone to editing errors
• Wait until runtime to discover SQL syntax errors
• Retrieve column values by passing the string name or position of the column and calling an
appropriate get method, according to the datatype of the column value
While JDBC is really the only game in town for working with dynamic SQL in Java programs, for
cases when SQL operations are known in advance, SQLJ offers an industry-standard syntax that
enables developers to work with static SQL inside Java much more productively. SQLJ allows Java
developers to include #sql directives in their Java source code to seamlessly embed, execute,
and process the results of any SQL operation. A SQLJ source file is precompiled using the Oracle
SQLJ command-line compiler. The compiler translates the preprocessor directives into Java
source code, which invokes the JDBC APIs corresponding to the indicated operations. This gives
the developer using SQL and Java a number of valuable benefits:
• SQL statements can be embedded verbatim in Java source code, spanning any number of
lines
• SQL SELECT, INSERT, UPDATE, DELETE, and EXEC (stored procedure) operations are
carried out without developer-written JDBC API calls
• SQL statements can reference Java variables by name as bind variables
• SQL syntax can be validated during precompilation for early error detection instead of
waiting until runtime
• Type-safe iterators can be defined to access query results more conveniently than using
native JDBC
As an added benefit, developers using the Oracle JDeveloper Integrated Development
Environment (IDE) can work with SQLJ files as easily as with Java source code. The IDE handles
invoking the Oracle SQL precompiler when necessary.
Example 11.4 illustrates the stock quotes example implemented using SQLJ. At the top, we use
the #sql iterator directive to define a named, type-safe iterator class for iterating the results of
the query. The line:
#sql iterator QuotesIter(String symbol, float price, float change);
declares an iterator class named QuotesIter, establishing the names and expected Java types of
each column in a row of query results. The iterator column names must match the names of the
columns in the SQL statement that you assign to the iterator later in the program. Note that
neither the case of the column names nor their position needs to match exactly.
Example 11.4. Generating XML Using SQLJ
import java.sql.*;
import sqlj.runtime.ref.DefaultContext;
#sql iterator QuotesIter(String symbol, float price, float change);
class StockQuotesSqlj
{
public static void main (String arg[]) throws Exception
{
QuotesIter quotes;
// Connect to the Database
DefaultContext.setDefaultContext(new
DefaultContext(Examples.getConnection( )));
// Use first command line arg as customer id
int id = Integer.parseInt( arg[0] );
#sql quotes = { SELECT q.symbol as "Symbol",
q.price as "Price",
q.change as "Change"
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = :id };
System.out.println("<?xml version=\"1.0\"?>");
System.out.println("<Quotes>");
while (quotes.next( )) {
System.out.println("<Symbol>" + quotes.symbol( ) + "</Symbol>");
System.out.println( "<Price>" + quotes.price( ) + "</Price>") ;
System.out.println("<Change>" + quotes.change( ) + "</Change>");
}
System.out.println("</Quotes>");
quotes.close( );
}
}
SQLJ also simplifies sharing of the same database connection across many SQL operations in a
program by hiding the details in the SQLJ default connection context. All the programmer has to
do is construct a new DefaultContext instance, passing in the connection, and set it as the
default. Then all subsequent #sql statements share the same connection without having to
complicate their syntax.
Once the iterator is defined, we declare the Java program variable quotes to be of type
QuotesIter and then use the following syntax:
#sql quotes = { SQL Statement };
to assign the query to the iterator. Notice that the SQL statement appears intact in the code,
without any distracting string concatenation, and that the Java program variable id can be
directly referenced in the SQL statement using :id , prefixing the name of the variable with a
colon to indicate its use as a bind variable. To loop over and reference the resulting data, we use
the iterator's default next method and type-safe column-accessor methods. For example, the call
to quotes.price( ) returns the price column of the current row as a float as defined in the
iterator declaration.
Since SQLJ interoperates easily with JDBC, you can mix and match the two in the same program.
A JDBC ResultSet can be cast to a SQLJ iterator, and any SQLJ iterator exposes its underlying
ResultSet through the iterator's getResultSet method. Example 11.5
shows how the
SQLJ-based stock quotes example can make use of our XMLForResultSet class to avoid
handwritten XML tag generation code.
Example 11.5. Generating XML Using SQLJ with
XMLForResultSet
import java.sql.*;
import java.io.*;
import sqlj.runtime.ref.DefaultContext;
#sql iterator QuotesIter2(String symbol, float price, float change);
class StockQuotesSqljRsetMeta
{
public static void main (String arg[]) throws Exception
{
QuotesIter2 quotes;
// Connect to the Database
DefaultContext.setDefaultContext(new
DefaultContext(Examples.getConnection( )));
// Use first command line arg as customer id
int id = Integer.parseInt( arg[0] );
#sql quotes = { SELECT q.symbol as "Symbol",
q.price as "Price",
q.change as "Change"
FROM quotes q, portfolio_stocks ps
WHERE q.symbol = ps.symbol
AND ps.owner = :id };
PrintWriter out = new PrintWriter(System.out);
XMLForResultSet.print( quotes.getResultSet( ),"Quotes","Quote",out);
out.close( );
quotes.close( );
}
}
11.2 Serving XML Datagrams over the Web
The examples we've encountered so far in this chapter show that using Java to produce XML for
database query results is straightforward. However, all of the examples we've seen so far print
the XML datagram to the standard "console" output stream System.out. While this makes sense
for use on the command line and in scripts, it is one step short of what we need for real Oracle
XML applications. For these to be effective, we need to serve application information in XML over
the Web.
11.2.1 Serving Datagrams Using Java Servlets
Leveraging the work we've already done in the StockQuotesXml class from Example 11.3, we can
produce dynamic stock quote datagrams for web delivery by extending HttpServlet in a class
called StockQuotesXmlServlet and performing these simple steps inside its overridden doGet
method:
• Set the MIME type of the servlet's response to text/xml
• Retrieve the customer id from a URL parameter instead of a command-line argument
• Call StockQuotesXml.print() to produce the XML for the stocks in the customer's
portfolio
• Pass the output stream to the Servlet's HttpServletResponse object instead of
System.out
The StockQuotesXmlServlet class in Example 11.6
shows the code that gets the job done.
To run a Java servlet from JDeveloper 3.1, just select Run from
the right mouse button menu in the project navigator.
JDeveloper will launch a single-user web server on port 7070
and start your default browser to exercise the running servlet
code. You can also debug servlet code by clicking on the Debug
menu option instead of Run.
Example 11.6. Returning XML Stock Quotes Using a Servlet
import javax.servlet.*;
import javax.servlet.http.*;
public class StockQuotesXmlServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
// Set MIME type of Response to indicate XML
response.setContentType("text/xml");
// Use HTTP request parameter 'id' as customer id
int id = Integer.parseInt(request.getParameter("id"));
try {
// Use StockQuotesXml.print to generate the XML Stock Quotes,
// passing the Servlet's HTTP Response OutputStream.
StockQuotesXml.print(id, response.getOutputStream( ));
}
catch (Exception e) {
throw new ServletException("Error processing query");
}
}
}
To generalize this servlet to handle requests for XML datagrams that may contain the results of
any SQL statement, we start by creating another helper class called XMLForQuery. This class
encapsulates the XML generation for a SQL statement provided as a string. Example 11.7
shows
that we just need to:
1. Create a ResultSet from the SQL statement passed in
2. Pass the ResultSet to the XMLForResultSet.print() method
3. Handle any SQL errors that might come back from the query
