JDBC TableModel for a JTable in Java?

Question

I want to display a database table as a JTable. I have never used JTable before so I googled JTable and TableModel.

With that googling, I am able to write my own custom TableModel which show data stored in

Object[][] data;

Now, I want to show my database table data into JTable. I searched that also and have got an idea of that but still confused about what should goes where in the implementation class of AbstractTableModel.

Following is the code of custom TableModel.

public abstract class AbstractPOLDATTableModel extends AbstractTableModel {
protected boolean DEBUG = false;
private String[] columnNames;
private Object[][] data;

protected AbstractPOLDATTableModel(String[] columnNames, Object[][] data) {
    this.columnNames = columnNames;
    this.data = data;
}

public int getColumnCount() {
    return columnNames.length;
}

public int getRowCount() {
    return data.length;
}

@Override
public String getColumnName(int col) {
    return columnNames[col];
}

public Object getValueAt(int row, int col) {
    return data[row][col];
}

@Override
public Class getColumnClass(int c) {
    return getValueAt(0, c).getClass();
}

@Override
public boolean isCellEditable(int row, int col) {
    if (col < 2) {
        return false;
    } else {
        return true;
    }
}

@Override
public void setValueAt(Object value, int row, int col) {
    if (DEBUG) {
        System.out.println("Setting value at " + row + "," + col
                           + " to " + value
                           + " (an instance of "
                           + value.getClass() + ")");
    }

    data[row][col] = value;
    fireTableCellUpdated(row, col);

    if (DEBUG) {
        System.out.println("New value of data:");
        printDebugData();
    }
}

private void printDebugData() {
    int numRows = getRowCount();
    int numCols = getColumnCount();

    for (int i=0; i < numRows; i++) {
        System.out.print("    row " + i + ":");
        for (int j=0; j < numCols; j++) {
            System.out.print("  " + data[i][j]);
        }
        System.out.println();
    }
    System.out.println("--------------------------");
}
}

Now, how to change the above code so that my JTable can have the follwing features:

1. It shows data from the database
2. User can edit the table directly and when he clicks on a "save" button, the changes reflect in the database data
3. User can insert data directly.
4. User can delete data directly.

Answer 1

I'm assuming you've implemented 1 according to what Adamski suggested in your previous question. Also, as suggested by Sanoj, change to using some sort of List to store your data.

1. To support 2-4, you must ensure that the data you're pulling out of the database comes from only one table and involves no derived data (eg. aggregations, column1 + column2). You will need to keep track of your where clause, if you intend to let the user filter rows.

2. Within each Row, store another Row (let's call it updatedRow) that represents the updates the user has made using the GUI. Once any update is made to the row, this field needs to be populated with a new Row containing the updated data . When "Save" is clicked, you run update queries for all Rows with a non-null updatedRow, updating the database with data in updatedRow which do not match those from the original Row (don't update if the user changes data back to how it was originally). You might also have another "Undo Updates" button which populates the table with the original data for all Rows with a non-null updatedRow.
   
   I would strongly recommend that you store additional metadata on which fields form the primary key and prevent those from being changed, as updates to such columns may be expensive (since they are presumably indexed and may have some RI constraints attached). This should be reflected in the GUI by making such columns not editable. The where clause in updates would use only these fields rather than all fields (you will still need other filters that your user set in 1).

3. I'd suggest a two step process. The user starts by clicking a button which adds a row to the table. After entering data, the user clicks another button to insert into the database (or you can combine this functionality with updates in the "save" button). You will need to allow primary key fields to be editable for newly inserted rows if the primary key columns aren't some auto-generated ID.
   
   To different between which Rows are already in the database and which ones aren't, I'd suggest storing a list of newly inserted Rows.
   
   If you let the user pick only certain columns to display in the table, you will need to determine how to deal with the columns that aren't being displayed (eg. let the database set a default, populate with auto-generated ID).

4. Probably best to implement this by having a checkbox with each Row, then when the "Delete" button is clicked, it calls SQL to delete each checked Row using the filter from 1 and primary key metadata mentioned in 2, as well as to remove it from the table.

General considerations:

• The same setValueAt method will be used for both updated and inserted Rows, but you want different behaviour. You want to set updatedRow for updates, but not when editting Rows you're about to insert.

• How will you handle errors such as constraints not being met or invalid input (eg. 'abcde' in a numeric field)?

• SQL injection.