//  File:  StudentDataAccessor.java
//

package shod.register;

import java.sql.*;
import java.io.*;

import java.util.*;

/**
 *  This business object provides data entry and retrieval of
 *  student data in a database.
 *
 *  @author Chad (shod) Darby,  darby@j-nine.com
 *  @version 0.6, 5 Jan 1998 - 0.9, 31 July 1998
 *
 */
public class StudentDataAccessor
{
    // data members
    private Connection dbConnection;
    private PreparedStatement displayStatement;
    private PreparedStatement registerStatement;
            
    private final int LAST_NAME_POSITION  = 1;
    private final int FIRST_NAME_POSITION = 2;
    private final int EMAIL_POSITION      = 3;
    private final int COMPANY_POSITION    = 4;
    private final int EXPECTATIONS_POSITION = 5;
    private final int COURSE_TITLE_POSITION        = 6;
    private final int COURSE_LOCATION_POSITION     = 7;
    private final int COURSE_DATE_POSITION   = 8;
  
  	/**
	 *  Constructor that make a database connection and prepares SQL statements
	 */
    public StudentDataAccessor(String dbDriver, String dbURL, String userID, String passwd)
    {       
        // use println statements to send status messages to web server console
        try {
            log("StudentDataAccessor init: Start");
            
            log("StudentDataAccessor init: Loading Database Driver: " + dbDriver);
            Class.forName(dbDriver);
            
            log("StudentDataAccessor init: Getting a connection to - " + dbURL);
            dbConnection = DriverManager.getConnection(dbURL, userID, passwd);
            
            log("StudentDataAccessor init: Preparing display statement");
            displayStatement = 
            dbConnection.prepareStatement("select * from Students order by LastName");
            
            log("StudentDataAccessor init: Preparing register statement");
            registerStatement = 
               dbConnection.prepareStatement("insert into Students "
                 + "(LastName, FirstName, Email, Company, CourseExpectations, CourseTitle, CourseLocation, CourseStartDate)"
                 + " values (?, ?, ?, ?, ?, ?, ?, ?)");            
                           
            log("StudentDataAccessor init: End");
        }
        catch (Exception e)
        {
            cleanUp();
            log(e);   
        }     
    }

    
	/**
	 *  Closes the database connection
	 */
    public void cleanUp()
    {
        try {
            log("Closing database connection");
            dbConnection.close();
        }
        catch (SQLException e)
        {
            log(e);   
        }        
    }
    
	/**
	 *  Queries the database and gets a list of students
	 */
    public Vector getStudentList()
    {
        
        Vector studentVector = new Vector();
        
        try
        {
            // execute the query to get a list of the students
			System.out.println("starting query...");
            ResultSet dataResultSet = displayStatement.executeQuery();
            System.out.println("finishing query...");
			
			Student aStudent = null;

            // build a student vector based on database results
            int size = 1;
			while (dataResultSet.next())
            {
				System.out.println("building student = " + size);
                aStudent = new Student(dataResultSet);
                studentVector.addElement(aStudent);
            	size++;	
			}

            dataResultSet.close();
			System.out.println("result set closed\n\n");
        }
        catch (SQLException e)
        {
            log(e);    
        }
        finally
        {
          return studentVector;    
        }
    }

	/**
	 *  Registers a student by inserting them into the database
	 */
    public void registerStudent(Student aStudent)
    {
        try {
            // set sql parameters
            registerStatement.setString(LAST_NAME_POSITION, aStudent.getLastName());
            registerStatement.setString(FIRST_NAME_POSITION, aStudent.getFirstName());
            registerStatement.setString(EMAIL_POSITION, aStudent.getEmail());
            registerStatement.setString(COMPANY_POSITION, aStudent.getCompany());
            registerStatement.setString(EXPECTATIONS_POSITION, aStudent.getExpectations());
            registerStatement.setDate(COURSE_DATE_POSITION, aStudent.getCourseDate());
            registerStatement.setString(COURSE_TITLE_POSITION, aStudent.getCourseTitle());
            registerStatement.setString(COURSE_LOCATION_POSITION, aStudent.getCourseLocation());
            
            // execute sql
            registerStatement.executeUpdate();            
        }
        catch (Exception e)
        {
            cleanUp();
            log(e);   
        }
    }
    
	/**
	 *  Simply closes the database connection
	 */
    public void destroy()
    {
        log("StudentDataAccessor: destroy");
        cleanUp();
    }
      
	/**
	 *  Simple method for logging messages to console.
	 */
    protected void log(Object msg)
    {
        System.out.println(msg);    
    }
}