Final Exam Part 2: Student Search GUI Application (Continuation)

NOTE: Need to complete Part 1 prior to proceeding

Part 2 includes the following tasks:
1. Creating the StudentData Class File
2. Creating the StudentDBAccess Class File
Note: These Class Files are for setting up the data variables (fields) and creating the connection to the MySQL database

Program Specifications:
Create a Java GUI application that will allow the user to search for students and also display the list of students attending LMC. The application will also require the functionality to add, update and delete student records. The Java GUI will need to connect to the MySQL database to access the student records..

Below is the SQL database schema structure:
Database Name: lmc_college_schema
Table Name: student
Field Name and DataType:
1. id INT(11) NOT NULL PRIMARY KEY id
2. last_name VARCHAR(64)
3. first_name VARCHAR(64)
4. email VARCHAR(64)
5 phone VARCHAR(15)

Requirements to Create the Classes

1. Create a class called StudentData

2. Create a class called StudentDBAccess

Requirements for the StudentData Class:

1. Replace the code in the StudentData Class by copying/ pasting the code below.
NOTE: This Java class will setup the constructors for the variables and create the get and set methods for the variables used in the application.

public class StudentData {

private int id;

private String lastName;

private String firstName;

private String email;

private String phone;

// constructor method if table is empty

public StudentData(String lastName, String firstName, String email, String phone) {

this(0, lastName, firstName, email, phone);

}

// constructor method if table has records.

public StudentData(int id, String lastName, String firstName, String email, String phone) {

super();

this.id = id;

this.lastName = lastName;

this.firstName = firstName;

this.email = email;

this.phone = phone;

}

// below are the get and set methods for the variables- id, lastname, firstname, email & phone

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

public String getFirstName() {

return firstName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

// method to display the data format in the GUI

@Override

public String toString() {

return String

.format("Student [id=%s, lastName=%s, firstName=%s, email=%s, phone=%s]",

id, lastName, firstName, email, phone);

}

}

2. Verify the code you pasted in the StudentData class

Requirements for the StudentDBAccess:

1. Replace the code in the StudentDBAccess Class by copying/ pasting the code below
NOTE: This Java class will setup the database connection and SQL Statement execution methods.

// import Java SQL Library Classes

import java.util.*;

import java.sql.*;

import java.io.*;

public class StudentDBAccess {

// create the object- myconnection from the Connection class

private Connection myConnection;

// constructor method to setup the connection to the database

public StudentDBAccess() throws Exception {

// create object from the Properties class and load the access data from external file

Properties connect = new Properties();

connect.load(new FileInputStream("dbAccess.txt"));

// create the SQL connection variables and initialize

String user = connect.getProperty("user");

String password = connect.getProperty("password");

String dburl = connect.getProperty("dburl");

// connect to the database

myConnection = DriverManager.getConnection(dburl, user, password);

// display the database connection status

System.out.println("DB connection successful to: " + dburl);

}

// void method used for the SQL query statement in deleting a record

public void deleteStudent(int studentId) throws SQLException {

PreparedStatement myStmt = null;

try {

// prepare the SQL statement

myStmt = myConnection.prepareStatement("delete from students where id=?");

// set the parameter to passed to the statement

myStmt.setInt(1, studentId);

// execute SQL statement

myStmt.executeUpdate();

}

finally {

// call the close void method and pass the paramenter

close(myStmt);

}

}

// void method used for the SQL query statement in updating a record

public void updateStudent(StudentData theStudent) throws SQLException {

PreparedStatement myStmt = null;

try {

// prepare the SQL statement

myStmt = myConnection.prepareStatement("update students"

+ " set first_name=?, last_name=?, email=?, phone=?"

+ " where id=?");

// set the parameter to passed to the statement

myStmt.setString(1, theStudent.getFirstName());

myStmt.setString(2, theStudent.getLastName());

myStmt.setString(3, theStudent.getEmail());

myStmt.setString(4, theStudent.getPhone());

myStmt.setInt(5, theStudent.getId());

// execute SQL statement

myStmt.executeUpdate();

}

finally {

// call the close void method and pass the paramenter

close(myStmt);

}

}

// void method used for the SQL query statement in adding a record

public void addStudent(StudentData theStudent) throws Exception {

PreparedStatement myStmt = null;

try {

// prepare the SQL statement

myStmt = myConnection.prepareStatement("insert into students"

+ " (first_name, last_name, email, phone)"

+ " values (?, ?, ?, ?)");

// set the parameter to passed to the statement

myStmt.setString(1, theStudent.getFirstName());

myStmt.setString(2, theStudent.getLastName());

myStmt.setString(3, theStudent.getEmail());

myStmt.setString(4, theStudent.getPhone());

// execute SQL statement

myStmt.executeUpdate();

}

finally {

// call the close void method and pass the paramenter

close(myStmt);

}

}

// method of the SQL query statement for displaying all records sorted by last name

public List<StudentData> getAllStudents() throws Exception {

// initialize the list using an array

List<StudentData> list = new ArrayList<>();

// create the objects from the SQL class objects and initialize

Statement myStmt = null;

ResultSet myResult = null;

try {

// create the SQL statement

myStmt = myConnection.createStatement();

myResult = myStmt.executeQuery("select * from students order by last_name");

// loop to display all the records in the table

while (myResult.next()) {

StudentData tempStudent = convertRowToStudent(myResult);

list.add(tempStudent);

}

// return the list of records

return list;

}

finally {

// call the close void method and pass the paramenter

close(myStmt, myResult);

}

}

// method of the SQL query statement for searching for a record by last name

public List<StudentData> searchStudents(String lastName) throws Exception {

// initialize the list using an array

List<StudentData> list = new ArrayList<>();

// create the objects from the SQL class objects and initialize

PreparedStatement myStmt = null;

ResultSet myResult = null;

try {

// prepare the SQL statement

lastName += "%";

myStmt = myConnection.prepareStatement("select * from students where last_name like ? order by last_name");

// set the parameter to passed to the statement

myStmt.setString(1, lastName);

// execute SQL statement

myResult = myStmt.executeQuery();

// loop to display the selected record

while (myResult.next()) {

StudentData tempStudents = convertRowToStudent(myResult);

list.add(tempStudents);

}

return list;

}

finally {

// call the close void method and pass the paramenter

close(myStmt, myResult);

}

}

// method created from the StudentData calls.

private StudentData convertRowToStudent(ResultSet myResult) throws SQLException {

// get the data from the record result

int id = myResult.getInt("id");

String lastName = myResult.getString("last_name");

String firstName = myResult.getString("first_name");

String email = myResult.getString("email");

String phone = myResult.getString("phone");

// create the object from the StudentData class and pass the parameter

StudentData tempStudent = new StudentData(id, lastName, firstName, email, phone);

// return the data from the calling method

return tempStudent;

}

// void method to close the SQL statement objects by accepting the 3 arguments

private static void close(Connection myConn, Statement myStmt, ResultSet myRs)

throws SQLException {

if (myRs != null) {

myRs.close();

}

if (myStmt != null) {

}

if (myConn != null) {

myConn.close();

}

}

// void method to close the SQL statement objects by accepting the 2 arguments

private void close(Statement myStmt, ResultSet myRs) throws SQLException {

close(null, myStmt, myRs);

}

// void method to close the SQL statement objects by accepting the 1 argument

private void close(Statement myStmt) throws SQLException {

close(null, myStmt, null);

}

}

2. Verify the code you pasted in the StudentDBAccess class