studentdao.java

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

package bmpsample;

//package j2eebootcamp.developingEJB.chapter9.student;

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.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.Context;
import java.util.Calendar;

// Implementation of Data Access Object pattern

public class StudentDAO {

  //private Connection connection = null;
  //private DataSource dataSource = null;

  public StudentDAO() throws StudentDAOException {
    System.out.println("--->>> StudentDAO  - StudenDAO() ***** ");
    try {
      System.out.println(
          "\n====== in StudentDAO -- StudentDAO() before initialcontext =====");

      InitialContext ictx = new InitialContext();
      System.out.println(
          "\n********* in StudentDAO -- StudentDAO() after initialcontext *****");

      //dataSource = (DataSource) ictx.lookup("java:comp/env/jdbc/JCampDS");
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      System.out.println("StudentDAO jcampDataSource lookup OK!");

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

  }

  public Connection getConnection() throws StudentDAOException {
    System.out.println("\n===== StudentDAO  - getConnection() ********");

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

  public void closeConnection(Connection connection) throws StudentDAOException {
    System.out.println("\n===== StudentDAO  - closeConnection() ********");

    try {
      if (connection != null && !connection.isClosed()) {
        connection.close();
      }
    }
    catch (SQLException se) {
      String msg = se.getMessage();
      throw new StudentDAOException(
          " SQL exception while attempting to close connection =" + msg);
    }
  }

  public void closeResultSet(ResultSet rset) throws StudentDAOException {
    System.out.println("\n==== StudentDAO  -closeResultSet() ********");

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

  public void closeStatement(PreparedStatement pstmt) throws
      StudentDAOException {
    System.out.println("\n ==== StudentDAO  - closeStatement() ********");

    try {
      if (pstmt != null) {
        pstmt.close();
      }
    }
    catch (SQLException se) {
      throw new StudentDAOException(
          " SQL exception while attempting to close statement =" +
          se.getMessage());
    }
  }

  public void insertStudent(String pKey, String password, String firstName,
                            String lastName, String email, String phone,
                            String companyName) throws StudentDAOException {

    System.out.println("----->>> StudentDAO  - insertStudent() ");

    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 {
      pstmt = conn.prepareStatement("Insert into StudentEJBTable(id, password, firstName, lastName, email, phone, companyName, createDate) values(?,?,?,?,?,?,?,?)");

      pstmt.setString(1, pKey);
      pstmt.setString(2, password);
      pstmt.setString(3, firstName);
      pstmt.setString(4, lastName);
      pstmt.setString(5, email);
      pstmt.setString(6, phone);
      pstmt.setString(7, companyName);
      pstmt.setDate(8, now);

      System.out.println(" StudentDAO  prepared statment OK");

      pstmt.executeUpdate();
      System.out.println(" StudentDAO Student inserted");

    }
    catch (SQLException se) {
      throw new StudentDAOException(" Query exception " + se.getMessage());
    }
    finally {
      closeStatement(pstmt);
      closeConnection(conn);
    }
    System.out.println("StudentDAO  - inserted successfully");

  }

  public void updateStudent(String pKey, String password, String firstName,
                            String lastName, String email, String phone,
                            String companyName) throws StudentDAOException {
    System.out.println("--->>> StudentDAO  - updateStudent() key =" + pKey +
                       ", password=" + password + ", last=" + lastName +
                       ", first=" + firstName + ", email=" + email + ", phone=" +
                       phone + ", company=" + companyName);

    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();

    try {
      String updateStatement = "UPDATE StudentEJBTable set password = ?, firstName = ?, lastName = ?, email = ?, phone = ?, companyName = ? WHERE id = ?";
      System.out.println("StudentDAO updateStatement ");
      pstmt = conn.prepareStatement(updateStatement);

      pstmt.setString(1, password);
      pstmt.setString(2, firstName);
      pstmt.setString(3, lastName);
      pstmt.setString(4, email);
      pstmt.setString(5, phone);
      pstmt.setString(6, companyName);
      pstmt.setString(7, pKey);

      int rowCount = pstmt.executeUpdate();
      if (rowCount == 0) {
        throw new StudentDAOException("Update Failed for Student primary key =" +
                                      pKey);
      }

    }
    catch (SQLException se) {
      String msg = se.getMessage();
      throw new StudentDAOException(
          " SQL exception while attempting to UPDATE =" + msg);
    }
    finally {
      closeStatement(pstmt);
      closeConnection(conn);
    }

  }

  public void deleteStudent(String pKey) throws StudentDAOException {
    System.out.println("---->>> StudentDAO  - delete() pkey =" + pKey);

    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();

    try {
      String updateStatement = "DELETE FROM StudentEJBTable WHERE id = ?";
      pstmt = conn.prepareStatement(updateStatement);

      pstmt.setString(1, pKey);

      int rowCount = pstmt.executeUpdate();
      if (rowCount == 0) {
        throw new StudentDAOException("DELETE Failed for Student id =" + pKey);
      }

    }
    catch (SQLException se) {
      throw new StudentDAOException(
          " SQL exception while attempting to DELETE =" + se.getMessage());
    }
    finally {
      closeStatement(pstmt);
      closeConnection(conn);
    }
  }

  public ResultSet selectByPrimaryKey(String pKey) throws StudentDAOException {
    System.out.println("---->>> StudentDAO  - selectByPrimaryKey pkey =" + pKey);

    PreparedStatement pstmt = null;
    Connection conn = this.getConnection();

    ResultSet rs = null;

    System.out.println(
        "\nStudentDAO  - in selectPrimaryKey() before select statement ********");
    try {
      String selectStatement = "SELECT * from StudentEJBTable where ID = ?";
      pstmt = conn.prepareStatement(selectStatement);

      pstmt.setString(1, pKey);

      rs = pstmt.executeQuery();
      System.out.println(
          "\nStudentDAO  - in selectPrimaryKey() successful with resultset size =" +
          rs.getFetchSize());
    }
    catch (SQLException se) {
      String msg = se.getMessage();
      throw new StudentDAOException(
          " SQL exception while attempting to SELECT By Primary Key =" + msg);
    }
    finally {
      closeStatement(pstmt);
      closeConnection(conn);
    }

    return rs;
  }

} //;-)