Now that we have an application that can publish our order and quote data to a JDBC database, let's explore different ways of building the client (or subscriber) application. We'll describe several approaches in varying levels of detail: JDBC polling, JDBC with simple JMS-based change notifications, and JDBC with continuous querying. To display data in a Swing GUI, we'll use a very handy component called the QuickTable (see http://quicktable.org for more info), which can build a very nice JTable from nothing more than a JDBC ResultSet.

Maintaining Real-Time Views Using Continuous Polling
A simple and somewhat na•ve implementation would let the user set a refresh interval and continuously re-execute the query to refresh its view. The code in Listing 3 illustrates the JTable and the use of a thread to continuously refresh the view.

While simple, this method has several obvious disadvantages:

(1)  With many concurrent views across hundreds of trader desktops, the database will be inundated with SQL select requests. In a more realistic application the queries will be more complex and the volume of data much larger, causing the database to buckle under pressure.

(2)  It's quite inefficient to continuously re-execute the query, particularly when the underlying data hasn't changed. This is especially true when working with obscure stocks that may change only a few times during the trading day.

View Maintenance Using JMS
The most common approach in use today for real-time information management is to employ messaging to capture and route events from the backend database. Rather than route every single event, active filtering can be done in the backend servers through the use of JMS selectors. More information on JMS and JMS selectors can be found at http://java.sun.com/products/jms/.

Modern databases all support the asynchronous capture and propagation of database events. For instance, with Oracle, a simple mechanism would be to use a row-level trigger on the order and quote table and route the DML events to an Oracle Advanced Queue (AQ). The AQ can then be accessed from remote machines as a JMS destination (Queue).

The code in Listing 4 illustrates how our example in Figure 2 can be changed to make use of JMS events to refresh the JTable. Though this method doesn't require the application client to continuously execute queries, it still requires the client to re-execute the complete query to the database. One could argue that the database event generator could provide sufficient information in the message so as to reconstruct the client JTable from the message itself. But, in most real-life situations, the query could be quite complex involving joins, complex query predicates, projections involving aggregate column values and such, making it nearly impossible to calculate how exactly the client-side view has been impacted. Again, much like the first naïve approach, with many concurrent clients with hundreds or even thousands of views to maintain, the approach quickly stretches the limits of database scalability.

Real-Time View Maintenance with Continuous Query
Now that we have examined polling- and messaging-based approaches to building a client application for Order Status Tracking, let's take a look at how we might build a better mousetrap with Continuous Querying (See Figure 3). To build this example, we use the GemFire Real-Time Events (RTE) Server - a JDBC in-memory database product that provides a robust CQ Engine.

The first requirement is to re-configure the DataPublisher application to connect to the RTE CQ Server JDBC database instead of a traditional RDBMS. Thanks to standard JDBC, this only means changing the JDBC driver classname and connection URL parameter. Nothing else in the DataPublisher applications needs to change as the database interaction is all in standard SQL-92, which all JDBC databases support.

The client side is encapsulated in the CQExampleGUI class, which, again, requires very little code beyond standard JDBC. Less, in fact, than the polling- and JMS-based client examples described above. The main difference is the addition of some logic to handle Continuous Query callbacks instead of a JMS listener or polling thread. All of the logic is encapsulated in the CQExampleGUI class:

For this simple example, CQExampleClient has only three important members:

Connection c = null; // The JDBC Connection to the CQ Server
CQManager cqManager; // The Continuous Query manager
DBTable dBTable1 = null; // A handy QuickTable for rich visual display

The constructor for CQExampleGUI looks like Listing 5. With these few lines of code, we've painted a nice GUI with the contents of our simple order management database. Note that though the SQL Select statement used to initiate the Continuous Query is relatively simple, most Financial Order Tracking systems actually have a "view-builder" screen that lets users select columns and filter conditions, which results in much more complex dynamically generated SQL.

Now that we've built a GUI in much the same way as traditional client/server, what remains is to handle the CQ update events. The logic for this is implemented in the afterResultsUpdated ( ) method, which passes in an CQUpdate argument containing a collection of RowDelta objects and a handle on the refreshed JDBC ResultSet. With the convenience of a QuickTable, only one line of code is necessary to update the GUI:

dbTable1.refresh (cqUpdate.getResultSet(); );

In a more sophisticated application, you can handle the update in granular detail by iterating over the provided RowDeltas, which give you the update type (UPDATE, INSERT, or DELETE), an array representing the old row column values, an array representing the new row column values, and a list of the columns actually modified. In short, everything you need to handle exactly the data that has been modified in the CQ Engine's database and nothing else. A more sophisticated CQ Callback implementation usually builds nested loop logic (rows, then update columns) to handle each delta individually.

The runtime sequence of events from start to finish is as follows:

1.  The publishing application receives an event from some external source. In response to the event, it modifies data in the CQ Engine's in-memory JDBC database (i.e., updates a filled quantity on an existing order). The code here is exactly the same as with any non-CQ JDBC database.

2.  The CQ Engine gets the update, applies it to the database, and then identifies which Continuous Queries are impacted by the data modification. This is really the magic that makes the entire design pattern successful.

3.  The CQ Engine packages row deltas specific to each Continuous Query's requirements and pushes them back to the clients over the JDBC connection. Note that these row deltas are from the client's perspective - meaning that they represent added, modified, and removed items from the client's ResultSet view. An UPDATE statement in the CQ Server database can thus lead to completely different client view deltas, depending on whether the table update caused CQ join conditions to be newly met (causes INSERT RowDelta), dropped out (causes DELETE RowDelta), or if a previously met condition continues to do so (causes UPDATE RowDelta).

4.  The CQ Clients update their JDBC ResultSet objects with the deltas and invoke any registered callbacks - in our example, we simply bind the ResultSet to a GUI grid component.

If you download and run this example, you'll see a screen that looks like Figure 4.

Since the Publisher application is continuously updating the CQ Engine database's Quotes table and Orders.FilledQty/Orders.OrderStatus fields, you'll notice that they keep changing several times a second. Thus the example shows how you use a simple JDBC syntax to create a continuously updated view of data and then handle some very straightforward callbacks to execute custom logic in response to data changes. This architecture is capable of scaling to thousands of inbound events/second servicing thousands of Continuous Queries.

Conclusion
Continuous Query technology promises to provide a robust new development model for applications that require complex real-time views of rapidly changing operational data. What makes this approach so exciting is the ability to combine the syntactical power of standard query languages with the performance and scalability required of modern applications.

Although we focused on the basic functional aspects of CQ technology in this article, you'll find that the tools on the market have some very advanced capabilities as well. Features such as CQs with User Defined Aggregations (UDAs), advanced horizontal scale-out, transparent high availability and failover, intelligent flow control, distributed transactions, and much more are either available now or soon will be. This is a technology worth keeping an eye on as you encounter use cases where client views of operational data must be continuously maintained.