java - 如何使用MySQL数据库正确更新JTable数据？

Question

我正在开发一个连接到 MySQL 数据库的 Swing 应用程序。它从数据库中获取一些数据并显示在表中。此外，还有 3 个按钮，用于更改表数据(添加行到表)、更新数据库(更新数据库)和放弃更改(丢弃更改)，如下所示，

单击向表中添加行 按钮后，表单中的新条目应添加到表中。之后如果点击更新按钮，数据将在数据库中更新。 Discard changes会删除表最后一行的数据，与db无关。该应用程序有两个类 CoffeesFrame.java 和 CoffeesTableModel.java 我提供了以下两个类的示例代码，

public class CoffeesFrame extends JFrame implements RowSetListener {

    private static final long serialVersionUID = 1L;

    private static Connection myConn = null;
    private static String url = "jdbc:mysql://localhost:3306/myDemo";
    private static String user = "student";
    private static String password = "student";

    // initiate a table object 
   JTable table; 

// labels of the table 

// text data fields of the table 

// buttons of the table 


// initiate a table model object
CoffeesTableModel myCoffeesTableModel;

// table constructor 
public CoffeesFrame(Connection myConn) throws SQLException {


    table = new JTable();       
    createNewTableModel(myConn);

    /*
       label, text field and buttons 
    */

    Container contentPane = getContentPane();
    contentPane.setComponentOrientation(ComponentOrientation.LEFT_TO_RIGHT);
    contentPane.setLayout(new GridBagLayout());
    GridBagConstraints c = new GridBagConstraints();

    /*
    Code for making the GUI
    */



    button_ADD_ROW.addActionListener(new ActionListener() {

        public void actionPerformed(ActionEvent e) {

            // showMessageDialog works 
            JOptionPane.showMessageDialog(CoffeesFrame.this, new String[] {

                    "Adding the following row:",
                    "Coffee name: [" + textField_COF_NAME.getText() + "]",
                    "Supplier ID: [" + textField_SUP_ID.getText() + "]",
                    "Price: [" + textField_PRICE.getText() + "]",
                    "Sales: [" + textField_SALES.getText() + "]",
                    "Total: [" + textField_TOTAL.getText() + "]" });

            try {

                // Insert row is not adding data to the table 
                myCoffeesTableModel.insertRow(
                        textField_COF_NAME.getText(),
                        Integer.parseInt(textField_SUP_ID.getText().trim()),
                        Float.parseFloat(textField_PRICE.getText().trim()),
                        Integer.parseInt(textField_SALES.getText().trim()),
                        Integer.parseInt(textField_TOTAL.getText().trim()));

                // table.getModel();
            }

            catch (Exception ex) {

                ex.printStackTrace();
            }
        }

    });


    button_UPDATE_DATABASE.addActionListener(new ActionListener() {

        public void actionPerformed(ActionEvent e) {

            try {

                myCoffeesTableModel.coffeesRowSet.acceptChanges(myConn);
            }

            catch (Exception ex) {

                ex.printStackTrace();
            }


            try {

                createNewTableModel(myConn);
            }

            catch (Exception el) {

                el.printStackTrace();
            }

        }
    });


    button_DISCARD_CHANGES.addActionListener(new ActionListener() {

        public void actionPerformed(ActionEvent e) {

            System.out.println("The changes are DISCARDED");
        }
    });
}



private void createNewTableModel(Connection myConn) throws SQLException {

    myCoffeesTableModel = new CoffeesTableModel(getContentsOfCoffeesTable(myConn));
    myCoffeesTableModel.addEventHandlersToRowSet(this);
    table.setModel(myCoffeesTableModel);
  }

@Override
public void rowSetChanged(RowSetEvent event) {

    // TODO Auto-generated method stub
}

@Override
public void rowChanged(RowSetEvent event) {

    // TODO Auto-generated method stub
}

@Override
public void cursorMoved(RowSetEvent event) {

    // TODO Auto-generated method stub

}

public CachedRowSet getContentsOfCoffeesTable(Connection mycConn)
        throws SQLException {

    CachedRowSet crs = null;
    ResultSet resultSet = null;
    Statement stmt = null;
    String sql = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";

    try {

        stmt = myConn.createStatement();
        resultSet = stmt.executeQuery(sql);

        crs = new CachedRowSetImpl();
        crs.populate(resultSet);

    }

    catch (Exception e) {

        e.printStackTrace();
    }

    return crs;
}


public static void main(String[] args) throws SQLException {

    try {

        myConn = DriverManager.getConnection(url, user, password);

        if (myConn != null) {

            System.out.println("Connected to the database myDemo");
        }
    }

    catch (SQLException ex) {

        System.out
                .println("An error occurred. Maybe user/password is invalid");
        ex.printStackTrace();
    }

    myConn.setAutoCommit(false);

    CoffeesFrame coffeesFrame = new CoffeesFrame(myConn);
    coffeesFrame.pack();
    coffeesFrame.setVisible(true);

}

CoffeesTableModel.java 如下，//

public class CoffeesTableModel implements TableModel {

CachedRowSet coffeesRowSet; // The ResultSet to interpret
ResultSetMetaData metadata; // Additional information about the results
int numcols, numrows; // How many rows and columns in the table


public CoffeesTableModel(CachedRowSet rowSetArg) throws SQLException {

    this.coffeesRowSet = rowSetArg;
    this.metadata = this.coffeesRowSet.getMetaData();
    numcols = metadata.getColumnCount();

    // Retrieve the number of rows.
    this.coffeesRowSet.beforeFirst();
    this.numrows = 0;

    while (this.coffeesRowSet.next()) {

        this.numrows++;
    }

    this.coffeesRowSet.beforeFirst();
}

public CachedRowSet getCoffeesRowSet() {

    return coffeesRowSet;
}

public void addEventHandlersToRowSet(RowSetListener listener) {

    this.coffeesRowSet.addRowSetListener(listener);
}

public void insertRow(String coffeeName, int supplierID, float price,
        int sales, int total) throws SQLException {

    try {

        this.coffeesRowSet.moveToInsertRow();
        this.coffeesRowSet.updateString("COF_NAME", coffeeName);
        this.coffeesRowSet.updateInt("SUP_ID", supplierID);
        this.coffeesRowSet.updateFloat("PRICE", price);
        this.coffeesRowSet.updateInt("SALES", sales);
        this.coffeesRowSet.updateInt("TOTAL", total);
        this.coffeesRowSet.insertRow();
        this.coffeesRowSet.moveToCurrentRow();
    }

    catch (SQLException e) {

        e.printStackTrace();
        // JDBCTutorialUtilities.printSQLException(e);
    }
}

public void close() {

    try {

        coffeesRowSet.getStatement().close();
    }

    catch (SQLException e) {

        e.printStackTrace();
        // JDBCTutorialUtilities.printSQLException(e);
    }
}

/** Automatically close when we're garbage collected */
protected void finalize() {

    close();
}

/** Method from interface TableModel; returns the number of columns */

public int getColumnCount() {

    return numcols;
}

/** Method from interface TableModel; returns the number of rows */
public int getRowCount() {

    return numrows;
}

/**
 * Method from interface TableModel; returns the column name at columnIndex
 * based on information from ResultSetMetaData
 */

public String getColumnName(int column) {

    try {

        return this.metadata.getColumnLabel(column + 1);
    } 

    catch (SQLException e) {

        return e.toString();
    }
}


/**
 * Method from interface TableModel; returns the most specific superclass
 * for all cell values in the specified column. To keep things simple, all
 * data in the table are converted to String objects; hence, this method
 * returns the String class.
 */

public Class getColumnClass(int column) {

    return String.class;
}

/**
 * Method from interface TableModel; returns the value for the cell
 * specified by columnIndex and rowIndex. TableModel uses this method to
 * populate itself with data from the row set. SQL starts numbering its rows
 * and columns at 1, but TableModel starts at 0.
 */

public Object getValueAt(int rowIndex, int columnIndex) {

    try {

        this.coffeesRowSet.absolute(rowIndex + 1);
        Object o = this.coffeesRowSet.getObject(columnIndex + 1);

        if (o == null)
            return null;

        else
            return o.toString();
    } 

    catch (SQLException e) {

        return e.toString();
    }       
}

/**
 * Method from interface TableModel; returns true if the specified cell is
 * editable. This sample does not allow users to edit any cells from the
 * TableModel (rows are added by another window control). Thus, this method
 * returns false.
 */

public boolean isCellEditable(int rowIndex, int columnIndex) {

    return false;
}

// Because the sample does not allow users to edit any cells from the
// TableModel, the following methods, setValueAt, addTableModelListener,
// and removeTableModelListener, do not need to be implemented.

public void setValueAt(Object value, int row, int column) {

    System.out.println("Calling setValueAt row " + row + ", column "
            + column);
}

public void addTableModelListener(TableModelListener l) {

}

public void removeTableModelListener(TableModelListener l) {

}

}

表格中显示的数据是从数据库中抓取并以表格形式呈现的。 “向表格添加行”显示表单插入的弹出窗口，但不要将数据作为新行放在表格的末尾。但是，我没有收到任何错误。 “更新数据库”按钮不起作用，并提供 java.sql.SQLException: Table not specified.。我怎样才能改进代码？

Answer 1

您应该从 AbstractTableModel 扩展而不是实现 TableModel 接口(interface)，它具有一些您不想使用的“正常”功能的默认功能复制。

删除 addTableModelListener 和 removeTableModelListener，您当前的实现基本上破坏了模型的契约，因为它没有提供向其他观察者发出通知的能力(例如 JTable )

您的insertRow 方法必须调用fireTableRowsInserted (来自 AbstractTableModel)以便通知 JTable 模型已更改并且需要使用新数据更新自身

Java 不保证 finalize 会被调用，不要依赖它

getRowCount 将受 RowSet 大小的影响，因此，您需要能够保持运行值(即您需要在您需要时增加它添加新行)或从 RowSet 本身计算行数。我相信你可以通过将光标移动到末尾(或超出最后一个位置)并使用 getRow 来做到这一点，不过不要忘记，ResultSet 是 1基于 ，而不是基于 JTable 期望的 0