examinationpaperdao.java

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

    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_STATE,E.E_STATE AS ESTATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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_STATE,E.E_STATE AS ESTATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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_STATE,E.E_STATE AS ESTATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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_STATE,E.E_STATE AS ESTATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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 {
          conditionStr += "&e_end=" + e_end;
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_NOBEGINTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        }
      } else {
        conditionStr += "&e_begin=" + e_begin;
        if ( ("".equals(e_end)) || (e_end == null)) {
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_NOENDTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        } else {
          conditionStr += "&e_end=" + e_end;
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_BOTHTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        }
      }
      pstmt.setString(1, e_type);
      pstmt.setString(2, e_name);
      pstmt.setString(3, e_state);
      rs = pstmt.executeQuery();
      int j = 0;
      while (rs.next()) {
        j++;
      }

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

      rs.absolute( -1);
      this.rowCount = rs.getRow();
      int offset = 1;
      int pagesize = getLength();
      if (getLength() < 1) {
        pagesize = rowCount;
        this.pageCount = 1;
      } else {
        this.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++) {
        testpaper = new ExaminationPaper();
        testpaper.setE_id(rs.getLong("E_ID"));
        testpaper.setE_type(rs.getString("E_TYPE"));
        testpaper.setE_name(rs.getString("E_NAME"));
        testpaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        testpaper.setE_begin(rs.getString("E_BEGIN"));
        testpaper.setE_end(rs.getString("E_END"));
        testpaper.setE_state(rs.getString("ESTATE"));
        testpaper.setE_stateName(rs.getString("E_STATE"));
        testpaper.setE_total(rs.getFloat("E_TOTAL"));
        testpaper.setE_examineeList(rs.getString("E_EXAMINEELIST") == null ? "" :
                                    rs.getString("E_EXAMINEELIST"));

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

    String e_type = randomDistributeExamPaperForm.getE_type();
    String e_name = randomDistributeExamPaperForm.getE_name();
    String e_state = randomDistributeExamPaperForm.getE_state();
    String e_begin = randomDistributeExamPaperForm.getE_begin();
    String e_end = randomDistributeExamPaperForm.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_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND B1.B_ID=E.E_TYPE AND B2.B_ID=E.E_STATE AND E_TYPE LIKE ? AND e_name LIKE ? AND e_state='038'";
    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_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now) AND 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='038'";
    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_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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='038'";
    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_STATE FROM EX_EXAMINATIONPAPER E,EX_BASEINFO B1,EX_BASEINFO B2 WHERE E.E_END>(now()) AND 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='038'";

    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 {
          conditionStr += "&e_end=" + e_end;
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_NOBEGINTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        }
      } else {
        conditionStr += "&e_begin=" + e_begin;
        if ( ("".equals(e_end)) || (e_end == null)) {
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_NOENDTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        } else {
          conditionStr += "&e_end=" + e_end;
          pstmt = conn.prepareStatement(GET_SEARCH_RESULT_BOTHTIME,
                                        ResultSet.
                                        TYPE_SCROLL_SENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
        }
      }
      pstmt.setString(1, e_type);
      pstmt.setString(2, e_name);
      rs = pstmt.executeQuery();

      /*int j = 0;
      while (rs.next()) {
        j++;
      }

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

      rs.absolute( -1);
      this.rowCount = rs.getRow();
      int offset = 1;
      int pagesize = getLength();
      if (getLength() < 1) {
        pagesize = rowCount;
        this.pageCount = 1;
      } else {
        this.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++) {
        testpaper = new ExaminationPaper();
        testpaper.setE_id(rs.getLong("E_ID"));
        testpaper.setE_type(rs.getString("E_TYPE"));
        testpaper.setE_name(rs.getString("E_NAME"));
        testpaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        testpaper.setE_begin(rs.getString("E_BEGIN"));
        testpaper.setE_end(rs.getString("E_END"));
        testpaper.setE_state(rs.getString("E_STATE"));
        testpaper.setE_total(rs.getFloat("E_TOTAL"));
        testpaper.setE_examineeList(rs.getString("E_EXAMINEELIST") == null ? "" :
                                    rs.getString("E_EXAMINEELIST"));

        rs.next();
        list.add(testpaper);
      }*/
  while(rs.next()){
    testpaper = new ExaminationPaper();
        testpaper.setE_id(rs.getLong("E_ID"));
        testpaper.setE_type(rs.getString("E_TYPE"));
        testpaper.setE_name(rs.getString("E_NAME"));
        testpaper.setE_passvalue(rs.getFloat("E_PASSVALUE"));
        testpaper.setE_begin(rs.getString("E_BEGIN"));
        testpaper.setE_end(rs.getString("E_END"));
        testpaper.setE_state(rs.getString("E_STATE"));
        testpaper.setE_total(rs.getFloat("E_TOTAL"));
        testpaper.setE_examineeList(rs.getString("E_EXAMINEELIST") == null ? "" :
                                    rs.getString("E_EXAMINEELIST"));

        list.add(testpaper);
  }
    } 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;
  }

}