Introduction

IBM ® WebSphere® Studio Application Developer is an application development product that supports the building of a large spectrum of applications using different technologies, such as JSP^TM, servlets, HTML, XML, Web services, databases, and EJBs. In particular, Application Developer provides tight integration between XML and relational data.

Application Developer supports all of the databases that WebSphere Application Server supports, including DB2®, Oracle, Sybase, and Microsoft® SQL Server.

This is Part 3 of a series of articles focusing on the XML tools provided with Application Developer. Part 3 discusses the features available to incorporate data access and XML in your application. Using the SQL query that was created in Part 2, I will now demonstrate how to do the following using Application Developer.

• Generate XML and related files from an SQL statement with the XML from SQL Query wizard
• Deploy the sample servlet to WebSphere Application Server from a Web project

In the section, "Generate XML and related files from an SQL statement with the XML from SQL Query wizard," you will see how the SQL to XML generation tool that is included with Application Developer allows you to easily generate XML, DTD, XSD, XSL, and query template (XST) files from SQL queries.

In the section, "Deploy the sample servlet to WebSphere Application Server from a Web project," I describe the dynamic use of SQL queries to generate XML within a servlet and show how to deploy the servlet to WebSphere Application Server within Application Developer. An SQLtoXML Java^TM class library is shipped with Application Developer; you can use this class library in your Java application or servlet to execute your SQL statements and construct the results as XML. The generated XST file is used to execute the SQL statement at run time in WebSphere Application Server using the XMLIntegratorServlet sample. You can modify this servlet for use in your own application.

We recommend that you read the other articles in this series:

• Part 1 Learn how to use Application Developer to develop XML Schema.
• Part 2 Learn how to create an SQL query using Application Developer's SQL Builder.
• Part 3 Learn about the Application Developer features available to incorporate data access and XML in your application.
• Part 4 Learn how to use the XML Editor, a visual tool for creating and editing XML documents.
• Part 5 Learn how to use the RDB to XML Mapping Editor to create DAD files for use with DB2 XML Extender.
• Part 6 Learn how to use the XML Schema Editor and the XML Editor together to develop XML applications that make use of XML namespaces.

Back to top

Scenario and sample query

The sample application we will be working with is a retail video store application. The application allows the store employees to create, maintain and query customers, inventory and rental information. The store database holds data related to the store customers, inventory, video rentals and employees. The application requires a query that allows the store employees to look up the video titles that a particular customer has rented and what day of the week they are due. The query takes a customer name as a parameter at run time. The query looks as follows in the SQL Builder:

Back to top

Generate XML from an SQL query

In this section, I will demonstrate how to generate XML and related files based on the customer rentals query that we have just created. The XML from SQL Query wizard is used to accomplish this:

1. Right-click on the customerRentals statement in the Data view and select Generate new XML from the pop-up menu. The XML From SQL wizard opens as shown in Figure 2 below.
   Figure 2. The XML from SQL Query wizard
   
   
2. Select to show the result table columns as elements in the XML that is generated. In addition to the XML document, we will generate a DTD definition for it. As you can see in Figure 2 above, the schema definition generation is optional and can be an XML Schema instead of a DTD if you choose.
3. Next, select to generate a query template file (XST script). This will later be used by the video store Java application to query a database and retrieve the results as XML. Click Finish.
4. Switch to the Navigator view and observe the generated HTML, XSD, XST, XSL, and XML files. You can use the XML tools to work further with these files. For example, you could trace through the generated XSL file with the XSL Trace tool by applying it to the XML file. To invoke the XSL Trace tool, select both the XSL and XML file, and select Apply XSL => As XML from the pop-up menu. You can use the XML Schema editor, HTML editor, or the XML editor to browse or edit the XSD, HTML, and XML files respectively.

Applying the XSL file to the XML file as HTML or viewing the HTML file gives us the following results:

Back to top

Using a query template file in a servlet

This section describes how queries can be executed at run time to dynamically generate XML from SQL. Snippets of code are presented to give you an idea of how to incorporate this into your own servlet. A sample servlet and HTML file are shipped with Application Developer that you can modify for your own purposes. They use the XST file and code described in this section. For an example of how to use the sample servlet, see the following section on deploying the sample servlet.

You can use the query template (XST) file created with the SQL to XML wizard in the video store application at run time to dynamically generate XML from SQL. To do this, use the SQLtoXML Java class shipped with Application Developer. The generated XST file contents are as follows. The XST file provides the SQLtoXML class with database connection information, the SQL query and the options you selected to generate the XML result.

<?xml version="1.0"?>  
<SQLGENERATEINFORMATION>  
  <DATABASEINFORMATION> 
    <LOGINID>JOAN</LOGINID> 
    <PASSWORD><![CDATA[somepassword</PASSWORD> 
    <JDBCDRIVER>COM.ibm.db2.jdbc.app.DB2Driver</JDBCDRIVER> 
    <JDBCSERVER>jdbc:db2:VIDEOS</JDBCSERVER> 
  </DATABASEINFORMATION>  
  <STATEMENT>  
    <![CDATA[ SELECT WSAXML.VIDEOS.TITLE AS VIDEOTITLE,  
    WSAXML.CUSTOMERS.NAME AS CUSTNAME,  
    CASE DAYOFWEEK(WSAXML.RENTALS.DATE)  
    WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday'  
    WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' 
    WHEN 7 THEN 'Saturday' END AS DUEDATE FROM WSAXML.VIDEOS,  
    WSAXML.RENTALS, WSAXML.CUSTOMERS WHERE WSAXML.VIDEOS.VID_ID =  
    WSAXML.RENTALS.VID_ID AND WSAXML.RENTALS.CUST_ID =  
    WSAXML.CUSTOMERS.CUST_ID AND WSAXML.CUSTOMERS.NAME =  
    :custName GROUP BY DAYOFWEEK(WSAXML.RENTALS.DATE),  
    WSAXML.VIDEOS.TITLE, WSAXML.CUSTOMERS.NAME 
  </STATEMENT>  
    <OPTIONS>  
    <FORMATOPTION>GENERATE_AS_ELEMENTS</FORMATOPTION>  
    <RECURSE>FALSE</RECURSE>  
  </OPTIONS>  
</SQLGENERATEINFORMATION>

To use the SQLToXML class in a Java servlet, the classpath must be set to include the Xalan processor and the Xerces parser as well as the sqltoxml.jar file. You can create a servlet from scratch or modify the generic servlet (XMLIntegratorServlet.java) that is shipped with Application Developer. You can use WebSphere Application Server or Tomcat to run the servlet. See the next section for more information on servlet deployment.

To load the template file, use the QueryProperties class that is included with the SQLToXML run-time library as follows:

QueryProperties qp = newQueryProperties(); 
qp.load("locateRentedVideo.xst");

The code to instantiate the SQLToXML class is as follows:

SQLToXML sql2xml = new SQLToXML(qp);

You can use the execute methods from the SQLToXML class to generate the files that will be required to display the query results to the user of the application. For our video store query to locate rented videos for a customer, we used a variable in our search condition. The name of the customer will be entered by an employee of the video store to query the customers, videos and rentals stored in the store database. In the Java code, you would pass a parameter containing the value entered by the employee through the parameters list of the execute method to provide the name of the video to the query.

An example of one of the execute methods in use for the video store Java application is shown below. Note that the first argument is passing a value for the :custname variable that we have in our query to the Java application.

sql2xml.execute('John Doe', xmlPrintWriter, "customerRentals.dtd",  
  "customerRentals.xsd", xslPrintWriter);

The results of execute method (XML, XSD, DTD, XSL) are used by the Java application to display the query results to the employee.

Back to top

Deploy the sample servlet to WebSphere Application Server from a Web project

A generic servlet (XMLIntegratorServlet.java) for WebSphere Application Server, Version 4.0 demonstrates the typical use of the SQLToXML run-time library. You can modify this servlet and use it for other applications such as the video store application. You can then deploy the servlet to WebSphere Application Server from within Application Developer. Here, I will show you how to modify the generic servlet along with the query template (XST) file generated earlier and use it to deploy to WebSphere Application Server. The steps, described in more detail below, are as follows.

1. Create a Web project
2. Import the servlet into the Web project
3. Set classpath variables
4. Add required libraries to the project build path
5. Import application sample files
6. Modify HTML and servlet to point to our application
7. Modify the servlet and HTML to accept a parameter
8. Import the SQLToXML JAR file into the WEB-INF directory
9. Run the application on the server using the query template file

Back to top

Create a Web project

First, we will create a new Web project for the video store application. To do this:

1. From the menu, select File => New => Project.
2. In the New Project wizard, select the Web category on the left side and then Web Project from the right side. Click Next.
3. Enter VideoWeb for the name of the project and accept all of the other default values. Click Finish.
4. The Web project is created in the workspace for you and the perspective has changed to the Web perspective.

Back to top

Import the servlet into the Web project

Next, we need to import the servlet into the project so that we can use it for deployment. To do this:

1. From the menu, select File => Import.
2. In the Import wizard, select to import from the File system. Click Next.
3. For the directory, use the Browse button and navigate to the following path under the directory where Application Developer is installed: plugins\com.ibm.etools.sqltoxml\samples\was4.0. Click OK.
4. Expand the tree, and select the source check box.
5. In the Folder field (this is the destination directory for the import), enter VideoWeb.
6. Click Finish.
7. Look in the VideoWeb\source directory in the Navigator view and you will see the path com\ibm\etools\sqltoxml\servlet that contains two Java files. Ignore any errors displayed in the Tasks view. They will disappear as we finish setting up the project.

Back to top

Set classpath variables

The SQLTOXML, WAS_XALAN, and XERCES classpath variables need to be set for the project. The XML perspective automatically initializes these variables. We will switch to the XML Perspective to take advantage of this. From the menu, select Perspective => Open => XML. You can check which classpath variables are defined in the environment by looking at the Java preferences. To do this:

1. Select Window => Preferences.
2. In the Preferences wizard, expand the Java node and click on Classpath Variables.
3. On the right side of the wizard, you will see all of the classpath variables listed.

Back to top

Add required libraries to the project build path

The SQLToXML, XALAN, and XERCES libraries need to be added to the Java build path for the Web project. Since the libraries are already variables in the classpath, we can simply add the variable to the libraries list for the Web project. This method of adding the JAR files means that the paths are not hardcoded and therefore relocatable. To do this:

1. Right-click on the Web project and select Properties from the pop-up menu.
2. In the Properties dialog, select Java Build Path from the list on the left side of the dialog.
3. On the right side, click the Libraries tab and you will see the libraries already in the project build path.
4. Click the Add Variable button.
5. In the Classpath Variable Selection dialog, use the Browse button to select the SQLTOXML variable. Click OK.
6. Click OK again to close the Classpath Variable Selection dialog.
7. Repeat the last four steps to add the following two variables to the Libraries list:
• XERCES
• WAS_XALAN
8. Now all of the libraries have been added (see Figure 3 below). Click OK.
   
   Figure 3. The Java Build Path with all of the libraries added
   
   

Back to top

Import application sample files

To use the sample application files, we need to import them into the Web project:

1. From the menu, select File => Import. Select to import from File System, and click Next.
2. Browse to the plugins\com.ibm.etools.sqltoxml\samples\was4.0\ directory.
3. Expand the directory tree and select the webApplication check box.
4. In the Folder field (this is the destination directory for the import), enter VideoWeb. Click Finish.
5. Select Yes to overwrite web.xml with the imported copy.

Back to top

Modify HTML and servlet to point to our application

Some paths in the sample code need to be modified to match the path for the VideoWeb project. Namely, the context root for the POST command in the HTML and the servlet variable path in the servlet. First, we will modify the path for the context root:

1. In the Navigator view, expand the webApplication view and find the XMLIntegrator.html file.
2. Right-click on the XMLIntegrator.html file, and select Open with => Source Editor.
3. The HTML source is now displayed in the editor.
4. Change the FORM tag:

   <FORM name="sqltoxmlsample" METHOD="POST" ACTION="/sqltoxml/servlet/XMLIntegratorServlet">

   
   
   to:
   

   <FORM name="sqltoxmlsample" METHOD="POST" ACTION="/VideoWeb/servlet/XMLIntegratorServlet">

   
   
5. Save the file by selecting File => Save XMLIntegrator.html.

Next, we will modify the XMLIntegratorServlet SERVLET variable:

1. In the Navigator view, expand the VideoWeb project source directories until you find the XMLIntegratorServlet.java file.
2. Double-click on XMLIntegratorServlet.java to open the file in the Java editor.
3. Find the line:

   private static final String SERVLET = "/sqltoxml/servlet/XMLIntegratorServlet";

   
   
   and change it to:
   

   private static final String SERVLET = "/VideoWeb/servlet/XMLIntegratorServlet";

   
   
4. Save the file by selecting File => Save XMLIntegratorServlet.java.

Back to top

Modify the servlet and HTML to accept a parameter

The sample XMLIntegrator.html file has one input field to get the name of the XST file from the user. The customerRentals query has a parameter that also requires customer input: the customer name. To modify the HTML file so that there is an input field for the parameter, do the following:

1. If the file is not already open, open it by right-clicking and selecting Open with => Source Editor.
2. Look for the line:
   

   <input type="Hidden" name="params" VALUE="" SIZE=50 MAXLENGTH=50>

   
   
3. Add a label, change the type to Text, and put the label and text in a table row as follows:
   

   <tr> <td> <B>Customer Name:</B> </td> <td> <input type="Text" name="params" VALUE="" SIZE=50 MAXLENGTH=50> </td> </tr>

   
   
4. Save the file. The XMLIntegratorServlet already has code to process parameters so this is the only change we need to make.

Back to top

Import the SQLToXML JAR file into the WEB-INF directory

At run time, the SQLToXML JAR file will be needed to access things like the QueryProperties class that was described in the last section. To make it available at run time, the JAR file needs to be in the Web project's WEB-INF directory. Import the JAR file as follows:

1. In the Navigator view, select the lib directory under VideoWeb\webApplication\WEB-INF.
2. From the menu, select File => Import.
3. Select to import from the File system and click Next.
4. Browse to plugins\com.ibm.etools.sqltoxml\jars\ under the directory where Application Developer is installed.
5. Click on jars in the left pane. In the right pane, check the sqltoxml.jar check box.
6. Set the destination folder to be VideoWeb/webApplication/WEB-INF/lib.
7. Click Finish. The JAR file appears in the lib directory for the Web application.

Back to top

Run the application on the server using the query template file

Now we are ready to run the application on the server. I will show you how start the server and then how to run the query using the query template file.

1. Right-click on the XMLIntegrator.html file in the Navigator view.
2. Choose Run on Server from the pop-up menu. A publishing message appears followed by a message that the server is starting. The Server perspective now opens.
3. Messages in the Console at the bottom of the Server perspective show you what is happening with the application server. You will see messages indicating that the server is starting.
4. Once the server is running, the XMLIntegrator.html file will appear in the Application Developer Web browser. See Figure 4 below.
   Figure 4. The XMLIntegrator.html file as it appears in the Application Developer Web browser
   
   
5. In the entry field, enter the location of the customerRentals.xst file that was generated earlier.
6. Enter the parameter 'John Doe' for the query in the parameter entry field.
7. Click Submit to submit the query. You will see the results in the Web browser as shown in Figure 5 below.
   Figure 5. The results of the query displayed in the Web browser
   
   

To stop the server when you are finished running the application:

1. Click the Servers tab in the console.
2. Right-click on the running server, and select Stop from the menu.

Back to top

Summary

In this article, I discussed the support that WebSphere Studio Application Developer provides for using SQL with XML in a Web application. You have seen how to use Application Developer to:

• Generate XML from an SQL query and other XML-related files: XSL, DTD, XML Schema and query template file.
• Dynamically generate XML from SQL queries at run time using the generated query template, DTD and the SQLToXML class library within a servlet.
• Deploy this servlet from a Web project to WebSphere Application Server using the sample servlet.

In future articles, you will be shown the integration of the SQL Builder with other XML tools. For example, the creation of DAD files using the RDB to XML mapping tool and the use of DAD files for DADX Web services.

Back to top

Download

Name	Size	Download method
createVideoStore.zip	3 KB	FTP|HTTP

Information about download methods

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Scenario and sample query
• Generate XML from an SQL query
• Using a query template file in a servlet
• Deploy the sample servlet to WebSphere Application Server from a Web project
• Create a Web project
• Import the servlet into the Web project
• Set classpath variables
• Add required libraries to the project build path
• Import application sample files
• Modify HTML and servlet to point to our application
• Modify the servlet and HTML to accept a parameter
• Import the SQLToXML JAR file into the WEB-INF directory
• Run the application on the server using the query template file
• Summary
• Download
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into WebSphere on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers