package dataentry.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import dataentry.db.DBConnectionPool;
import dataentry.db.DBException;
import dataentry.forms.StudentFormDelete;
import dataentry.forms.StudentFormInsertUpdate;

public class StudentModel {

	private static String selectStr = "SELECT * FROM Students";
	private static String insertStr = "INSERT INTO Students VALUES (?, ?, ?, ?)";
	private static String updateStr = "UPDATE Students SET firstName = ?, "
			+ "middleName = ?, lastName = ? WHERE ubid = ?";
	private static String deleteStr = "DELETE FROM Students WHERE ubid = ?";

	public static CachedRowSet getAllStudents() throws DBException {

		try {
			Connection conn = DBConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectStr);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allStudents = pStmt.executeQuery();

			CachedRowSet crsStudents = new CachedRowSetImpl();
			crsStudents.populate(allStudents);

			allStudents.close();
			pStmt.close();
			conn.close();

			return crsStudents;
		} catch (SQLException ex) {
			throw new DBException(ex.getMessage());
		} catch (NamingException ex) {
			throw new DBException(ex.getMessage());
		}
	}

	public static void insertStudent(StudentFormInsertUpdate student)
			throws DBException {

		try {
			Connection conn = DBConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			PreparedStatement pStmt = conn.prepareStatement(insertStr);

			pStmt.setInt(1, Integer.parseInt(student.getId()));
			pStmt.setString(2, student.getFirst());
			pStmt.setString(3, student.getMiddle());
			pStmt.setString(4, student.getLast());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DBException(ex.getMessage());
		} catch (NamingException ex) {
			throw new DBException(ex.getMessage());
		}
	}

	public static void updateStudent(StudentFormInsertUpdate student)
			throws DBException {

		try {
			Connection conn = DBConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// UPDATE student values in the Students table.
			PreparedStatement pStmt = conn.prepareStatement(updateStr);

			pStmt.setString(1, student.getFirst());
			pStmt.setString(2, student.getMiddle());
			pStmt.setString(3, student.getLast());
			pStmt.setInt(4, Integer.parseInt(student.getId()));
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DBException(ex.getMessage());
		} catch (NamingException ex) {
			throw new DBException(ex.getMessage());
		}
	}

	public static void deleteStudent(StudentFormDelete student)
			throws DBException {

		try {
			Connection conn = DBConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			PreparedStatement pStmt = conn.prepareStatement(deleteStr);

			pStmt.setInt(1, Integer.parseInt(student.getId()));
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DBException(ex.getMessage());
		} catch (NamingException ex) {
			throw new DBException(ex.getMessage());
		}
	}

}
