问题描述
我有一个JTable,它由Access DB使用ResultSet& AbstractTableModel
填充.我有一种方法可以从数据库中正确删除记录,但是在刷新表模型的当前视图时遇到了麻烦.我看过类似的文章,并尝试使用fireTableRowsDeleted
和fireTableDataChanged
,但是没有运气.我还注意到其他帖子提到了DefaultTableModel
的使用,因为它具有添加/删除行方法,但是我正在使用的代码来自我去年使用的Java教科书(教授从未达到这一点,所以我试图学习这个我自己)...
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)...
这是自定义JFrame的类:
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 EmpInOutBoard
这是AbstractTableModel的类(从我的课本中稍作修改):
And 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 ResultSetTableModel
如您所见,我尝试在第二个类中添加removeRow方法,但最终出现错误:attempt to assign to non-updatable column
.我缺少什么来刷新我的表模型?如果这更容易/更简单,我愿意将其更改为使用DefaultTableModel
及其内置方法.谢谢
As 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
推荐答案
我没有使用直接行索引,因为如果用户按任一列对数据进行排序,则该行索引可能会发生变化.我觉得,由于employeeID是唯一的,因此最好在该ID上进行匹配:
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();
}
}
当然,现在JTable中的数据正在消失,我不明白.
Of course, now the data in the JTable is disappearing which I don't understand.
这篇关于删除或插入后更新JTable的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!