XML as a Bridge between SQL and Web Applications

Abstract

Over the past few years XML technology has gained great popularity as a format for exchanging information over the Internet. Today, XML is often portrayed as a distinct technology, but initially it was born as an Internet technology (somewhere between HTML and SGML). This article looks at how XML can be used as a "transport protocol" between a database and an end user.

The most popular relational database management systems use SQL queries for working with data. Although XML-oriented databases are already on the market, they are not yet ubiquitous. Keeping in mind the popularity of XML, the developers of relational databases are moving forward by adding XML compatibility to their products. This article looks at one such approach: having a database return XML. An Oracle database is used in the sample code, which is supposed to be an XML-compliant database that already has a mechanism for working with XML data.

This article will be divided into two parts. In the first part we'll prepare Java code to work with an Oracle database, make an SQL query, and receive XML output. The second part will be devoted to a web application that will receive XML data from the database and output it as HTML text.

Requirements

The following software is used in this article:

• BEA WebLogic Platform 8.1 with SP4 as the application server.
• Oracle 10g Database Server as the database server. You can try the code with different versions; as far as we know, this solution does not require a specific version.
• Oracle XML-SQL Utility (XSU). The Oracle XML-SQL Utility is a set of Java classes and PL/SQL wrappers that permit queries to return result sets or objects wrapped in XML.
• Oracle XML Parser, Version 2. Oracle's Java-based validating XML parser, which has XSL support. Normally, XSU and XML Parser are part of Oracle Developer Suite.

The code in this article was run on Microsoft Windows XP but should work on any operating system with only minor changes.

We assume that the reader is an experienced Java developer who is familiar with BEA WebLogic Server and has some experience programming with JDBC.

Preparing a Connection Pool and a Data Source

If you are familiar with the configuration of JDBC connection pools and data sources, skip this section.

First we need to configure a connection pool and data source. The code will later retrieve the data source using JNDI. This requires a little configuration. We need to configure a JDBC Connection Pool, which holds connections to the database. Log in to the WebLogic console and choose the Service Configurations -> JDBC -> Connection Pools node.

Now create a new Connection Pool by selecting the Configure a new JDBC Connection Pool... link. On the next screen, choose a database type and a driver (Figure 1).

Figure 1. JDBC Connection Pool: Select database

You'll see many different databases to choose from. We need an Oracle database type, and we'll use BEA's Oracle Driver (Type 4). Click Continue to define the connection properties (Figure 2).

Figure 2. JDBC Connection Pool: Connection properties

On this screen, select a name for the JDBC Connection Pool, and set the additional database parameters such as database name, host name, port to connect, user name, and password.

Instead of creating a new database and set of tables, we'll use the sample SCOTT/TIGER schema in Oracle and the EMP table. This sample exists on almost every Oracle installation, therefore it should not require any additional configuration from your side. If you do not have the EMP table or it is empty, you can use the scripts at the Oracle directory \sqlplus\demo\demobld.sql to build tables from scratch and fill them with data, and use \sqlplus\demo\demodrop.sql to drop old values.

After you have properly configured these parameters, click Continue (Figure 3).

Figure 3. JDBC Connection Pool: Connection test

Generally you don't need to change anything on this page. This is a connection test page (which you can skip by clicking the Skip This Step button). WebLogic Server shows the database parameters like driver classname, URL (for JDBC drier), and credentials for review. After you have reviewed these parameters you are ready to test. Click the Test Driver Configuration link, and if everything is correct, you will see the "Connection Successful" message. You can click the Create and deploy button to finish the JDBC Connection Pool configuration.

Now that we have finished with the JDBC Connection Pool, we need to create an appropriate Data Source. Go back to the main page of the WebLogic Server console, and follow Service Configurations -> JDBC -> Data Source. On the next screen you should click the Configure a new JDBC Data Source link. You'll get to the Data Source configuration page (Figure 4).

Figure 4. JDBC Data Source: Configuration

You should define the JDBC Data Source name and the JNDI path for where this JDBC Data Source will be bound. Remember the JNDI path; we'll use it later to set up a connection from our code. Next select Continue, and then choose the correct connection pool to associate with the Data Source. Select the Connection Pool that you just created, and click Continue. The next page allows you to select servers and clusters on which to deploy the Data Source. Check the necessary ones from the list, and then click Create. The Data Source has been created and we ready to start writing the code.

Preparing the Environment for a Stand-Alone Java Application

Let's prepare our environment for writing a simple client application. To be able to make an SQL query and receive XML data as output, we will use the Oracle XML-SQL utility (XSU). We will need to configure the CLASSPATH variable to point to the Oracle XML-SQL Utility library and the Oracle XML Parser. Normally, XSU can be found in the Oracle path \rdbms\jlib\xsu12.jar, and the XML Parser can be found at \lib\xmlparserv2.jar. Also, as we're using JNDI, we need to have weblogic.jar in our CLASSPATH. Normally, you can find it in the WebLogic Server install directory at \bea\weblogic81\server\lib\weblogic.jar.

The typical CLASSPATH will look like the following:

CLASSPATH=c:\Program Files\java\jdk1.5.0_01\lib;.;

  C:\DevSuiteHome\rdbms\jlib\xsu12.jar;

  C:\DevSuiteHome\lib\xmlparserv2.jar;

  C:\bea\weblogic81\server\lib\weblogic.jar;

Considering Code for a Stand-Alone Java Application

Before showing you the code, it's worth noting that there are two approaches to working with XML in Oracle. They are quite different, and you should use the appropriate approach for your task. The first approach is to use Oracle's XSU, which allows you to return XML from any SQL query. The second approach is to use Oracle's XMLType column type.

XMLType columns allow you to treat XML as a native datatype within the database. Consequently, these columns can participate in queries just like any other column type. Oracle provides the XMLTYPE() function to construct an XMLType data object and also provides different functions for working with this data type, like XMLELEMENT() and XMLAGG(). You can read more about this approach and see examples in the WebLogic Server documentation on the Oracle driver, or on the Oracle Technology Network. In this article we are going to focus exclusively on the XSU approach.

Here is the complete source code ( oraxml.java) needed to make an SQL query on an Oracle database and produce XML output.

1. import javax.naming.*;

2. import javax.sql.*;

3. import java.sql.*;

5. import oracle.xml.sql.query.*;

5. public class oraxml

6. {

7.      public static void main(String args[]) throws SQLException, NamingException

8.      {

9.              String tabName = "emp";

10.             int maxRows = 3;

11.             Context ctx = new InitialContext (); 

12.             DataSource ds = (DataSource) ctx.lookup ("MyOra"); 

13.             Connection conn = ds.getConnection (); 
                        
14.             OracleXMLQuery qu = new OracleXMLQuery (

                  conn, "select EMPNO, ENAME from " + tabName);

15.             qu.setMaxRows (maxRows);

16.             qu.setRowsetTag ("EMPLOYERS");

17.             qu.setRowTag ("PERSON");

18.             String xmlString = qu.getXMLString();

19.             System.out.println (xmlString);

20.             conn.close ();

21.     }

22. }

The code is very simple. Lines 11-12 retrieve a Data Source with a JNDI name of MyOra. Lines 13 and 20 establish and close the connection. The most interesting lines are 14-18 which make a so-called "XML query". Technically, this is an SQL query, converted to XML by XSU. Line 14 initializes the query. Lines 15-17 set the structure of the future XML document. Line 15 sets the maximum number of rows returned, limited to maxRows. Lines 16 and 17 set the root element of the document and the item delimiters. Line 18 generates an XML document and stores it in the xmlString variable. As you can see, the code is very simple and easy to read. Editor's note: Note that this is not production code. You should close a connection in a finally clause.

We should not forget about JNDI, which we're using in this code. As this is a stand-alone Java application, we need to set a naming provider for it to use. We'll use our WebLogic Server; to configure this we need to create a jndi.properties file and make sure it's accessible from the classpath. If your CLASSPATH has the current directory (.) in the path, you should place the jndi.properties into the current directory. Here is our jndi.properties file:

java.naming.factory.initial=weblogic.jndi.WLInitialContextFactory

java.naming.provider.url=t3://localhost:7001

The typical compilation command and result of running the program is shown below (remember that the output is limited to maxRows):

C:\white\work\Java\xmlweb_src>javac oraxml.java

C:\white\work\Java\xmlweb_src>java oraxml

<?xml version = '1.0'?>

<EMPLOYERS>

   <PERSON num="1">

      <EMPNO>7369</EMPNO>

      <ENAME>SMITH</ENAME>

   </PERSON>

   <PERSON num="2">

      <EMPNO>7499</EMPNO>

      <ENAME>ALLEN</ENAME>

   </PERSON>

   <PERSON num="3">

      <EMPNO>7521</EMPNO>

      <ENAME>WARD</ENAME>

   </PERSON>

</EMPLOYERS>

Let's look at the Oracle XML-SQL Utility in just a little more detail. As you can see, this is quite a handy utility that can be used for performing SQL queries and returning an XML formatted output. However, XSU is not limited only to this functionality. XSU can also perform tasks such as dynamically generate DTDs (Document Type Definitions), and it can perform simple transformations. XSU can also generate XML documents in their string or DOM representations, insert XML into database tables or views, update or delete records from a database object, and given an XML document can generate complex nested XML documents. XSU can also store them in relational tables by creating object views over the flat tables and querying over these views. Starting in Oracle9i, XSU can also generate an XML schema given an SQL query. In addition, it supports XML attributes during generation. To fully understand how you can utilize XSU functionality for your needs, refer to the XSU documentation.

Advantages of Using XML As a Bridge

We want to highlight the advantages of the XML transfer method described in this tutorial and explain why we think it's useful. One of the questions you may be asking yourself is why we are using XML here and why we are calling it "a bridge." The best way to explain the advantage of XML as "a bridge" is through a small example. Imagine you've got an information portal, which is designed to deliver news from the SQL database to end users, whoever they are: mobile users using WAP browsers or regular web surfers with modern browsers. Furthermore, you, as an information owner, can resell it to other end users, and your news should be shown with different titles, like "News from the Acme, Corp." and "Latest news from Big Company" and whatever else. But the news that you are delivering does not change. So why should you perform extra coding for every kind of end user who may receive your news, when you technically need only change the "look and feel"?

Now you can see that you need some transparent bridge that can receive SQL on one side and produce different formats on the other. This bridge is XML. Your servlet makes a request to the database, receives XML output, and applies an XSL template to it, producing a page for an end user. So, you need to change only a design in XSL and nothing more. No extra coding, no wasted time. What about sharing your news with others? That's great, but why should you allow others to access your SQL database? Why should you explain the structure of it? You can simply produce XML from the basic SQL query and share it.

Preparing the Environment for a Web Application

Now that we have successfully coded our stand-alone application and it can query SQL and produce XML, we are halfway there. The next step is mastering the WebLogic Server options for converting XML into different formats, and building a web application. At the moment, there are different technologies for handling XML streams. Let's focus on only one of them, which is the XSLT JSP tag method. WebLogic Server provides this small JSP tag library for convenient access to an XSLT transformer from within a JSP. It is used for transforming XML documents into HTML, WML, and other formats. Let's first look at preparing an environment for our web application that will take advantage of these tools.

It is not a good idea to configure the CLASSPATH every time for a web application, therefore we need to take all our Oracle jar files and put them into the \WEB-INF\lib directory of our application. We will also need to copy from the Oracle home the \rdbms\jlib\xsu12.jar and \lib\xmlparserv2.jar jar files. WebLogic Type 4 JDBC drivers are installed with WebLogic Server in the home directory of a server in the \server\lib folder, and the drivers are automatically added to your CLASSPATH on the server.

The process of using the WebLogic XML tags is pretty simple and well documented in the WebLogic documentation Developing XML Applications with WebLogic Server. However, we will guide you through the whole process to make it even easier for you.

Take the xmlx.zip from the WebLogic Server installation directory path \bea\weblogic81\server\ext\. We need the xmlx-tags.jar from this archive, and we have to put it in the \WEB-INF\lib directory of our web application:

C:\white\work\Java\xmlweb\xmlweb_war\WEB-INF\lib>pkzipc -extract 

  C:\bea\weblogic81\server\ext\xmlx.zip xmlx-tags.jar

PKZIP(R)  Version 4.00  FAST! Compression Utility for Windows

Copyright 1989-2000 PKWARE Inc.  All Rights Reserved. Shareware Version

PKZIP Reg. U.S. Pat. and Tm. Off.  Patent No. 5,051,745

 Masking file attributes: Read-Only, Hidden, System, Archive

Extracting files from .ZIP: C:\bea\weblogic81\server\ext\xmlx.zip

    Inflating: xmlx-tags.jar

Finally, you will need a web.xml in your WEB-INF directory with a <taglib> tag referring to xmlx-tags.jar file like this:

<web-app> 

  <taglib>  

        <taglib-uri>xmlx.tld</taglib-uri>  

        <taglib-location>/WEB-INF/lib/xmlx-tags.jar</taglib-location>

  </taglib>

</web-app>

See the included sample WAR application for the complete example.

Coding the Web Application

Our goal in this section is to take the lesson we learned about producing XML in our stand-alone application, and apply it to build a web application that does the same&mdash&with the addition of transforming the XML to HTML. Let's review the files that are required for our web application. We will use \WEB-INF\web.xml (mentioned above), html.xls (a style sheet for the XSL converter, which will be used for producing HTML), and an index.jsp (the main page of the web application). Let's look at these files in detail.

First of all, let's consider the style sheet file. The html.xls file is used for converting XML output to HTML.

<?xml version="1.0" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<table width="60%" border="1" cellspacing="0" cellpadding="1">

<tr>

<td align="center"><b>Employer No.</b></td>

<td align="center"><b>Employer Name</b></td>

</tr>

<xsl:apply-templates select="EMPLOYERS"/>

</table>

</xsl:template>

<xsl:template match="PERSON">

<tr>

<td align="center"><xsl:value-of select="EMPNO"/></td>

<td align="center"><xsl:value-of select="ENAME"/></td>

</tr>

</xsl:template>

</xsl:stylesheet>

As you can see, this matches the root element, creates a table, and inserts a row for every template match of the PERSON element. Using this we should be able to produce HTML from the incoming XML.

The index.jsp is our main file in the web application. This is a regular JSP file. As we're using JSP tags, let's take a look at them. The XSLT JSP tag syntax is based on XML. A JSP tag consists of a start tag, an optional body, and a matching end tag. We'll just use the <xslt> tag in our example. The tag can take optional (attribute) parameters such as xml, which specifies the location of the XML file that we want to transform (relative to the document root of the web application), and stylesheet, which specifies the location of the style sheet to use to transform the XML document. This location is also relative to the document root of the web application. For more information about available tags and options, refer to Using the JSP Tag to Transfer XML Data documentation, to the "XSLT JSP Tag Syntax" section. Let's now look at the JSP:

1. <%@ taglib uri="xmlx.tld" prefix="x"%>

2. <HTML> 

3. <HEAD> 

4. <TITLE>:: An XML bridge ::</TITLE> 

5. </HEAD> 

6. <BODY> 

7. <%@page import="java.sql.*"%>

8. <%@page import="oracle.xml.sql.query.*"%>

9. <%@page import="oracle.jdbc.*"%>

10. <%

11.     String tableName = "emp";

12.     int maxRows = 3;

13.     Context ctx = new InitialContext (); 

14.     DataSource ds = (DataSource) ctx.lookup ("MyOra"); 

15.     Connection conn = ds.getConnection (); 

16.     OracleXMLQuery qu = new OracleXMLQuery 

      (conn, "select EMPNO, ENAME from " + tableName);

17.     qu.setMaxRows (maxRows);

18.     qu.setRowsetTag ("EMPLOYERS");

19.     qu.setRowTag ("PERSON");

20.     String xmlString = qu.getXMLString ();

21.     conn.close ();

22. %>

23. <x:xslt stylesheet="html.xsl">  

24. <x:xml>       

25.     <%=xmlString%>

26. </x:xml>

27. </x:xslt>

28. </BODY> 

29. </HTML>

Line 1 includes the xmlx tag library that we use later on in line 23. Lines 7-22 are the conversion from the regular Java code to JSP (without exception handling). It has a minor change of syntax but nothing more. Lines 23-27 is the actual XML shown. Line 23 configures the XML output and sets the style sheet file to be used to html.xsl. Lines 24-26 include the XML to be converted, which was retrieved from the database. As you can see, the code is quite straightforward.

Now that you are familiar with the code, we are ready to build and deploy the web application to WebLogic Server. As this is such a simple WAR, we'll build it manually. Here is how to create xmlweb.war for future deployment:

C:\white\work\Java\xmlweb\xmlweb_war>jar -cvf xmlweb.war .

added manifest

adding: html.xsl(in = 579) (out= 279)(deflated 51%)

adding: index.jsp(in = 935) (out= 486)(deflated 48%)

adding: WEB-INF/(in = 0) (out= 0)(stored 0%)

adding: WEB-INF/lib/(in = 0) (out= 0)(stored 0%)

adding: WEB-INF/lib/xmlparserv2.jar(in = 689990) (out= 645476)(deflated 6%)

adding: WEB-INF/lib/xmlx-tags.jar(in = 11106) (out= 9952)(deflated 10%)

adding: WEB-INF/lib/xsu12.jar(in = 456545) (out= 138160)(deflated 69%)

adding: WEB-INF/web.xml(in = 327) (out= 226)(deflated 30%)

Now, take the built web application WAR file and deploy it to WebLogic Server. You can use the WebLogic Console for this. After deploying, point your web browser to the following link: http://localhost:7001/xmlweb/index.jsp and view the result. You should get something resembling Figure 5.

Figure 5. Deployed web application

As you can see, the data, retrieved as XML from the database is nicely formatted for presentation. And now we can use this technology for a lot of different things, starting with a news feed and finishing with some complex listing engine with templates and style sheets.

Conclusion

This tutorial presented the basics of a technology that combines SQL queries with XML transformers, giving you a unique feature to use in your software. XML technology itself has many uses in web applications, but its combination with databases make it an even more powerful tool.

Resources

• Database Server Oracle 10g
• Oracle XML-SQL Utility (XSU)
• Oracle XML Parser, Version 2

Alexander Prohorenko is a certified professional, who holds Sun Certified System Administrator and Sun Certified Java Programmer certifications. His areas of interests include system development lifecycle methodologies, IT project management, server and application architecture.

Olexiy Prokhorenko is a Sun Certified Enterprise Architect, also holding Sun Certified Java Programmer and Sun Certified Web Component Developer certifications. His areas of interests include Web software architecture, development of software with frequently changing requirements, and management of distributed outsourcing teams.