Ad:

JSP Discussion Thread
Unsolved
Views: 1592

Sep 19th, 2009

Stock Levels

Expand Post »

Hello good citizens,
Am trying to implement a simple web based shopping system, however I want to find out if there is a way in which i can reduce the quantity of goods in stock after customer buys just by using servlets without me having to go to the database to change the stock levels.

Any feedback would be appreciated.

Similar Threads

Apple Stock Suffering From PR Debacle in Apple Hardware and Devices
Restarting Application in C++
Need suggestion on Inventory System database design in Database Design
Iinformation systems case study entities and events?? in Computer Science
final year project advice in Oracle

timon_zed

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Apr 2008

Permalink

Sep 20th, 2009

-1

Re: Stock Levels

In similar fashion as you query database to see what is available in stock you can link your customer purchase order action(on any website seen as button to "Confirm" after you provided delivery address and payment details) to a method in servlet to take data stored in order form to construct UPDATE query. Query will decrement you stock by certain amount, this would need to be done with sub-queries (query inside query) plus in same time you should also create new entry for new purchase order in different set of tables.
If you are more experienced with database you can even set up procedure for this sort of task.

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 871

Code tags enforcer

Offline

6,654 posts
since Dec 2004

Permalink

Sep 20th, 2009

Re: Stock Levels

Don't update or subtract values. Use aggregate sql functions.
For example,
1. Calculate the sum of quantity of a specific product in your purchase
table.
2. Calculate the sum of quantity of a specific product in your sales table.
3. Current stock = Sum of qty of purchase - Sum of qty of sales.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Sep 20th, 2009

-1

Re: Stock Levels

Click to Expand / Collapse Quote originally posted by adatapost ...

Don't update or subtract values. Use aggregate sql functions.
For example,
1. Calculate the sum of quantity of a specific product in your purchase
table.
2. Calculate the sum of quantity of a specific product in your sales table.
3. Current stock = Sum of qty of purchase - Sum of qty of sales.

Can you provide clearer explanation as I do understand that aggregate functions are

AVG() - Returns the average value
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum

and in my understanding these are used for calculation and not changing existing value in the table.

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 871

Code tags enforcer

Offline

6,654 posts
since Dec 2004

Permalink

Sep 20th, 2009

Re: Stock Levels

I apologize for inconvenience.
What I said,
Stock = Sum of quantity of purchased item - Sum of quantity of sold item.

EDIT: I suggest OP that don't update (add or subtract) total quantity.

Last edited by adatapost; Sep 20th, 2009 at 11:42 am.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Sep 20th, 2009

Re: Stock Levels

Click to Expand / Collapse Quote originally posted by adatapost ...

I apologize for inconvenience.
What I said,
Stock = Sum of quantity of purchased item - Sum of quantity of sold item.

EDIT: I suggest OP that don't update (add or subtract) total quantity.

I get the jist of what you're saying adatapost, so would this mean that i would have to add an update query within the same servlet that is processing the purchases made by customers?

Quote ...

I love to learn but I hate being taught - Winston Churchill

timon_zed

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Apr 2008

Permalink

Sep 20th, 2009

Re: Stock Levels

Here's something i came up with....

JSP Syntax (Toggle Plain Text)
<form name="purchase" action="Purchase" method="POST">
            <table border="1">
                <thead>
                    <tr>
                        <th></th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>Product Number</td>
                        <td><input type="text" name="ProdNum" value="" maxlength="7" /></td>
                    </tr>
                    <tr>
                        <td>Quantity</td>
                        <td><input type="text" name="quantity" value="" /></td>
                    </tr>
                    <tr>
                        <td><input type="reset" value="Clear" name="clear" /></td>
                        <td><input type="submit" value="Purchase" name="purchase" /></td>
                    </tr>
                </tbody>
            </table>
 
        </form>
<form name="purchase" action="Purchase" method="POST">
            <table border="1">
                <thead>
                    <tr>
                        <th></th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>Product Number</td>
                        <td><input type="text" name="ProdNum" value="" maxlength="7" /></td>
                    </tr>
                    <tr>
                        <td>Quantity</td>
                        <td><input type="text" name="quantity" value="" /></td>
                    </tr>
                    <tr>
                        <td><input type="reset" value="Clear" name="clear" /></td>
                        <td><input type="submit" value="Purchase" name="purchase" /></td>
                    </tr>
                </tbody>
            </table>

        </form>

The associated servlet code sample...

JSP Syntax (Toggle Plain Text)
String ProdID = null;
        String ProdName = null;
        String Price1 = null;
        String Num = null;
        int cost,amt,value;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:Concept","admin","");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery ("SELECT ProductID, PName, Price FROM Stock");
 
            while(rs.next())
            {
                ProdID = rs.getString("ProductID");
                if(ProdID.equals(request.getParameter("ProdNum")))
                {
                    ProdName = rs.getString("PName");
                    Price1 = rs.getString("Price");
                    value = Integer.parseInt(Price1);
                    Num = request.getParameter("quantity");
                    amt = Integer.parseInt(Num);
                    cost = amt*value;
                    out.print(ProdID +'\t');
                    out.print(ProdName + '\t');
                    out.print(Num + '\t');
                    out.print("K "+ cost);
                    out.println('\t'+"<a href = purchase.jsp>Back</a>");
                }
            }
            con.close();
            rs.close ();
            st.close ();
        }
        catch(Exception e)
        {
            System.out.print("Unable to find DB");
        }
    }
String ProdID = null;
        String ProdName = null;
        String Price1 = null;
        String Num = null;
        int cost,amt,value;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:Concept","admin","");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery ("SELECT ProductID, PName, Price FROM Stock");
            
            while(rs.next())
            {
                ProdID = rs.getString("ProductID");
                if(ProdID.equals(request.getParameter("ProdNum")))
                {
                    ProdName = rs.getString("PName");
                    Price1 = rs.getString("Price");
                    value = Integer.parseInt(Price1);
                    Num = request.getParameter("quantity");
                    amt = Integer.parseInt(Num);
                    cost = amt*value;
                    out.print(ProdID +'\t');
                    out.print(ProdName + '\t');
                    out.print(Num + '\t');
                    out.print("K "+ cost);
                    out.println('\t'+"<a href = purchase.jsp>Back</a>");
                }
            }
            con.close();
            rs.close ();
            st.close ();
        }
        catch(Exception e)
        {
            System.out.print("Unable to find DB");
        }
    }

What i need now is to find a way in which i can update the DB table by reducing the stocks of the purchased comodity just using this same servlet. Or do i need to write another servlet that would do that for me?

timon_zed

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Apr 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in JSP Forum Timeline: Adding a submit button to each row generated

Next Thread in JSP Forum Timeline: Prb with jsp and how to delete multiple rows

DaniWeb Message

Cancel Changes

User Name
Password