scheduledao.java

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

package slsbsample;

/*
 *
 * 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.chapter6.search;
//import j2eebootcamp.developingEJB.chapter6.model.ScheduleVO;

import java.util.Vector;
import java.io.*;
import javax.sql.*;
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.util.Logger;

/**
 ** <code>ScheduleDAO</code> is a user-defined class.
 */
public class ScheduleDAO {
  //private Connection connection = null;
  public ScheduleDAO() throws ScheduleDAOException {
    try {
//      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
    }
    catch (Exception ne) {
      throw new ScheduleDAOException(
          "NamingException while looking up datasource connection =" +
          ne.getMessage());
    }
  }

  public Connection getConnection() throws ScheduleDAOException {
    Connection conn;
    try {
      //open a connection to the datasource
      conn = DriverManager.getConnection(
          "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=RegSystem;User=sa;Password=sa");
//      conn = DriverManager.getConnection("jdbc:odbc:CWJ", "sa", "sa");
      System.out.println("Connection Creation OK");
    }
    catch (SQLException se) {
      throw new ScheduleDAOException(
          " --- SQL exception while attempting to open connection =" +
          se.getMessage());
    }
    return conn;
  }

  public void closeConnection(Connection connection) throws
      ScheduleDAOException {
    try {
      //close the connction to the datasource.
      //if( connection != null && !connection.isClosed())
      if (connection != null) {
        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 CourseID 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;
  }

}