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 JSPTM, 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 JavaTM 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.
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:
Figure 1. The query as it appears in the SQL Builder
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:
- 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
- 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.
- 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.
- 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:
|The Adventures of Kernighan and Ritchie||John Doe||Thursday|
|The Taming of the Foo||John Doe||Thursday|
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
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
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.
A generic servlet (
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.
- 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
First, we will create a new Web project for the video store application. To do this:
- From the menu, select File => New => Project.
- In the New Project wizard, select the Web category on the left side and then Web Project from the right side. Click Next.
- Enter VideoWeb for the name of the project and accept all of the other default values. Click Finish.
- The Web project is created in the workspace for you and the perspective has changed to the Web perspective.
Next, we need to import the servlet into the project so that we can use it for deployment. To do this:
- From the menu, select File => Import.
- In the Import wizard, select to import from the File system. Click Next.
- 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.
- Expand the tree, and select the source check box.
- In the Folder field (this is the destination directory for the import), enter VideoWeb.
- Click Finish.
- Look in the
VideoWeb\sourcedirectory in the Navigator view and you will see the path
com\ibm\etools\sqltoxml\servletthat contains two Java files. Ignore any errors displayed in the Tasks view. They will disappear as we finish setting up the project.
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:
- Select Window => Preferences.
- In the Preferences wizard, expand the Java node and click on Classpath Variables.
- On the right side of the wizard, you will see all of the classpath variables listed.
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:
- Right-click on the Web project and select Properties from the pop-up menu.
- In the Properties dialog, select Java Build Path from the list on the left side of the dialog.
- On the right side, click the Libraries tab and you will see the libraries already in the project build path.
- Click the Add Variable button.
- In the Classpath Variable Selection dialog, use the Browse button to select the SQLTOXML variable. Click OK.
- Click OK again to close the Classpath Variable Selection dialog.
- Repeat the last four steps to add the following two variables to the Libraries list:
Figure 3. The Java Build Path with all of the libraries added
To use the sample application files, we need to import them into the Web project:
- From the menu, select File => Import. Select to import from File System, and click Next.
- Browse to the
- Expand the directory tree and select the webApplication check box.
- In the Folder field (this is the destination directory for the import), enter VideoWeb. Click Finish.
- Select Yes to overwrite
web.xmlwith the imported copy.
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:
- In the Navigator view, expand the webApplication view and find the
- Right-click on the
XMLIntegrator.htmlfile, and select Open with => Source Editor.
- The HTML source is now displayed in the editor.
- Change the FORM tag:
<FORM name="sqltoxmlsample" METHOD="POST" ACTION="/sqltoxml/servlet/XMLIntegratorServlet">
<FORM name="sqltoxmlsample" METHOD="POST" ACTION="/VideoWeb/servlet/XMLIntegratorServlet">
- Save the file by selecting File => Save
Next, we will modify the XMLIntegratorServlet SERVLET variable:
- In the Navigator view, expand the VideoWeb project source directories
until you find the
- Double-click on
XMLIntegratorServlet.javato open the file in the Java editor.
- Find the line:
private static final String SERVLET = "/sqltoxml/servlet/XMLIntegratorServlet";
and change it to:
private static final String SERVLET = "/VideoWeb/servlet/XMLIntegratorServlet";
- Save the file by selecting File => Save
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:
- If the file is not already open, open it by right-clicking and selecting Open with => Source Editor.
- Look for the line:
<input type="Hidden" name="params" VALUE="" SIZE=50 MAXLENGTH=50>
- 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>
- Save the file. The XMLIntegratorServlet already has code to process parameters so this is the only change we need to make.
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:
- In the Navigator view, select the lib directory under
- From the menu, select File => Import.
- Select to import from the File system and click Next.
- Browse to
plugins\com.ibm.etools.sqltoxml\jars\under the directory where Application Developer is installed.
- Click on jars in the left pane. In the right pane, check the
- Set the destination folder to be
- Click Finish. The JAR file appears in the
libdirectory for the Web application.
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.
- Right-click on the
XMLIntegrator.htmlfile in the Navigator view.
- 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.
- 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.
- Once the server is running, the
XMLIntegrator.htmlfile 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
- In the entry field, enter the location of the
customerRentals.xstfile that was generated earlier.
- Enter the parameter
'John Doe'for the query in the parameter entry field.
- 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:
- Click the Servers tab in the console.
- Right-click on the running server, and select Stop from the menu.
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.
Joan Haggarty is a Staff Software Developer at the IBM Toronto Lab. She is a member of the XML tools team for WebSphere Studio Application Developer, focusing specifically on the SQL Builder. You can contact Joan at email@example.com.