I am trying to subtract the number entered into the textfield from the database but not sure how to do it

Question

I have a stock system where you can add a item to the system, increase the amount of different items, decrease the amount of different items in a system and completely delete an item from the system.

What I am trying to do is when the user inputs the name of a item in the first textbox and a number in the second textbox when they press remove the number they entered into the second textbox will be removed from the numberinstock variable in the database.

Here is my code for the remove button:

 private void RemoveButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             

    String url = "jdbc:derby://localhost:1527/Customers";
    String username = "username";
    String password = "password";

    try {
        Connection conn = DriverManager.getConnection(url, username, password);
        if (conn != null) {
            System.out.println("Connected");

            String sql = "UPDATE STOCK\n"
            + "SET NUMBERINSTOCK = NUMBERINSTOCK -"+RemoveTextField1.getText()+" \n"
            + "WHERE UPPER(name)=?";

            PreparedStatement statement = conn.prepareStatement(sql);

            statement.setString(1, RemoveTextField1.getText());
            statement.setString(2, RemoveAnItemTextField.getText().toUpperCase());

            int rowsUpdated = statement.executeUpdate();

            if (rowsUpdated > 0)
            {
                JOptionPane.showMessageDialog(null,"That item has been updated successfully");
            }
            else
            {
                JOptionPane.showMessageDialog(null,"That item is either not in the database or you have entered the wrong information");
            }
        }
    }catch (SQLException ex)
    {
        JOptionPane.showMessageDialog(null,ex.getMessage());
    }
      ResultSet rs = null;
         PreparedStatement ps = null;
      try{
       Connection conn = DriverManager.getConnection(url, username, password);
       ps = conn.prepareStatement("select * from Stock");
       rs = ps.executeQuery();

       SearchTable.setModel(DbUtils.resultSetToTableModel(rs));
    }catch(Exception ex){
        JOptionPane.showMessageDialog(null, ex.getMessage());
    }
}                                            

What this code does is it connects to the database, there is then an update statement that will update the database once the remove button is pressed,I had so that when the user entered the name of an item it would remove one from that item in that database, but I then realized that someone may want to remove more than one item at a time. This is where I am stuck, I am not sure how to get the number the user enters in the second textbox to be removed from the database once the remove button is pressed

Answer 1

Your code has many, many problems.

First, this is how you would code the PreparedStatement:

    String sql = "UPDATE STOCK\n"
            + "SET NUMBERINSTOCK = NUMBERINSTOCK - ? \n"
            + "WHERE UPPER(name) = ?";

    PreparedStatement statement = conn.prepareStatement(sql);
    int count = Integer.valueOf(RemoveTextField1.getText());
    statement.setInt(1, count);
    statement.setString(2, RemoveAnItemTextField.getText().toUpperCase());

I didn't do any error checking of the RemoveTextField1, which I'm assuming holds the item count. You'll need to check that an integer was typed in the field.

Second, variable names in Java start with a lower case letter. Method names start with a lower case letter. Class names start with an upper case letter. The JTextField name should be removeItemCountTextField. This is a much more descriptive name, which makes reading the code and debugging easier.

Third, separate your code and your concerns. You should have GUI code that just deals with the GUI, model code that contains the information from the database and the GUI, and database code that does nothing but transfer data from the model to the database and the database to the model. In how many different places in your code are you connecting to and disconnecting from the database? The answer should be one.