Error when updating MySQL database using UPDATE - SET - WHERE method in Eclipse-CodePudding

I am making a program using Eclipse that allows the user to update the volume of chemicals everytime they’re restocked/used, which requires them to enter the ID of the chemical and the amount they would like to add/subtract. A query is then performed to search for the chemical's ID in the database, and its volume is updated accordingly.

However, I’m having difficulties getting the volume to update. I tried adapting MySQL’s UPDATE statement from this website to SET volume = volume amount added, WHERE chemical ID = ID entered by the user; however, there appears to be some syntax errors in my code, more specifically at the UPDATE - SET - WHERE line:

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();
    }
}

Since I'm still new to MySQL, can someone help me correct this? Thank you so much for your help!

CodePudding user response：

Your whole query is badly formatted. Change your code to this:

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

You cannot use ' single quotes when defining Column names in queries. Single quotes are used for string values!

Still, this would not be the best way to do this. use PreparedStatement!

This way:

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();

If you need to read more for PreparedStatement there are a lot of great resources out there. They also protect against SQL injections.

CodePudding user response：

I think your problem might be with the "rs.getInt(Volume)"

Yours:

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

Can you try this:

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