Visitor ModelS2C Home « Visitor Model
In this lesson we take a second look at the model part of the MVC paradigm and code the model component for visitors to the website.
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 CustomerDBAccess class which allows a visitor to the website to access boot sale information which requires reading the bootsalelocation and bootsalediary database tables, searching for products and displaying customer feedback which entails reading the TraderDBAccess and stockitem database table, leaving feedback data which involves reading the registereduser database table and writing to the stockitem database table and registering which necessitates writing to the registereduser database table.
Visitor Database RequirementsTop
Visitors to the website require database access for searching products, registering and leaving feedback.
In this lesson we will code the CustomerDBAccess class which allows a visitor to search for products by category and register which then allows the visitor to leave product feedback. Visitors will also be able to look at boot sale information pertaining to boot sale locations and upcoming events, as well as look at a best sellers page where registered users feedback is displayed.
To fulfill these requirements of visitors to the website 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 visitors to the website.
MarketingTop
With regards to marketing visitors to the website will need to be able to see the following :-
- Look at information on relevant boot sales which are attended by the trader
- Retrieve all records from the
bootsalelocationdatabase table for processing within theBootSaleInfo.jsppage.
- Retrieve all records from the
- Look at a boot sale diary giving information on future boot sales which are to be attended by the trader and on what dates.
- Retrieve all records from the
bootsalediarydatabase table for processing within theBootSaleInfo.jsppage.
- Retrieve all records from the
- An interactive product search page that will allow visitors to search products by product category.
- Retrieve all records from the
stockitemdatabase table for processing within theProductSearch.jsppage.
- Retrieve all records from the
- Display a best sellers page with pictures of stock being sold at upcoming boot sales and customer feedback.
- Retrieve all records from the
stockitemdatabase table for processing within theBestSellers.jsppage.
- Retrieve all records from the
- Allow a visitor to register on the website with an email address. The visitor can then be mailed at regular intervals with products being sold at upcoming boot sales and a diary of which boot sales locations the trader will be at and on which dates.
- Add a record to the
registereduserdatabase table validated within theRegistration.jsppage.
- Add a record to the
- Allow a registered user on the website to leave feedback on products they have bought.
- Retrieve all records from the
stockitemandregistereduserdatabase tables for processing within theFeedback.jsppage.
- Retrieve all records from the
Compiling The CustomerDBAccess ClassTop
The CustomerDBAccess class controls visitor database access for all the applications Servlets and JSPs that pertain to visitor 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 CustomerDBAccess.java.
package model;
/**
* Title: CustomerDBAccess
* Description: Controls database access for the customer
* name: Kevin
*/
import java.sql.*;
import java.util.*;
public class CustomerDBAccess {
String error;
private Connection cn = null;
public CustomerDBAccess() {
}
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 ex) {
{System.out.println("Problem opening the database"+ex);}
error = "Exception: Unknown error while connecting to Database.";
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 ex) {
{System.out.println("Problem closing connection");}
error = "Exception: Unable to close Database connection.";
throw new Exception(error);
}
}
// Stock Item Methods
// method to modify a stock item record with customer feedback
public void modifySI(String stockname, String customername,
String feedbackText, String feedbackRating) throws SQLException {
try {
// Create a prepared statement
String s = "UPDATE stockitem SET 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");
updStock.execute();
} catch (SQLException e) {
error = "SQLException: modify failed." + e;
throw new SQLException(error);
}
}
// method to retrieve all stockitem records with a certain category
public ResultSet getSICategory(String stockCategory) throws SQLException {
ResultSet rs = null;
try {
// Execute a query string to access stockitem records
String queryString = ("SELECT * FROM stockitem WHERE stockitemCategory = "
+ stockCategory
+"ORDER BY stockname ASC");
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.";
throw new SQLException(error);
}
return rs;
}
// 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.";
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.";
throw new SQLException(error);
}
return rs;
}
// Boot Sale Location Methods
// 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 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;
}
// Registered User Methods
// method to add a registereduser record
public void addRUser(String email, String fname, String lname, String hname, String sname,
String town, String county, String postCode) throws SQLException {
try {
// Create a prepared statement
int checkNum;
String s = "INSERT INTO registereduser VALUES(?, ?, ?, ?, ?, ?, ?, ?);";
PreparedStatement addRUser = cn.prepareStatement(s);
System.out.println("...Statement created to INSERT a registered user record");
addRUser.setString(1, email);
addRUser.setString(2, fname);
addRUser.setString(3, lname);
addRUser.setString(4, hname);
addRUser.setString(5, sname);
addRUser.setString(6, town);
addRUser.setString(7, county);
addRUser.setString(8, postCode);
addRUser.execute();
} catch (SQLException e) {
error = "SQLException: add failed." + e;
throw new SQLException(error);
}
}
// method to retrieve a single registereduser record
public ResultSet getRUserRecord(String email) throws SQLException {
ResultSet rs = null;
try {
// Execute a query string to access registereduser records
String queryString = ("SELECT * FROM registereduser WHERE EmailAddress = '"+email+"'");
Statement st = cn.createStatement();
System.out.println("...Statement created to access a registereduser record");
rs = st.executeQuery(queryString);
} catch (SQLException e) {
error = "SQLException: unknown database error, Find failed.";
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 CustomerDBAccess.java using the java compiler with the -d options
javac -d ..\..\classes CustomerDBAccess.java
The following screenshot shows that we get a clean compile and also the CustomerDBAccess class is now compiled into the classes\model directory.
Lesson 4 Complete
In this lesson we took a final look at the model part of the MVC paradigm and coded the model components for visitors.
What's Next?
In the next lesson we take a first look at the view part of the MVC paradigm and code the view components for the trader.
Homepage
Top