Techniques for Robust, database-driven Web services: reuse of existing assets can create new interoperability

Techniques for Robust, database-driven Web services: reuse of existing assets can create new interoperability – Focus: tools and tricks

Steve Muench

Web services have now officially outlived the initial wave of hype, and many corporate developers are in some phase of designing, building, testing, or deploying their first mission-critical services. While Web services offer a new way to expose your application functionality to programmatic clients on different platforms, it’s no surprise that they are developed following the same process you use to deliver any high-quality software. This means the life cycle for developing Web services will start with good design and modeling, and include revision control, coding, debugging, and performance profiling, as well as both unit testing and system testing. Adopting a strong toolset that supports this complete Web services and application development life cycle will positively impact the success of your initial rollout and ongoing maintenance.

Since virtually all valuable Web services both access corporate data and enforce some form of business logic, any productivity gains you can realize during development for these common tasks will prove invaluable as well. This article explores two techniques for database-driven Web service development that I used on a recent project, a system to manage development-team responses to customer questions on the Oracle Technology Network ( online discussion forum. On this project, due to time pressure, wherever application functionality had already been written by other developers in a reusable way, I did my best to utilize it. In fact, I made quick work of some of my tasks by directly exposing their existing database stored procedures as Web services. For new functionality, I exploited a widely-used J2EE application framework to simplify my data access, XML message handling, and business logic enforcement instead of coding the “design patterns” to implement these aspects by hand. We will examine simplified versions of a few representative cases that I encountered in my application. This should make it easy to understand the basics of the approach I used.

Rolling Up Our Sleeves: Toolset or Tool Belt?

While you’re targeting the J2EE platform with your enterprise Web service development, it’s clear that you will exercise a healthy mix of J2EE technologies along the way. For example, at a minimum you’ll use JDBC APIs for data access, JAXP for XML processing, and JMS for working with message queues, and there’s a good chance you’ll incorporate some use of EJB for building transactional services that coordinate with other components and resources. I’ve found that J2EE developers building Web services typically fall into two camps: they either adopt an integrated development environment supporting the entire J2EE development life cycle, or they stitch together a patchwork quilt of favorite command-line tools and toolkits using popular build automation tools such as Apache Ant.

Both are valid approaches, to be sure, but the convenience offered by tools with everything under one roof is getting harder to ignore. Over the last year, the adoption rate of what industry analysts call the “Enterprise Development Studios” for J2EE development from companies like IBM, Borland, and Oracle has grown substantially. Since I’m not much of a command-line handyman, I used Oracle9i JDeveloper for my Web services project. Other IDEs may offer features similar to the ones I describe below, but I’ll detail what I’ve done with Oracle9i JDeveloper for my project as I’m most familiar with those capabilities.

Repurposing Existing Assets for Fun and Profit

In the online discussion forum management system I work on, we wanted to expose the ability for external customers to programmatically check on the discussion forum threads they’ve posted. After a little initial research, I found that other Oracle developers had already written something that would do most of the job. Their Forum_Info package of database stored procedures had a Forum_ Thread_Summary_Info function that accepts the ID number of a forum, and the ID number of a thread in that forum, and returns key summary information about the current state of the discussion thread. A simplified signature of their PL/SQL package looked like this:

/* Specification (Public Interface) for

Forum Info Package */


FUNCTION Forum_Thread_Summary_Info(

the_forum_id NUMBER,

the_thread_id NUMBER)

RETURN Forum_Thread;


These developers had leveraged the object features of the Oracle9i Database to create an object type named Forum_Thread to represent a single, named structure of information about a discussion forum thread. They created its definition using syntax like this:


forum_id NUMBER(8),

thread_id NUMBER(8),

title VARCHAR2(80),

author VARCHAR2(40),

last_reply DATE,

awaiting_oracle_reply VARCHAR2(1),

last_reply_from VARCHAR2(40),

total_replies NUMBER(4)


Out of curiosity, I asked them to mail me the code for the private implementation of their function so I could have a look, just to make sure their code was using the same interpretation of “awaiting Oracle reply” that I needed to support. Their code used a SELECT statement to retrieve the current state of the forum thread (see Listing 1; code for this article can be found online at services/sourcec.cfm).

The query does an outer-join between the Forum_Threads table and the Oracle_Team table so that they can detect whether the last person to reply to the discussion thread was an Oracle team member or a customer, and decode that fact into a letter Y or N, indicating whether the thread is still waiting on an Oracle team reply. Seeing this confirmed that it was just what I needed.

After creating a named connection to access their database, I used Oracle9i JDeveloper’s PL/SQL Web service wizard to expose the existing package as a Web service. This involved selecting the appropriate package name from a list and ticking the box next to the particular procedures and functions I wanted to be part of the external Web service interface. After finishing the wizard, the WSDL describing the interface for my Web service had been created for me automatically, and a deployment profile was set up so that deployment to my application server–in my case Oracle9i Application Server–took literally just another click.

Testing the New Web Service from a .NET Client

Since a key benefit of Web services is interoperability, I thought it would be a good idea to test my new J2EE Web service from a Microsoft .NET client tool. This would guarantee that customers wanting to programmatically check the status of their forum threads could really do it from any type of client. I pointed my browser to the correct service URL on the server hosting my new ForumInfo Web service and was greeted by a browser test page. After exercising the basic functionality from the browser, I copied the URL for the service description and pasted it into the Visual Studio .NET “Add Web Reference …” dialog. As it showed me the ForumInfo service’s WSDL contract, I noticed something I hadn’t paid attention to before: Oracle9i JDeveloper had automatically generated the XML Schema type description shown in Listing 2 for the ForumThread return type into the WSDL.

Visual Studio knew how to leverage this schema information, so after finishing the wizard I had two new C# language objects in my “Web References” folder: a ForumInfo Web service stub, and a ForumThread class that mimicked the structure of my service’s ForumThread return type. I cobbled together a simple C# program to verify that everything was working (see Listing 3). Running the program produced the expected output:

Title: BC4J deployment questions

Author: Peter Verhage

Waiting on Oracle?: Y

So for one aspect of my Web services application, I was already done. If your application server works similarly to Oracle9i Application Server, your database-driven Web services will exploit the J2EE container’s connection pooling facilities for scalability, and the PL/SQL stored procedure implementation leverages the robustness of the database–and saved me from reinventing the wheel.

Tackling the New Functionality

When I ran out of Web service functionality that I could implement “for free” by exposing existing stored procedures, it was time to design the services that I needed to implement from scratch. One of them was implementing a customer request to supply the complete details of a discussion forum thread as an XML document.

To support other new business functionality in the system, I had already used a combination of tools to design a set of Java business components to cleanly encapsulate the generic business logic and persistence implementation. Rather than coding these business domain classes by hand, I used the built-in J2EE framework called Business Components for Java (BC4J). It’s the same one that is used internally by over 800 developers in the Oracle E-Business Suite division, who use it to save time building Oracle’s own self-service Web application suite and Web services.

By using various wizards, editors, and UML modeling tools to derive my components from a handy business logic and data access framework, I avoided the pain of having to invent a custom J2EE application architecture on my own. In addition, I spared myself the chore of writing code by hand to implement the scores of J2EE design patterns that my analysis had warned I’d need. As even my simple example confirms, following a J2EE framework approach I was able to focus on writing business code that was relevant to satisfying my end-user functionality requirements instead of having to write low-level application plumbing code.

In addition to a set of “Entity Object” framework components comprising my shared business domain layer, I was able to leverage other BC4J components as well. Both for my Struts-based JSP Web user interface and for my new getForumThread AsXML() Web service, I created query components called “View Objects.” These conveniently handle all of the JDBC interaction for me and cleanly encapsulate all the database queries my application has to perform. I used the built-in “Explain Plan” button on the component editor to validate ahead of time that the queries would use all of the expected indices for best runtime performance. When creation, removal, or modification of rows of business data is required, the view object components delegate automatically to the underlying entity object components that enforce relevant business logic. To complete the job, I used BC4J “Application Module” components to encapsulate any service-related code in a convenient place and centralize the definition of the collections of business data needed by my client applications and Web services.

Figure 1 shows my ForumWebService project in Oracle9i JDeveloper, with the ForumService Web service in the UML modeler, along with the three key business domain components of interest to this service. To keep the diagram simple, I’ve hidden the methods on my domain components, though of course with a click I could show them again for my reference. In the System Navigator, you can see the forums package of shared business domain components (entity objects), and the package containing my data access components (view objects) and ForumService service component (application module).


The ThreadInfo component (SQL icon) contains the query to retrieve the information I want to expose to clients about forum threads, while the ThreadPosting component has the query for the “slice” of details I want to expose for the individual postings in a discussion thread. By creating the PostingDetailsFor Thread view link component, I define a master/ detail data relationship between the two view objects so that each value object “row” produced by the query result of the ThreadInfo view will automatically have a coordinated detail collection of value objects for the postings in that thread.

Since I was leveraging a J2EE framework, my code was limited to a few lines in two methods. First I wrote the following method in my ThreadInfo view object component to encapsulate the setting of its WHERE clause bind variables:


* Encapsulate bind variable setting

* inside ForumThread view object


public void findByThreadId(int

forumId,int threadId) {


Integer(forumId)); // set the


Integer(threadId)); // bind vars



Then I wrote the main getForumThread AsXML() service method in the ForumService application module class:


* Use an instance of the “ThreadInfo”

* view object to query the thread data,

* the related thread posting data (via

* the view link), and return a

* multi-level XML message to the client.

* /

public Element getForumThreadAsXML( int

forumid, int threadid) {

ThreadInfo info =



return (Element)



Then I just published the application module component as a Web service and deployed it to the target application server–in my case Oracle9i Application Server. To write out multilevel hierarchical XML messages, we use just one method, write XML(). It’s interesting to note as well that for other aspects of my application’s functionality, instead of using writeXML() I found the view object’s companion read XML() invaluable. It let me process incoming, multilevel XML messages using my shared business domain components with a single method call.


If you take home one message from this article, I hope it is: “Above all, don’t reinvent the wheel!” By using appropriate tools, it can be easy to repurpose existing assets like database stored procedures into Internet-ready Web services that interoperate with any kind of client.

For green field Web services development on the J2EE platform, rather than starting from scratch or risk making costly mistakes on our own, we saw that a J2EE framework tightly integrated with your Web services development tool can provide a proven application architecture out of the box, leaving only the “fun” code to write ourselves.

As I found in my project, you’ll likely end up using a combination of these time-saving techniques. Along the way, if you’re anything like me, you’ll thank yourself time and again for adopting your favorite full life cycle J2EE tool and a J2EE framework that let you finish faster and go home a little earlier every night!

Steve Muench is consulting product manager on the Business Components for Java Development Team at Oracle and their lead technical evangelist for XML. In his 13 years at Oracle, he’s been involved in the support, development, and evangelism of Oracle’s application development tools and database and a driving force in helping Oracle development teams from the database server, to application server, to tools, to packaged applications weave XML sensibly into their future development plans and to adopt a component-based development architecture for the future. Steve is the author of Building Oracle XML Applications (O’Reilly). STEVE.MUENCH@ORACLE.COM

COPYRIGHT 2003 Sys-Con Publications, Inc.

COPYRIGHT 2003 Gale Group