I have a JTable that is populated by an Access DB using a ResultSet &AbstractTableModel. I have a method that deletes the record from the DB correctly but am having trouble refreshing the current view of the table model. I've looked at similar posts and have tried using fireTableRowsDeleted and fireTableDataChanged but have had no luck. I also noticed that other posts mention the use of the DefaultTableModel as it has add/remove row methods but the code I have working is from my Java textbook I had used last year (professor never reached this point so I was trying to learn this myself)...
Here's the class for the custom JFrame:
class AdministrationFrame extends JFrame
{
//set variable for location of database
private static final File DB_FILE =
new File("C:\\Eclipse\\EmpInOutBoard - TEMP.accdb");
//database URL
private static final String DB_URL = "jdbc:ucanaccess://" + DB_FILE.getAbsolutePath();
//set default query to retrieve all users sorted by last name
private static final String DEFAULT_QUERY =
"SELECT EmployeeNo, FirstName, LastName, DisplayName, GroupName, CompanyName "
+ "FROM Employees "
+ "WHERE DisabledState = false "
+ "ORDER BY LastName";
//layout for window
private final BorderLayout layout;
//administration window
private final JFrame adminFrame;
private final JPanel tablePanel;
private final JPanel tablePanel2;
private final JPanel buttonPanel;
//items for tablePanel
private final JLabel filterLabel;
private final JTextField filterTextField;
private final JButton filterButton;
//items for buttonPanel
private final JButton updateButton;
private final JButton deleteButton;
private String employeeID;
private Connection conn;
private JTable resultTable;
private static ResultSetTableModel tblModel;
public AdministrationFrame()
{
layout = new BorderLayout(10, 10);
setLayout(layout);
//place GUI components on JFrame's content pane
adminFrame = new JFrame("Employee Modification Panel");
//set up JPanels
tablePanel = new JPanel();
tablePanel2 = new JPanel();
String tablePanelTitle = "Employee Details";
tablePanel.setBorder(BorderFactory.createTitledBorder(null,
tablePanelTitle, TitledBorder.CENTER, TitledBorder.TOP,
new Font("Arial", Font.BOLD + Font.ITALIC, 22), Color.BLACK));
tablePanel2.setLayout(new BoxLayout(tablePanel2, BoxLayout.Y_AXIS));
buttonPanel = new JPanel();
//set up items in each JPanel
filterLabel = new JLabel("Filter:");
filterLabel.setAlignmentX(LEFT_ALIGNMENT);
filterTextField = new JTextField();
filterTextField.setAlignmentX(LEFT_ALIGNMENT);
filterButton = new JButton("Apply Filter");
filterButton.setAlignmentX(LEFT_ALIGNMENT);
updateButton = new JButton("Add/Update");
deleteButton = new JButton("Delete");
//create ResultSetTableModel and display database table
try
{
//create TableModel for results of the default query
tblModel = new ResultSetTableModel(DB_URL, DEFAULT_QUERY);
//create JTable based on the tblModel
resultTable = new JTable(tblModel)
{
@Override
public Dimension getPreferredScrollableViewportSize()
{
return new Dimension(600, 250);
}
};
resultTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
resultTable.getTableHeader().setResizingAllowed(false); //disable column resizing
resultTable.getTableHeader().setReorderingAllowed(false); //disable column dragging
resultTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); //sets table to only allow selection of single row
resultTable.getSelectionModel().addListSelectionListener(new RowListener()); //register event handlers
final JScrollPane tablePane = new JScrollPane(resultTable);
//add items to JPanels
tablePanel2.add(filterLabel);
tablePanel2.add(Box.createRigidArea(new Dimension(0, 2)));
tablePanel2.add(filterTextField);
tablePanel2.add(Box.createRigidArea(new Dimension(0, 10)));
tablePanel2.add(filterButton);
tablePanel.add(tablePane);
tablePanel.add(tablePanel2);
buttonPanel.add(updateButton);
buttonPanel.add(deleteButton);
//add JPanels to frame
adminFrame.add(tablePanel, BorderLayout.NORTH);
adminFrame.add(buttonPanel, BorderLayout.SOUTH);
final TableRowSorter<TableModel> sorter = new TableRowSorter<TableModel>(tblModel);
resultTable.setRowSorter(sorter);
//create listener for filterButton
filterButton.addActionListener(new ActionListener()
{
//pass filter text to Listener
public void actionPerformed(ActionEvent e)
{
String text = filterTextField.getText();
if (text.length() == 0)
sorter.setRowFilter(null);
else
{
try
{
//make filter case-insensitive
sorter.setRowFilter(RowFilter.regexFilter("(?i)" + text));
}
catch (PatternSyntaxException pse)
{
JOptionPane.showMessageDialog(null, "Bad regex pattern",
"Bad regex pattern", JOptionPane.ERROR_MESSAGE);
}
}
}
});
deleteButton.addActionListener(new ActionListener()
{
@Override
public void actionPerformed(ActionEvent e)
{
if(employeeID != null && !employeeID.isEmpty())
{
try
{
deleteFromTable(employeeID);
JOptionPane.showMessageDialog(null, "User " + employeeID + " deleted from the table",
"Successful Deletion", JOptionPane.PLAIN_MESSAGE);
//tblModel.fireTableRowsDeleted(resultTable.getSelectedRow(), resultTable.getSelectedRow());
}
catch (SQLException e1)
{
e1.printStackTrace();
}
}
else
{
JOptionPane.showMessageDialog(null, "No user selected. Cannot perform delete.",
"ERROR", JOptionPane.ERROR_MESSAGE);
}
}
});
pack();
//dispose of window when user quits application
//(do not want to close application)
adminFrame.setDefaultCloseOperation(DISPOSE_ON_CLOSE);
adminFrame.setSize(800, 400);
adminFrame.setVisible(true);
adminFrame.setLocationRelativeTo(null);
adminFrame.setResizable(false);
//ensure database is closed when user quits application
adminFrame.addWindowListener(new WindowAdapter()
{
//disconnect from database and exit when window has closed
public void windowClosed(WindowEvent event)
{
tblModel.disconnectFromDatabase();
System.exit(0);
}
});
}
catch (SQLException sqlException)
{
JOptionPane.showMessageDialog(null, sqlException.getMessage(),
"Database error", JOptionPane.ERROR_MESSAGE);
tblModel.disconnectFromDatabase();
System.exit(1); //terminate application
}
}
private class RowListener implements ListSelectionListener
{
@Override
public void valueChanged(ListSelectionEvent event)
{
if(!event.getValueIsAdjusting())
{
int row = resultTable.getSelectedRow();
if(row == -1) //no row found
JOptionPane.showMessageDialog(adminFrame, "Selected row not found in filtered set",
null, JOptionPane.WARNING_MESSAGE);
else
{
employeeID = resultTable.getValueAt(row, resultTable.getColumn("EmployeeNo").getModelIndex()).toString();
}
}
}
};
//updates DB to delete selected user/record from the table
public void deleteFromTable(String empID) throws SQLException
{
PreparedStatement prepStmnt = null;
String deleteSQL = "DELETE "
+ "FROM Employees "
+ "WHERE EmployeeNo = ?";
try
{
conn = DriverManager.getConnection(DB_URL);
conn.setAutoCommit(false);
prepStmnt = conn.prepareStatement(deleteSQL);
prepStmnt.setString(1, empID);
prepStmnt.executeUpdate();
conn.commit();
}
catch (SQLException sqlExcep)
{
sqlExcep.printStackTrace();
if(conn != null)
{
try
{
System.err.print("Rolling back transaction");
conn.rollback();
}
catch (SQLException excep)
{
excep.printStackTrace();
}
}
System.exit(1); //terminate application
}
finally //close the connection
{
if(prepStmnt != null)
prepStmnt.close();
conn.setAutoCommit(true);
conn.close();
}
}
}//end class EmpInOutBoardAnd here's the class for the AbstractTableModel (slightly modified from my textbook):
class ResultSetTableModel extends AbstractTableModel
{
private final Connection connection;
private final Statement statement;
private ResultSet resultSet;
private ResultSetMetaData resultSetMetaData;
private int numRowCount;
//track DB connection status
private boolean dbConnStatus = false;
//constructor initializes rSet and obtains its
//metadata object; also determines number of rows
public ResultSetTableModel(String url, String query) throws SQLException
{
//connect to the database
connection = DriverManager.getConnection(url);
//create statement to query database
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//update database connection status
dbConnStatus = true;
//set query and execute it
setQuery(query);
}
//get class that represents column type
@SuppressWarnings({ "unchecked", "rawtypes" })
public Class getColumnClass(int column) throws IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
//determine Java class of column
try
{
String className = resultSetMetaData.getColumnClassName(column + 1);
//return Class object that represents class Name
return Class.forName(className);
}
catch (Exception e)
{
e.printStackTrace();
}
return Object.class; //if problems occur above, assume type Object
}
//remove row in the ResultSet
public void removeRow(int row)
{
// try
// {
// resultSet.absolute(row);
// resultSet.deleteRow();
// }
// catch (SQLException e)
// {
// e.printStackTrace();
// }
// fireTableRowsDeleted(row, row);
}
//get the number of columns in the ResultSet
public int getColumnCount() throws IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
//determine number of columns
try
{
return resultSetMetaData.getColumnCount();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return 0; //if problem occur above, return 0 for number of columns
}
//get name of a particular column in ResultSet
public String getColumnName(int column) throws IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
//determine column name
try
{
return resultSetMetaData.getColumnName(column + 1);
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return ""; //if problems occur above, return empty string for column name
}
//return number of rows in ResultSet
public int getRowCount() throws IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
return numRowCount;
}
//obtain value in particular row and column
public Object getValueAt(int row, int column) throws IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
//obtain a value at specified ResultSet row and column
try
{
resultSet.absolute(row + 1);
return resultSet.getObject(column + 1);
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return ""; //if problems occur above, return empty string object
}
//set new database query string
public void setQuery(String query) throws SQLException, IllegalStateException
{
//ensure database connection is available
if(!dbConnStatus)
throw new IllegalStateException("No connection to the Database");
//specify query and execute it
resultSet = statement.executeQuery(query);
//obtain metadata for ResultSet
resultSetMetaData = resultSet.getMetaData();
//determine number of rows in ResultSet
resultSet.last(); //move to last row
numRowCount = resultSet.getRow(); //get row number
//notify JTable that model has changed
fireTableStructureChanged();
}
//close Statement and Connection
public void disconnectFromDatabase()
{
//ensure database connection is available
if(dbConnStatus);
//determine number of columns
try
{
resultSet.close();
statement.close();
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
finally
{
dbConnStatus = false;
}
}
} //end class ResultSetTableModelAs you can see, I've tried to add a removeRow method within the second class but I end up getting an error: attempt to assign to non-updatable column. What am I missing to refresh my table model? I am willing to change this to use DefaultTableModel with its built-in methods if this would be easier/simpler. Thanks
I did not use a direct row index because that may change if the user sorted the data by any one of the columns. I felt that since employeeID was unique that it would be better to match on that:
//remove row in the ResultSet
public void removeRow(String empID)
{
int rsRow = 0;
try
{
//set cursor to beginning of data set (before first row)
if(!resultSet.isBeforeFirst())
resultSet.beforeFirst();
//iterate through resultSet to find matching record with
//correct employee ID. once found delete row
while(resultSet.next())
{
if(resultSet.getString("EmployeeNo") == empID)
{
rsRow = resultSet.getRow();
resultSet.deleteRow();
System.out.println("User: " + empID + " was deleted from row: " + rsRow);
break;
}
}
resultSet.last();
numRowCount = resultSet.getRow();
fireTableRowsDeleted(rsRow, rsRow);
// resultSet.absolute(rsRow);
// resultSet.deleteRow();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
Of course, now the data in the JTable is disappearing which I don't understand.