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.

Back to top

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.

Back to top

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.

5. On the SdoDem project, click Web contents.
6. Right-click and create a new folder called jsp
7. 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.

Back to top

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.

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.

3. Drag Relational Record into the JSP page.
4. 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.
5. Select Creating a new record, because you are inserting a record into the database. Click Next.

6. The wizard next displays the choice for a new connection. Click Next.
7. 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
8. Click Test Connection to make sure that the connection works.

9. Click Next.
10. Click Finish. The database and all its schemas are displayed in the Record Properties page, as shown in Figure 5.

11. 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.
12. Click Next. Because you are updating all of the columns in the table, you can leave everything selected, as shown in Figure 6.
13. Click Finish.

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

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

14. Right-click SdoDem.jsp and select Run As > Run on Server.
15. Pick an appropriate server and the Web browser launches, displaying the final JSP page, as shown in Figure 8.
16. 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.

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.

4. On the Record Properties page, expand the GTANDON schema and highlight the PERSONAL table.
5. Click Next.
6. On the Add Relational Record page, make sure that all of the columns are selected, because you want to display them all.
7. Click Next.
8. Click Finish on the final page. The result should look like that shown in Figure 10.

9. 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.

                
                

<%@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.

                
                

<?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.

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.

3. 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.
4. Click Next.
5. In the Add Relational Record wizard, click the Add another database table through a relationship link under Advanced tasks, as shown in Figure 13.

6. 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.
7. Click Next.

8. 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
9. Click Finish to exit the join wizard, which will bring you back to the Relational Record page.
10. Click Next.

11. 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.
12. In this case, leave the section unchanged and click Finish.

13. The final table will appear as shown in Figure 17, with entries from both the tables joined to produce one single seamless table.

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.

6. 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.

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

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

9. 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.

Back to top

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.

                

<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.

Back to top

Acknowledgements

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

Resources

Learn

• Visit the Rational software area on developerWorks for technical resources and best practices for Rational Software Delivery Platform products.
  
  
• Subscribe to the IBM developerWorks newsletter, a weekly update on the best of developerWorks tutorials, articles, downloads, community activities, webcasts and events.
  
  
• Subscribe to the developerWorks Rational zone newsletter. Keep up with developerWorks Rational content. Every other week, you'll receive updates on the latest technical resources and best practices for the Rational Software Delivery Platform.
  
  
• Subscribe to the Rational Edge newsletter for articles on the concepts behind effective software development.
  
  
• Take a Rational Software Architect Course to learn more about its capabilities.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  
• Read these developerWorks articles for more information:
  • Using Service Data Objects with Enterprise Information Integration technology
  • Introduction to Service Data Objects
  • JSP best practices: Combine JavaBeans components and JSP technology
  • Accelerated JSF development for XML-based SOA using Rational Application Developer and WebSphere Application Server -- Part 1
  • Accelerated JSF development for XML-based SOA using Rational Application Developer and WebSphere Application Server -- Part 2
  
  
• IBM Redbooks can also increase your understanding of this subject:
  • JavaServer Faces and Service Data Objects
  
  

Get products and technologies

• Download trial versions of IBM Rational software.
  
  
• Download these IBM product evaluation versions and get your hands on application development tools and middleware products from Rational®, DB2®, Lotus®, Tivoli®, and WebSphere®.
  
  

About the author

Geetika 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

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Overview
• Supporting technologies
• Initial setup
• Accessing information from the database using the SDO wizard
• Extending beyond the wizard
• What you have learned
• Acknowledgements
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Rational Application Developer for WebSphere Software
• Demo: Build, test and deploy JAX-WS Web services
• Webcast: Webcast: Develop innovative applications efficiently using the right application foundation in today's economy
• Briefing: Software development conference
• Buy: Rational Application Developer for WebSphere Software

My developerWorks community

Dig deeper into Rational on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers