Homework SoursePrepare a pilot example of your case study solution using JS
Prepare a pilot example of your case study solution using JSPs, beans, and servlets. Attached you will find an example main menu page for a pilot project. The grade for this project will be a combined grade with the 404 class which will have the same assignment. Prepare a group PowerPoint presentation to brief your pilot. Zip into a single file all JSP, bean, and servlet source code files used in creating your pilot project and attach them to the weekly assignment. All pilot projects deliverables, with the exception of the PowerPoint presentation, must be from individual work, not from group work. Do not forget to fully document all of your source code.
Making a Pilot Project - A \'Pilot\' is somewhat like a \'proof of concept\' of a project. It is a limited implementation of a project design that provides a testing ground or proves that the original design is sound. For a pilot project in our case studies, we will need to show that our general idea of how the case study solution should work is sound. In the short series of web pages you create for your pilot project, you will solve one of the common problems with a typical E-Commerce site. The following \'Students-Classes\' example will provide guidance on solving this problem.
The concept behind the case studies in our courses is to create database tables and an interface that shows how the case study could be solved. There are many solutions to most of the case study problems. However, most solutions will have at least one \'many to many\' relationship. In a \'many to many\' relationship it is usually necessary to create a third table so that the relationships can be broken down to \'one to many\'. In most cases, the \'many to many\' relationship will be between customer orders and products. For each product, there may be many orders and for each order, there could be many products. However, resolving the \'many to many\' problem is generic and we will solve it generically.
Student-Classes Example
Diagram
In a college, there can be many students.
In a college, there can be many courses.
For any single student, there can be many courses that the student is taking.
For any single course, there can be many students enrolled.
If we try to make tables that show what students are enrolled in what classes, we find that a single table will not be adequate. We find that two tables (we will title them Classes and Students) have a many to many relationship and entering and setting a class load for a single student cannot be done without entering the entire class list as columns for the student table. For each class, we would have to have a list of students in the class, so the classes table would have enough columns for all the students in the school. A simple thing like a student dropping out of a course would cause the entire database design to change.
Therefore, whenever we have a many-to-many relationship, we will need an associative entity table to resolve the schema into a set of one-to-many relationships.
The following are definitions for the student and classes tables:
§ DROP TABLE StudentsClasses;
§ DROP TABLE Student;
§ DROP TABLE Classes;
§ CREATE TABLE Student (STUDENT_ID varchar(20), FIRST_NAME varchar(25), LAST_NAME varchar(25), PRIMARY KEY (STUDENT_ID));
§ CREATE TABLE Classes(CLASS_ID varchar(20), CLASS_NAME varchar(20), PRIMARY KEY (CLASS_ID));
§ CREATE TABLE StudentsClasses(STUDENT_ID varchar(20), CLASS_ID varchar(20),
PRIMARY KEY (STUDENT_ID, CLASS_ID),
FOREIGN KEY (STUDENT_ID) REFERENCES Student,
FOREIGN KEY (CLASS_ID) REFERENCES Classes);
§ INSERT INTO Student Values(\'1\',\'Dan\', \'Creagan\');
§ INSERT INTO Student Values(\'2\',\'Marty\',\'Kroft\');
§ INSERT INTO Student Values(\'3\',\'Jesse\',\'James\');
§ INSERT INTO Student Values(\'4\',\'Nemo\',\'Fish\');
§ INSERT INTO Student Values(\'5\',\'Arsenio\',\'Hall\');
§ INSERT INTO Student Values(\'6\',\'Jen\',\'Lopez\');
§ INSERT INTO Student Values(\'7\',\'George\',\'Bush\');
§ INSERT INTO Student Values(\'8\',\'Gabriel\',\'Horn\');
§ INSERT INTO Student Values(\'9\',\'James\',\'Bond\');
§ INSERT INTO Student Values(\'10\',\'Edgar\',\'Poe\');
§ INSERT INTO Classes Values(\'1\',\'Basket_Weaving\');
§ INSERT INTO Classes Values(\'2\',\'Spying\');
§ INSERT INTO Classes Values(\'3\',\'Advanced_Java\');
§ INSERT INTO StudentsClasses Values(\'1\',\'1\');
§ INSERT INTO StudentsClasses Values(\'2\',\'1\');
§ INSERT INTO StudentsClasses Values(\'3\',\'1\');
§ INSERT INTO StudentsClasses Values(\'4\',\'1\');
§ INSERT INTO StudentsClasses Values(\'1\',\'2\');
§ INSERT INTO StudentsClasses Values(\'3\',\'2\');
§ INSERT INTO StudentsClasses Values(\'5\',\'2\');
§ INSERT INTO StudentsClasses Values(\'7\',\'2\');
§ INSERT INTO StudentsClasses Values(\'7\',\'3\');
§ INSERT INTO StudentsClasses Values(\'8\',\'3\');
§ INSERT INTO StudentsClasses Values(\'9\',\'3\');
§ INSERT INTO StudentsClasses Values(\'10\',\'3\');
§ We can see by the above that the \'StudentsClasses\' table acts as a \'join\' table which is a way to resolve the \'many to many\' problem. For each Student, there may be many entries in the StudentClasses table, for each Class, there may be many entries in the StudentsClasses table. For any Student_ID in the StudentsClasses table, there is only one Student_ID match in the Student table. Similarly, for any Class_ID in the StudentsClasses table, there is only one Class_ID match in the Classes table. The two columns in the StudentsClasses table together make the key for the table. The key is said to be shared.
It is easy to modify the above so that we add or drop students, or classes, or enrollments at will. Compare the effort for creating a new class in this schema versus the effort for creating a new class without the join table. In this version, we simply add a new class into the Classes table. In the other version, we would have to change the schema to accommodate a new class.
With this setup, it is important to enforce Relational Integrity. That is, if you delete a student, you should be sure to take the Student_ID out of the StudentsClasses table, too. Similarly, if you delete a Class, then the Class_ID should come out of the StudentsClasses table. Some Database Management System (DBMS) implementations do Relational Integrity (RI) for us automatically. Others (like some MySQL variants) require us to maintain the relationships in code.
o Pilot Project Requirements - Using the above as a background, implement a user interface (very basic) for your pilot project. Your Pilot Project will need to implement a minimum of the following interfaces into the database:
§ A menu page that links to all the pages in your project.
§ A page to create all required tables and insert the initial records needed for the application to function.
§ A page to drop all tables used in the project.
§ A form, that when submitted, inserts data into a database table(s).
§ A page the displays the data in the table(s) updated for verification.
§ The pages for this project should not be plain white pages. Add a few images, styles sheets, etc. that enhance the pages\' appearance.
Solution
index.html
<%@ page language=\"java\" contentType=\"text/html; charset=ISO-8859-1\"
pageEncoding=\"ISO-8859-1\"%>
<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">
<html>
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\">
<title>Welcome</title>
</head>
<body>
<jsp:forward page=\"/StudentController?action=listStudent\"></jsp:forward>
</body>
</html>
ListStudents.jsp
..................
<%@ page language=\"java\" contentType=\"text/html; charset=ISO-8859-1\"
pageEncoding=\"ISO-8859-1\"%>
<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\"%>
<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">
<html>
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\">
<title>Show All Students</title>
</head>
<body>
<table>
<thead>
<tr>
<th>Student ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Course</th>
<th>Year</th>
<th colspan=\"2\">Action</th>
</tr>
</thead>
<tbody>
<c:forEach items=\"${students}\" var=\"student\">
<tr>
<td><c:out value=\"${student.studentId}\" /></td>
<td><c:out value=\"${student.firstName}\" /></td>
<td><c:out value=\"${student.lastName}\" /></td>
<td><c:out value=\"${student.course}\" /></td>
<td><c:out value=\"${student.year}\" /></td>
<td><a
href=\"StudentController.do?action=edit&studentId=<c:out value=\"${student.studentId }\"/>\">Update</a></td>
<td><a
href=\"StudentController.do?action=delete&studentId=<c:out value=\"${student.studentId }\"/>\">Delete</a></td>
</tr>
</c:forEach>
</tbody>
</table>
<p>
<a href=\"StudentController.do?action=insert\">Add Student</a>
</p>
</body>
</html>
Student.jsp
............
<%@ page language=\"java\" contentType=\"text/html; charset=ISO-8859-1\"
pageEncoding=\"ISO-8859-1\"%>
<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\"%>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\">
<title>Add New Student</title>
</head>
<body>
<form action=\"StudentController.do\" method=\"post\">
<fieldset>
<div>
<label for=\"studentId\">Student ID</label> <input type=\"text\"
name=\"studentId\" value=\"<c:out value=\"${student.studentId}\" />\"
readonly=\"readonly\" placeholder=\"Student ID\" />
</div>
<div>
<label for=\"firstName\">First Name</label> <input type=\"text\"
name=\"firstName\" value=\"<c:out value=\"${student.firstName}\" />\"
placeholder=\"First Name\" />
</div>
<div>
<label for=\"lastName\">Last Name</label> <input type=\"text\"
name=\"lastName\" value=\"<c:out value=\"${student.lastName}\" />\"
placeholder=\"Last Name\" />
</div>
<div>
<label for=\"course\">Course</label> <input type=\"text\" name=\"course\"
value=\"<c:out value=\"${student.course}\" />\" placeholder=\"Course\" />
</div>
<div>
<label for=\"year\">Year</label> <input type=\"text\" name=\"year\"
value=\"<c:out value=\"${student.year}\" />\" placeholder=\"Year\" />
</div>
<div>
<input type=\"submit\" value=\"Submit\" />
</div>
</fieldset>
</form>
</body>
</html>
model
.........
public class Student {
private int studentId;
private String firstName;
private String lastName;
private String course;
private int year;
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public int getYear() {
return year;
}
public void setYear(int year) {
this.year = year;
}
@Override
public String toString() {
return \"Student [studentId=\" + studentId + \", firstName=\" + firstName
+ \", lastName=\" + lastName + \", course=\" + course + \", year=\"
+ year + \"]\";
}
}
import java.io.IOException;
import javax.servlet.RequestDispatcher;
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 com.junald.dao.StudentDAO;
import com.junald.dao.StudentDAOImplementation;
import com.junald.model.Student;
@WebServlet(\"/StudentController\")
public class StudentController extends HttpServlet {
private StudentDAO dao;
private static final long serialVersionUID = 1L;
public static final String lIST_STUDENT = \"/listStudent.jsp\";
public static final String INSERT_OR_EDIT = \"/student.jsp\";
public StudentController() {
dao = new StudentDAOImplementation();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String forward = \"\";
String action = request.getParameter( \"action\" );
if( action.equalsIgnoreCase( \"delete\" ) ) {
forward = lIST_STUDENT;
int studentId = Integer.parseInt( request.getParameter(\"studentId\") );
dao.deleteStudent(studentId);
request.setAttribute(\"students\", dao.getAllStudents() );
}
else if( action.equalsIgnoreCase( \"edit\" ) ) {
forward = INSERT_OR_EDIT;
int studentId = Integer.parseInt( request.getParameter(\"studentId\") );
Student student = dao.getStudentById(studentId);
request.setAttribute(\"student\", student);
}
else if( action.equalsIgnoreCase( \"insert\" ) ) {
forward = INSERT_OR_EDIT;
}
else {
forward = lIST_STUDENT;
request.setAttribute(\"students\", dao.getAllStudents() );
}
RequestDispatcher view = request.getRequestDispatcher( forward );
view.forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Student student = new Student();
student.setFirstName( request.getParameter( \"firstName\" ) );
student.setLastName( request.getParameter( \"lastName\" ) );
student.setCourse( request.getParameter( \"course\" ) );
student.setYear( Integer.parseInt( request.getParameter( \"year\" ) ) );
String studentId = request.getParameter(\"studentId\");
if( studentId == null || studentId.isEmpty() )
dao.addStudent(student);
else {
student.setStudentId( Integer.parseInt(studentId) );
dao.updateStudent(student);
}
RequestDispatcher view = request.getRequestDispatcher( lIST_STUDENT );
request.setAttribute(\"students\", dao.getAllStudents());
view.forward(request, response);
}
}
import java.util.List;
import com.junald.model.Student;
public interface StudentDAO {
public void addStudent( Student student );
public void deleteStudent( int studentId );
public void updateStudent( Student student );
public List<Student> getAllStudents();
public Student getStudentById( int studentId );
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.junald.model.Student;
import com.junald.util.DBUtil;
public class StudentDAOImplementation implements StudentDAO {
private Connection conn;
public StudentDAOImplementation() {
conn = DBUtil.getConnection();
}
@Override
public void addStudent( Student student ) {
try {
String query = \"insert into student (firstName, lastName, course, year) values (?,?,?,?)\";
PreparedStatement preparedStatement = conn.prepareStatement( query );
preparedStatement.setString( 1, student.getFirstName() );
preparedStatement.setString( 2, student.getLastName() );
preparedStatement.setString( 3, student.getCourse() );
preparedStatement.setInt( 4, student.getYear() );
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void deleteStudent( int studentId ) {
try {
String query = \"delete from student where studentId=?\";
PreparedStatement preparedStatement = conn.prepareStatement(query);
preparedStatement.setInt(1, studentId);
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void updateStudent( Student student ) {
try {
String query = \"update student set firstName=?, lastName=?, course=?, year=? where studentId=?\";
PreparedStatement preparedStatement = conn.prepareStatement( query );
preparedStatement.setString( 1, student.getFirstName() );
preparedStatement.setString( 2, student.getLastName() );
preparedStatement.setString( 3, student.getCourse() );
preparedStatement.setInt( 4, student.getYear() );
preparedStatement.setInt(5, student.getStudentId());
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public List<Student> getAllStudents() {
List<Student> students = new ArrayList<Student>();
try {
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery( \"select * from student\" );
while( resultSet.next() ) {
Student student = new Student();
student.setStudentId( resultSet.getInt( \"studentId\" ) );
student.setFirstName( resultSet.getString( \"firstName\" ) );
student.setLastName( resultSet.getString( \"lastName\" ) );
student.setCourse( resultSet.getString( \"course\" ) );
student.setYear( resultSet.getInt( \"year\" ) );
students.add(student);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return students;
}
@Override
public Student getStudentById(int studentId) {
Student student = new Student();
try {
String query = \"select * from student where studentId=?\";
PreparedStatement preparedStatement = conn.prepareStatement( query );
preparedStatement.setInt(1, studentId);
ResultSet resultSet = preparedStatement.executeQuery();
while( resultSet.next() ) {
student.setStudentId( resultSet.getInt( \"studentId\" ) );
student.setFirstName( resultSet.getString( \"firstName\" ) );
student.setLastName( resultSet.getString( \"LastName\" ) );
student.setCourse( resultSet.getString( \"course\" ) );
student.setYear( resultSet.getInt( \"year\" ) );
}
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
}
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtil {
private static Connection conn;
public static Connection getConnection() {
if( conn != null )
return conn;
InputStream inputStream = DBUtil.class.getClassLoader().getResourceAsStream( \"/db.properties\" );
Properties properties = new Properties();
try {
properties.load( inputStream );
String driver = properties.getProperty( \"driver\" );
String url = properties.getProperty( \"url\" );
String user = properties.getProperty( \"user\" );
String password = properties.getProperty( \"password\" );
Class.forName( driver );
conn = DriverManager.getConnection( url, user, password );
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection( Connection toBeClosed ) {
if( toBeClosed == null )
return;
try {
toBeClosed.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
