Trader ModelS2C Home « Trader Model

In this lesson we take a first look at the model part of the MVC paradigm and code the model component for the trader.

In the Project Proposal lesson we mentioned that data will be stored in MySQL database tables and accessed using JDBC and SQL. Information is retrieved from the MySQL database tables via embedded SQL statements within two Java Classes, these being TraderDBAccess and CustomerDBAccess.

In this lesson we will code the TraderDBAccess class which allows the trader to manipulate data within the stockitem, bootsalelocation and bootsalediary database tables and read registered user information from the registereduser database table.

Trader Database RequirementsTop

The trader requires database access for stock management, updating boot sale information so visitors can see what boot sales the trader is going to and when and for displaying a boot sale diary and marketing products to registered users via a customer report sent via email.

To fulfill these requirements of the trader we laid out functional requirements within the Project Proposal lesson which were mapped to database tables within the Data Tier section of that lesson.

In this lesson we break down the data mapping of functional requirements into methods that add/modify/delete/read from the required tables within the MySQL database. For this purpose we will look at the functional requirements again and decide what type of database access is required to fulfill the taks required by the trader.

Stock ManagementTop

With regards to stock management the trader will need to be able to do the following: -

  • Add stock item
    • Insert a record into the stockitem database table using mandatory data entered by the trader within the AddSI.jsp page along with defaults for the optional database fields.
  • Select stock items
    • Retrieve all records from the stockitem database table for selection by the trader within the SelectSI.jsp page.
  • Select stock item
  • Modify stock item
    • Update a record on the stockitem database table using mandatory and optional data entered by the trader within the ModifySI.jsp page along with defaults for optional database fields that have not been entered.
  • Delete stock item
  • Produce an inventory report of all stock
  • Produce a stock reorder report for stock items that are equal or less than a pre-specified stock level for that item
  • Produce a sales report of stock sold at last boot sale
  • Produce an out of stock report of stock items with a stock level of zero

Boot Sale InformationTop

With regards to boot sales the trader will need to be able to do the following :-

  • Add boot sale location
    • Insert a record into the bootsalelocation database table using mandatory data entered by the trader within the AddBSLoc.jsp page along with defaults for the optional database fields.
  • Select boot sale locations
  • Select boot sale location
  • Modify boot sale location
    • Update a record on the bootsalelocation database table using mandatory and optional data entered by the trader within the ModifyBSLoc.jsp page along with defaults for optional database fields that have not been entered.
  • Delete boot sale location
  • Add boot sale diary
  • Select boot sale diaries
  • Select boot sale diary
  • Modify boot sale diary
  • Delete boot sale diary

MarketingTop

With regards to marketing the trader will need to be able to do the following :-

Compiling The TraderDBAccess ClassTop

The TraderDBAccess class controls trader database access for all the applications Servlets and JSPs that pertain to trader functionality. This includes connecting and disconnecting from the bootsale database as well as all the SQL query strings and prepared statements required for JDBC connectivity.

Cut and paste the following code into your text editor and save it in the   c:\_ServletsJSP_Case_Study\bstrader\src\model directory as TraderDBAccess.java.


package model;

/**
 * Title:       TraderDBAccess
 * Description: Controls database access for the trader
 * name:        Kevin 
 */

import java.sql.*;
import java.util.*;

public class TraderDBAccess {
    String error; 
    String result = null;
    private Connection cn = null;

    public TraderDBAccess() {
    }

    public void connect() throws Exception {
        //Constructor sets up connection to the database
        String jdbcDriver = "com.mysql.jdbc.Driver";
        String protocolHeader = "jdbc:mysql://localhost:3306/";
        String dbname = "bootsale";
        String user = "root";
        String password = "root";
        try {
           // Load in the driver
           Class.forName(jdbcDriver);
           System.out.println("...Driver loaded");
           // Get a connection
           cn = DriverManager.getConnection(protocolHeader+dbname, user, password);
           System.out.println("...Connection established");
        } catch(Exception e) {
           {System.out.println("Problem opening the database"+e);}
           error = "Exception: Unknown error while connecting to Database." + e;
           throw new Exception(error);
        }
    }

    public void disconnect() throws Exception {
        System.out.println("...Terminating");
        // Close database connection
        try {
            cn.close();
            {System.out.println("...Connection closed");}
        } catch(Exception e) {
            {System.out.println("Problem closing connection");}
            error = "Exception: Unable to close Database connection." + e;
            throw new Exception(error);
        }
    }

    //
    // Stock Item Methods
    //

    // method to add a stock item record
    public void addSI(String stockname, String selectcat, String stockdesc,
            String stockprice, String stocklevel, String stockreorderlevel) throws SQLException {
        try {
            // Create a prepared statement
            int checkNum;
            String s = "INSERT INTO stockitem VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            PreparedStatement addStock = cn.prepareStatement(s);
            System.out.println("...Statement created to INSERT a stock item record");
            addStock.setString(1, stockname);
            addStock.setString(2, selectcat);
            addStock.setString(3, stockdesc);
            checkNum = Integer.parseInt(stockprice);
            addStock.setInt(4, checkNum);
            checkNum = Integer.parseInt(stocklevel);
            addStock.setInt(5, checkNum);
            checkNum = Integer.parseInt(stockreorderlevel);
            addStock.setInt(6, checkNum);
            checkNum = 0;
            addStock.setInt(7, checkNum);
            addStock.setString(8, " ");
            addStock.setString(9, " ");
            addStock.setString(10, " ");
            addStock.execute();
        } 
        catch (SQLException e) {
            error = "SQLException: add failed, possible duplicate Stock Item entered." + e;
            throw new SQLException(error);
        }
    }

    // method to modify a stock item record
    public void modifySI(String stockname, String selectcat, String stockdesc, 
            String stockprice, String stocklevel, String stockreorderlevel, 
            String stocksoldlastbootsale, String customername, String feedbacktext, 
            String feedbackrating) throws SQLException {
        try {
            // Create a prepared statement
            int checkNum, checkNum2, checkNum3, checkNum4;
            checkNum = Integer.parseInt(stockprice);
            checkNum2 = Integer.parseInt(stocklevel);
            checkNum3 = Integer.parseInt(stockreorderlevel);

            // set to zero if stocksoldlastbootsale had not been entered
            if (stocksoldlastbootsale == "") {
                checkNum4 = 0;
            }  else {
                checkNum4 = Integer.parseInt(stocksoldlastbootsale);
            }
            if (customername.equals("")) {
                customername = " ";
            }
            if (feedbacktext.equals("")) {
                feedbacktext = " ";
            }
            if (feedbackrating.equals("")) {
                feedbackrating = " ";
            }

            String s = "UPDATE stockitem SET stockitemcategory = '"+selectcat
                    +"', stockitemdescription = '" +stockdesc+"', stockprice = "+checkNum
                    +", stocklevel = "+checkNum2+", stockreorderlevel = " +checkNum3
                    +", stocksoldlastbootsale = "+checkNum4+", customername = '" +customername
                    +"',feedbacktext = '"+feedbacktext+"', feedbackrating = '" +feedbackrating
                    +"' WHERE stockname = '"+stockname+"'";

            PreparedStatement updStock = cn.prepareStatement(s);
            System.out.println("...Statement created to MODIFY a stock item record");
            System.out.println("...Statement = "+s);
            updStock.execute();
        }
        catch (SQLException e) {
            error = "SQLException: modify failed." + e;
            throw new SQLException(error);
        }
    }

    // method to delete selected stock item records
    public void deleteSI(String[] nameKeys) throws SQLException {
        try {
            // Create a prepared statement
            String s = ("DELETE FROM stockitem WHERE stockname=?;");
            PreparedStatement delete = cn.prepareStatement(s);
            System.out.println("...Statement created to delete selected stock item records");
            for (int i=0; i < nameKeys.length; i++) {
                delete.setString(1, nameKeys[i]);
                delete.execute();
            }
        }
        catch (SQLException e) {
            error = "SQLException: deletion of stock item records failed." + e;
            throw new SQLException(error);
        }
    }

    // method to retrieve a single stockitem record
    public ResultSet getSIRecord(String stockname) throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access bootsalelocation records
            String queryString = ("SELECT * FROM stockitem WHERE stockname = '"+stockname+"'");
            Statement st = cn.createStatement();
            System.out.println("...Statement created to access a stockitem record");
            rs = st.executeQuery(queryString);
        }
        catch (SQLException e) {
            error = "SQLException: fail reading stockitem database record." + e;
            throw new SQLException(error);
        }
        return rs;
    }

    // method to retrieve all stockitem records
    public ResultSet getSIFields() throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access bootsalelocation records
            String queryString = "SELECT * FROM stockitem ORDER BY stockname ASC;";
            Statement st = cn.createStatement();
            System.out.println("...Statement created to access stockitem records");
            rs = st.executeQuery(queryString);
        }
        catch (SQLException e) {
            error = "SQLException: fail reading stockitem database table." + e;
            throw new SQLException(error);
        }
        return rs;
    }

    //
    // Boot Sale Location Methods
    //

    // method to add a boot sale location record
    public void addBSLoc(String name, String addr1, String addr2, 
            String addr3, String addr4, String postcode) throws SQLException {
        try {
            // Create a prepared statement
            System.out.println("name and address: " + name +  addr1 + addr2 
                    + addr3 + addr4 + postcode);
            String s = "INSERT INTO bootsalelocation VALUES(?, ?, ?, ?, ?, ?);";
            PreparedStatement addBSLoc = cn.prepareStatement(s);
            System.out.println("...Statement created to INSERT a boot sale location record");
            addBSLoc.setString(1, name);
            addBSLoc.setString(2, addr1);
            addBSLoc.setString(3, addr2);
            addBSLoc.setString(4, addr3);
            addBSLoc.setString(5, addr4);
            addBSLoc.setString(6, postcode);
            addBSLoc.execute();
        }
        catch (SQLException e) {
            error = "SQLException: add failed, possible duplicate boot sale Location entered." + e;
            throw new SQLException(error);
        }
    }

    // method to modify a boot sale location record
    public void modifyBSLoc(String name, String addr1, String addr2, 
            String addr3, String addr4, String postcode) throws SQLException {
        try {
            // Create a prepared statement
            if (addr3.equals("")) {
                addr3 = " ";
            }
            if (addr4.equals("")) {
                addr4 = " ";
            }

            String s = "UPDATE bootsalelocation SET address1 = '" +addr1+"', address2 = '"
                    +addr2+"', address3 = '"+addr3+"', address4 = '"+addr4+"', postcode = '"
                    +postcode+"' WHERE name = '"+name+"'";
            System.out.println("...Statement = "+s);

            PreparedStatement updBSLoc = cn.prepareStatement(s);
            System.out.println("...Statement created to MODIFY a boot sale location record");
            updBSLoc.execute();
        }
        catch (SQLException e) {
            error = "SQLException: modify failed." + e;
            throw new SQLException(error);
        }
    }

    // method to delete selected boot sale location records
    public void deleteBSLoc(String[] nameKeys) throws SQLException {
        try {
            // Create a prepared statement
            String s = ("DELETE FROM bootsalelocation WHERE name=?;");
            PreparedStatement delete = cn.prepareStatement(s);
            System.out.println("...Statement created to delete selected boot sale location records");
            for (int i=0; i < nameKeys.length; i++) {
                delete.setString(1, nameKeys[i]);
                delete.execute();
            }
        }
        catch (SQLException e) {
            error = "SQLException: deletion of boot sale location records failed.";
            throw new SQLException(error);
        }
    }

    // method to retrieve a single bootsalelocation record
    public ResultSet getBSLocRecord(String locname) throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access a bootsalelocation record
            String queryString = ("SELECT * FROM bootsalelocation WHERE name = '"+locname+"'");
            Statement st = cn.createStatement();
            System.out.println("...Statement created to access a bootsalelocation record");
            rs = st.executeQuery(queryString);
        }
        catch (SQLException e) {
            error = "SQLException: fail reading bootsalelocation database record.";
            throw new SQLException(error);
        }
        return rs;
    }

    // method to retrieve all bootsalelocation records
    public ResultSet getBSLocFields() throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access bootsalelocation records
            String queryString = "SELECT * FROM bootsalelocation ORDER BY name ASC;";
            Statement st = cn.createStatement();
            System.out.println("...Statement created to access bootsalelocation records");
            rs = st.executeQuery(queryString);
        }
        catch (SQLException e) {
            error = "SQLException: fail reading bootsalelocation database table.";
            throw new SQLException(error);
        }
        return rs;
    }

    //
    // Boot Sale Diary Methods
    //

    // method to add a boot sale diary record
    public void addBSDiary(String selectdate, String selectname)
            throws SQLException {
        try {
            // Create a prepared statement
            String s = "INSERT INTO bootsalediary VALUES(?, ?);";
            PreparedStatement addDiary = cn.prepareStatement(s);
            System.out
                    .println("...Statement created to INSERT a boot sale diary record");
            addDiary.setString(1, selectdate);
            addDiary.setString(2, selectname);
            addDiary.execute();
        } catch (SQLException e) {
            throw new SQLException(e);
        }
    }

    // method to modify a boot sale diary record
    public void modifyBSDiary(String date, String name) throws SQLException {
        try {
            // Create a prepared statement
            String s = "UPDATE bootsalediary SET name = '" + name
                    + "' WHERE date = '" + date + "'";

            PreparedStatement updBSDiary = cn.prepareStatement(s);
            System.out
                    .println("...Statement created to MODIFY a boot sale diary record");
            updBSDiary.execute();
        } catch (SQLException e) {
            error = "SQLException: modify failed.";
            throw new SQLException(error);
        }
    }

    // method to delete selected boot sale diary records
    public void deleteBSDiary(String[] dateKeys) throws SQLException {
        try {
            // Create a prepared statement
            String s = ("DELETE FROM bootsalediary WHERE date=?;");
            PreparedStatement delete = cn.prepareStatement(s);
            System.out
                    .println("...Statement created to delete selected boot sale diary records");
            for (int i = 0; i < dateKeys.length; i++) {
                String dateYYYYMMDD = dateKeys[i].substring(6, 10) + "/"
                        + dateKeys[i].substring(3, 5) + "/"
                        + dateKeys[i].substring(0, 2);
                delete.setString(1, dateYYYYMMDD);
                delete.execute();
            }
        } catch (SQLException e) {
            error = "SQLException: deletion of boot sale diary records failed..";
            throw new SQLException(error);
        }
    }

    // method to retrieve a single bootsalediary record
    public ResultSet getBSDiaryRecord(String date) throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access bootsalelocation records
            String queryString = ("SELECT * FROM bootsalediary WHERE date = '"
                    + date + "'");
            Statement st = cn.createStatement();
            System.out
                    .println("...Statement created to access a bootsalediary record");
            rs = st.executeQuery(queryString);
        } catch (SQLException e) {
            error = "SQLException: fail reading bootsalediary database record.";
            throw new SQLException(error);
        }
        return rs;
    }

    // method to retrieve all boot sale diary information
    public ResultSet getBSDiaryFields() throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access bootsalediary records
            String queryString = "SELECT * FROM bootsalediary ORDER BY date ASC;";
            Statement st = cn.createStatement();
            System.out
                    .println("...Statement created to access bootsalediary records");
            rs = st.executeQuery(queryString);
        } catch (SQLException e) {
            error = "SQLException: fail reading bootsalediary database table.";
            throw new SQLException(error);
        }
        return rs;
    }

    // method to retrieve all registereduser information
    public ResultSet getRUserFields() throws SQLException {
        ResultSet rs = null;
        try {
            // Execute a query string to access registereduser records
            String queryString = "SELECT * FROM registereduser ORDER BY email ASC;";
            Statement st = cn.createStatement();
            System.out.println("...Statement created to access registereduser records");
            rs = st.executeQuery(queryString);
        } catch (SQLException e) {
            error = "SQLException: fail reading registereduser database table.";
            throw new SQLException(error);
        }
        return rs;
    }
}

Compiling Our Source File With the -d Option

Open your command line editor:

Change to directory  cd c:\_ServletsJSP_Case_Study\bstrader\src\model

Compile TraderDBAccess.java using the java compiler with the -d options
  javac -d ..\..\classes TraderDBAccess.java

The following screenshot shows that we get a clean compile and also the TraderDBAccess class is now compiled into the classes\model directory.

compile TraderDBAccess

Lesson 3 Complete

In this lesson we took a first look at the model part of the MVC paradigm and coded the model components for the trader.

What's Next?

In the next lesson we take a final look at the model part of the MVC paradigm and code the model components for visitors to the site.

go to home page Homepage go to top of page Top