java - 将一行插入 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);

   }
 }

我的主要类(class)如下:

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;

 }

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

问题:当我单击保存来保存编辑时，数据仅发送到数据库，而不是发送到表模型中。

我希望在保存到数据库后，数据也被插入到模型中，并且这也应该反射(reflect)在显示屏上。

问题:我们如何让它按照我上面建议的方式工作？另外添加一行后应该如何发生同样的情况？

我已经尝试过

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

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

Answer 1

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

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

这可能需要某种Map，它以行的“key”或“id”为键，以便您可以更轻松地更新它。

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