/*

 *

 * Copyright 2000 Sun Microsystems, Inc. All Rights Reserved.

 * 

 * This software is the proprietary information of Sun Microsystems, Inc.  

 * Use is subject to license terms.

 * 

 */



import java.rmi.RemoteException; 

import javax.ejb.*;

import java.sql.*;

import javax.sql.*;

import java.util.*;

import javax.naming.*;





public class EnrollerEJB implements SessionBean {

 

   private Connection con;

   private String dbName = "java:comp/env/jdbc/CollegeDB";



   public void enroll(String studentId, String courseId) {



       try {

          insertEntry(studentId, courseId);

       } catch (Exception ex) {

           throw new EJBException("enroll: " +

              ex.getMessage());

       }

   }



   public void unEnroll(String studentId, String courseId) {



       try {

          deleteEntry(studentId, courseId);

       } catch (Exception ex) {

           throw new EJBException("unEnroll: " +

              ex.getMessage());

       }

   }



   public void deleteStudent(String studentId) {



       try {

          deleteStudentEntries(studentId);

       } catch (Exception ex) {

           throw new EJBException("deleteStudent: " +

              ex.getMessage());

       }

   }



   public void deleteCourse(String courseId) {



       try {

          deleteCourseEntries(courseId);

       } catch (Exception ex) {

           throw new EJBException("deleteCourse: " +

              ex.getMessage());

       }

   }



   public ArrayList getStudentIds(String courseId) {



       try {

          return selectStudent(courseId);

       } catch (Exception ex) {

           throw new EJBException("getStudentIds: " +

              ex.getMessage());

       }

   }



   public ArrayList getCourseIds(String studentId) {



       try {

          return selectCourse(studentId);

       } catch (Exception ex) {

           throw new EJBException("getCourseIds: " +

              ex.getMessage());

       }

   }



   public void ejbCreate() {



      try {

         makeConnection();

      } catch (Exception ex) {

          throw new EJBException("Unable to connect to database. " +

             ex.getMessage());

      }

   }



   public void ejbRemove() {



      try {

         con.close();

      } catch (SQLException ex) {

          throw new EJBException("ejbRemove: " + ex.getMessage());

      }

   }



   public EnrollerEJB() {}

   public void ejbActivate() {}

   public void ejbPassivate() {}

   public void setSessionContext(SessionContext sc) {}



/*********************** Database Routines *************************/



   private void makeConnection() throws NamingException, SQLException {



      InitialContext ic = new InitialContext();

      DataSource ds = (DataSource) ic.lookup(dbName);

      con =  ds.getConnection();

   }



   private void insertEntry(String studentId, String courseId)

       throws SQLException {



          String insertStatement =

                "insert into enrollment values ( ? , ? )";

          PreparedStatement prepStmt = 

                con.prepareStatement(insertStatement);



          prepStmt.setString(1, studentId);

          prepStmt.setString(2, courseId);



          prepStmt.executeUpdate();

          prepStmt.close();

   }



   private void deleteEntry(String studentId, String courseId) 

      throws SQLException {



      String deleteStatement =

            "delete from enrollment " +

            "where studentid = ? and courseid = ?";

      PreparedStatement prepStmt =

            con.prepareStatement(deleteStatement);



      prepStmt.setString(1, studentId);

      prepStmt.setString(2, courseId);

      prepStmt.executeUpdate();

      prepStmt.close();

   }



   private void deleteStudentEntries(String studentId)

      throws SQLException {



      String deleteStatement =

            "delete from enrollment " +

            "where studentid = ?";

      PreparedStatement prepStmt =

            con.prepareStatement(deleteStatement);



      prepStmt.setString(1, studentId);

      prepStmt.executeUpdate();

      prepStmt.close();

   }



   private void deleteCourseEntries(String courseId)

      throws SQLException {



      String deleteStatement =

            "delete from enrollment " +

            "where courseid = ?";

      PreparedStatement prepStmt =

            con.prepareStatement(deleteStatement);



      prepStmt.setString(1, courseId);

      prepStmt.executeUpdate();

      prepStmt.close();

   }



   private ArrayList selectStudent(String courseId) 

      throws SQLException {



      String selectStatement =

            "select studentid " +

            "from enrollment where courseid = ? ";

      PreparedStatement prepStmt = 

            con.prepareStatement(selectStatement);



      prepStmt.setString(1, courseId);

      ResultSet rs = prepStmt.executeQuery();

      ArrayList a = new ArrayList();



      while (rs.next()) {

         String id = rs.getString(1);

         a.add(id);

      }



      prepStmt.close();

      return a;

   }





   private ArrayList selectCourse(String studentId) 

      throws SQLException {



      String selectStatement =

            "select courseid " +

            "from enrollment where studentid = ? ";

      PreparedStatement prepStmt = 

            con.prepareStatement(selectStatement);



      prepStmt.setString(1, studentId);

      ResultSet rs = prepStmt.executeQuery();

      ArrayList a = new ArrayList();



      while (rs.next()) {

         String id = rs.getString(1);

         a.add(id);

      }



      prepStmt.close();

      return a;

   }



} // EnrollerEJB