examineedao.java

来自「基于struts框架的在线考试系统」· Java 代码 · 共 847 行 · 第 1/3 页

   * @throws SQLException
   */
  public void updateExaminee(ExamineeActionForm examinee) throws SQLException {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(UPDATE_EXAMINEE);
      pstmt.setString(1, examinee.getName());
      pstmt.setString(2, examinee.getSex());
      pstmt.setString(3, examinee.getAge());
      pstmt.setString(4, examinee.getOrganization_id());
      pstmt.setString(5, examinee.getPost_index());
      pstmt.setString(6, examinee.getState());
      pstmt.setString(7, examinee.getOperation());
      pstmt.setString(8, examinee.getEducation_index());
      pstmt.setString(9, examinee.getAddress());
      pstmt.setString(10, examinee.getPhone());
      pstmt.setString(11, examinee.getEmail());
      pstmt.setString(12, examinee.getRemark());
      pstmt.setString(13, examinee.getExaminee_id());
      pstmt.executeUpdate();

    } catch (SQLException ex) {
      ex.getStackTrace();
      throw new SQLException("SQLExction on : ExamineeDAO.updateExaminee()");
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  /**
   * get all record from DB
   * @throws SQLException
   * @return Collection
   */
  public Collection getAll() throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    Examinee examinee = null;
    Collection list = null;

    list = new ArrayList();

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(GET_ALL_RESULT);
      while (rs.next()) {
        examinee = new Examinee();
        examinee.setExaminee_id(rs.getString(1));
        examinee.setPassword(rs.getString("PASSWORD"));
        examinee.setName(rs.getString("NAME"));
        examinee.setSex(rs.getString("SEX"));
        examinee.setSexName(rs.getString("SEXNAME"));
        examinee.setAge(rs.getString("AGE"));
        examinee.setOrganization_id(rs.getString("ORGANIZATION_ID"));
        examinee.setOrganizationName(rs.getString("ORGANIZATIONNAME"));
        examinee.setPost_index(rs.getString("POST_INDEX"));
        examinee.setPostName(rs.getString("POSTNAME"));
        examinee.setState(rs.getString("STATE"));
        examinee.setStateName(rs.getString("STATENAME"));
        examinee.setOperation(rs.getString("OPERATION"));
        examinee.setOperationName(rs.getString("OPERATIONNAME"));
        examinee.setEducation_index(rs.getString("EDUCATION_INDEX"));
        examinee.setEducationName(rs.getString("EDUCATIONNAME"));
        examinee.setAddress(rs.getString("ADDRESS"));
        examinee.setPhone(rs.getString("PHONE"));
        examinee.setMobile(rs.getString("MOBILE"));
        examinee.setEmail(rs.getString("EMAIL"));
        examinee.setRemark(rs.getString("REMARK"));

        list.add(examinee);
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        rs.close();
        rs = null;
        stmt.close();
        stmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
     
    }
    return list;
  }

  public Collection getSearch(ExamineeActionForm examinnForm, int ipage) throws
      SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Examinee examinee = null;
    Collection list = null;
    list = new ArrayList();

    String examinee_id = examinnForm.getExaminee_id();
    String name = examinnForm.getName();
    String sex = examinnForm.getSex();
    String organization_id = examinnForm.getOrganization_id();
    String post_index = examinnForm.getPost_index();
    String state = examinnForm.getState();
    String operation = examinnForm.getOperation();

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

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

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

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

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

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

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


    try {
      if ("%".equals(post_index)) {
        pstmt = conn.prepareStatement(GET_SHARCH_RESULT,
                                      ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
        pstmt.setString(1, examinee_id);
        pstmt.setString(2, name);
        pstmt.setString(3, sex);
        pstmt.setString(4, organization_id);
        pstmt.setString(5, state);
        pstmt.setString(6, operation);
      } else {
        pstmt = conn.prepareStatement(GET_SHARCH_RESULT2,
                                      ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
        pstmt.setString(1, examinee_id);
        pstmt.setString(2, name);
        pstmt.setString(3, sex);
        pstmt.setString(4, organization_id);
        pstmt.setString(5, state);
        pstmt.setString(6, operation);
        pstmt.setString(7, post_index);
      }
      rs = pstmt.executeQuery();

      if (false == rs.last()) {
        rowCount = 0;
        pageCount = 0;
        ipage = 0;
        return list;
      }

      this.rowCount = rs.getRow();
      int offset = 1;
      int pagesize = getLength();
      if (getLength() < 1) {
        pagesize = rowCount;
        pageCount = 1;
      } else {
        pageCount = rowCount / getLength() +
            ( (rowCount % getLength()) > 0 ? 1 : 0);
        offset = (ipage - 1) * getLength() + 1;
        if (offset < 1) {
          offset = 1;
        }

        if (offset > rowCount) {
          offset = rowCount;
        }
      }
      rs.absolute(offset);

      for (int i = 0; i < pagesize && offset < rowCount + 1; i++, offset++) {
        examinee = new Examinee();
        examinee.setExaminee_id(rs.getString(1));
        examinee.setPassword(rs.getString("PASSWORD"));
        examinee.setName(rs.getString("NAME"));
        examinee.setSex(rs.getString("SEX"));
        examinee.setSexName(rs.getString("SEXNAME"));
        examinee.setAge(rs.getString("AGE"));
        examinee.setOrganization_id(rs.getString("ORGANIZATION_ID"));
        examinee.setOrganizationName(rs.getString("ORGANIZATIONNAME"));
        examinee.setPost_index(rs.getString("POST_INDEX"));
        examinee.setPostName(rs.getString("POSTNAME"));
        examinee.setState(rs.getString("STATE"));
        examinee.setStateName(rs.getString("STATENAME"));
        examinee.setOperation(rs.getString("OPERATION"));
        examinee.setOperationName(rs.getString("OPERATIONNAME"));
        examinee.setEducation_index(rs.getString("EDUCATION_INDEX"));
        examinee.setEducationName(rs.getString("EDUCATIONNAME"));
        examinee.setAddress(rs.getString("ADDRESS"));
        examinee.setPhone(rs.getString("PHONE"));
        examinee.setMobile(rs.getString("MOBILE"));
        examinee.setEmail(rs.getString("EMAIL"));
        examinee.setRemark(rs.getString("REMARK"));

        rs.next();
        list.add(examinee);
      }
    } 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 Collection getSearchWithoutPage(RandomDistributeExamPaperForm randomDistributeExamPaperForm) throws
      SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Examinee examinee = null;
    Collection list = null;
    list = new ArrayList();

    String examinee_id = randomDistributeExamPaperForm.getExaminee_id();
    String name = randomDistributeExamPaperForm.getName();
    String sex = randomDistributeExamPaperForm.getSex();
    String organization_id = randomDistributeExamPaperForm.getOrganization_id();
    String post_index = randomDistributeExamPaperForm.getPost_index();
    String state = randomDistributeExamPaperForm.getState();
    String operation = randomDistributeExamPaperForm.getOperation();

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

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

    if (sex == null || "".equals(sex)) {
      sex = "%";
    } else if (! ("%".equals(sex))) {