examinationpaperdao.java

该系统采用了B/S结构模式

      ex.getStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }

  }

  public void updatePaperBaseinfo(ExaminationPaper examinationPaper) {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(UPDATE_PAPER_BASEINFO);
      pstmt.setString(1, examinationPaper.getE_name());
      pstmt.setString(2, examinationPaper.getE_type());
      pstmt.setInt(3, examinationPaper.getE_timer());
      pstmt.setString(4, examinationPaper.getE_begin());
      pstmt.setString(5, examinationPaper.getE_end());
      pstmt.setFloat(6, examinationPaper.getE_passvalue());
      pstmt.setFloat(7, examinationPaper.getE_total());
      pstmt.setLong(8, examinationPaper.getE_id());
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  /**
   * clearIdList
   *
   * @param e_id String
   * @param action String
   */
  public void clearIdList(String e_id, String action) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    GetQuestionCount gqc = null;
    String[] questionTypes = null;
    try {
      pstmt = conn.prepareStatement(GET_IDLIST);
      pstmt.setString(1, e_id);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        String idList = rs.getString(1);
        gqc = new GetQuestionCount();
        questionTypes = gqc.GetTypeIdListWithoutDecorate(idList);
        if ("single".equals(action)) {
          questionTypes[0] = "0";
        } else if ("multi".equals(action)) {
          questionTypes[1] = "0";
        } else if ("fitin".equals(action)) {
          questionTypes[2] = "0";
        } else if ("answer".equals(action)) {
          questionTypes[3] = "0";
        }
        idList = "@@" + questionTypes[0] + "@@" + questionTypes[1] + "@@" +
            questionTypes[2] + "@@" +
            questionTypes[3];
        pstmt.close();
        pstmt = null;

        pstmt = conn.prepareStatement(CLEAR_IDLIST);
        pstmt.setString(1, idList);
        pstmt.setString(2, e_id);
        pstmt.executeUpdate();
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  //Get by String!!
  public ExaminationPaper findBykey(long e_id) {

    PreparedStatement pstmt = null;
    ExaminationPaper examinationPaper = null;
    ResultSet rs = null;
    try {
      pstmt = conn.prepareStatement(GETBYKEY);
      pstmt.setLong(1, e_id);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        examinationPaper = new ExaminationPaper();
        examinationPaper.setE_name(rs.getString("E_NAME"));
        examinationPaper.setE_id(rs.getLong("E_ID"));
        examinationPaper.setE_type(rs.getString("E_TYPE"));
        examinationPaper.setE_timer(rs.getInt("E_TIMER"));
        examinationPaper.setE_idlist(rs.getString("E_IDLIST"));
        examinationPaper.setE_begin(rs.getString("E_BEGIN"));
        examinationPaper.setE_end(rs.getString("E_END"));
        examinationPaper.setE_examineeList(rs.getString(
            "E_EXAMINEELIST"));
        examinationPaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        examinationPaper.setE_total(rs.getFloat("E_TOTAL"));
        examinationPaper.setE_state(rs.getString("E_STATE"));
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        rs.close();
        rs = null;
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
    return examinationPaper;
  }

  //getByKeyWhenE_stateNull!!
  public ExaminationPaper getByKeyWhenE_stateNull(long e_id) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    ExaminationPaper examinationPaper = new ExaminationPaper();

    try {
      pstmt = conn.prepareStatement(GET_BY_KEY_WHEN_ESTATE_NULL);
      pstmt.setLong(1, e_id);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        examinationPaper.setE_id(rs.getLong("e_id"));
        examinationPaper.setE_name(rs.getString("e_name"));
        examinationPaper.setE_type(rs.getString("e_type"));
        examinationPaper.setE_timer(rs.getInt("e_timer"));
        examinationPaper.setE_begin(rs.getString("e_begin"));
        examinationPaper.setE_end(rs.getString("e_end"));
        examinationPaper.setE_idlist(rs.getString("e_idlist"));
        examinationPaper.setE_examineeList(rs.getString("e_examineelist"));
        examinationPaper.setE_passvalue(rs.getFloat("e_passvalue"));
        examinationPaper.setE_total(rs.getFloat("e_total"));
        examinationPaper.setE_state(rs.getString("e_state"));

      }

    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        rs.close();
        rs = null;
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
    return examinationPaper;
  }

  //GetAll ExaminationPaper!!
  public Collection getAll() {
    PreparedStatement pstmt = null;
    ExaminationPaper examinationPaper = new ExaminationPaper();
    ResultSet rs = null;
    Collection list = null;
    try {
      list = new ArrayList();
      pstmt = conn.prepareStatement(GETALL_EXAMINATIONPAPER);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        examinationPaper.setE_type(rs.getString("E_TYPE"));
        examinationPaper.setE_name(rs.getString("E_NAME"));
        examinationPaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        examinationPaper.setE_id(rs.getLong("E_ID"));
        examinationPaper.setE_timer(rs.getInt("E_TIMER"));
        examinationPaper.setE_idlist(rs.getString("E_IDLIST"));
        examinationPaper.setE_begin(rs.getString("E_BEGIN"));
        examinationPaper.setE_end(rs.getString("E_END"));
        examinationPaper.setE_examineeList(rs.getString(
            "E_EXAMINEELIST"));
        examinationPaper.setE_total(rs.getInt("E_TOTAL"));
        examinationPaper.setE_state(rs.getString("E_STATE"));
        list.add(examinationPaper);
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        rs.close();
        rs = null;
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
    return list;
  }

  public ExaminationPaper findByE_state() {
    ExaminationPaper examinationPaper = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      pstmt = conn.prepareStatement(FINDBYE_STATE);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        examinationPaper = new ExaminationPaper();
        examinationPaper.setE_id(rs.getLong("E_ID"));
        examinationPaper.setE_name(rs.getString("E_NAME"));
        examinationPaper.setE_type(rs.getString("E_TYPE"));
        examinationPaper.setE_timer(rs.getInt("E_TIMER"));
        examinationPaper.setE_idlist(rs.getString("E_IDLIST"));
        examinationPaper.setE_begin(rs.getString("E_BEGIN"));
        examinationPaper.setE_end(rs.getString("E_END"));
        examinationPaper.setE_examineeList(rs.getString(
            "E_EXAMINEELIST"));
        examinationPaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        examinationPaper.setE_total(rs.getInt("E_TOTAL"));
        examinationPaper.setE_state(rs.getString("E_STATE"));
      }

    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        rs.close();
        rs = null;
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
    return examinationPaper;
  }

  //get search!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  public Collection getSearch(SearchActionForm searchForm, int ipage) throws
      SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    ExaminationPaper testpaper = null;
    Collection list = null;
    list = new ArrayList();

    String e_type = searchForm.getE_type();
    String e_name = searchForm.getE_name();
    String e_state = searchForm.getE_state();
    String e_begin = searchForm.getE_begin();
    String e_end = searchForm.getE_end();

    String GET_SEARCH_RESULT_NOTIME =
        "SELECT E.E_ID E_ID,E.E_NAME E_NAME,B1.B_VALUE E_TYPE,E.E_TIMER E_TIMER,E.E_IDLIST E_IDLIST,date_format(E.E_BEGIN,'%Y-%m-%d') E_BEGIN,date_format(E.E_END,'%Y-%m-%d') E_END,E.E_EXAMINEELIST E_EXAMINEELIST,E.E_PASSVALUE E_PASSVALUE,E.E_TOTAL E_TOTAL,B2.B_VALUE E_STATENAME,E.E_STATE AS E_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE B1.B_ID=E.E_TYPE AND B2.B_ID=E.E_STATE AND E_TYPE LIKE ? AND e_name LIKE ? AND e_state LIKE ? order by e.e_begin desc";
    String GET_SEARCH_RESULT_NOENDTIME =
        "SELECT E.E_ID E_ID,E.E_NAME E_NAME,B1.B_VALUE E_TYPE,E.E_TIMER E_TIMER,E.E_IDLIST E_IDLIST,date_format(E.E_BEGIN,'%Y-%m-%d') E_BEGIN,date_format(E.E_END,'%Y-%m-%d') E_END,E.E_EXAMINEELIST E_EXAMINEELIST,E.E_PASSVALUE E_PASSVALUE,E.E_TOTAL E_TOTAL,B2.B_VALUE E_STATENAME,E.E_STATE AS E_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE B1.B_ID=E.E_TYPE AND B2.B_ID=E.E_STATE AND E_BEGIN>=date_format('" +
        e_begin +
        "','%Y-%m-%d') and e_type LIKE ? AND e_name LIKE ? AND e_state LIKE ? order by e.e_begin desc";
    String GET_SEARCH_RESULT_NOBEGINTIME =
        "SELECT E.E_ID E_ID,E.E_NAME E_NAME,B1.B_VALUE E_TYPE,E.E_TIMER E_TIMER,E.E_IDLIST E_IDLIST,date_format(E.E_BEGIN,'%Y-%m-%d') E_BEGIN,date_format(E.E_END,'%Y-%m-%d') E_END,E.E_EXAMINEELIST E_EXAMINEELIST,E.E_PASSVALUE E_PASSVALUE,E.E_TOTAL E_TOTAL,B2.B_VALUE E_STATENAME,E.E_STATE AS E_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE B1.B_ID=E.E_TYPE AND B2.B_ID=E.E_STATE AND E_END<=date_format('" +
        e_end +
        "','%Y-%m-%d') and e_type LIKE ? AND e_name LIKE ? AND e_state LIKE ? order by e.e_begin desc";
    String GET_SEARCH_RESULT_BOTHTIME =
        "SELECT E.E_ID E_ID,E.E_NAME E_NAME,B1.B_VALUE E_TYPE,E.E_TIMER E_TIMER,E.E_IDLIST E_IDLIST,date_format(E.E_BEGIN,'%Y-%m-%d') E_BEGIN,date_format(E.E_END,'%Y-%m-%d') E_END,E.E_EXAMINEELIST E_EXAMINEELIST,E.E_PASSVALUE E_PASSVALUE,E.E_TOTAL E_TOTAL,B2.B_VALUE E_STATENAME,E.E_STATE AS E_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE B1.B_ID=E.E_TYPE AND B2.B_ID=E.E_STATE AND E_BEGIN>=date_format('" +
        e_begin + "','%Y-%m-%d') and e_end<=date_format('" + e_end +
        "','%Y-%m-%d') and e_type LIKE ? AND e_name LIKE ? AND e_state LIKE ? order by e.e_begin desc";

    if ("".equals(e_type) ||
        (e_type == null)) {
      e_type = "%";
    } else {
      conditionStr += "&e_type=" + e_type;
    }

    if ( ("".equals(e_name)) ||
        (e_name == null)) {
      e_name = "%";
    } else {
      conditionStr += "&e_name=" + e_name;
    }

    if ( ("".equals(e_state)) ||
        (e_state == null)) {
      e_state = "%";
    } else {
      conditionStr += "&e_state=" + e_state;
    }

    try {
      if ( ("".equals(e_begin)) || (e_begin == null))

      {
        if ( ("".equals(e_end)) || (e_end == null)) {
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_NOTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        } else {