How to enable GUI behaviors for sorting a JTable when SQL does the sorting?

Question

How do I enable JTable icons and behaviors for sorting table rows by a column, without letting it use a comparison predicate to do the sorting? That is to say, how do I tell the table headers to show the arrow for ascending/descending sort order in the column being used, and get it to call appropriate methods when sort order/column change?

I am trying to create an (editable, filterable, sortable) JTable backed by an SQL query or view. The rows may not fit in memory, and may not map cleanly to java objects, so I want to do all sorting/filtering within SQL. I have already written the code for changing a query to accommodate sorting by column, filtering by values, and visible columns.

To use this, I am planning to write a JTableModel based on a ResultSet with TYPE_SCROLL_SENSITIVE, and CONCUR_UPDATABLE, so changes to the DB get propagated to the ResultSet. I will periodically (several times a second) force a refresh of the visible JTable from the ResultSet, so changes to the database become visible to the user. User changes to the table will be passed to the updateable ResultSet after validation.

I've looked a little bit at how sorting is done normally, but most implementations seems to rely on the JTable creating a javax.swing.RowSorter with a Comparator predicate, or on maintaining a sorted list of rows that fires events when changed. So, my questions:

ORM frameworks are NOT an answer to this question, because the data do not map well to entity objects. Also, the DBMS I am using is H2.

EDIT: Sortable JTable libraries based on applying Comparators or sorting predicates to row objects are also unsuitable, unfortunately. I do not believe I will be able to hold all objects in memory in order to perform sorting. This problem prevents me from using the SwingX JXTables, GlazedLists, or similar libraries. I wish I could, but I can't. Period.

** I will be dealing with many thousand rows, potentially millions, with numerous columns. Yes, I really DO need to use SQL to do the sorting and filtering.**

Questions: (in descending importance)

1. How do I show indicators for which column is used to sort rows?
2. How do I get the JTable to fire appropriate events when the column headers are LEFT-clicked to change sort order?
3. Is there an easier way to force the JTable to update when the database changes?
4. Is there a library that would make all this considerably easier (connecting DB queries or views and JTables)?
5. Am I going to run into horrible, horrible problems when I design the system like this?

Answer 1

You should be able to subclass javax.swing.RowSorter in order to create a row sorter that does the sorting in the database. From the API docs:

"RowSorter implementations typically don't have a one-to-one mapping with the underlying model, but they can. For example, if a database does the sorting, toggleSortOrder might call through to the database (on a background thread), and override the mapping methods to return the argument that is passed in."

http://docs.oracle.com/javase/6/docs/api/javax/swing/RowSorter.html

Answer 2

I have never used it myself but JIDE Data Grids provides a DatabaseTableModel that provides filtering and sorting support using SQL WHERE and ORDER BY.

Answer 3

Leaving aside the database stuff there's a class called SortableTable that's a part of JIDE Grids. It displays the sorting with a little ^ or v in the table header, and supports sorting by more than 1 column (1v, 2v, etc.).

Answer 4

In answer to 1 and 2, check out SwingX, which already includes a table class with built-in sorting (and filtering). You may be able to adapt this.

Am I going to run into horrible, horrible problems when I design the system like this?

From experience, yes. I worked on a project almost exactly the same as this, where someone had designed a JTable that supposedly 'magically' bound to a database table. This coupled display logic and database access together in one big horrible mess, which we replaced entirely with reflection-driven table models and separate record CRUD operations.

You say that ORM is not the answer...

• If the format of the data doesn't change, then it's worth considering anyway. Your 'entity' classes need not represent real-world entities.

• If (as I suspect) your entity format changes, it might be worth considering:

  • A flexible map-based Record class which stores records as key-value pairs;

  • Dynamically-built table models for your display logic, built by querying record keys, plugged into SwingX tables to get sort and filter for free;

  • A similarly-designed Repository class which encapsulates your database access separately from the table itself, responsible for loading and saving Records. This acts as an adapter between your updateable ResultSet and the view (although I'd check whether using a ResultSet this way is going to require an open database connection whilst data is visible...).

  This separation into 'a table that displays and sorts records' and 'a repository that manages the data' means:

  • You can reuse the table for non-database-bound data;
  • You can display database-bound records in things other than tables;
  • You won't go mad trying to build and test the thing :)