在ResultTableModel和数据库中插入一行 [英] Inserting a row into a ResultTableModel as well as Database
本文介绍了在ResultTableModel和数据库中插入一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个从数据库填充的JTable.我正在使用自定义模型ResultSetTableModel.下面是自定义模型:
I have a JTable that is populated from a database. I am using a custom model ResultSetTableModel. Below is the custom model:
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，当单击该按钮时，将弹出一个包含JPanel的JDialog，其中的详细信息根据表中突出显示的行进行更新.这是编辑类:
Int this main class I have a button edit that when clicked will popup a JDialog containing a JPanel with details to update based on the highlighted row in the table. Here is the edit class:
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保存所做的编辑时，数据仅发送到数据库，而不发送到表模型中.
Problem: When I click save to save my edits the data is sent to the database only and not in the table model.
我希望在将数据保存到数据库之后也将其插入模型中，并且这也应该反映在显示屏上.
问题:我们如何使它像我上面建议的那样工作?而且添加行后应该怎么办?
Question: How do we make it work the way I have suggested above? And Also how should the same happen after adding a row?
我尝试过
public void rowInserted(){
this.fireTableDataChanged();
}
并在插入数据库后调用它，但是并不能解决问题.
and call it after inserting into the database but it did not solve the problem.
推荐答案
您可能可以使用的一种方法是提供一种方法，使您可以从数据库更新" TableModel，仅包括那些行.发生了变化.
One method you might be able to use is to provide a means by which the you can "update" the TableModel from the database, including only those rows which have changed.
这将需要您知道插入/更新的行的键"或"id"，并仅对这些值运行select查询.然后，您可以通过某种更新"方法将此ResultSet传递给先前存在的TableModel，该方法将简单地加载或更新ResultSet中的行.
This would require you to know the "key" or "id" of the row(s) inserted/updated and run a select query only for those values. You would then pass this ResultSet to the pre-existing TableModel via some kind of "update" method, which would simply load or update the rows from the ResultSet.
这可能需要某种Map，该Map键入到该行的键"或"id"，因此您可以更轻松地对其进行更新.
This may require have some kind of Map which is keyed to the "key" or "id" of the row, so you can update it more easily.
我可能要考虑的事情之一就是提供行数据的POJO，不仅可以用来存储查找Map的行，还可以发现指定记录的模型的行索引.
One of things I might consider is providing an POJO of the row data, which you can use to not only store in you look up Map, but also discover the model's row index for the specified record.
这篇关于在ResultTableModel和数据库中插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
