testpaperdao.java

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

      pstmt.setLong(2, testpaper.getE_id());
      pstmt.setString(3, testpaper.getT_begin());
      pstmt.setString(4, testpaper.getT_end());
      pstmt.setDouble(5, testpaper.getT_passvalue());
      pstmt.setDouble(6, testpaper.getE_autovalue());
      pstmt.setDouble(7, testpaper.getE_manualvalue());
      pstmt.setDouble(8, testpaper.getT_total());
      pstmt.setString(9, testpaper.getT_state());
      pstmt.setLong(10, testpaper.getT_id());
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
      throw new SQLException(
          "SQLExction on : TestpPaperDAO.updateTestPaper()");
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  public void updateTestPaper(float manualValue, float totalValue, String state,
                              long t_id) throws SQLException {
    PreparedStatement pstmt = null;
    String UPDATE_TESTPAPER =
        "UPDATE EX_TESTPAPER set E_MANUALVALUE=?,T_TOTAL=?,T_STATE=? where T_ID=?";
    try {
      pstmt = conn.prepareStatement(UPDATE_TESTPAPER);
      pstmt.setFloat(1, manualValue);
      pstmt.setFloat(2, totalValue);
      pstmt.setString(3, state);
      pstmt.setLong(4, t_id);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
      throw new SQLException("SQLExction on : TestpPaperDAO.updateTestPaper()");
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  public void updateT_state(long t_id,String t_state,String t_begin,String t_end){
    PreparedStatement pstmt = null;
    String UPDATET_STATAE = "UPDATE EX_TESTPAPER SET T_STATE=?,T_BEGIN=DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s'),T_END=DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') WHERE T_ID=?";
    try {
      pstmt = conn.prepareStatement(UPDATET_STATAE);
      pstmt.setString(1, t_state);
      pstmt.setString(2, t_begin);
      pstmt.setString(3, t_end);
      pstmt.setLong(4, t_id);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }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 Vector getT_idList(long e_id) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    Vector list = null;
    String GET_T_IDLIST = "select t_id from ex_testpaper where e_id=" +
        e_id;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(GET_T_IDLIST);
      list = new Vector();
      while (rs.next()) {
        list.add(new Long(rs.getLong("T_ID")));
      }
    } catch (SQLException ex) {
    } finally {
      try {
        rs.close();
        rs = null;
        stmt.close();
        stmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }

      
    }
    return list;

  }

  public Collection getByE_id(long e_id, int ipage) throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    TestPaper testpaper = null;
    Collection list = null;
    list = new ArrayList();

    try {
      pstmt = conn.prepareStatement(GET_BY_EID,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
      pstmt.setLong(1, e_id);
      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++) {
        testpaper = new TestPaper();
        testpaper.setT_id(rs.getLong("T_ID"));
        testpaper.setExaminee_id(rs.getString("EXAMINEE_ID"));
        testpaper.setName(rs.getString("NAME"));
        testpaper.setE_id(rs.getLong("E_ID"));
        testpaper.setT_begin(rs.getString("T_BEGIN"));
        testpaper.setT_end(rs.getString("T_END"));
        testpaper.setT_passvalue(rs.getFloat("T_PASSVALUE"));
        testpaper.setT_total(rs.getFloat("T_TOTAL"));
        testpaper.setT_state(rs.getString("T_STATE"));
        testpaper.setT_stateName(rs.getString("T_STATENAME"));
        testpaper.setE_stateName(rs.getString("E_STATENAME"));
        testpaper.setE_id(rs.getLong("E_ID"));
        testpaper.setE_state(rs.getString("E_STATE"));
        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 getSearch(long e_id, int ipage) throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    TestPaper testpaper = null;
    Collection list = null;
    list = new ArrayList();
    String GET_SEARCH_RESULT =
        "select test.examinee_id as examinee_id,e.name as name,exam.e_name as e_name,test.t_begin as t_begin,test.t_end as t_end,b1.b_value as t_state,test.e_autovalue as e_autovalue, test.e_manualvalue as e_manualvalue from ex_examinationpaper exam,ex_testpaper test,ex_examinee e,ex_baseinfo b1 where e.examinee_id=test.examinee_id and test.e_id=exam.e_id and b1.b_id=test.t_state and test.e_id＝?";

    try {
      pstmt = conn.prepareStatement(GET_SEARCH_RESULT,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
      pstmt.setLong(1, e_id);
      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++) {
        testpaper = new TestPaper();
        testpaper.setExaminee_id(rs.getString("EXAMINEE_ID"));
        testpaper.setName(rs.getString("NAME"));
        testpaper.setE_name(rs.getString("E_NAME"));
        testpaper.setT_begin(rs.getString("T_BEGIN"));
        testpaper.setT_end(rs.getString("T_END"));
        testpaper.setT_state(rs.getString("T_STATE"));
        testpaper.setE_autovalue(rs.getFloat("E_AUTOVALUE"));
        testpaper.setE_manualvalue(rs.getFloat("E_MANUALVALUE"));
        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 sorting(long e_id, int ipage) throws
      SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    TestPaper testpaper = null;
    Collection list = null;
    list = new ArrayList();

    try {
      pstmt = conn.prepareStatement(SORTING,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
      pstmt.setLong(1, e_id);
      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++) {
        testpaper = new TestPaper();
        testpaper.setExaminee_id(rs.getString("EXAMINEE_ID"));
        testpaper.setE_name(rs.getString("E_NAME"));
        testpaper.setT_total(rs.getFloat("T_TOTAL"));
        testpaper.setName(rs.getString("NAME"));
        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 statistic(long e_id, float e_total, float e_passvalue) throws
      SQLException {
    Statement stmt = null;
    TestPaper testpaper = null;
    ResultSet rs = null;
    Collection list = null;
    list = new ArrayList();

    //get the full score examinees
    testpaper = new TestPaper();
        String findFullScore =
            "select distinct exam.e_name as e_name,round((100*(select count(*) from ex_testpaper test where test.t_total=" +
            e_total + " and test.e_id=" +
            e_id +
            ")/(select count(*) from ex_testpaper test where test.e_id=" +
            e_id + ")),1)||'%' as e_percent,(select count(*) from ex_testpaper test where test.t_total=" +
            e_total + " and test.e_id=" +
            e_id +
            ") as subSum from ex_testpaper test,ex_examinationpaper exam where test.e_id=exam.e_id and test.e_id=" +
            e_id;

        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
        rs = stmt.executeQuery(findFullScore);
        while (rs.next()) {
          testpaper.setScore_area("满分");
          testpaper.setE_name(rs.getString("E_NAME"));
          testpaper.setE_percent(rs.getString("E_PERCENT"));
          testpaper.setSubSum(rs.getInt("SUBSUM"));
          list.add(testpaper);
        }

    //get the rest examinees
    try {
      for (float i = e_total; i >= 0; i = i - 10) {
        testpaper = new TestPaper();
        String score_area;
        float j = 0;
        if (i > e_passvalue) {
          j = i - 10;
          if(j < 0){
            j = 0;
          }
        } else {
          j = 0;
        }
        score_area = j + "-" + i;

        String STA =
            "select distinct exam.e_name as e_name,round((100*(select count(*) from ex_testpaper test where test.t_total<" +
            i + " and test.t_total>=" + j + " and test.e_id=" +
            e_id +
            ")/(select count(*) from ex_testpaper test where test.e_id=" +
            e_id + ")),1)||'%' as e_percent,(select count(*) from ex_testpaper test where test.t_total<" +
            i + " and test.t_total>=" + j + " and test.e_id=" +
            e_id +
            ") as subSum from ex_testpaper test,ex_examinationpaper exam where test.e_id=exam.e_id and test.e_id=" +
            e_id;

        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
        rs = stmt.executeQuery(STA);
        while (rs.next()) {
          testpaper.setScore_area(score_area);
          testpaper.setE_name(rs.getString("E_NAME"));
          testpaper.setE_percent(rs.getString("E_PERCENT"));
          testpaper.setSubSum(rs.getInt("SUBSUM"));
          list.add(testpaper);
        }
        if (i <= e_passvalue) {
          break;
        }
      }
    } 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 findAbsence(long e_id) throws
      SQLException {
    PreparedStatement pstmt = null;
    TestPaper testpaper = null;
    ResultSet rs = null;
    Collection list = null;
    list = new ArrayList();

    try {
      pstmt = conn.prepareStatement(FIND_ABSENCE);
      pstmt.setLong(1, e_id);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        testpaper = new TestPaper();
        testpaper.setExaminee_id(rs.getString("EXAMINEE_ID"));
        testpaper.setName(rs.getString("NAME"));
        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;
  }
}