删除或插入后更新JTable

删除或插入后更新JTable

本文介绍了删除或插入后更新JTable的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个JTable,它由Access DB使用ResultSet& AbstractTableModel填充.我有一种方法可以从数据库中正确删除记录,但是在刷新表模型的当前视图时遇到了麻烦.我看过类似的文章,并尝试使用fireTableRowsDeletedfireTableDataChanged,但是没有运气.我还注意到其他帖子提到了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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 00:52