Introduction

IBM's WebSphere^TM Studio Application Developer is an application development product that supports the building of a large spectrum of applications using different technologies, such as JSPs, 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^TM, Oracle, Sybase, and Microsoft^TM SQL Server.

This is Part 2 of a series of articles focusing on the XML tools provided with Application Developer. Part 1 covered how to use WebSphere Studio Application Developer to develop XML Schema. This article, Part 2, covers the database query support provided in Application Developer. In this article, you will learn how to create an SQL statement using Application Developer's SQL Builder. I will be using a sample statement to demonstrate how to use the SQL Builder to include a table alias, a column alias, a database function expression, a CASE expression, and a grouping clause in a SELECT statement.

Back to top

Creating an SQL statement using the SQL Builder

In this article, we will be working with a database for a video store. The database holds data related to the store customers, inventory, video rentals, and employees. The video store application needs a query that allows the store employees to look up the video titles a particular customer has rented and what day of the week they are due. The query involves two joins, a query condition (where clause) and a group by clause. The SQL statement will also need a CASE expression, a function and a host variable to get the day of the week and to substitute the name of the customer at run-time. We will be completing the following steps to create the query.

1. Connect to a database and import a local copy
2. Create a new SELECT statement
3. Add tables to the statement
4. Add a table alias
5. Specify result columns
6. Add a column alias
7. Create joins
8. Create a query condition
9. Add a group by clause
10. Execute and run the SQL query

1. Connect to a database and import a local copy

To connect to the database and obtain a local copy:

1. In WebSphere Studio Application Developer, switch to the Data perspective by selecting Perspective => Open => Data.
2. Once the Data perspective is open, launch the Database Connection wizard from the New Connection pop-up menu in the DB Explorer view.
3. In the Database Connection wizard (see Figure 1 below), provide the connection details. In the Connection name field, enter VideoStore, and in the Database field, enter VIDEOS. Enter your user ID and password, and the JDBC information. Click Finish. The VideoStore connection that was created now appears in the dB Explorer view.
   Figure 1. The Database Connection wizard
   
   
4. To import the database to the workspace and work on it locally, expand the VideoStore connection in the dB Explorer view to see the VIDEOS database. Select Import to Folder from the pop-up menu on the database. The Import dialog opens.
5. In the Folder field of the Import dialog, enter VideoStoreProject. Select Yes when prompted to create the project.
6. Switch from the dB Explorer view to the Data view, and expand the VideoStoreProject project and the items below it in the tree. The VIDEOS database now appears as part of the VideoStoreProject.

2. Create a new SELECT statement

You can add Select, Insert, Delete, Update, With and FullSelect statements using the pop-up menu on the Statements folder in the Data view. We will create a Select statement for the VIDEOS database. To create the new statement:

1. Right-click on the Statements folder and select New => Select Statement. Name the statement customerRentals in the dialog that appears. Press OK.
2. The Select statement is created and opened in the SQL Builder for you, as shown in Figure 2 below.
   Figure 2. The Select statement created and opened in the SQL Builder
   
   

The SQL Builder is made up of four main views: the outline view, the source view, the graph view and the design view. The outline view (bottom left in Figure 2) shows the statement you are currently working on. For more complex statements such as With and FullSelect, the outline view shows the structure of the statement such as subselects and common table expressions. The source view (top right) shows the SQL source for your statement as it is being built. The graph view (below the source view) shows the tables and table columns that have been added to the statement. The design view (bottom right) changes depending on the statement being edited and is used to fill in details for statement clauses. You will see the uses of all of these views in more detail throughout this article.

The Select statement we created is already open in the SQL Builder so we will continue to construct it there. Note that we will only be creating a Select statement in this article. Insert, Delete, Update, FullSelect and With SQL statements can be created using the SQL Query builder in the same manner using the source, graph and design views.

3. Add tables to the statement

We will add three tables to the statement for the customerRentals query. To do this:

1. Expand the WSAXML schema in the tree for the VIDEOS database in the Data view. You will see the tables for the database. See Figure 3 below.
   Figure 3. The database tables, as listed in the Data view
   
   
2. Drag the VIDEOS, RENTALS and CUSTOMERS tables into the graph view in the SQL Builder. You will see the tables appear in this view. Notice that the SQL source has changed.
   Figure 4. The graph view in the SQL Builder
   
   

4. Add a table alias

Table aliases can be added when you add the table or after the table is added by using the pop-up menu on the table in the graph view. In this view, you can also use the pop-up menus on the tables to delete tables or create joins between tables. Joins can also be created using drag and drop. In a moment, I will describe adding joins to the statement.

We will add V as the alias for VIDEOS, R as the alias for RENTALS, and C as the alias for CUSTOMERS as follows:

1. Right-click on the VIDEOS table in the graph view.
2. Select Update alias from the pop-up menu.
3. In the dialog, enter V as the alias for the table.
4. Click OK.
5. Observe the alias shown on the table in the graph view and the "AS V" in the source view for the VIDEOS table.
6. Repeat the above steps to add the aliases for the RENTALS and CUSTOMERS databases.

5. Specify result columns

Next, we want to specify what columns to show in the query result. We will simply include two of the columns and add a CASE expression for the third result column. Add the VIDEOS.TITLE and CUSTOMERS.NAME columns to the result column set in the graph view as follows:

1. Click on the check box beside the WSAXML.VIDEOS.TITLE column name in the V table.
2. Click on the check box beside the and WSAXML.CUSTOMERS.NAME column name in the C table.
3. Take a look at the source view to make sure that the columns have been added to the SQL source.

Creating a CASE expression

The third column for the query result set will be the result of a column expression. The video store database stores the date that the video is due in DATE format. The DATE format needs to be translated to a character string that contains the name of the weekday. A database function will give an integer that corresponds to the day of the week from the date. We will use a CASE expression to evaluate the function and convert the integers returned from the function into character strings for each day of the week (that is, 1 will be changed to "Sunday" in the query result). We will complete the following steps in the Expression Builder:

1. Launch the Expression Builder.
2. Create a simple type CASE expression.
3. Add the DAYOFWEEK function for evaluation by the CASE expression.
4. Add WHEN clauses to produce the results for the CASE expression.

Launch the Expression Builder

We will launch the Expression Builder from the Columns tab in the design view:

In the third row of the Column column, select the Build Expression option from the drop-down menu in the table cell. The Expression Builder opens.

Create a simple type CASE expression

Using the Expression Builder:

1. From the list of expression types on the first page of the Expression Builder, choose the option to create a CASE - Search or simple type expression. Click Next.
2. On the following page, choose the option to create a CASE expression of Simple-When-Clause type. Click Next. You will now see the Simple Type Case Page of the Expression Builder:

Add DAYOFWEEK function for evaluation by the CASE expression

To get the day of the week from the DATE column, we will use the DAYOFWEEK function. This function returns an integer that corresponds to the day of the week. In the next section, you will see how to add the WHEN clauses for the CASE expression to map the integer to a character string. To add the DAYOFWEEK function to the CASE expression:

1. In the CASE drop down at the top of the page, select the Build function expression option.
2. The Function Builder Page opens in a separate dialog, with the details filled in. See Figure 7 below.
   Figure 7. The Function Builder Page
   
   
3. On the Function Builder Page, select the Date and time function category.
4. Select the DAYOFWEEK function from the list of Date and Time functions.
5. Select the DAYOFWEEK(DATE) => INTEGER function signature from the list of DAYOFWEEK function signatures.
6. The function signature shows that the function requires one argument. In the Value column of the argument table, use the drop-down combo to select the WSAXML.RENTALS.DATE column.
7. Review the syntax of the function at the bottom of the dialog.
8. Click Finish to return to constructing the CASE expression. The function is now displayed in the CASE drop-down at the top of the Simple Type CASE Page in the Expression Builder.

Now we will complete the rest of the CASE expression.

Add WHEN clauses to produce the results for the CASE expression

Next, we need to add seven WHEN clauses to the CASE expression (one for each day of the week) to translate the integer returned from the DAYOFWEEK function into character strings. The WHEN clause table (shown completed in Figure 8 below) needs to be filled in for each of the seven clauses as follows:

1. Click the Add WHEN Clause button to add the first WHEN clause.
2. Click in the table cell in the first row under the Expression column and enter the integer 1.
3. Click in the table cell in the first row under the Result Expression column and enter the character string "Sunday."
4. Repeat the above steps six more times, once for each day of the week.
5. Preview the CASE expression at the bottom of the page.
6. Click Finish to close the Expression Builder. The completed CASE expression appears in the list of column expressions in the Columns tab in the design view and also in the statement source.

6. Add a column alias

In the customerRentals SQL statement, we will add a column alias for the CASE column expression. This can be done in the Columns tab of the SQL Builder's design view as follows:

1. Click on the Columns tab of the design view.
2. Click in the Alias column next to the CASE column expression and enter DUEDAY.
3. Check the source view and you will see the column alias "AS DUEDAY"' after the CASE expression. This alias will appear as the title for the column in the result table when the statement is executed.

The Columns view now looks as follows:

7. Create joins

Two joins are needed for this query. The query needs to match up the RENTALS and CUSTOMERS table entries that have matching CUST_ID column values. Then, the RENTALS and VIDEOS video IDs (VID_ID) also need to match for result rows. Create a join as follows:

1. Drag and drop the CUST_ID column from the RENTALS table to the CUST_ID column in the CUSTOMERS. A join connector appears between the two columns.
2. Repeat the same procedure for the VID_ID columns of the RENTALS and VIDEOS tables.
3. Look at the source view to see the joins in the source:
   

   WHERE V.VID_ID = R.VID_ID AND R.CUST_ID = C.CUST_ID

   
   
4. In the graph view, you will see that both joins have been created:
   Figure 10. The graph view showing that both joins have been created
   
   
5. You can change the join type (that is, from the default inner join to left, right and full outer joins) in the graph view by right-clicking on the connector and selecting Specify Join Type from the menu.

8. Create a query condition

Next, the query needs a query condition so that the query extracts only result rows that have the customer name that we want. Conditions are added to the query using the Conditions tab in the design view:

1. Click the Conditions tab to see the conditions table.
2. Click in the Column column and select WSAXML.VIDEOS.TITLE from the combo box.
3. Click in the Operator column and select the "=" operator.
4. Click in the Value column and enter :custName. The colon followed by a variable name is the SQL syntax for a variable that will be substituted with a value when the query is executed. You will see how this is done during testing in the section below about executing your statement. The variable can also be passed parameters from your Java^TM application. This is also described later.
5. The Conditions view looks like Figure 11 below.

9. Add a group by clause

We will group the query by the day of the week, then by title and then by customer name. To create a group by clause in the SQL Builder, use the Groups tab in the design view. In addition to creating simple group by clauses in this view, you can also create more advanced groupings in your query result using, column expressions, nested groups, grouping sets (in DB2 only) and the ROLLUP and CUBE grouping functions (in Oracle and DB2). To add the group by clause for the customerRentals query, do the following:

1. Click on the Groups tab in the design view.
2. In the Column table, choose to Build expression from the drop-down menu in the first row. The Expression Builder appears.
3. Select to create a Function expression. Complete the Function Builder Page the same way as we did for the DAYOFWEEK function in the CASE expression. Alternatively, in the source view, you can copy the DAYOFWEEK function from the CASE expression and paste it into your group by clause. When you have finished entering the function, press Finish to return to the Groups tab. You will see the function you created in the Column table.
4. In the second and third rows of the table, select the WSAXML.VIDEOS.TITLE and WSAXML.CUSTOMERS.NAME columns from the drop down menus. The group by clause is complete. The Groups view now looks as follows:

Now the query is complete. The query looks as follows in the SQL Builder:

10. Execute and run the SQL query

Before incorporating the SQL query in the video store application, it needs to be tested to ensure that it returns correct results. We can do this using the SQL Builder as follows:

1. Click in the source view of the SQL Builder to enable the SQL menu. Select SQL => Execute.
2. Review the statement at the top of the Execute SQL Statement dialog. Click the Execute button beneath the SQL statement field.
3. The Specify Variable Values dialog opens, prompting for a value for the host variable that we added to the statement for the customer name. Change the value from the default NULL to "John Doe." Press Enter to accept the value and then click Finish to close the dialog.
4. You will see the results of your query displayed at the bottom of the Execute SQL Statement dialog. A portion of this dialog is shown in Figure 14 below.
   Figure 14. The results of the query displayed in the Execute SQL Statement dialog
   
   
5. When you are finished reviewing the results of the query, click Close to close the Execute SQL Statement dialog.

Back to top

Summary

WebSphere Studio Application Developer provides tight integration between XML and relational data. In this article, I discussed much of the SQL-related support provided with Application Developer. This support will be very useful in your XML development process. You have seen how to use Application Developer to:

• Construct SQL queries in the SQL Builder, including joins, expressions, conditions and groups.
• Execute SQL queries against the database and pass in host variables for the query.

In the next article of this series, you will learn how 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.
• Use the SQL Builder with other XML tools. For example, you will learn how to create DAD files using the RDB to XML mapping tool, and how to use 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

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Creating an SQL statement using the SQL Builder
• Summary
• Download
• About the author
• Comments

Next steps from IBM

With the no-charge DB2 Express-C, pureXML, Zend Core PHP, and Java you can develope advanced Web 2.0 applications quickly that matter.

---

• Try: Download a free trial version of DB2 Express-C, which includes Zend Core PHP and pureXML technology that is ideally suited for managing both XML and relational data.
• Article: See how DB2 pureXML schema capabilities can evolve schemas without making any changes to your applications.
• Tutorial: This tutorial shows you how to use DB2's pureXML to expose the native XML capabilities of DB2.
• Buy: DB2 9 for Linux, UNIX and Windows

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers