Order Entry System 1: JSP, Servlet, Session Bean, JQuery

In the following series, a typical web based order entry application will be discussed by using JavaSerer Pages, Java Servlets, JQuery and Java Beans (both data bean and command bean). It targets on any java based web server and relational DBMS althought it has been tested on Glassfish Open Source Server and MySQL database server only.

The series consist of three parts:

In this first part, I will discuss the component web flow chart and the starting page control first . Then, I will focus on the model part of MVC design pattern: its database tables (persistence) and its mapped memory structures Java Data Bean (volatile) .

Web Site Flow Chart

Order Entry System Web Site Flow Chart

Order Entry System Web Site Flow Chart

The order entry web application will use the list.jsp (Java Servlet and JSP Web Application 2) as the starting point. Just add the following tag onto list.jsp:

….

            <td>
               <a href="OrderServlet?id=<%= rs.getString(1) %>">
                 Order
               </a>
             </td>

….

The order entry web application will perform the following functions:

  • allow user to choose the item from inventory list and enter the quantity;
  • calculate the GST and total price on the page;
  • collect the customer information of the order;
  • save the information to database, adjust the stock quantity and generate the customer id and order id;
  • display order id once the order entry completed.

Data Model

************************************
* database model                    *
* DDL: Database Definition Language *
*************************************/
use test;

CREATE TABLE item (
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 description VARCHAR(30) NOT NULL,
 price DOUBLE NOT NULL,
 stock INTEGER NOT NULL) engine = InnoDB;;

CREATE TABLE customer (
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 address VARCHAR(200) NOT NULL,
 tel VARCHAR(15) NOT NULL,
 email VARCHAR(100) NOT NULL) engine = InnoDB;;

CREATE TABLE orders (
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 itemid INTEGER NOT NULL,
 saleprice DOUBLE NOT NULL,
 gst DOUBLE NOT NULL,
 qty INTEGER NOT NULL,
 customerid INTEGER NOT NULL,
 status ENUM('RECEIVED','PROCESSED','SHIPPED','COMPLETED') DEFAULT 'RECEIVED',
 orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)
 engine = InnoDB;

ALTER TABLE item ADD CONSTRAINT chk_stock CHECK (stock &gt;=0);
/*******************************************************
* TRIGGER: the deduct the order qty from the stock qty *
********************************************************/

CREATE TRIGGER itemupdate BEFORE INSERT ON orders FOR EACH ROW
UPDATE item SET stock = stock - NEW.qty WHERE item.id=NEW.itemid;

OrderServlet.java

The servlet retrieves the selected item from the database based on an order id, and passed the result set to OrderEntry.jsp.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet(name = "OrderServlet", urlPatterns = {"/OrderServlet"})
public class OrderServlet extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * &lt;code&gt;GET&lt;/code&gt; and
     * &lt;code&gt;POST&lt;/code&gt; methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("jdbc/MySQLPool");

            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("select * from item where id="+ request.getParameter("id"));
            request.setAttribute("rs", rs);
            // activate List.jsp
            getServletContext().getRequestDispatcher("/OrderEntry.jsp").
            forward(request, response);

            stmt.close();
            conn.close();

        } catch (Exception e) {
            //e.printStackTrace();
        } finally {           
        }
    }

    // &lt;editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code."&gt;
    /**
     * Handles the HTTP
     * &lt;code&gt;GET&lt;/code&gt; method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * &lt;code&gt;POST&lt;/code&gt; method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// &lt;/editor-fold&gt;
}

OrderBean.java

This bean is a memory structure with a scope of session to hold the order data chosen or entered by user from the OrderEntry.jsp.

package mybean;

public class OrderBean {
    int itemid;
    String itemname;
    double saleprice;
    double gst;
    int qty;
    public OrderBean () { }
    public void setItemid ( int value )
    {
        itemid = value;
    }
    public void setItemname ( String value )
    {
        itemname = value;
    }
    public void setSaleprice ( double value )
    {
        saleprice = value;
    }
     public void setGst ( double value )
    {
        gst = value;
    }
     public void setQty ( int value )
    {
        qty = value;
    }
    public int getItemid() { return itemid; }
    public String getItemname() { return itemname; }
    public double getSaleprice() { return saleprice; }
    public double getGst() { return gst; }
    public int getQty() { return qty; }
}</pre>
<h3>CustomerBean.java</h3>
This bean is a memory structure with a scope of session to hold the customer data entered by user from the OrderCustomer.jsp.
<pre>package mybean;

public class CustomerBean {
    String name;
    String address;
    String tel;
    String email;

    public CustomerBean () {}

    public void setName( String value )
    {
        name = value;
    }
    public void setAddress( String value )
    {
        address = value;
    }
    public void setTel( String value )
    {
        tel = value;
    }
    public void setEmail( String value )
    {
        email = value;
    }

    public String getName() { return name; }
    public String getAddress() { return address; }
    public String getTel() { return tel; }
    public String getEmail() { return email; }
}

About henry416
I am a computer technology explorer and an university student based on Toronto. If you have any question, please feel free to discuss and comment here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s