sparkles sparkles - 1 year ago 67
Java Question

update item total in database

i'm having a little trouble with increasing the quantity of a product, when it is purchased, in a database.

I was told to look at this addProduct() method to see how I might go about writing the purchased(int quantityPurchased) method.

Here is that code:

public void addProduct(String desc, int qty, double price) throws SQLException {
try (Connection conn = SimpleDataSource.getConnection()) {
try (PreparedStatement stat = conn.prepareStatement(
"INSERT INTO ProductsDB (Product_Code, Description, Quantity, Price) VALUES (?, ?, ?, ?)")) {
stat.setString(1, productCode);
stat.setString(2, desc);
stat.setInt(3, qty);
stat.setDouble(4, price);

and this is what I'm supposed to finish:

* Increases the quantity of product when we've purchased products to
* replenish our supply.
* @param number
* the count of products purchased.
* @throws SQLException
* - on any database error
public void purchased(int qtyPurchased) throws SQLException {
// TODO: Update the ProductsDB table's quantity for this
// object's product code.


Answer Source

First off, if you are updating the same table you need to know what product you are updating a quantity for. in that respect you need another parameter in your purchased() method so it will be like: purchased(string productCode, int qtyPurchased).

After this you need to write another prepared statement that updates that table with the new value for the product.

Have a look at this:

        //Added another parameter for the method that takes the product code.
        public void purchased(string productCode, int qtyPurchased) throws SQLException 
            try (Connection conn = SimpleDataSource.getConnection()) 
                //Updated prepared statement to update a product row instead of inserting a new one using the specified product code.
                try (PreparedStatement stat = conn.prepareStatement("UPDATE ProductsDB SET Quantity = ? WHERE Product_Code = ?") 
                    //Update the values used in the prepared statement
                    stat.setInt(1, qtyPurchased);
                    stat.setString(2, productCode);

                    //Execute the statement (important)

Further reading here:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download