在Eclipse中使用UPDATE - SET - WHERE方法更新MySQL数据库时出错

Question

我正在使用Eclipse编写一个程序，允许用户在每次重新进货/使用时更新化学品的体积，这要求他们输入化学品的ID以及他们想要添加/减去的数量。然后执行查询以在数据库中搜索该化学品的ID，并相应地更新其体积。

但是，我在更新音量时遇到了困难。我尝试从this website修改MySQL的UPDATE语句，以设置volume = volume + amount added，其中化学ID = ID由用户输入；但是，我的代码中似乎存在一些语法错误，更具体地说，在UPDATE - SET - WHERE行：

public void IDEnter() {
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:8889/StockControlSystem","root","root");
        Statement stmt = con.createStatement();
        String sql = "Select * from Chemicals where `Chemical ID` ='" + txtChemical_ID.getText()+"'";
        ResultSet rs = stmt.executeQuery(sql);
        if(rs.next()) {
            stmt.executeUpdate("UPDATE Chemicals" + "SET `Volume` = rs.getInt(Volume) + Integer.parseInt(AmountAdded.getText()) WHERE `Chemical ID` in (txtChemical_ID.getText())");
        }
        else {
            JOptionPane.showMessageDialog(null, "Invalid chemical ID");
            txtChemical_ID.setText(null);
        }
    } catch(Exception exc) {
        exc.printStackTrace();
    }
}

因为我还是个MySQL新手，有人能帮我改正这个错误吗？非常感谢你的帮助！

Answer 1

您的整个查询的格式都很糟糕。将您的代码更改为：

stmt.executeUpdate("UPDATE Chemicals SET Volume = " +
 rs.getInt(Volume) + Integer.parseInt(AmountAdded.getText())
 + " WHERE Chemical_ID in (" + txtChemical_ID.getText() +  ")");

在查询中定义列名时，不能使用'单引号。字符串值使用单引号！

尽管如此，这不是最好的方法。使用PreparedStatement！

这样：

String updateString = "UPDATE Chemicals SET Volume = ? WHERE Chemical_ID in (?)"; // Creation of the prepared statement, the ? are used as placeholders for the values
PreparedStatement preparedStatement = con.prepareStatement(updateString);
preparedStatement.setInt(1, rs.getInt(Volume) + Integer.parseInt(AmountAdded.getText())); // Setting the first value
preparedStatement.setString(2, txtChemical_ID.getText()); // Setting the second. I am supposing that this txtChemical_ID textField has values seperated by commas, else this will not work!
preparedStatement.executeUpdate();

如果你需要阅读更多关于PreparedStatement的文章，有很多很棒的resources。它们还可以防止SQL注入。

Answer 2

我想你的问题可能是“rs.getInt(音量)”

你的：

    "UPDATE Chemicals" + "SET `Volume` = rs.getInt(Volume)
+ Integer.parseInt(AmountAdded.getText()) 
WHERE `Chemical ID` in (txtChemical_ID.getText())"

你能试试这个吗：

 "UPDATE Chemicals" + "SET `Volume` = " + 
Integer.parseInt(AmountAdded.getText()) + " 
WHERE `Chemical ID` in (" + (txtChemical_ID.getText()) +")"