Using Service Data Objects in JavaServer Pages with Rational Application Developer 7.0 and later

This article demonstrates how you can use IBM® Rational® Application Developer features to exploit Service Data Objects technology. This allows you to create database views using complex queries without requiring any programmatic knowledge.

Geetika Tandon (geetika@us.ibm.com), Senior IT Specialist, IBM

Geetika Tandon photoGeetika Tandon is a Senior IT Specialist at IBM Federal Software Group in the Washington D.C area. She was a J2EE and WebSphere developer with ISSW working on RFID solutions, in her previous capacity. She is also actively involved with patenting and innovation within IBM. Geetika holds two masters degrees, one in Computer Science from UC Santa Barbara and another Masters in Architecture from University of Southern California. You can reach Geetika at geetika@us.ibm.com



08 April 2008

Also available in Chinese

Overview

Service Data Objects (SDO) is a standard-based technology used for passing data between service components in the Service Component Architecture. This technology has now been extended to Java™Server Pages to create data views and database queries using a simple, wizard-based, drag-and-drop technology.

This article demonstrates how you can use IBM® Rational® Application Developer features to exploit Service Data Objects technology and assumes some level of familiarity with the product, the Java™ Database Connectivity API (JDBC), and Data Sources.


Supporting technologies

Following are definitions of some of the basic technologies used in this article.

Service Data Objects

The Service Data Object (SDO) framework provides a unified framework for data application development. With SDO, you do not need to be familiar with a technology-specific API in order to access and utilize data (see Introduction to Service Data Objects in the Resources section). In some ways it is analogous to the Eclipse framework, which provides a base to integrate development tools. SDO enables both a static (or strongly typed) programming model and a dynamic (or loosely typed) programming model. This enables a simple unified programming model without sacrificing the dynamic model needed by tools and frameworks. Using SDO, you (as an application programmer) can uniformly access and manipulate data from heterogeneous data sources, including relational databases, XML data sources, Web services, and enterprise information systems.

JavaServer Pages

JavaServer Pages (JSP) were initially developed to separate Web designers from programmers (so that the presentation logic stayed separate from the business logic layer), in conjunction with the Model View Controller architecture. However, database access within a JSP was accomplished using either embedded SQL code (embedded as scriptlets in the JSP code), or via Java™Bean references (thus requiring knowledge of Java™ and JDBC). The use of SDO-based wizards has resolved this issue. Using this technology, you can perform complex database queries without the use of any programmatic constructs or embedded code.

Rational Application Developer

Rational Application Developer Version 7.0 (or later) features provide the comprehensive Integrated Development Environment (IDE) used to develop applications requiring Servlets and JSPs.


Initial setup

In order to display this technology, you need to set up three main things:

  1. A database with at least one table (this example uses an IBM® DB2® database named SDOJSP)
  2. A Dynamic Web Project in Rational Application Developer, with at least one JSP to access the database
  3. A DataSource in IBM® WebSphere® Application Server

Database setup

The database setup is not covered in this article. It is assumed that you have prior knowledge of databases, and can create a basic database and tables. For the purposes of this article, the database details are as follows:

  • Database name: SDOJSP
  • Schema Name: GTANDON

It has two tables, with details as follows:

  • PERSONAL with columns ID(VARCHAR 20), FIRSTNAME(BARCHAR 25), LASTNAME(VARCHAR 25), and PRIMARY KEY:ID
  • PROFESSION with columns ID(VARCHAR 20). WORK(VARCHAR 30), EMAIL(VARCHAR 30), and FOREIGN KEY:ID

Setting up a Web Project

To create a Dynamic Web Project in Rational Application Developer, perform the following steps.

  1. Start Rational Application Developer from the Start menu and open a new Workspace.
  2. Once you are in the workspace, click the perspective icon on the right and switch to the Web perspective.
  3. Then, start a new project by right-clicking in the project explorer and selecting New > Dynamic Web Project.
  4. Enter a project name and click Finish.

A new Web project and a corresponding EAR project are created in the project explorer.

Figure 1: Create a Dynamic Web Project in Rational Application Developer
project configuration options
  1. On the SdoDem project, click Web contents.
  2. Right-click and create a new folder called jsp
  3. Then right-click the jsp folder and select New > File > sdoDemo.jsp

You have created a blank JSP page. This example used a pre-existing template.

This completes the basic setup for the Web project. Now all that you need to do is create a database connection, and you will be ready to create SDOs to access the database.

Setting up a DataSource on WebSphere Application Server

This is a critical step in making sure that the SDO service works properly. Since this article focuses on the details of the SDO wizard in JSP technology, it will not outline these steps in detail. However, the most important aspects of setting up the data source are:

  • Make sure that the data source name is EXACTLY the same as jdbc/[DATABASENAME]. In your case, because the database name is SDOJSP, the Java™ Naming and Directory Interface (JNDI) name of the datasource is jdbc/SDOJSP.
  • Use the XA JDBC provider to create the datasource in the admin console.
  • In the Java™ 2 Platform, Enterprise Edition (J2EE Platform) Connector Architecture, or J2C, authentication alias, input the correct administrator login and password for the database being used.

Accessing information from the database using the SDO wizard

The Web page is now ready, and your database has been created. At this point, you are ready to add information to access the database. You will start by adding a form to perform an INSERT into the database, and then you will retrieve a record from the database and display it on the page. Lastly, you will do a join, and display records from multiple tables together on one page.

Insert a record in the database

In order to create a record in the database from the JSP, you need to establish a database connection. You can reuse this connection to create, read, update, or delete other records.

  1. Open the JSP page and click the Design tab. On the right is the JSP and the HTML Palette, as shown in Figure 2.
  2. From the palette, click Data. This displays your data manipulation choices.
Figure 2. JSP Tag Palette details
pallette

You can use the first option, JavaBean, to insert a JavaBean, which is the traditional method of database interaction in JSP pages. The second and third are the new, SDO-related Palette choices, Relational Record and Relation Record List. You will use these commands to create, read, update, or delete records from your database.

  1. Drag Relational Record into the JSP page.
  2. This will start the Add Relation Record wizard, in which you specify a name as shown in Figure 3. Enter e_name in the Name field.
  3. Select Creating a new record, because you are inserting a record into the database. Click Next.
Figure 3. Enter a reference name for the record
dialog with record configuration options
  1. The wizard next displays the choice for a new connection. Click Next.
  2. This leads to the JDBC Connection Parameters page, as shown in Figure 4. Enter the following:
    1. JDBC Driver: IBM DB2 Universal
    2. Database: SDOJSP
    3. Host: localhost
    4. User ID and Password as needed
  3. Click Test Connection to make sure that the connection works.
Figure 4: Specify options in the New JDBC Connection wizard
specify manager, driver, and parameters
  1. Click Next.
  2. Click Finish. The database and all its schemas are displayed in the Record Properties page, as shown in Figure 5.
Figure 5: Select a table
list of tables in database
  1. Expand the relevant schemas and click the table in which you want the record to be updated. In this case it will be the schema GTANDON and table PERSONAL.
  2. Click Next. Because you are updating all of the columns in the table, you can leave everything selected, as shown in Figure 6.
  3. Click Finish.
Figure 6: Select the columns to include in your result
all columns in Personal table selected

After the insert, the JSP page should look like that shown in Figure 7.

Figure 7: Review the JSP in Design mode
design view shows code for input fields

Now you can test it on the server by actually inserting a record into the database using the form placed on the JSP page.

  1. Right-click SdoDem.jsp and select Run As > Run on Server.
  2. Pick an appropriate server and the Web browser launches, displaying the final JSP page, as shown in Figure 8.
  3. Enter valid values and click Submit. In this example, the submit page is redirected to another JSP that displays the results of the updated table.

The next section describes how you can display this updated data on a JSP page.

Figure 8: Review the rendered JSP page
finished UI with input fields and buttons

Retrieve a record from the database using an existing connection

In order to display employee personal information from the PERSONAL table in the database, you will create a new page called DisplayPersonal.jsp, using the same page template.

  1. Click Data from the Pallette on the right and drag Relational Record onto the page. At this point, you have already created a connection to your database using the JDBC display parameters, so the Relational Record page is displayed.
  2. Enter e_personal as the Name, and then select Displaying an existing record (read-only), as shown in Figure 9.
  3. Click Next.
Figure 9: Configure the Relational Record for displaying data on a JSP page
pallette
  1. On the Record Properties page, expand the GTANDON schema and highlight the PERSONAL table.
  2. Click Next.
  3. On the Add Relational Record page, make sure that all of the columns are selected, because you want to display them all.
  4. Click Next.
  5. Click Finish on the final page. The result should look like that shown in Figure 10.
Figure 10: In Design mode, review the JSP page as configured to display data retrieved from the database
design view shows variables in code
  1. If you click the Source tab, the code sections for the JSP and the SDO entries have been added by Rational Application Developer. The SDO-related sections are shown in bold below the <meta name="GENERATOR"> and <tr-class="content area"> tags in Listing 1, following.
Listing 1. Source code generated by Rational Application Developer

<%@page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@taglib uri="http://www.ibm.com/websphere/sdo/core" prefix="wdo"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%-- tpl:insert page="/theme/A_blue.htpl" --%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <meta http-equiv="Content-Style-Type" content="text/css"> <link rel="stylesheet" href="/SdoDemo/theme/blue.css" type="text/css"> <%-- tpl:put name="headarea" --%> <title>sdoDemo</title> <meta name="GENERATOR" content="Rational Application Developer"> <%-- wdo:useDataObject id="e_personal" mediator="e_personalMediator" input="/WEB-INF/wdo/e_personal.xml" datatype="RDB" action="FILL" --%><%-- /wdo:useDataObject --%> <wdo:useMediator id="e_personalMediator" input="/WEB-INF/wdo/e_personal.xml" connection="SDOJSP" datatype="RDB"> <wdo:setFilterParameter id="e_personalMediator" name="paramID" value="${param.ID}" /> </wdo:useMediator> <wdo:find mediator="${e_personalMediator}" id="e_personal"></wdo:find> <%-- wdo:useDataList id="disPerson" mediator="disPersonMediator" input="/WEB-INF/wdo/disPerson.xml" datatype="RDB" action="FILL" --%> <%-- /wdo:useDataList --%> <wdo:useMediator id="disPersonMediator" input="/WEB-INF/wdo/disPerson.xml" connection="SDOJSP" datatype="RDB"></wdo:useMediator> <wdo:execute mediator="${disPersonMediator}" id="disPerson" /> <%-- /tpl:put --%> </head> <body> <table width="760" cellspacing="0" cellpadding="0" border="0"> <tbody> <tr> <td valign="top"> <table class="header" cellspacing="0" cellpadding="0" border="0" width="100%"> <tbody> <tr> <td width="150"><img border="0" width="150" height="55" alt="Company's LOGO" src="/SdoDemo/theme/logo_blue.gif"></td> <td></td> </tr> </tbody> </table> </td> </tr> <tr> <td valign="top" class="nav_head" height="20"></td> </tr> <tr class="content-area"> <td valign="top" height="350"><%-- tpl:put name="bodyarea" --%> <br> <b> <font size="+2"><br>EMPLOYEE PROFESSIONAL DATABASE </font><br></b> <br>Employee Information:<br> <br><table cellspacinjg="0" cellpadding="10" border="1" width="100%"> <thead> <tr> <th bgcolor="#d0e8f0" align="left">Id</th> <th bgcolor="#d0e8f0" align="left">Firstname</th> <th bgcolor="#d0e8f0" align="left">Lastname</th> </tr> </thead> <tbody> <c:forEach var="varDisPerson" items="${disPerson}"> <tr><td bgcolor="#d0e8f0"><c:out value="${varDisPerson.ID}" /></td> <td bgcolor="#d0e8f0"><c:out value="${varDisPerson.FIRSTNAME}" /></td> <td bgcolor="#d0e8f0"><c:out value="${varDisPerson.LASTNAME}" /></td> </tr> </c:forEach> </tbody> </table><form action="sdoDemo.jsp" method="post"></form><br> <%-- /tpl:put --%></td> </tr> <tr> <td valign="top" height="20" class="footer"></td> </tr> </tbody> </table> </body> </html> <%-- /tpl:insert --%>

Notice that the <c:out value="${varDisPerson.LASTNAME}" /> code is what actually displays a record in the database. The mediator section on the top creates a query XML file called e_personal.xml in the wdo directory located in the WEB-INF folder of the Web project. When the JSP page runs, the <wdo> tags execute database requests, and the data is displayed in HTML .The wdo file creates the actual query in XML format.

The SDO code translates this XML into a format that the database can understand and, using the connection parameters supplied by the Web project, it retrieves the required information to display it on the JSP page. The wdo/e_personal.xml file created by the SDO code is shown in Listing 2.

Listing 2. The query XML file code

<?xml version="1.0" encoding="UTF-8"?> <com.ibm.websphere.sdo.mediator.jdbc.metadata:Metadata xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:com.ibm.websphere.sdo.mediator.jdbc.metadata= "http:///com/ibm/websphere/sdo/mediator/jdbc/metadata.ecore" rootTable="//@tables.0"> <tables schemaName="GTANDON" name="PERSONAL"> <primaryKey columns="//@tables.0/@columns.0"/> <columns name="ID"/> <columns name="FIRSTNAME" type="4"/> <columns name="LASTNAME" type="4" nullable="true"/> </tables> </com.ibm.websphere.sdo.mediator.jdbc.metadata:Metadata>

Now you can test it on the server using the same method as in the previous section. All records in the database are retrieved at runtime and displayed on the JSP page without requiring you to use any programming paradigms (such as JDBC or JavaBeans). The result should look like that shown in Figure 11.

Figure 11: Review the final rendered output of the JSP page
screen shows table with three records

Retrieve records from multiple tables using an existing connection

In this section, you will display records from multiple tables using the SDO wizard (in other words, perform a traditional JOIN with it). Start by creating a new page called DisplayProfessional.jsp, using the same page template.

  1. Click Data from the Pallette on the right and drag Relational Record List onto the JSP page. You have already created a connection to your database using the JDBC display parameters, so the Relational Record List page is diplayed.
  2. Enter e_professional as the Name, as shown in Figure 12, and then click Next.
Figure 12: Enter a reference name for the record list
the Add data controls option is selected
  1. On the Record List Properties page, click the GTANDON schema name and select the PERSONAL table. You will start with the PERSONAL table and use a JOIN operation to get the professional information from the PROFESSION table.
  2. Click Next.
  3. In the Add Relational Record wizard, click the Add another database table through a relationship link under Advanced tasks, as shown in Figure 13.
Figure 13: Select the columns to include in your result
specified link is highlighted
  1. On the Choose Your Relationship page, select ID <-->ID (as shown in Figure 14), because you will use the IDs to connect the tables together.
  2. Click Next.
Figure 14: Select an existing relationship or create a new one
list of existing relationships to choose from
  1. On the Edit Your Relationship page, select 1->*PRIMARY KEY -> FOREIGN KEY as the Multiplicity for this example, as shown in Figure 15. The other options provided are:
    • PRIMARY KEY->FOREIGN KEY
    • FOREIGN KEY->PRIMARY KEY
    • PRIMARY KEY->PRIMARY KEY
  2. Click Finish to exit the join wizard, which will bring you back to the Relational Record page.
  3. Click Next.
Figure 15: Configure the JOIN relationship
dialog shows Key column mappings and Relationship
  1. The Datagrid section now shows columns from both of the tables that you have joined, as shown in Figure 16. Select all of the necessary columns.
  2. In this case, leave the section unchanged and click Finish.
Figure 16: Specify the columns to display and how to display them
all of the available columns are selected
  1. The final table will appear as shown in Figure 17, with entries from both the tables joined to produce one single seamless table.
Figure 17. In Design mode, review the final page that you created by joining two tables together
screen shows columns from both tables

You have completed the major components of this example. This technology is extremely exciting, because it:

  • Is simple and easy to use
  • Does not require you to have any specific database, SQL, or JDBC knowledge in order to perform even complex queries and joins
  • Provides for dynamic queries, sorts, and updates

Sorting and filtering

It is also possible for you to sort and filter the results using the wizard. This article will demonstrate this using the newly created e_professional relational record from the previous section.

  1. Using the Page Data view on the bottom left of the screen, open the Relational Records and right-click e_professional(RECORD).
  2. Click Configure to open the Configure Relational Record wizard.
  3. Click Next twice.
  4. This will get you to the Add Relational Record page. On this page, the Tasks menu on the right provides various options for modifying the query results.
  5. Click Filter results.
Figure 18: Select from task options including Modify primary key, Order results, and Filter results
red box around Tasks
  1. You are now on the Filters page, which lists all of the tables that are displayed. You can highlight any table and then click the plus sign to create a filter condition.

  2. On the Conditions page, you can pick a column on the left and an expression value on the right.

  3. The Value text box can take a Constant, as shown in Figure 19, or a Variable (such as $value).

Figure 19: Set a condition
dialog shows condition selected By columns
  1. On the Filters page, the condition that you created will show up as shown in Figure 20. You can further enhance this by adding more conditions, or by creating a different condition by using OR instead of AND.
Figure 20: Select Filter parameters
Tables in top section and Conditions in bottom

Extending beyond the wizard

This technology is very flexible in terms of further user interface extensions. This article has really only outlined the very basic interactions that you can accomplish using the wizard. There are many ways in which this technology can be extended by customizing the XML code.

In the example shown in Listing 3, the <c: if > tag set is used to perform an if statement, and then pass the result retrieved from the database as a parameter to the Onclick Java™Script construct, amalgamating three technologies in the process.

Listing 3. Modify the XML code
<c:if test="${uid == us_id}">
<c:forEach var="varProjdet" items="${projdet}">
<c:url var="project_detail" value="/AddTask">
<c:param name="projectName" value="${varProjdet.NAME}" />
<c:param name="projectId" value="${varProjdet.ID}" />
<c:param name="projectPhase" value="${varProjdet.STATUS}" />
</c:url>
<span class="button-blue">
<input type="submit" 
onClick="parent.location='<c:out value="${project_detail}"/>'" value="Add Task" />
</span>
</c:forEach>
</c:if>

What you have learned

Service Data Objects were created to provide a unified framework for data development. If you use this framework and the existing wizards in Rational Application Developer, it is possible for you to create complex queries in JSP pages effortlessly and easily. This article has explored some common database functions such as updating a database, displaying query results, joining two tables together, and filtering the results using various filter conditions.


Acknowledgements

The author wishes to acknowledge Tom Hill and Kevin Brown for their valuable comments and contributions to the article.

Resources

Learn

Get products and technologies

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=299343
ArticleTitle=Using Service Data Objects in JavaServer Pages with Rational Application Developer 7.0 and later
publish-date=04082008