将行插入ResultTableModel和数据库中

Question

我有一个从数据库填充的JTable。我使用的是定制型号ResultSetTableModel。以下是自定义模型：

public class ResultSetTableModel extends AbstractTableModel {
private String[] columnNames;
private Class[] columnClasses;
private List<List<Object>> cells = new ArrayList<List<Object>>();
public ResultSetTableModel() {
columnNames = new String[1];
columnNames[0] = "Result Set";
List<Object> row = new ArrayList<Object>();
row.add("No data");
cells.add(row);
}
public ResultSetTableModel(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
// SOMETHING TO KEEP AN EYE ON! It looks like the ResultSetMetaData
// object screws up once the ResultSet itself has been read (ie by
// rs.next() ). Putting any rsmd.XXXX commands after the "while" loop at
// the bottom throws a nasty exception. A bug on the SQLite side I
// think.
columnNames = new String[rsmd.getColumnCount()];
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
columnNames[i - 1] = rsmd.getColumnName(i);
}
columnClasses = new Class[rsmd.getColumnCount()];
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
int columnType = rsmd.getColumnType(i);
switch (columnType) {
case java.sql.Types.INTEGER:
case java.sql.Types.NUMERIC:
columnClasses[i - 1] = Integer.class;
break;
case java.sql.Types.VARCHAR:
case java.sql.Types.CHAR:
case java.sql.Types.DATE:
columnClasses[i - 1] = String.class;
break;
case java.sql.Types.DECIMAL:
case java.sql.Types.FLOAT:
columnClasses[i - 1] = Float.class;
default:
columnClasses[i - 1] = Object.class;
break;
}
}
while (rs.next()) {
List<Object> row = new ArrayList<Object>();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
row.add(rs.getString(i));
}
cells.add(row);
}
}
public int getRowCount() {
return cells.size();
}
public int getColumnCount() {
return columnNames.length;
}
@Override
public Class getColumnClass(int columnIndex) {
if (columnClasses != null) {
return columnClasses[columnIndex];
} else {
return Object.class;
}
}
public boolean isEmpty() {
return cells.isEmpty();
}
public Object getValueAt(int rowIndex, int columnIndex) {
return cells.get(rowIndex).get(columnIndex);
}
@Override
public String getColumnName(int columnIndex) {
return columnNames[columnIndex];
}
public void deleteRowDisplay(int index) {
this.cells.remove(index);
}
public void deleteRow(String username, String query, int indexOndisplay) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/quotgen",
"root", "3498140591");
Statement statement = connection.createStatement();
statement.executeUpdate(query);
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e.getMessage());
}
this.deleteRowDisplay(indexOndisplay);
this.fireTableRowsDeleted(indexOndisplay, indexOndisplay);
}
}

我的主要课程如下：

public class ControlP extends JFrame {
// variables
private static String query = "SELECT code as Code,description as Description, price as Price, quantity"
+ " as Quantity FROM products";
private JTable table;
private JScrollPane sp;
ResultSetTableModel rm;
JButton edit;
JButton add;
ControlP() {
this.initUI();
// button actions
this.edit.addActionListener((ActionEvent) -> {
JDialog editProduct = new JDialog(this,
ModalityType.APPLICATION_MODAL);
editProduct.setTitle("Edit Product");
editProduct.setSize(400, 400);
editProduct.setLayout(new java.awt.BorderLayout());
editProduct.setLocationRelativeTo(this);
final int selectedRow = this.table.getSelectedRow();
EditProductRecord ap = new EditProductRecord(editProduct,
this.table, selectedRow);
editProduct.add(ap);
editProduct.setVisible(true);
editProduct.pack();
});
}
private void initUI() {
this.setSize(600, 600);
this.setVisible(true);
this.setLayout(new BorderLayout(5, 5));
this.setLocationRelativeTo(null);
edit = new JButton("Edit");
add = new JButton("Add");
this.add(edit, BorderLayout.NORTH);
this.add(add, BorderLayout.SOUTH);
// constructing the table here
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/quotgen",
"root", "3498140591");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(query);
this.rm = new ResultSetTableModel(result);
this.table = new JTable(rm);
if (!rm.isEmpty()) {
table.setRowSelectionInterval(0, 0);
}
this.sp = new JScrollPane(this.table);
this.add(sp, BorderLayout.CENTER);
} catch (SQLException | ClassNotFoundException ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
// close connection
}
}
public static void main(String[] args) {
Runnable run = new Runnable() {
@Override
public void run() {
ControlP cp = new ControlP();
}
};
EventQueue.invokeLater(run);
}
}

在这个主类中，我有一个按钮edit，当单击它时，将弹出一个JDialog，其中包含JPanel，并根据表中突出显示的行更新详细信息。这是编辑类：

public class EditProductRecord extends JPanel {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/quotgen";
// Database credentials
static final String USER = "root";
static final String PASS = "3498140591";
public EditProductRecord(JDialog dialog, JTable table, int selectedRow) {
this.initUI();
// initialize panel fields
this.codeT.setText((table.getValueAt(selectedRow, 0)).toString());
this.descT.setText((table.getValueAt(selectedRow, 1)).toString());
this.priceT.setText((table.getValueAt(selectedRow, 2)).toString());
this.quantityT.setText((table.getValueAt(selectedRow, 3)).toString());
this.codeT.setEnabled(false);
this.codeT.setToolTipText("You cannot change the product code!");
// /////////////////////////////////////////
this.cancel.addActionListener((ActionEvent) -> {
dialog.dispose();
});
this.save.addActionListener((ActionEvent) -> {
// check that all input fields are not empty!
if (this.descT.getText().equals("")
|| this.priceT.getText().equals("")
|| this.quantityT.getText().equals("")) {
this.emptyField.setVisible(true);
} else {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
stmt = conn.createStatement();
String sql = "INSERT INTO products (code, description,price,quantity) "
+ "VALUES ('"
+ this.codeT.getText()
+ "', '"
+ this.descT.getText()
+ "','"
+ this.priceT.getText()
+ "','"
+ this.quantityT.getText()
+ "')"
+ "ON DUPLICATE KEY UPDATE description = '"
+ this.descT.getText()
+ "',price='"
+ this.priceT.getText()
+ "',quantity='"
+ this.quantityT.getText() + "'";
stmt.execute(sql);
JOptionPane.showMessageDialog(dialog, "Done.");
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null)
conn.close();
} catch (SQLException se) {
}// do nothing
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
}// end try
}
});
}
private void initUI() {
// main panel properties
this.setPreferredSize(new Dimension(400, 400));
this.setLayout(new BorderLayout(5, 5));
// dummy panels
JPanel dummyN = new JPanel();
dummyN.setPreferredSize(new Dimension(350, 50));
JPanel dummyS = new JPanel();
dummyS.setPreferredSize(new Dimension(350, 50));
dummyS.setLayout(new net.miginfocom.swing.MigLayout());
JPanel dummyE = new JPanel();
dummyE.setPreferredSize(new Dimension(50, 300));
JPanel dummyW = new JPanel();
dummyW.setPreferredSize(new Dimension(50, 300));
JPanel centerPanel = new JPanel();
centerPanel.setPreferredSize(new Dimension(350, 300));
centerPanel.setLayout(new net.miginfocom.swing.MigLayout());
centerPanel.setBorder(BorderFactory.createTitledBorder("Product"));
this.codeL = new JLabel("Code");
this.codeT = new JTextField(10);
this.descL = new JLabel("Description");
;
this.descT = new JTextField(10);
this.priceL = new JLabel("Price");
;
this.priceT = new JTextField(10);
this.quantityL = new JLabel("Quantity");
;
this.quantityT = new JTextField(10);
emptyField = new JLabel("All fields should have Input!");
emptyField.setForeground(Color.red);
emptyField.setVisible(false);
centerPanel.add(this.codeL);
centerPanel.add(this.codeT, "wrap");
centerPanel.add(this.descL);
centerPanel.add(this.descT, "wrap");
centerPanel.add(this.priceL);
centerPanel.add(this.priceT, "wrap");
centerPanel.add(this.quantityL);
centerPanel.add(this.quantityT, "wrap");
centerPanel.add(emptyField);
this.cancel = new JButton("Cancel");
this.clear = new JButton("Clear");
this.save = new JButton("Save");
JLabel dummy = new JLabel("Dummy");
dummy.setVisible(false);
dummyS.add(dummy);
dummyS.add(this.cancel);
dummyS.add(this.clear);
dummyS.add(this.save);
this.add(centerPanel, BorderLayout.CENTER);
this.add(dummyN, BorderLayout.NORTH);
this.add(dummyS, BorderLayout.SOUTH);
this.add(dummyW, BorderLayout.WEST);
this.add(dummyE, BorderLayout.EAST);
}
// controls
JLabel codeL;
JTextField codeT;
JLabel descL;
JTextField descT;
JLabel priceL;
JTextField priceT;
JLabel quantityL;
JTextField quantityT;
JLabel emptyField;
JButton cancel;
JButton clear;
JButton save;
}

现在一切正常，除了在保存编辑行后更新表中显示的数据。

问题：单击save保存编辑时，数据仅发送到数据库，而不发送到表模型中。

我希望在保存到数据库后，数据也插入到模型中，这也应该反映在显示器上

问题：我们如何按照我上面建议的方式工作？此外，在添加一行之后，应该如何发生同样的情况？

我试过

public void rowInserted(){
this.fireTableDataChanged();
}

并在插入数据库后调用它，但它没有解决问题。

Answer 1

您可以使用的一种方法是提供一种方法，通过该方法您可以从数据库"更新"TableModel，只包括那些已更改的行。

这将要求您知道插入/更新的行的"key"或"id"，并仅对这些值运行select查询。然后，您可以通过某种"更新"方法将这个ResultSet传递给预先存在的TableModel，该方法只需从ResultSet加载或更新行。

这可能需要某种类型的Map，它被键控到行的"key"或"id"，因此您可以更容易地更新它。

我可能会考虑的一件事是提供行数据的POJO，您可以使用它不仅存储在查找Map中，还可以发现指定记录的模型行索引。