Have Geek Brain

How can I enable GUI behavior for JTable sort when SQL does the sort?

How to enable JTable icons and behavior for sorting table rows by column, preventing it from using a comparison predicate to sort? That is, how can I tell the table headers to show an arrow for ascending / descending sort order on the column being used, and make it call the appropriate methods when the sort / column order changes?

I am trying to create a (editable, filterable, sortable) JTable supported by a SQL query or view. The strings may not fit in memory and may not display Java objects correctly, so I want to do all sorting / filtering in SQL. I already wrote the code to modify the query to sort by column, filter by value and visible columns.

To use this, I plan on writing a JTableModel based on the ResultSet with TYPE_SCROLL_SENSITIVE,

and CONCUR_UPDATABLE

so the changes to the DB propagate to the ResultSet. I periodically (several times per second) force-refresh the visible JTable from the ResultSet so the changes in the database become visible to the user. User changes to the table will be propagated to the updated ResultSet after validation.

I took a little look at how sorting works fine, but most implementations rely on the JTable, creating a javax.swing.RowSorter with a comparator predicate, or maintaining a sorted list of strings that fires events when changed. So my questions are:

ORM structures are NOT the answer to this question because data does not represent entity objects well. Also, the DBMS I use is H2.

EDIT: Sortable JTable libraries based on using comparators or sorting predicates for object strings are also unusable, unfortunately. I don't believe I can keep all the objects in memory to perform sorting. This issue prevents me from using SwingX JXTables, GlazedLists, or similar libraries. I wish I could, but I can't. Period.

** I will be dealing with many thousands of rows, potentially millions, with numerous columns. Yes, I really need to use SQL for sorting and filtering. **

Questions: (descending)

  • How do I show indicators that use a column to sort rows?
  • How do I get the JTable to fire appropriate events when the column headers are LEFT-clicked in order to change the sort order?
  • Is there an easier way to make the JTable update when the database changes?
  • Is there a library that will make all this much easier (connecting queries or DB views and JTables)?
  • Am I going to run into terrible, terrible problems when I design such a system?
+1


a source to share


4 answers


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



+1


a source


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

Am I going to run into terrible, terrible problems when I design such a system?

From experience, yes. I worked on a project in much the same way as this where someone developed a JTable that is supposedly "magically" bound to a database table. This combined display logic and database access comes together into one big ugly mess, which we completely replaced with reflection table models and separate CRUD writes.



You say ORM is not the answer ...

  • If the data format does not change, then this should be considered anyway. Your entity classes do not need to represent real world entities.

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

    • A flexible map-based class Record

      that stores records as key-value pairs;

    • Dynamically built table models for your display logic, built using record key queries, are connected to SwingX tables to get sort and filter for free;

    • A similarly designed Repository

      class that encapsulates database access separate from the table itself responsible for loading and saving Record

      s. This acts as an adapter between the updatable ResultSet

      and the view (although I would check if using ResultSet

      this way would require an open database connection while the data is visible ...).

    This division into "a table that displays and sorts records" and a "repository that manages data" means:

    • You can reuse a table for data that is not tied to a database;
    • You can display records tied to a database in things other than tables;
    • You won't go crazy trying to build and test a thing :)
+1


a source


You must subclass javax.swing.RowSorter to create a row sorter that sorts on the database. In the API docs:

"RowSorter implementations usually do not have a one-to-one mapping to the underlying model, but they can. For example, if the database is doing a sort, toggleSortOrder can access the database (in a background thread) and override the mapping methods to return the passed argument. "

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

+1


a source


Database stuff aside, a class called SortableTable that is part of the JIDE grid. It displays sorting with a small ^ or v in the table header, and supports sorting with more than 1 column (1v, 2v, etc.).

alt text

0


a source






More articles:


All Articles