StudentDB.java

package com.registration.bean;

/**
 *
 * @author isaac
 */

import java.sql.Connection;
import java.sql.DriverManager;
import  java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import javax.faces.bean.ApplicationScoped;
import javax.faces.context.FacesContext;
@ApplicationScoped
public class StudentDB {
public static Connection connection;//manages connection
public static Statement statement; //query statement
public static ResultSet resultSet; //manage results//manage results
public static PreparedStatement ps;

public static StudentBean studentBean;

// method to connect to the database
public static Connection getDBConnection() {
    //any attempts on database must be enclosed in a try...catch block
    //throws SQLException
    try {
        //getting the driver
        Class.forName("com.mysql.jdbc.Driver");
        //database parameters
        final String url = "jdbc:derby://localhost:1527/Publishers";
        final String user = "root";
        final String password = "";
        //establishing database connection
        connection = DriverManager.getConnection(url,user,password);
        System.out.println("Connection established succesfully");
    } catch (ClassNotFoundException ex) {
        ex.printStackTrace();
    } catch(SQLException sqlException) {
        sqlException.printStackTrace();
    }
   return connection;
} //end of method to get database connection
//method to retrieve Students list  from database
public static ArrayList getStudentsList() {
    //create a arraylist object to  store the students
    ArrayList studentList = new ArrayList();
    //any attempts on a database must be  enclosed in try...catch block
    try {
        statement = getDBConnection().createStatement();
        resultSet = statement.executeQuery("SELECT * FROM student_record");
        while (resultSet.next()) {
            StudentBean student = new StudentBean(); //new student object
            student.setId(resultSet.getInt("student_id"));
            student.setName(resultSet.getString("student_name"));
            student.setEmail(resultSet.getString("student_email"));
            student.setPassword(resultSet.getString("student_password"));
            student.setGender(resultSet.getString("student_gender"));
            student.setAddress(resultSet.getString("student_address"));
            //store retrieved objects into arrayList
            studentList.add(student);
        }
        System.out.println("Total students registered: " + studentList.size());
    } catch (SQLException sqlException) {
        sqlException.printStackTrace();
    } finally {
        //destroy objects once  done
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    } //end finally
    return studentList;
}
//method to add new students into the database
public static String addStudents(StudentBean student) {
   int result = 0 ;
   String navigationResult = "";
   try {
       ps = getDBConnection().prepareStatement("INSERT INTO student_record(student_id,student_name,student_email,student_password,student_gender,student_address) VALUES(?,?,?,?,?,?)");
       ps.setInt(1, student.getId());
       ps.setString(2, student.getName());
       ps.setString(3, student.getEmail());
       ps.setString(4, student.getPassword());
       ps.setString(5, student.getGender());
       ps.setString(6, student.getAddress());
       ps.executeUpdate();
       result = 1;
   } catch(SQLException sqlException) {
       sqlException.printStackTrace();
   } finally {
       try{
       connection.close(); //close database connection
       }catch(Exception ex) {
           ex.printStackTrace();
       }
   }//end try..catch..finally block
   if (result != 0){
       navigationResult = "studentList.xhtml?faces-redirect=true";
   }else {
       navigationResult = "createStudent.xhtml?faces-redirect=true";
   }
   return navigationResult;
}
//method to edit the student list
public static String editStudentsRecords(int student_id) {
    StudentBean editRecords = null;

    System.out.println("editStudentsRecords(): Student Id: " + student_id);
    //Setting a particular students details in session
    Map<String,Object> sessionMapObject = FacesContext.getCurrentInstance().getExternalContext().getSessionMap();
    try {
        statement = getDBConnection().createStatement();
        resultSet = statement.executeQuery("SELECT * FROM student_record WHERE student_id = " + student_id);
        if (resultSet != null) {
            resultSet.next();
           editRecords = new StudentBean();
            editRecords.setId(resultSet.getInt("student_id"));
            editRecords.setName(resultSet.getString("student_name"));
            editRecords.setEmail(resultSet.getString("student_email"));
            editRecords.setPassword(resultSet.getString("student_password"));
            editRecords.setGender(resultSet.getString("student_gender"));
            editRecords.setAddress(resultSet.getString("student_gender"));
        }
        sessionMapObject.put("editRecords", editRecords);
    } catch(SQLException sqlException) {
        sqlException.printStackTrace();
    }
    return "EditStudentRecord.xhtml?faces-redirect=true";
}
//method to update student  records  in Database
public static String updateStudentRecords(StudentBean updateStudent) {
try {
ps = getDBConnection().prepareStatement("UPDATE student_record SET student_id = ?,student_name = ?,student_email = ?,student_password = ?,student_gender = ?,student_address = ? WHERE student_id = ?");
ps.setInt(1, updateStudent.getId());
ps.setString(2, updateStudent.getName());
ps.setString(3, updateStudent.getEmail());
ps.setString(4, updateStudent.getPassword());
ps.setString(5, updateStudent.getGender());
ps.setString(6, updateStudent.getAddress());
ps.executeUpdate();
} catch(SQLException sqlException) {
    sqlException.printStackTrace();
} finally {
    try {
        connection.close();
    } catch(Exception ex) {
        ex.printStackTrace();
    }
}
return  "/studentList.xhtml?faces-redirect=true";
} //end of method update
//method to delete students
public static String deleteStudentRecords(int studentId) {
    System.out.println("deleteStudentRecords() : Student Id: " + studentId);
    try {
        ps = getDBConnection().prepareStatement("DELETE from student_record WHERE student_id = " + studentId);
        ps.executeUpdate();
    } catch(SQLException sqlException) {
        sqlException.printStackTrace();
    } finally {
        try {
            connection.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } //end try...catch...finally
    return "/studentList.xhtml?faces-redirect=true";
} //end delete method
} //end of studentDB Class