Click here to Skip to main content
15,887,822 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
i am working on project where for example admin has quantity coloumn as 10 then when customer enters 9 quantity it has to update in admin that quantity left over is 1 i mean it has to be subtracted..can you tell me the code in servlets using database connection.
Posted
Comments
ZurdoDev 5-Sep-13 13:25pm    
Do you know Oracle? It will be something like UPDATE table SET field1 = value - 1
Member 10255420 5-Sep-13 13:31pm    
suppose value what customer entered is 10 , admin table value is 12,if i give UPDATE table SET field1 = value - 1,value will be 11 not 2.
ZurdoDev 5-Sep-13 13:41pm    
Again, do you know Oracle? Update table1 SET field1 = (SELECT value FROM table WHERE field = 'admin') -- Something like that.
Member 10255420 5-Sep-13 13:44pm    
ya i know .i use oracle for database connection let me try it out.
Shubhashish_Mandal 14-Sep-13 5:14am    
Update table1 SET field1 = (SELECT current value of field1) - New Value Entered

1 solution

Firstly do not build the SQL as a string, that is just asking for trouble.

Try something like this:

Java
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateInventory
{
    // set this to some connection protocol like "jdbc:sqlserver://"
    private final String protocol;
    // set this to the name of your database
    private final String dbName;
    private final String username;
    private final String password;
    private final Connection connection;

    public UpdateInventory(String protocol,
            String dbName,
            String username,
            String password)
                throws SQLException
    {
        this.protocol = protocol;
        this.dbName =dbName;
        this.username = username;
        this.password = password;
        this.connection = DriverManager.getConnection(protocol + dbName,
                username, password);
    }

    public int update(String item, int quantity)
            throws SQLException
    {
        // validate
        if (item == null ||
                "".equals(item))
        {
            throw new IllegalArgumentException("Item must be set");
        }
        if (quantity <= 0)
        {
            throw new IllegalArgumentException("Quantity must be greater than zero");
        }

        PreparedStatement updateInventory = this.connection.prepareStatement(
                "UPDATE inventory SET quantity = (quantity - ?) WHERE item = ?");

        updateInventory.setInt(1, quantity);
        updateInventory.setString(2, item);
        //there are similar methods for other SQL types in PerparedStatement

        return updateInventory.executeUpdate();//returns the number of rows changed
    }
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900