Introduction to SDOs and the JDBC Data Mediator Service

The introduction of the J2EE programming model marked a dramatic shift in the manner in which applications are developed and delivered. Despite the ongoing evolution of the J2EE programming model, one of the major shortcomings of the model is that clients need to understand the different representation of business data objects and their associated APIs, such as the following:

• Value objects
• Enterprise JavaBeans
• JDBC result sets
• JAX/RPC Web services binding classes

In an effort to simplify the J2EE data programming model, IBM and BEA teamed together to develop the concept of Service Data Objects (SDOs). SDOs provide a framework for developers to uniformly access and manipulate data from a variety of data sources without worrying about the technology-specific APIs typically associated with the data source.

This two-part paper examines usage patterns and scenarios where SDOs are most appropriate, and provides a step-by-step example using SDO with the Java DataBase Connectivity (JDBC) Data Mediator Service (DMS) that ships with WebSphere Application Server V6.0. The JDBC DMS lets you retrieve and manipulate data from a back-end JDBC data source without having to write the code needed to transform data from an SQL result set into usable business objects. This two-part paper will then suggest additional tips and techniques for efficient SDO and JDBC DMS usage.

Using a sample checkout and reservation application model for lab computer systems, we start with the most simplistic code required to access application data from the database. We then iterate through a set of changes to improve this implementation. For each step, we closely examine the motivations behind the change, the pros and cons, and provide performance results to quantify the improvement. Specifically, we examine the following potential optimizations:

• Passive connection wrappers
• Statically defined JDBC DMS metadata
• Reduction of return data based on better definition of metadata (using External tables)
• User Supplied SQL
• Statically defined data models

Part 2 of this paper concludes with a summary of the recommended best practices, performance gains, and trade-offs for you to apply to your SDO application.

Back to top

When do SDOs make sense?

Unlike the established pessimistic entity bean technologies which hold locks in the database for data currently being used in a J2EE transaction (referred to as connected), SDO promotes a disconnected and optimistic programming model. Optimistic programming models are best suited for read only data or data that is retrieved, manipulated, and later pushed back to the data source under low data concurrency. Applications that follow this data access pattern are well-suited for SDO. Most Web-based applications use this type of disconnected data model, but does that mean that every application for the Web should now use SDO?

Not necessarily. Some Web applications still require a tight coupling to the data that only connected programming models like EJB can provide. Even though the JDBC DMS provides for data updates and an optimistic concurrency control (OCC) mechanism for update collisions, SDO may not be suited for all Web applications. SDO provides the ability to track changes to data objects using a ChangeSummary, and the JDBC Data Mediator Service allows the specification of an OCC column to manage data collisions on updates. This is sufficient to handle a relatively small number of updates and a low level of concurrency. However, the disconnected nature of SDO is not well-suited for applications requiring large numbers of updates or higher levels of concurrency.

Back to top

Business data model (Hardware Reservation application)

For this paper, we chose to model a simple Web-based Hardware Reservation system that might be used to keep track of who has reserved a particular computer system in a shared environment. This model is simple enough for demonstration purposes and provides enough flexibility to highlight a number of usage scenarios and effective coding techniques for SDO and the JDBC DMS. Furthermore, this application is mostly read-oriented in nature and lends itself easily to a SDO implementation.

The data model for the Hardware Reservation system is made up of three primary objects: A user (UserAccount), a hardware system (HWSystem), and a check-out record (CheckOutRecord). A UML diagram of this data model is provided below:

As you can see from the data model, the UserAccount object is responsible for maintaining account information for users in the Hardware Reservation system. The HWSystem object represents a computer system that a user can reserve based on the availability of the system. The CheckOutRecord object links a particular user to a reserved system using foreign keys in the CheckOutRecord table that backs this model. This model not only provides an effective means of reserving computer systems, but the model also provides the ability to maintain a checkout history for each user and computer system.

Back to top

JDBC DMS metadata and basic queries

All J2EE persistence technologies provide some mechanism to map business data objects back to a persistent data source. With Container-Managed Persistence Entity EJB technology, the mapping between CMP fields and columns in a database table is managed by code generated at deployment time, and the queries for finder and select methods are defined using EJB Query Language (EJBQL). Applications with direct JDBC access use SQL to define the queries and rely on custom code to map the result set data back to the actual business objects.

The JDBC Data Mediator Service provides a metadata API to specify this object-to-relational mapping. This metadata API not only defines the shape of the data in the relational data store, but also defines the queries associated with that data and establishes relationships between different objects. For instance, take the HWSystem object defined in the previous section. The code to define the metadata of this object to the JDBC DMS would look like the following:

...
MetadataFactory mFactory = MetadataFactory.eINSTANCE;
Metadata hwsystem = mFactory.createMetadata();
Table table = hwsystem.addTable("HWSYSTEM");
		
Column id = table.addIntegerColumn("ID");
table.addStringColumn("HOSTNAME");
table.addStringColumn("IPADDRESS");
table.addStringColumn("MODEL");
table.addStringColumn("NOTES");
table.addStringColumn("CPUTYPE");
table.addIntegerColumn("CPUSPEED");
table.addIntegerColumn("NUMCPUS");
table.addIntegerColumn("MEMORY");
table.addBooleanColumn("AVAILABLE");
		
id.setNullable(false);
table.setPrimaryKey(id);

hwsystem.setRootTable(table);
...

The metadata API defines the shape of the HWSystem data object using the exact database table and column names. The metadata API also provides the ability to define nullable columns, establish primary keys, and set-up relationships between tables.

By defining the shape of the database to the JDBC DMS, we have all the information we need to retrieve HWSystem data objects from the database. Based on the metadata defined above, the following code sample obtains a connection to the database through a ConnectionWrapper, constructs an instance of the JDBC DMS, and performs a default find all query against the database. Please note that the try/catch blocks have been removed to simplify the sample code.

// Establish connection and connection wrapper
DataSource datasource = (DataSource) initialContext.lookup(...);
ConnectionWrapperFactory factory = ConnectionWrapperFactory.soleInstance;
Connection conn = datasource.getConnection();
conn.setAutoCommit(false);
ConnectionWrapper wrapper = factory.createConnectionWrapper(conn);
...

// Create the mediator and perform the query based on the 
// hwsystem metadata defined above.
JDBCMediatorFactory mFactory = JDBCMediatorFactory.soleInstance;
JDBCMediator med = mFactory.createMediator(hwsystem, wrapper);
		
DataObject system = med.getGraph();

...
// Close the connection
wrapper.getConnection().close();

To perform more complex queries against the database, you must define filters against the metadata. The following code creates a find by hostname filter on the HWSystem metadata that will return an HWSystem data object with the desired hostname, if one is found.

// Building upon Code Sample 1
...		
Filter filter = mFactory().createFilter();
filter.setPredicate("HOSTNAME = ?");
		
FilterArgument arg = mFactory().createFilterArgument();
arg.setName("HOSTNAME");
arg.setType(Column.STRING);
		
filter.getFilterArguments().add(arg);
table.setFilter(filter);
...

Assuming this filter is applied to the metadata for HWSystem that was defined in Listing 1, the code needed to perform this find by hostname query using the mediator would look like the following:

// Establish connection and connection wrapper as in Code Sample 2
...

JDBCMediatorFactory mFactory = JDBCMediatorFactory.soleInstance;
JDBCMediator med = mFactory.createMediator(hwsystem, wrapper);

DataObject args = med.getParameterDataObject();
args.setString("HOSTNAME", hostname);
		
DataObject system = med.getGraph(args);

...
// Close the connection

The only difference between this and the code used to perform the find all query is that a parameter data object is now created and used to pass the hostname argument to the mediator on the getGraph() call.

Back to top

Relationships and complex queries

Just as EJB supports the notion of Container-Managed Relationships to navigate primary and foreign key relationships in the database, the JDBC DMS metadata also lets you define relationships between SDOs based on the database structure. Refer back to Figure 1 for our Hardware Reservation system, and you will notice the relationship between UserAccount and CheckOutRecord. From the relational database perspective, this relationship is supported by a foreign key field (USERACCOUNT_ID) in the CheckOutRecord table that provides a link to the primary key of a particular UserAccount. When translating this relationship back to the JDBC DMS metadata, you must define both tables before establishing the relationship.

...
MetadataFactory mFactory = MetadataFactory.eINSTANCE;
Metadata userWithRecord = mFactory.createMetadata();

// Define UserAccount table
Table userTable = userWithRecord.addTable("USERACCOUNT");
		
Column userID = userTable.addIntegerColumn("ID");
userTable.addStringColumn("SHORTNAME");
userTable.addStringColumn("FULLNAME");
userTable.addStringColumn("EMAIL");
userTable.addStringColumn("PASSWORD");
	
userId.setNullable(false);
userTable.setPrimaryKey(userID);

// Define CheckOutRecord table on the same set of metadata. Both tables
// are needed in the metadata to establish the relationship between
// the PrimaryKey of the UserAccount table and the associated ForeignKey
// in the CheckOutRecord table.
Table recTable = userWithRecord.addTable(CHECKOUTRECORD);
		
Column recID = recTable.addIntegerColumn("ID");
recTable.addStringColumn("USAGE");
recTable.addTimestampColumn("CHECKIN");
recTable.addTimestampColumn("CHECKOUT");
recTable.addIntegerColumn("SYSTEM_ID");
Column userFK = recTable.addIntegerColumn("USERACCOUNT_ID");
		
recId.setNullable(false);
recTable.setPrimaryKey(recID);

// Set the UserAccount table as the root table of the datagraph
userWithRecord.setRootTable(userTable);

// Define the relationship
Key record_userAccountFK = record.addForeignKey(userFK);
userWithRecord.addRelationship(userTable.getPrimaryKey(), 
record_userAccountFK);
...

With this relationship in place, the resulting data graph from a getGraph() call will now consist of two separate listings of data objects with references linking the UserAccount data objects directly to their corresponding CheckOutRecord data objects (and vice versa). Based on this metadata model, we can now perform a query that would return all of the users that are currently holding systems. We can do this by defining a filter on the metadata to search for checkout records with a CHECKIN value of null. Since the data objects are linked through the relationship, both the checkout records and associated user accounts will be returned for all users that are currently holding systems. By now, you should be fairly comfortable with the JDBC Data Mediator Service metadata and how it is used to shape the resulting SDO data graph.

Back to top

Sample queries for the Hardware Reservation application

Now that we have covered how to define simple and complex queries for your SDO application using the JDBC DMS metadata, we will explore mediator API coding techniques and other optimizations that can be used to improve the performance of this application.

To demonstrate these techniques and the associated performance gains, we will continue to build upon the Hardware Reservation system data model. Three of the queries we focus on have already been described in the previous metadata discussions:

1. allSystems (Listings 1 and 2)
2. systemByHostname (Listings 3 and 4)
3. usersHoldingSystems (Listing 5)

In addition to these queries, we also take a look at two other queries: recordsByShortname and systemsCheckedOutByUser. A quick description of the pseudo-metadata code and the resulting data graph structure is provided below.

Back to top

Performance testing methodology

To quantify the performance improvements associated with the optimizations discussed in the upcoming Part 2 of this paper, service methods were written to call the specific test scenarios used in each test. The service methods were wrapped by a call to the System.getCurrentTimeMillis() function to obtain the start and end execution times of a single iteration. This execution block was then placed within a for loop and the sum of the execution times was recorded as the total execution time for a given test scenario. In order to show performance differences between different scenarios, the number of iterations was adjusted and should be noted in each scenario’s graph.

In some cases, the operations performed by the service method were limited to a single operation to demonstrate a specific point. However, most of the test scenarios have a broader scope, focusing on the entire sequence of operations needed to generate SDOs using the JDBC Data Mediator Service. Therefore, the service method performs all operations required to obtain a connection to the database, construct a mediator instance based on the metadata, generate randomly selected argument (if necessary), perform the mediator getGraph() call, and close the database connection.

While reviewing the performance data presented in the upcoming Part 2 of this paper, please keep in mind that the measurements are based on the total execution time, not throughput. Therefore, lower numbers equate to better performance in graphs showing quantitative comparisons. This is in contrast to throughput performance graphs where higher numbers would indicate improved performance. This subtle difference is important to remember throughout the remainder of this discussion.

Back to top

Conclusion

At this point you should have a basic understanding of Service Data Objects and the JDBC DMS that WebSphere Application Server V6.x provides and where these technologies fit into the growing number of persistent data object technologies like Enterprise JavaBeans. These technologies are not the best architectural choice for every application, so take care when making the decision to use SDOs and the JDBC Data Mediator Service in your application.

Thus far, we discussed how to write your JDBC Data Mediator Service code to perform some basic select queries and more advanced queries involving filters and multiple tables against a database. In Part 2, we will focus on specific coding techniques and best practices that you can use to optimize the performance of your JDBC DMS and metadata code. We will not only apply these techniques and best practices to the five transactional queries that were defined here for our sample hardware reservation system, but also analyze the gains associated with each based on the performance test methodology that was just discussed. With this information already on the table, we can dive right into a full discussion of these techniques in the next paper.

Resources

Learn

• Introduction to Service Data Objects: Learn about the next-generation data programming with SDOs (developerWorks, September 2004).
  
  
• Best performance practices for Service Data Objects and the JDBC Data Mediator Service, Part 2: Learn how to optimize your SDO and JDBC DMS application for performace (developerWorks, October 2005).
  
  
• An Introduction to Service Data Objects: Learn about integrating relational data into Web applications (SYS-CON Media, October 2004).
  
  
• Service Data Objects, WorkManager, and Timers: Read the specifications for programming models and APIs for Java 2 Enterprise Edition (J2EE) application servers that provide programmers with simpler and more powerful ways of building portable server applications (June, 2005).
  
  
• JSR 235: Service Data Objects: Get more information about SDOs at the Java Community Process Web site.
  
  
• Service Data Objects (SDO): Learn more about SDOs at the eclipse Web site.
  
  

Get products and technologies

• Get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®. You can download evaluation versions of the products at no charge, or select the Linux® or Windows® version of developerWorks' Software Evaluation Kit.
  
  

Discuss

• developerWorks blogs: Get involved in the developerWorks community.
  
  

About the authors

Christopher Blythe joined the WebSphere Application Server Performance and Benchmarking organization in Research Triangle Park, North Carolina after graduating from North Carolina State University with a Master's degree in computer engineering in 2001. In addition to his work on Service Data Objects, Christopher has also been highly involved with the SPECjAppServer and Trade performance benchmarks.

Andrew Spyker is a team lead of the WebSphere Application Server Performance and Benchmarking organization within the WebSphere development organization. His current focus area is on programming models including J2EE and SDO, Web services including WS-Security and other evolving Web services standards, and how these areas fit into a Service-Oriented Architecture. Given this SOA focus, he also works to understand key areas of performance as it relates to all WebSphere products including, but not limited to, WebSphere Business Integration Server and WebSphere Portal Server. His performance work includes enabling more performant and scalable application server features and helping customers architect their current and future applications for best performance. Andrew has two and a half years of performance experience and over seven years of Java programming experience. He received his BS in Computer Engineer from Pennsylvania State University.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction to SDOs and the JDBC Data Mediator Service
• When do SDOs make sense?
• Business data model (Hardware Reservation application)
• JDBC DMS metadata and basic queries
• Relationships and complex queries
• Sample queries for the Hardware Reservation application
• Performance testing methodology
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: WebSphere Application Server
• Webcast: Using Rational Application Developer with WebSphere Application Server to increase productivity
• Briefing: Building SOA solutions and managing the service lifecycle
• Buy: WebSphere Application Server

My developerWorks community

Dig deeper into Java on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers