EoD SQL Applied – Part 2 / 5 (Swing Applications)

Swing Applications

Introduction

We start off the series with Swing Applications. Why? Although probably the application type least likely to be using EoD SQL right now, they nevertheless make use of the most commonly demonstrated EoD SQL data-structure: the DataSet. Swing Applications often have direct access to their database. They can therefore make much better use of long-lived database connections and cursor based ResultSets than web-applications can. So we open with a more detailed than-usual discussion of the DataSet interface and it’s implementations.

The DataSet is the original return type of the EoD API from Java 6 – beta. It’s a relatively thin wrapper on top of a scrollable ResultSet, and it performs best when your database that supports cursors, and your client that will be looking at large volumes of data over a longer periods of time. You’ll notice that this fits well with an intranet Swing client that has a direct connection to it’s database.

DataSet

Despite there only being one DataSet interface and one @Select annotation in EoD SQL, there are three DataSet implementations:

  1. ConnectedDataSet
  2. DisconnectedDataSet
  3. UpdatableDataSet

The two we will be working with in Swing will be the ConnectedDataSet and the WritableDataSet (the DisconnectedDataSet effectively acts like a normal List object, and so isn’t interesting in the context of a Swing application).

Now some important factors to remember about the default DataSet implementations in EoD SQL:

  • DataSet extends the java.util.List interface and thus can be treated as a normal Java Collection object
  • DataSet implementations are not Serializable
  • Like the basic Collection implementations in Java SE, DataSet is not thread-safe
  • The ConnectedDataSet and UpdatableDataSet hold a Connection open when they are in use
    • These implementations need to be closed when they are no longer in use
  • The ConnectedDataSet and UpdatableDataSet both use a DataSetCache to cache objects
    • Most commonly used to keep identity equality working as expected
    • Can also be extended (later in this post) to perform more complex caching of objects

The Swing side of things

Swing has the advantage of being backed by generic Model interfaces, allowing you to back many Swing objects directly against the database through a DataSet object. When backing your Swing application onto a cursor based database (such as Postgres or Oracle), the DataSet class allows you to very quickly fetch data from the database and display it to the user without fetching the entire ResultSet into memory up front. It also allows you to treat the data within the Swing object as it’s true Object type rather than a Database row (as Swing was intended to do). Considering that Swing widgets generally only display a snapshot of all the available data (JList, JTree, JTable are all scrollable) working with a DataSet means that only the displayed data needs to be in memory (and sometimes not even that data).

Example: A ListModel

public class Movie {
 @AutoGeneratedKeys
 public long id;

 public String title;
 public String director;

 // ...

 public String toString() {
  return title;
 }
}

public interface MovieQuery extends BaseQuery {
 public static final MovieQuery MOVIES = QueryTool.getQuery(MovieQuery.class);

 @Select("SELECT * FROM movies ORDER BY title")
 DataSet<Movie> selectMovies();

 // ...
}

public class MovieListModel extends AbstractListModel {
 private final DataSet<Movie> movies = MovieQuery.MOVIES.selectMovies();

 public MovieListModel() {
 }

 public int getSize() {
  return movies.size();
 }

 public Object getElementAt(final int index) {
  return movies.get(index);
 }

 /**
 * Unlike a normal ListModel this implementation will hold open a
 * database Connection until we release it. Thus when the window
 * our JList is added to gets closed, we should get closed as well,
 * unfortunately for this example: we have no knowledge of when the
 * window will be closed, and as such we rely on outside code to
 * invoke our close() method so that we can close our DataSet.
 */
 public void close() {
  movies.close();
 }
}

DataSetCache

Every ConnectedDataSet and UpdatableDataSet makes use of a DataSetCache object (each DataSet instance has it’s own DataSetCache instance). The default DataSetCache returned by a methods annotated with @Select will use WeakReference objects to allow the Garbage Collector to destroy any objects no longer in use. The main purpose of this default implementation is to keep identity equality working. Since the objects are only fetched from the DataSet during painting and when the outside application asks for the selected objects, the Garbage Collector is free to throw away any objects that are not visible (or even those that are visible, but have already been painted). The following code illustrates the difference between the ArrayDataSetCache (default) and the NullDataSetCache (which does no caching):

public interface MovieQuery extends BaseQuery {
  @Select(sql="SELECT * FROM movies LIMIT 2",cache=ArrayDataSetCache.class)
  DataSet<Movie> selectMoviesWithCache();

  @Select(sql="SELECT * FROM movies LIMIT 2",cache=NullDataSetCache.class)
  DataSet<Movie> selectMoviesWithNullCache();
}

public class MovieQueryTest {

  @Test
  public void testCacheIdentity() {
    final MovieQuery query = QueryTool.getQuery(MovieQuery.class);
    final DataSet<Movie> ds = query.selectMoviesWithCache();

    final Movie m1 = ds.get(0);
    final Movie m2 = ds.get(1);

    assertNotNull(m1);
    assertNotNull(m2);

    assertNotSame(m1, m2); // m1 != m2

    assertSame(m1, ds.get(0)); // m1 == ds.get(0)
    assertSame(m2, ds.get(1)); // m2 == ds.get(1)
  }

  @Test
  public void testNonCacheIdentity() {
    final MovieQuery query = QueryTool.getQuery(MovieQuery.class);
    final DataSet<Movie> ds = query.selectMoviesWithNullCache();

    final Movie m1 = ds.get(0);
    final Movie m2 = ds.get(1);

    assertNotNull(m1);
    assertNotNull(m2);

    assertNotSame(m1, m2); // m1 != m2

    // now the interesting part
    assertNotSame(m1, ds.get(0));
    assertNotSame(m2, ds.get(1));

    // and finally, just to prove the point
    assertNotSame(ds.get(0), ds.get(0));
  }
}

The problem is: this can create a lot of load on your database when you have tens-of-thousands of concurrent users (Swing applications seldom see the scale of concurrent users to the database that web applications do, which is why a ConnectedDataSet still makes sense). In this case it’s a good idea to change how the objects are cached by the DataSet by implementing your own custom DataSetCache (such as a SoftReference cache or a Least Recently Used (LRU) cache). This will allow you to make fewer round-trips to the database, while still taking advantage of a cursor based database. A very simple example of a Least Recently Used cache based on a LinkedHashMap might look as follows:

public class LRUDataSetCache<T> implements DataSetCache<T> {
 private final Map<Integer, T> map = new LinkedHashMap<Integer, T>(16, 0.75f, true) {
  protected boolean removeEldestEntry(final Map.Entry<Integer, T> entry) {
   return size() > 64;
  }
 };

 public void init(final DataSet<T> dataSet) {}

 public boolean isCached(final int row) {
  return map.containsKey(Integer.valueOf(row));
 }

 public T getObject(final int row) {
  return map.get(Integer.valueOf(row));
 }

 public void setObject(final int row, final T object) {
  map.put(Integer.valueOf(row), object);
 }

 public void destroy() {
  map.clear();
 }
}

This is not a very good or efficient way to cache rows in an LRU mechanism, but it’s a simple example. The only rule for a DataSetCache (besides implementing  the DataSetCache interface) is that it must have a default constructor (public with no parameters). The next question is, how do we integrate the new cache with our application? The same way as we did with our unit test above:

@Select(sql="SELECT * FROM movies ORDER BY title",cache=LRUDataSetCache.class)
DataSet<Movie> selectMovies();

The result: our MovieListModel will now keep up to 64 of the most recently used Movie objects in memory for the DataSet, eliminating the need to re-fetch them from the database. Note: we haven’t dealt with time-to-live or any such common caching practices here, it’s way beyond the scope of this post (however, you may want to look into doing such things if you are implementing a more complex DataSetCache).

UpdatableDataSet

So we’ve covered the reading side of a DataSet: opening a DataSet; closing a DataSet; and caching the data within a DataSetCache. What about writing data back into a DataSet? The ResultSet interface has several methods to add new rows and update or delete existing rows. This code is available to you through EoD SQL, but is subject to the same constraints as those placed on a ResultSet object by the JDBC spec and driver. Some databases do not support updatable ResultSets at all, in which case this functionality will not work for you (check the “database_notes.txt” file in the docs directory of your EoD SQL distribution).

The most common constraints placed on an updatable ResultSet are:

  • You must only select from a single table
  • You must select the entire primary-key
  • You generally must select all of the columns that don’t have a default value (and populate them)

There is also one very important note with an UpdatableDataSet: it will not pickup changes to your objects unless you explicitly invoke the set method for that row and object. The main reason for this is that EoD SQL does no enhancement of your bytecode (in fact EoD SQL is quite capable of running in a very tight sandbox), and as such has no way of knowing when your objects change (and then no way of knowing when you want those changes pushed back into the database). Thus you need to tell it when you’ve updated the object by “putting it back” into the DataSet.

UpdatableDataSet Mutation Methods

UpdatableDataSet actually extends the default ConnectedDataSet class. ConnectedDataSet has no mutation methods available (add, remove, clear and co all throw UnsupportedOperationExceptions). UpdatableDataSet implements some, but not all of the mutation methods defined in the List class:

But what about all the other mutation methods like add(int, E) or retainAll(Collection<?>)? These methods has been deliberately excluded from the implementation for some important reasons:

  1. The ResultSet update methods don’t allow inserting rows at specific indicis. Thus add(int, E) and addAll(int, Collection<? extends E>) are impossible
  2. Methods like remove(Object) and retainAll(Collection<?>) require a scan of the entire DataSet to find which Object or Objects to remove

While the first problem is impossible to solve, the second one is fairly easy. So why leave those methods out? These methods are left out in order to make end-developers using EoD SQL think about what they are doing. ConnectedDataSet includes the indexOf and lastIndexOf methods, so if you really want to perform a remove(Object), you can use:

DataSet&amp;lt;Movie&amp;gt; movies = ...;
int index = movies.indexOf(movieToRemove);
if(index &amp;gt;= 0) movies.remove(index);

However I would strongly advise rather using an @Update method with a “DELETE” in it over this method (ie: get the database to do the work for you). You need to bare in mind that EoD SQL will never generate any SQL code for you, so it’ll never try creating a “DELETE” statement for you. The remove method is great for processing rows in an iterator, and then removing them (or some similar functionality), but be careful with it.

Conclusion

We’ve taken a deep, long look at the DataSet interface, and it’s implementation details. Swing applications that directly access a database almost always have the database locally, or within an intranet. This means that a ConnectedDataSet can be used to leverage the database resources, allowing the Swing application to seemingly deal with very large volumes of data very quickly. When dealing with smaller volumes of data in a Swing application, it may be an idea to use a normal Collection object; an array or a single object as a return type instead of a DataSet. In the next post we’ll take a look at another EoD SQL specific data-structure better suited to web-applications dealing with large volumes of data.

LinkedHashMap
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: