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
- Select stock items
- Retrieve all records from the
stockitemdatabase table for selection by the trader within theSelectSI.jsppage.
- Retrieve all records from the
- Select stock item
- Retrieve a record from the
stockitemdatabase table selected by the trader within theSelectSI.jsppage.
- Retrieve a record from the
- Modify stock item
- Update a record on the
stockitemdatabase table using mandatory and optional data entered by the trader within theModifySI.jsppage along with defaults for optional database fields that have not been entered.
- Update a record on the
- Delete stock item
- Delete a record(s) from the
stockitemdatabase selected by the trader within theDeleteSI.jsppage.
- Delete a record(s) from the
- Produce an inventory report of all stock
- Retrieve all records from the
stockitemdatabase table for viewing by the trader within theInventoryReport.jsppage.
- Retrieve all records from the
- Produce a stock reorder report for stock items that are equal or less than a pre-specified stock level for that item
- Retrieve all records from the
stockitemdatabase table for viewing by the trader within theStockReorderReport.jsppage.
- Retrieve all records from the
- Produce a sales report of stock sold at last boot sale
- Retrieve all records from the
stockitemdatabase table for viewing by the trader within theSalesReport.jsppage.
- Retrieve all records from the
- Produce an out of stock report of stock items with a stock level of zero
- Retrieve all records from the
stockitemdatabase table for viewing by the trader within theOutOfStockReport.jsppage.
- Retrieve all records from the
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
bootsalelocationdatabase table using mandatory data entered by the trader within theAddBSLoc.jsppage along with defaults for the optional database fields.
- Insert a record into the
- Select boot sale locations
- Retrieve all records from the
bootsalelocationdatabase table for selection by the trader within theSelectBSLoc.jsppage.
- Retrieve all records from the
- Select boot sale location
- Retrieve a record from the
bootsalelocationdatabase table selected by the trader within theSelectBSLoc.jsppage.
- Retrieve a record from the
- Modify boot sale location
- Update a record on the
bootsalelocationdatabase table using mandatory and optional data entered by the trader within theModifyBSLoc.jsppage along with defaults for optional database fields that have not been entered.
- Update a record on the
- Delete boot sale location
- Delete a record(s) from the
bootsalelocationdatabase selected by the trader within theDeleteBSLoc.jsppage.
- Delete a record(s) from the
- Add boot sale diary
- Insert a record into the
bootsalediarydatabase table using mandatory data entered by the trader within theAddBSDiary.jsppage.
- Insert a record into the
- Select boot sale diaries
- Retrieve all records from the
bootsalediarydatabase table for selection by the trader within theSelectBSDiary.jsppage.
- Retrieve all records from the
- Select boot sale diary
- Retrieve a record from the
bootsalediarydatabase table selected by the trader within theSelectBSDiary.jsppage.
- Retrieve a record from the
- Modify boot sale diary
- Update a record on the
bootsalediarydatabase table using mandatory data entered by the trader within theModifyBSDiary.jsppage.
- Update a record on the
- Delete boot sale diary
- Delete a record(s) from the
bootsalediarydatabase selected by the trader within theDeleteBSDiary.jsppage.
- Delete a record(s) from the
MarketingTop
With regards to marketing the trader will need to be able to do the following :-
- Produce a customer report
- Retrieve all records from the
stockitem,bootsalelocationandbootsalediarydatabase tables for processing within theCustomerReport.jsppage.
- Retrieve all records from the
- Mass email customer reports
- Retrieve all records from the
registereduserdatabase table for processing within theMassEmail.jsppage.
- Retrieve all records from the
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.
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.
Homepage
Top