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!