java - MySQL获取选中行数据并更新的方法

Question

我试图通过单击按钮仅更改 1 列和行，并根据字符串更新 mySQL。我设法编写了一个代码，但它将整个列更改为字符串而不是选定的行。我试图仅更改所选行的值。我怎样才能做到这一点？

private void jButtonBorrowActionPerformed(java.awt.event.ActionEvent evt) {                                              
        // TODO add your handling code here:

    DefaultTableModel model = (DefaultTableModel) jTableBooks1.getModel();
    int row = jTableBooks1.getSelectedRow();

    String notavail = model.getValueAt(row, 2).toString();
    notavail = "Not Available";

    PreparedStatement ps;  
    String query = "UPDATE `Books` SET `Availability`=?";      

    try {
        ps = dbConnect.getConnection().prepareStatement(query);            
        ps.setString(1, notavail);
        ps.executeUpdate();

        JOptionPane.showMessageDialog(null, "Book Borrowed Successfully!");
        BookMenu bm = new BookMenu();
        bm.setVisible(true);
        bm.setLocationRelativeTo(null);
        this.dispose();
    } catch (SQLException ex) {
        Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
    }
}       

public ArrayList<Books> booksList(){
    ArrayList<Books> booksList = new ArrayList<>();
    PreparedStatement ps;
    ResultSet rs;

    String query = "SELECT * FROM `Books`";
    try {
        ps = dbConnect.getConnection().prepareStatement(query);
        rs = ps.executeQuery();
        Books book;

        while (rs.next()){
            book = new Books(rs.getString("author"),rs.getString("bookname"),rs.getString("ISBN"), rs.getString("Course"),rs.getString("Year"),rs.getString("Availability"));
            booksList.add(book);
        }

    } catch (SQLException ex) {
        Logger.getLogger(Register.class.getName()).log(Level.SEVERE, null, ex);
    }

    return booksList; 
}

public void showBooks(){
    ArrayList<Books>list = booksList();
    DefaultTableModel model = (DefaultTableModel)jTableBooks1.getModel();
    Object[] row = new Object[6];

    for(int i=0;i<list.size();i++){
        row[0] =list.get(i).getAuthor();
        row[1] =list.get(i).getBookname(); 
        row[2] = list.get(i).getAvailability();
        model.addRow(row);
    }

如上所述，我只想更改所选行中的数据。附件是一张图片，说明它是如何以及我希望它如何。

Answer 1

String query = "UPDATE Books SET Availability = ?";

您的更新查询中需要一个 WHERE 子句，否则所有行都会更新。

假设书名和作者可以用作主键，这可能会完成工作:

String author = model.getValueAt(row, 0).toString();
String bookname = model.getValueAt(row, 1).toString();
String query = 
    "UPDATE Books SET availability = ? WHERE author = ? AND bookname = ?";
try {
    ps = dbConnect.getConnection().prepareStatement(query);
    ps.setString(1, notavail);
    ps.setString(2, author);
    ps.setString(3, bookname);
    ...