scheduledao.java~2~

来自「100多M的J2EE培训内容」· JAVA~2~ 代码 · 共 221 行

package sfsbsample;

/*
 *
 * Copyright 2001, 2002 JavaCamp.com, Inc. All Rights Reserved.
 *
 * Grant the rights to the purchaser of the book to use the source code.
 * .
 * @author	Pravin Tulachan
 * @version	1.0
 * @see
 * @since
 *
 */

//package j2eebootcamp.developingEJB.common;

import java.util.Vector;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.Date;
import java.sql.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.Context;

//import j2eebootcamp.developingEJB.common.ScheduleVO;

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

  public ScheduleDAO() throws ScheduleDAOException {
    try {
//      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      // need to look up the data source, so it can access the database
      /*InitialContext ictx = new InitialContext();
           dataSource = (DataSource) ictx.lookup("java:comp/env/jdbc/JCampDS");
           System.out.println("ScheduleDAO jcampDataSource lookup OK!");*/

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

  }

  public Connection getConnection() throws ScheduleDAOException {
    Connection connection = null;
    try {
      //open a connection to the datasource
      //connection = dataSource.getConnection();
      connection = DriverManager.getConnection("jdbc:odbc:CWJ", "sa", "sa");
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(
          " --- SQL exception while attempting to open connection =" +
          se.getMessage());
    }
    return connection;
  }

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

  public void closeResultSet(ResultSet rset) throws ScheduleDAOException {
    try {
      //a good practise - close resouces you don't need. close reasultset
      if (rset != null) {
        rset.close();
      }
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(
          " --- SQL exception while attempting to close result set =" +
          se.getMessage());
    }
  }

  public void closeStatement(Statement stmt) throws ScheduleDAOException {
    try {
      //a good practise - close resouces you don't need. close statement
      if (stmt != null) {
        stmt.close();
      }
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(
          " --- SQL exception while attempting to close statement =" +
          se.getMessage());
    }
  }

  public Vector searchByCourseTitle(String courseTitle) throws
      ScheduleDAOException {
    Statement stmt = null;
    ResultSet rset = null;
    //We'll use the Vector to hold and pass the fields from the result set.
    Vector scheduleList = new Vector(20);

    // get the connection to the database
    Connection conn = this.getConnection();

    //create a query statement
    String queryString = "SELECT sid, courseid, locationid, city, state, country, startdate, enddate, status, title, instructorID,  price, maxenrollment, currentenrollment FROM ScheduleEJBTable s, CourseEJBTable c,  LocationEJBTable l  where courseid = c.id AND locationid = l.id AND  title LIKE '%" +
        courseTitle + "%'";

    System.out.println("   --- ScheduleDAO  - searchByCourseTitle ");
    System.out.println("   --- queryString = " + queryString);
    try {
      stmt = conn.createStatement();
      System.out.println(" stmt creation OK");

      rset = stmt.executeQuery(queryString);
      System.out.println("  --- SchedeulDAO -- got the resulset rset ");

      while (rset.next()) {
        ScheduleVO schedule = new ScheduleVO(
            rset.getString("sid"), rset.getString("courseid"),
            rset.getString("locationid"),
            rset.getString("city"), rset.getString("state"),
            rset.getString("country"),
            rset.getDate("startdate"), rset.getDate("enddate"),
            rset.getString("status"), rset.getString("title"),
            rset.getFloat("price"), rset.getInt("maxenrollment"),
            rset.getInt("currentenrollment")
            );

        System.out.println(
            "   --- ScheduleDAO - created schedule item, before adding to a vector");

        scheduleList.addElement(schedule);
      }
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(" Query exception " + se.getMessage());
    }
    finally {
      closeResultSet(rset);
      closeStatement(stmt);
      closeConnection(conn);
    }
    System.out.println(
        "   --- ScheduleDAO  - searchByCourseTitle - returning Vector ");
    return scheduleList;
  }

  public ScheduleVO searchByScheduleID(String scheduleID) throws
      ScheduleDAOException {
    Statement stmt = null;
    ResultSet rset = null;
    ScheduleVO schedule = null;

    Connection conn = this.getConnection();
    // create a query statement with scheduleID as the search criteria
    String queryString = "SELECT sid, courseid, locationid, city, state, country, startdate, enddate, status, title, instructorID,  price, maxenrollment, currentenrollment FROM ScheduleEJBTable s, CourseEJBTable c,  LocationEJBTable l  where courseid = c.id AND locationid = l.id AND sid LIKE '%" +
        scheduleID + "%'";

    System.out.println("queryString = " + queryString);

    try {
      stmt = conn.createStatement();
      System.out.println("  --- ScheduleDAO -- stmt creation OK");

      rset = stmt.executeQuery(queryString);
      System.out.println(" got the resulset rset ");

      while (rset.next()) {
        schedule = new ScheduleVO(
            rset.getString("sid"), rset.getString("courseid"),
            rset.getString("locationid"),
            rset.getString("city"), rset.getString("state"),
            rset.getString("country"),
            rset.getDate("startdate"), rset.getDate("enddate"),
            rset.getString("status"), rset.getString("title"),
            rset.getFloat("price"), rset.getInt("maxenrollment"),
            rset.getInt("currentenrollment")

            );
      }
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(" ScheduleDAO Query exception " +
                                     se.getMessage());
    }
    finally {
      closeResultSet(rset);
      closeStatement(stmt);
      closeConnection(conn);
    }
    System.out.println(
        "  --- ScheduleDAO  - searchByCourseID - returning schedule ");
    if (schedule == null) {
      System.out.println(" in ScheduleDAO --  returning schedule is null");
    }
    return schedule;
  }

}