rosterdao.java

来自「100多M的J2EE培训内容」· Java 代码 · 共 193 行

package bmpsample;

//package j2eebootcamp.developingEJB.chapter9.roster;

import java.util.Vector;
import java.lang.reflect.*;
import java.io.*;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Date;
import java.sql.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.Context;
import java.util.Calendar;

//import j2eebootcamp.developingEJB.chapter9.roster.*;

/**
 ** <code>RosterDAO</code> is a user-defined class.
 */
public class RosterDAO {
  //private Connection connection = null;
  //private DataSource dataSource = null;

  public RosterDAO() throws RosterDAOException {
    try {
//           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      /* InitialContext ictx = new InitialContext();
       dataSource = (DataSource) ictx.lookup("java:comp/env/jdbc/JCampDS");
       System.out.println("RosterDAO jcampDataSource lookup OK!");*/

    }
    catch (Exception ne) {
      throw new RosterDAOException(
          "NamingException while looking up datasource connection =" +
          ne.getMessage());
    }
  }

  public Connection getConnection() throws RosterDAOException {
    Connection connection = null;
    try {
      connection = DriverManager.getConnection(
          "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=RegSystem;User=sa;Password=sa");
      //connection = dataSource.getConnection();
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to open connection =" +
          se.getMessage());
    }
    return connection;
  }

  public void closeConnection(Connection connection) throws RosterDAOException {
    try {
      if (connection != null && !connection.isClosed()) {
        connection.close();
      }
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to close connection =" +
          se.getMessage());
    }
  }

  public void closeResultSet(ResultSet rset) throws RosterDAOException {
    try {
      if (rset != null) {
        rset.close();
      }
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to close result set =" +
          se.getMessage());
    }
  }

  public void closeStatement(PreparedStatement pstmt) throws RosterDAOException {
    try {
      if (pstmt != null) {
        pstmt.close();
      }
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to close statement =" +
          se.getMessage());
    }
  }

  public void insert(String scheduleID, String studentID) throws
      RosterDAOException {
    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();

    Calendar calendar = Calendar.getInstance();
    java.util.Date theTime = calendar.getTime();
    java.sql.Date now = new java.sql.Date(theTime.getTime());

    try {
      String updateStatement = "INSERT INTO RosterEJBTable (StudentID, ScheduleID, RegistrationDate) VALUES (?, ?, ?)";
      //String updateStatement = "INSERT INTO RosterEJBTable (StudentID, ScheduleID) VALUES (?, ?)";
      pstmt = conn.prepareStatement(updateStatement);

      pstmt.setString(1, studentID);
      pstmt.setString(2, scheduleID);
      pstmt.setDate(3, now);

      int rowCount = pstmt.executeUpdate();
      if (rowCount == 0) {
        throw new RosterDAOException("Update Failed for StudentID =" +
                                     studentID + " with ScheduleID =" +
                                     scheduleID);
      }

      pstmt.close();
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to open connection =" +
          se.getMessage());
    }
  }

  public void delete(String scheduleID, String studentID) throws
      RosterDAOException {
    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();
    try {
      String updateStatement =
          "DELETE FROM RosterEJBTable WHERE ScheduleID= ? AND studentID = ?";
      pstmt = conn.prepareStatement(updateStatement);

      pstmt.setString(1, scheduleID);
      pstmt.setString(2, studentID);

      int rowCount = pstmt.executeUpdate();
      if (rowCount == 0) {
        throw new RosterDAOException("DELETE Failed for StudentID =" +
                                     studentID + " with ScheduleID =" +
                                     scheduleID);
      }

      pstmt.close();
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to open connection =" +
          se.getMessage());
    }

  }

  public Vector getClassList(String studentID) throws RosterDAOException {
    Vector classList = new Vector(20);
    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();
    ResultSet rset = null;

    try {
      String updateStatement =
          "SELECT ScheduleID FROM RosterEJBTable WHERE studentID= ?";
      pstmt = conn.prepareStatement(updateStatement);

      pstmt.setString(1, studentID);

      rset = pstmt.executeQuery();

      while (rset.next()) {
        classList.addElement(rset.getString("ScheduleID"));
      }

      pstmt.close();
    }
    catch (SQLException se) {
      throw new RosterDAOException(
          " SQL exception while attempting to open connection =" +
          se.getMessage());
    }

    return classList;
  }

} //;-)