22S-CST8288 Assignment1 (15% of grade) Overview Refactoring an application so that it uses the DAO pattern Project
Question:
22S-CST8288 Assignment1
(15% of grade)
Overview
Refactoring an application so that it uses the DAO pattern
Project Description
You are provided with starter code that accesses a MySQL database.
o a console application that runs in a single class with main()
o which accesses the database and prints all of the rows in a table
Also you are given:
o the SQL that creates the database schema and
o a sample .properties file which contains the details of the database connection.
Your tasks are to:
o refactor the single class to implement the DAO pattern corresponding a table:
student
Deliverables required for the Assignment 1
Your application must be implement the following as Java packages:
o businesslayer
o dataaccesslayer (including an class to encapsulate the "data source")
o transferobjects
Your application must:
o use MySQL - SQL scripts are provided
o use the class ResultSet and PreparedStatement
o implement the DAO pattern corresponding to the student table
o implement separate methods for each of the CRUD operations
o use proper Java class and method structure
o not require any user input
There should only be program output. It should be in column format like
the output of the starter code.
o follow Java coding conventions
o have Javadoc comments for all public classes, methods, variables, etc
o (for full marks) code submitted must be in the form of NetBeans project.
Main.java
import java.io.IOException; import java.io.InputStream; import java.nio.file.Files; import java.nio.file.Paths; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;
import java.util.Properties;
/** * main() class that accesses a SQL database serves as the starter class for * Assignment1 of 22F_CST8288 The intent of the assignment is refactor this * class into DAO pattern * * @author kriger */ public class Main {
/** * main() method - includes all access to database * * @param args the command line arguments (not used in this program) */ public static void main(String[] args) {
/* step 1: read file into instance of Properties and get an instance of Connection */ Properties props = new Properties();
try ( InputStream in = Files.newInputStream(Paths.get("src/database.properties"));) { props.load(in); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }// catch()
String url = props.getProperty("jdbc.url"); String username = props.getProperty("jdbc.username"); String password = props.getProperty("jdbc.password"); //try with resources try ( Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement pstmt = connection.prepareStatement( "SELECT StudentID, LastName, FirstName, Email, PhoneNumber FROM student"); ResultSet rs = pstmt.executeQuery();) { /* Step 2: print all rows from the table: student */
System.out.printf("%10s %-10s %-10s %-20s %-15s ", "StudentID", "LastName", "FirstName", "Email", "PhoneNumber" ); while (rs.next()) { System.out.printf("%10d %-10s %-10s %-20s %-15s ", rs.getInt("StudentID"), rs.getString("LastName"), rs.getString("FirstName"), rs.getString("Email"), rs.getString("PhoneNumber") );
} } catch (SQLException ex) { ex.printStackTrace(); }
}//end of main()
}//end of class
tutoring2-MYSQL.sql
/* for CST8288 - Case Study 2*/ /* for MySQL*/ DROP DATABASE IF EXISTS tutoring;
CREATE DATABASE tutoring;
USE tutoring;
CREATE TABLE course ( CourseCode VARCHAR(7) NOT NULL PRIMARY KEY, CourseName VARCHAR(45) NOT NULL, CourseDescription TEXT NOT NULL ) ;
CREATE TABLE experience ( ExperienceID INT NOT NULL PRIMARY KEY, Description TEXT NOT NULL ) ;
CREATE TABLE student ( StudentID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(45) NOT NULL, LastName VARCHAR(45) NOT NULL, Email VARCHAR(45), PhoneNumber VARCHAR(45) ) ;
CREATE TABLE tutor ( TutorID INT NOT NULL PRIMARY KEY, LastName VARCHAR(45) NOT NULL, FirstName VARCHAR(45) NOT NULL, Email VARCHAR(45), PhoneNumber VARCHAR(15), Status VARCHAR(45) NOT NULL, experience_ExperienceID INT NOT NULL, FOREIGN KEY (experience_ExperienceID) REFERENCES experience (ExperienceID) ) ;
CREATE TABLE session ( DateKey DATE NOT NULL , TimeKey TIME NOT NULL , StudentLastName VARCHAR(45) NOT NULL, SessionStatus INT NOT NULL, SessionTopic TEXT NOT NULL, tutor_TutorID INT NOT NULL, course_CourseCode VARCHAR(7) NOT NULL, student_StudentID INT NOT NULL, PRIMARY KEY (DateKey, TimeKey, tutor_TutorID, course_CourseCode, student_StudentID), FOREIGN KEY (tutor_TutorID) REFERENCES tutor(TutorID), FOREIGN KEY (course_CourseCode) REFERENCES course(CourseCode), FOREIGN KEY (student_StudentID) REFERENCES student(StudentID) ) ;
CREATE TABLE tutorcourse ( tutor_TutorID INT NOT NULL, course_CourseCode VARCHAR(7) NOT NULL, PRIMARY KEY (tutor_TutorID, course_CourseCode), FOREIGN KEY (tutor_TutorID) REFERENCES tutor (TutorID), FOREIGN KEY (course_CourseCode) REFERENCES course (CourseCode) ) ;
CREATE TABLE studentcourse ( student_StudentID INT NOT NULL, course_CourseCode VARCHAR(7) NOT NULL, PRIMARY KEY (student_StudentID,course_CourseCode), FOREIGN KEY (student_StudentID) REFERENCES student(StudentID), FOREIGN KEY (course_CourseCode) References course(CourseCode) ) ;
/* ------------------------------------ */
INSERT INTO COURSE VALUES('CST8101','Computer Essentials','The essentials of computer software, hardware, and laptop management form the foundation for building further technical programming skills'); INSERT INTO COURSE VALUES('CST8110','Introduction to Computer Programming','Introduction to algorithms and Object-Oriented software development. Use the Java programming language to develop programs which contain in'); INSERT INTO COURSE VALUES('CST8215','Database','Students learn the fundamentals of Relational Databases design using Entity Relation diagrams, and use SQL to create, modify and query a dat...'); INSERT INTO COURSE VALUES('CST8300','Achieving Success in Changing Environments','Rapid changes in technology have created personal and employment choices that challenge each of us to find our place as contributing citizen...'); INSERT INTO COURSE VALUES('ENL1813','Communications I','Communication remains an essential skill sought by employers, regardless of discipline or field of study. Using a practical, vocation-orient... '); INSERT INTO COURSE VALUES('MAT8001','Technical Mathematics for Computer Science','The study of algebraic and transcendental functions is an essential prerequisite to Calculus. Students manipulate algebraic expressions, sol..');
INSERT INTO S..3@livealgonquincom','613-555-1212'); INSERT INTO STUDENT VALUES(9016002,'Donald E..3@livealgonquincom','613-555-1212'); INSERT INTO STUDENT VALUES(9016003,'Robert C..3@livealgonquincom','613-555-1212'); INSERT INTO STUDENT VALUES(9016004,'Edsger W..3@livealgonquincom','613-555-1212'); INSERT INTO S..3@livealgonquincom','613-555-1212'); INSERT INTO S..3@livealgonquincom','613-555-1212'); INSERT INTO S..3@livealgonquincom','613-555-1212'); INSERT INTO S..3@livealgonquincom','613-555-1212'); INSERT INTO S..3@livealgonquincom','613-555-1212');
I really need help! Thanks a lot!
Financial and managerial accounting
ISBN: 978-1118016114
1st edition
Authors: Jerry J. Weygandt, Paul D. Kimmel, Donald E. Kieso