Database to GlazedList/Jtable and then edit the database through the GlazedList/JTable

Question

I am able to break this problem down into two questions:

1. What is the best way to put the contents of a database (MS-Access) into a GlazedList/JTable?
2. How do I make sure any changes made to the GlazedList/JTable are reflected on the database (MS-Access)?

Here are the things I know:

1. I know how to retrieve/manipulate the information from a database using the JDBC method.
2. I know that GlazedList's require reflection so I would need to make a class that contains every column/field in the database. This is not very expandable...

What is the best way to go about this problem?

edit:// I have managed to create a class generator. It takes the column headings and creates an instance field. This should resolve the #2 http://pastebin.ca/1770996 - It creates the class but I do not think I used reflection correctly... edit2:// Edited my code from above so it works... http://pastebin.ca/1776722

Answer 1

I had a very similar problem, and I think my result was similar too, except it didn't need reflection (static DB schema). You need to create row objects for each row (which may just include row number and references to a ResultSet and column info).

Then write a ca.odell.glazedlists.gui.WritableTableFormat implementation to map these objects to table cells.

To avoid problems with #2, you can create a flexible row class that fetches column info once from the ResultSet and caches it for reuse.

Edit: I found an original and simpler implementation (fairly simple) that mine was based upon. You can view it here: ResultSet Table. It might be sufficient for your purposes. Then you add this to the AbstractTableModel implementation provided by the link.

public void setValueAt(Object ob, int row, int column) throws SQLException {
    resultSet.absolute(r+1);
    if (ob == null) {
        resultSet.updateNull(column+2);
    } else {
        resultSet.updateObject(column+2,ob);
    }
    rs.updateRow();
    this.fireTableCellUpdated(row,column);  
}
public boolean isCellEditable(int row, int col) {
    return true;
}

There are three catches though: your ResultSet needs to be updatable, support scrolling both directions, and be sensitive to updates to the DB. These are part of the JDBC spec, but not all drivers support them, and you need to make sure your ResultSet is created with them enabled. In that case, you just do this.fireTableDataChanged() periodically to force a full update of the table data. It's not the fastest approach, but it does work.

---

Edit2: Another approach

What about using one of the Object-relational mapper libraries, and then do the ca.odell.glazedlists.gui.WritableTableFormat like I suggested above?