by Lucas Jellema
Published December 2011
A common challenge in today's world of integration within and across organizations is the publication of web services to expose information to external consumers, often along with operations to submit and manipulate information. The information is typically and classically stored in an Oracle Database. This database is already accessed by Forms applications, batch programs, perhaps some .NET or Java web applications. However, the exposure of web services is a new ball game for the DBA, the developers, the architect, and anyone else involved.
This article describes the steps and considerations a real organization went through when faced with the challenge of making data services available. Their starting point—and ours in this article—was their existing database and the data it contained, their current skill set and the requirements for a number of web services. They established the need for implementation of the Enterprise Service Bus design pattern, explored possible implementations of that pattern and chose a straightforward implementation with minimal needs in terms of new development skills and software licenses. We will follow their thought process, options and choices.
A simple case, the Jukebox, is used to illustrate the way this organization selected for exposing the database through web services. You can download the sources for this case and try them out for yourself. Note: the infrastructure required consists of an Oracle Database (10g XE will do) and Oracle JDeveloper 11g with integrated Oracle WebLogic Server.
This article will refer to the organization in question as "Stuff, Inc." The challenge the organization faced is simple: external parties requested access to information through web services. This information is held in the enterprise (Oracle) database at Stuff, Inc.
The organization had no prior experience with web services. They ran a few simple web applications that accessed the database. These applications were developed using various open source Java frameworks, and were deployed on the open source GlassFish application server. Open source is a preference for Stuff's management—though not a dogma—as demonstrated by their long running investments in Oracle Forms and Reports. The Stuff development organization is highly skilled in SQL, PL/SQL and Forms and also has some Java programmers.
The project manager, the enterprise architect and a senior developer sat down to discuss the approach for extending the database with the web services front. Their discussions initially focused on architecture rather than implementation. They adopted the Enterprise Service Bus architecture pattern and applied it to the situation of Stuff, Inc.
This ESB pattern is defined in Wikipedia as "a software architecture model used for designing and implementing the interaction and communication between mutually interacting software applications". In other words: the decoupling of interacting parties: invokers of the data services need not know about nor be dependent on the location of the database and the technical implementation of the services. This model is described in many different ways and is also implemented through a large number of software products. It helped Stuff's staff to organize their discussions, recognize the key elements they needed to design and implement, and also lead to consideration of a formal ESB product.
While literature suggests a vast number of functions to be allocated to an Enterprise Service Bus, the key roles identified at Stuff, Inc. for the ESB to fulfill are commonly identified as VETRO:
Other functions that the ESB tier could take on include security, caching, monitoring and SLA management, throttling and queuing/staging, split and merge (for parallel processing), adapting between synchronous and asynchronous, and bridging across various protocols and technologies.
Regardless of how they would actually implement the service layer, Stuff's team established that they wanted to implement the VETRO pattern. Any technical design and any selection of tooling and technology would have to provide support for these core functions. Additionally, it was very important to Stuff to come up with a design that allowed for flexibility, as it was already clear that these initial few data services would have to undergo considerable changes and would be complemented with additional services in the near future. It also seemed likely that some new external parties would join in, requiring their own flavor—similar yet different on the outside—of the initial set of services
The web service contract was been agreed upon with the external partners. This meant that the names of services and operations were established, along with the definition of the XML messages to be exchanged. In technical terms this meant the creation of the abstract WSDL (the service contract expressed in web service Definition Language) and the XSD (the XML structure described by a XML Schema Definition). The WSDL plus the XSD established the goals that Stuff's implementation team needed to work towards.
Given this definition of the planned facade (SOAP Messages with XML payload over the HTTP communication protocol) and the existing back end (the world of the Oracle Database with its PL/SQL and SQL APIs and data types) Stuff knew that the implementation of the ESB pattern would also have to bridge between these two worlds. Note that Stuff had decided early on that it would adopt the Java platform and a Java EE Application Server as its middleware. The web services would be exposed on this platform for the benefit of the external consumers.
Clearly, the bridge from the Java EE Application Server to Oracle Database was an important aspect in designing Stuff's service architecture. Before making any final decisions, it was quite important to analyze all the different ways available to connect to the database – and what these ways would entail on both the Application Server's side as well as the database end.
The figure below shows various ways to connect to the Oracle Database from a JEE application server. It discerns between communication over HTTP, via JDBC over TCP (to the database listener), and through alternative channels, including email and the file system as the intermediary. The illustration shows blue connections for communication in untyped, textual form—including XML, JSON and Comma Separated Values format—and red connections for strongly typed interaction.
A number of the interactions shown here depend on the Technology Adapters that are available in Oracle WebLogic Server, for example, when Oracle SOA Suite or Oracle Service Bus are installed. Stuff, Inc. had no plans to use either Oracle SOA Suite or Oracle Service Bus, and as a result would not have the adapters at its disposal. However, the team at Stuff knew that when the number of services, service calls. and service consumers grew, or the complexity of the services increased, they would very likely reconsider the use of those products. The ideal design would allow for easily inserting an ESB product at a later stage.
The development team at Stuff, Inc. is primarily skilled in Oracle database application development. The developers excel at SQL and PL/SQL, know the data model inside out, are highly committed to performance, data integrity and security. These skills and this knowledge and commitment provided the foundation for the web service implementation, and the architecture design team wanted to leverage them to the fullest. One of the rules at Stuff regarding the enterprise database is that no SQL is defined outside the database: all access is done through PL/SQL APIs that encapsulate the SQL. Thus the database developers ensure that no untested, unskilled queries can threaten performance or stand in the way of future data model optimizations. In the case of data manipulation, the PL/SQL API will be able to validate, enrich and route data passed in to the target tables it belongs in.
Stuff has adopted the use of abstract data types, aka user defined types, in its database development. It has recognized that the use of these types aids in the clean, elegant development of a data API, constructing and passing complex, nested data structures assembled from multiple tables using several queries. The types also help to decouple the API's operations and parameters from the underlying table structures, much more so than can be accomplished with, for example, ref cursors. Note that while it took some time for the PL/SQL developers to embrace the type based approach, they now use it with a vengeance.
Another guideline at Stuff is that the database should not be burdened with having to cater to the exact data structure requirements of every individual application or web service. Generic data services are offered in the PL/SQL API to all consumers – not fine tuned to the special needs of individual consumers. For the web service challenge under scrutiny, this meant that the database would provide the data required for the service – but probably not in exactly the right structure required according to the service contract. That was okay: transformation was one of the key roles identified for the ESB implementation.
A strong statement is that the number of server round trips to the database should be minimized. That means that if the data set required for an application or service is not available from a single function in the PL/SQL API, it is the custom at Stuff to extend the API with a new operation that is potentially a very thin wrapper around two or more existing operations that it combines together. The composition and decomposition is done in PL/SQL, allowing the consumer to get its business done through a single database call.
The architecture design team took all of the above into consideration when they sat down, and then stood up again and gathered round the white board to discuss the implementation of the ESB pattern.
The illustration above shows the result of their deliberations, with the database at the far right and the web service on the left. Decoupling, encapsulation, specialization, and flexibility drove the discussion, with the service contract on the one hand and the database and data model on the other setting the scene, along with the decision to go with a JEE application server and not with an ESB product.
The team concluded that the type based PL/SQL API was not readily accessible from Java code through JDBC. However, the essence of this API should not be undermined, especially given the fact that the database adapter that might be used at a later stage—after the introduction of Oracle SOA Suite or Oracle Service Bus—is very well suited to interact with a type based procedures. Instead, it was decided to have the type based API wrapped in a thin layer that converts between XMLType and the User Defined Types of the API. This conversion is generic and does not require type specific code.
Stuff's architecture team came up with the following design for the data web services:
The web service contract—WSDL and XSD—was used to generate a JAX-WS web service: a collection of Java Classes that together implement the contract. This is the front end of the service. It was deployed on the JEE application server and receives the service requests (SOAP messages with XML data payload) from the server. Using the JAXB binding infrastructure, it turns the XML payload into instances of generated classes derived from the XSD definition of the XML message structure. In doing this XML-to-OO conversion, a validation of the incoming message against the XSD can be performed.
Security policies can be configured, either in the Java Classes with JAX-WS annotations or in the application server during or after deployment. These policies govern requirements such as authentication, authorization, digital signing and encryption of messages.
The team decided to communicate with the database in terms of XML, using the XMLType of the database and having the XMLType generated from and to the User Defined Types used in the PL/SQL API of the database. The team concluded that mapping the XML structures defined in the external web service contract to the internal XML required for the PL/SQL API would be most easily done using XSLT – especially given that the same internal XML might have to be mapped to various distinct external XML formats for various service consumers. The team adopted a multi-stage approach in Java with four simple steps:
We will see elsewhere in this article how these same stages can be identified when an ESB product like Oracle SOA Suite or Oracle Service Bus is used, and how, in that case, far less programming is required, as the adapters and ESB infrastructure provide most functionality out of the box.
The implementation of a web service in the Stuff architecture described above starts with the service contract, which consists of a WSDL document that describes the interface (aka port type), operations and the messages (input, output and fault) combined with the XSD that describes the XML structure of the messages in detail. It also starts with the tables in the database and the PL/SQL API at the other extreme.
Let's take a look at an example: the Jukebox Service. This service exposes an operation, queryForCDs. This operation expects a CDQuery input element in the SOAP Request, and it will reply with a CDCollection element in the SOAP Response. The service is described by the contract laid down in JukeboxService.wsdl and the associated MusicalTypes.xsd.
The XML structure in the input and output elements is defined in the XSD document MusicalTypes.xsd. The parts for the CDQueryRequest input and CDQueryResponse output messages are described through the CDQuery and CDCollection elements in this XML Schema Definition.
At the opposite end of the web service is the database, with several relevant tables:
The PL/SQL API pertaining to the JukeboxService project is described through a package specification and a number of user defined types:
The following four steps constitute the road to the implementation of the JukeboxService
Let's walk down the road of implementation.
Use the Wizard in JDeveloper to generate a Java web service implementation, based on the WSDL and XSD that define the web service's contract.
After running the wizard and completing the generation, we are left with a number of generated classes – and a clear hook where we have to provide the actual implementation of the web service's functionality.
At this point, we will add a line to invoke the Coordinator class, to be developed in step four.
The image below shows where we are at this point in our implementation:
The Java front-end for the SOAP web service is in place. But it has nowhere yet to go and does nothing much except unmarshall the XML request into an OO structure. Note: we could now deploy this web service and even invoke it. However, the response would always be empty because the generated method, queryForCDs (the implementation of the web service's operation of that name), always returns null.
The implementation of the overloaded search_for_cds procedure with XMLType parameters is very simple and straightforward:
The XMLType input parameter p_cd_query is converted to its type-equivalent using the toobject operation on XMLType. Provided p_cd_query's XML structure is exactly what is required to make this conversion, the result of toobject will be an instance of cd_query_t. Note: It's easy to find out what that XML structure should be by performing the reverse operation on an instance of cd_query_t:
The resulting cd_collection_type is turned into an XMLType instance using the XMLType constructor acting on a single object type. Because an XMLType cannot be instantiated based on a TABLE OF <Type>, the cd_collection_type is wrapped in a jukebox_t. The XML structure returned by the search_for_cds procedure looks like this:
Note: using XMLType in the Java Client can be a little bit tricky as it requires the availability of specific Oracle JDBC and XDB libraries, ojdbc6.jar and xdb.jar, in the application server. For a more straightforward and less cumbersome implementation, it is probably easier for a prototype to communicate through String variables between Java and PL/SQL API; these Strings are the 'serialization' of the XMLType and contain the full XML Document. This approach means introducing another, even thinner wrapper around the PL/SQL API, one that converts the XMLType to its String representation and vice versa:
The Router Class has a straightforward role in the web service implementation: it invokes the PL/SQL API with an XML document in the structure dictated by the API and receives a response, also in XML format and also according to the structure dictated by the API, as illustrated above.
The key method in the Router Class is callMusicAPI. This method is generic: it is agnostic with regard to the operation invoked in the Music API and the data passed to it or received from it. It simply accepts a (DOM XML) Document as input and return a Document as result. It also expects a string containing the exact call to be made to the Music API. The method uses a JDBC data connection to invoke the stored procedure, retrieved from a helper class ConnectionManager, not further specified here. The method also relies on two helper methods to turn the input Document into a String and to create a Document from the String returned from the MusicAPI.
The public method searchForCds is called by Java consumers that want to indirectly leverage the PL/SQL API to search for CDs. The Coordinator class, discussed in the fourth implementation step, will be among them.
Note that the Router class has to deal with a number of exceptions, some of which are related to the back end system (the PL/SQL API in the Oracle database) that none of the invokers should even be aware of, and that may be resolved by invoking a fall-back service or using an auxiliary cache. Others have to do with the document passed into this method.
The production ready version of class MusicRouter will need to determine how exceptions are dealt with and what, if anything, is reported back to consumers regarding such exceptions.
The Mediator has a simple, single responsibility: it needs to transform XML documents, from the external structure published as part of the JukeboxService contract to the internal XML structure derived from the User Defined Types of the PL/SQL API, and vice versa.
The Coordinator (see the next step) will invoke the Mediator to perform transformation of the request it receives and will use the outcome to procure a CD search result from the Router. That result is then channeled through the Mediator to transform into the desired external XML structure.
The MusicMediator Class itself has a generic method that takes and XML Document, returns one and also has the location of an XSL-T stylesheet resource for an input parameter. The class has two specialized methods for the two specific transformations it offers to the Coordinator; these two invoke this generic method, indicating specific stylesheets.
Obviously, the real logic for this Mediator is in the stylesheets themselves. There is the mapping between external and internal XML structures - there is the decoupling between the canonical language as published to the world and the internal language derived directly from the database API.
Finally, pulling it all together is the Coordinator.
The MusicCoordinator class provides the implementation for the web service. The generated class JukeboxQueryProcessorImpl, derived from the WSDL service contract, has one manually added line that contains the call to the MusicCoordinator.
The queryForCDs method in the MusicCoordinator class does the work to implement the queryForCDs operation defined in the web service contract. This method is invoked with an instance of CDQueryType, the class generated by the wizard and mapped through JAXB to the CDQueryType complex type in the XSD document.
The cdQuery object is marshalled into an XML Document. This document is handed to the MusicMediator for transformation to the internal XML structure for the cdQuery. The MusicRouter is then invoked to get hold of a Jukebox document: an XML document with CD details as retrieved from the PL/SQL API in the database. This document needs to be transformed, in the MusicMediator, into the XML structure defined by the CDCollectionType in the XSD for the web service. Finally, this document is unmarshalled into an instance of the CDColllectionType class and associated objects for CDs and tracks.
At this moment, the MusicCoordinator does nothing in terms of validation and enrichment – roles in the ESB architecture pattern that it can easily take on. After marshalling the CDQueryType for example, the Coordinator could perform strict XSD validation on the CDQuery request or use Schematron validation for even more sophisticated rules. And after invoking the Router, it could also invoke other Routers to provide additional pieces of data that could be added to the CDCollectionType to be returned to the invoker of the service.
Note: the marshalling of CDQueryType and unmarshalling of CDCollectionType requires the addition of the annotation @XmlRootElement in both these classes.
The marshall operation itself is done in this method:
Similarly, the unmarshall is performed in this method:
The proof of the pudding always is in the eating. For this web service implementation that means that it needs to be deployed to a JEE Application Server and subsequently be invoked to actually process a CD Query Request and return a CD Collection Response – powered by the Stuff Music Database.
While developing in JDeveloper, running the JukeBoxService web service is as easy as activating the Run option in the context menu on the JukeboxQueryProcessorService node in the application navigator:
The integrated WebLogic Server is started (if it is not already running) and the JukeboxService application is deployed to it.
You can now make test calls to the web service. The easiest way of doing so is by simply clicking on the URL shown in the console. This will bring up the HTTP Analyzer tool in JDeveloper, which allows you to make a SOAP call to the JukeboxService.
Alternatively, you can use a tool such as SoapUI to make calls to the web service, as illustrated below.
Because the implementation of the ESB architecture pattern we have created above does not include monitoring or logging, there is no way available to us to track the processing of the request inside the web service. We can see the response, the CD Collection, returned to us from the web service and can only assume that processing inside went well, as designed, and no bottlenecks occurred along the way. Providing insight in service request processing and tools to analyze performance, bottlenecks, and exceptions is typically a function of an ESB product.
Given the implementation of the Jukebox service outlined above, is it really as flexible as suggested? Is the encapsulation between the stages effective? Does the architecture allow for changes to the service?
Without even trying this out, it is obvious that changes in the database table structure are completely shielded from view: the Java implementation of the web service is in no way tied to the structure of the tables – or even their existence – so modifications to the tables are strictly local. The same applies to the PL/SQL code that retrieves the data based on the search parameters: as long as the definition of the input and output parameters is not changed, it is of no consequence to the Java implementation how the PL/SQL code does its job.
Let's take a look at several other modifications.
What if we want our service consumers to be able to also search for the name of a track on the CDs? We would have to modify the CDQueryType in the XSD that is part of the web service contract. The Java [implementation of the] web service would have to be regenerated, or at least class CDQueryType would need to be extended.
At the other end of the architecture, we would have to add an attribute for track title to the cd_query_t user defined type. Of course the query logic inside the PL/SQL API—procedure search_for_cds—would have to be modified to make use of this new attribute in the type.
Finally, the XSL-T stylesheet that transforms the CDQueryType document into the internal CDQuery message with the new element needs to be extended with the new search element, TrackTitle.
The CD records that are returned could be enhanced by including the name of the record company that released the CD or a thumbnail of the CD cover. This change would require a modification of the CD Type in the XSD document. Subsequently, either regenerate the web service, or manually add a property to the generated CD Class.
Depending on whether the value for this new element in the CD originates from the database or some newly introduced service, the following changes are needed:
The Jukebox Service could be extended with a new operation, such as queryForMovies, or getCDDetails, or addCD. A new operation is likely to require changes at every level, from the generated Java web service to the PL/SQL API that needs to provide part of the functionality of the new operation. Apart from the XSL-T stylesheets, no new objects need to be created, but several need to be extended.
First of all, the WSDL and probably the XSD need to be extended with descriptions of the new operation and its parameters. Next, the Java web service needs to be regenerated.
Optionally, depending on whether the PL/SQL API already includes a procedure with the underlying functionality of the new operation, add a PL/SQL procedure to PL/SQL API package, create user defined types for the input and output parameters for this procedure and then create wrappers that do the XMLType to User Defined Types mapping as well as the XMLType to String conversion.
Add a method to the Router class to invoke the new PL/SQL procedure that supports the new operation.
Next, the XSLTs need to be created for transforming the incoming request message to the internal XML structure that can be passed to the PL/SQL API, and for transforming the XML output to the public response message.
Finally, create a method in the Coordinator class with calls to the Mediator for transformation and a call to the Router class to invoke the required service on the PL/SQL API.
Add security: for example the implementation of the requirement that authentication and authorization should be applied to the Jukebox Service, as we cannot have just anyone invoking that server. Without changing a single line of code in the Jukebox web service application, it can be subjected to security policies. If the Jukebox Service is only to be made available to authenticated parties—with specific roles—we can attach security policies to it in the application server. Any invocation of the service is then authenticated by the application server. User identities and their associated roles are defined in whatever user directory the server uses.
The architecture thought up by the team at Stuff, Inc. works. It has proven itself through the implementation discussed in the example above. It provides flexibility, scalability, encapsulation, and decoupling and makes maximal use of available database development skills. However, it requires quite a bit of manual activity, and this architecture and tooling provide very little in terms of monitoring and administration.
When Stuff, Inc. learned that they would be rapidly expanding their web services portfolio, adding both services and service consumers, they reassessed the original implementation, and decided to bring in Oracle Service Bus. This decision was primarily inspired by a desire for higher productivity and less dependence on Java programming skills. Additionally, Stuff's administrators were a little overwhelmed by the operational responsibility for the web services they had been given. They were looking for tools to monitor service activity and help analyze issues.
It turned out that the architecture initially designed for the data services is still very much valid when the Oracle Service Bus is used. The VETRO-stages are still present, though implemented in a slightly different way. The proxy service, helped by the standard facilities in the OSB, does most of the validation, enrichment, transformation, and routing while the business service leverages the Database Adapter to invoke the PL/SQL API. Note that this adapter is quite capable to speaking in user defined types, which means that the XMLType wrapper in the database is no longer required. The PL/SQL API based on user defined types can be used as is.
The OSB proxy service and business service replace the coordinator, mediator, and router Java Classes of the earlier custom Java implementation. However, the XSL-T transformations used by the mediator can be reused in the proxy service in OSB. Coordination and routing is configured declaratively and performed by the OSB infrastructure.
The Routing Node makes the call to the Business Service - the JCA Database Adapter that invokes the PL/SQL API on behalf of the Proxy Service JukeboxServicePS. The Replace action in the Request pipeline transforms the CDQuery request into the XML Structure required by the Business Service. The Replace action in the Response pipeline (right side of the Routing node) transforms the response from the Business Service into the CDCollection structure defined in the WSDL and XSD contract that the Proxy Service should adhere to.
The overall implementation using the OSB can also be depicted like this:
The detailed steps for implementing the JukeboxService using Oracle Service Bus are described in an appendix to this article.
It was very satisfying for the team at Stuff, Inc. to see how the architecture they had designed was equally valid for a custom code implementation of the ESB pattern as it was for an implementation based on an ESB product such as Oracle Service Bus. For the developers, it was rewarding to see how the PL/SQL API can be leveraged with Oracle Service Bus, too, and how even the XSL-T stylesheets can, by and large, be reused.
Introduction of Oracle Service Bus took place about three months ago. Stuff's staff reports increased productivity from the service implementation teams and much more confidence and more efficient operations from the administrators.
Publishing services based on the enterprise database to internal and external consumers is a common requirement in many organizations. Various architecture designs and implementations of such services can be constructed. This article shows one organization's decision making process, technical design, and implementation of a data service. Note that this organization, referred to here as "Stuff, Inc.", represents a large number of organizations with similar needs that have benefited or may benefit from similar implementations.
Database development skills are very strongly represented in Stuff, Inc. A PL/SQL API is the foundation for many applications and service implementations, and the only way for such entities to interact with the database. No SQL maybe executed from outside that API. In order to minimize the number of server round trips, various composite operations are provided in the PL/SQL API, based internally on other, more fine grained procedures.
The Enterprise Service Bus design pattern was investigated and applied to the challenge at hand for the Stuff, Inc's architecture team. A custom Java approach was adopted that implements four stages: web service interface (generated using JAX-WS and JAXB), coordinator (for validation, enrichment and activating mediator and router), mediator (for transformation of XML messages) and router (for interacting with the PL/SQL API). Together these stages implement the VETRO pattern of ESB literature. Central in the design is the notion for strict decoupling, interface based interactions, encapsulation of logic and pervasive flexibility to meet changing requirements.
The article describes the implementation of the Jukebox Service according to this four stage approach, and demonstrates how various changing demands can be satisfied in this implementation. The article concludes with a description of what the introduction of the Oracle Service Bus entailed for the implementation of the concrete web service and for the overall architecture. Zn the end, the impact on the architecture and design was very small. The Oracle Service Bus infrastructure takes on a number functions otherwise implemented through custom code, and the usage of the database adapter dramatically reduced the complexity of interacting with the PL/SQL API.