Displaying entity-attribute-value data in JTable?

Question

How can I use a JTable to display & edit attribute properties for entities retrieved from an entity,attribute,value (EAV) store (a relational DBMS)?

I know this is a question with a lot of possible answers, so PLEASE look at the requirements I have below before answering.

I promise to vote up answers that show you've read & understand the whole thing (as long as they aren't totally silly).

---

The user needs to be able to:

1. Filter/Search entities by their attributes

2. Choose which attributes to show (as columns)

3. Sort entities by chosen attributes

4. Edit attribute values

5. Do operations on selected entities

6. (Optional) Ability to save view for later use.

---

System Requirements:

1. Number of entities: needs to scale up to 100K+ unique entities

2. Attributes: user can add and define new attributes, system should be able to handle this

3. Underlying Storage: H2 Database (already designed), communicating by JDBC

4. Memory: not everything will fit, so somehow needs to pull from DBMS queries

5. Performance: should minimize number of queries needed to DBMS (one query per attribute OK, and I have a form with 1 query per table view, but it sucks).

6. Queries: ONE query should be required to generate list of entities matching a search/filter. Otherwise massive performance suck.

7. Reusing data: shouldn't have to re-query or re-sort the entire list when column is added.

---

Things I've looked at:

1. Glazed Lists library

   • Pros:

     • Flexible about column handling
     • Easy to implement sort/filter of entities
     • Flexible about column display format & editing

   • Cons:

     • One object per entity (if objects are complex, memory overhead becomes a serious memory problem!)
     • Object responsible for all functionality... but objects should be simple for memory reasons
     • How do I support user-selectable columns without a HashMap for EVERY entity object?

2. Extending AbstractTableModel to map data from a JDBC ResultSet to rows,columns

   • Pros:
     • Paging of results avoids memory problem
     • Searching/Filtering is directly in SQL
     • Memory-friendly, doesn't have to make an object per-row
   • Cons:
     • Implementing custom columns & sorting is a pain (table header renderer, managing sort columns and order, etc)!
     • Probably have to write custom JTableColumnModel too, and this gets messy!
     • Has to manipulate SQL a lot, so if DB schema changes, have to rewrite multiple pieces of code!
     • Hard to maintain entity ID info

3. ORM

   • Pros:
     • Designed to map DB rows to objects
     • Provides object management
   • Cons:
     • WORST POSSIBLE solution for entity-attribute-value model
     • Have to learn & write ORM code in addition to DBMS & Java code!
     • Entities can have any number of attributes, ORM is only good with static, limited object attributes
     • Lose flexibility/speed of custom SQL

---

Is there a better option that I missed, or some clever way to make Glazed Lists or custom Table Model easier?

I've totally discarded ORM as an option already, because of how badly matched it is to EAV storage.

Answer 1

I think your best option is to go with 'Extending AbstractTableModel with form map data from a JDBC ResultSet' because

• Java 6 JTable has built in sorting support so you don't really need to implement that.
• If you design your model carefully, you could survive some schema changes. Code clearly to allow yourself to make changes easier if you need.
• You'll have to write back changes anyway. Use a 'Save' button and batch update might even help your performance.
• You can override TableCellEditor to supply combobox instead of the default text editor.
• Don't try to do all edit in one table. Have separated means for entry creation etc.
• You can add/remove columns to JTable at runtime. Just fireTableModelChanged() and the new column becomes visible

Edit: One crazy thing I would do to create a custom component and do all rendering myself and perform the edit operations with well placed JTextField and JComboBox.

Edit2: Based on your comment. Save the position of the selected item before you do the fire...() call. Btw, I don't think the call resets the sorting or the selection - had no problem with that.

If you add a column, you could just fetch the key field and the values for the new column only. Display them in the column. Then do a hidden complete reload in the background and swap the model to that when it is finished. This is practically working from multiple ResultSets at the same time in one table.

Removing is easy as you don't show the values for that column.

Edit3:

DefaultRowSorter isn't that deep. It maintains a reindexing table for your records. So when JTable asks for the 10th row, the rowsorter checks its 10th entry of the index table and retrieves that indexth element from your actual model.

Also if you have lots of identical strings in your model use a simple Map of String to String cache when you query the data from the database. This way the tons of redundant String objects can be GC-d right away.

Edit4:

I would query the new field into a Map of key to value and have my primary model contain a list of map of key to value. Then I would use a getValue() implementation which returns the value from either the primary data source of from these additional maps on demand. I would lookup the row's key from the primary model and use that to retrieve the actual value from the additional maps. (Btw. Reputation gained from accepted answers are not subject to the daily limit.)