questiondao.java

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

        conditionStr += ("&question.q_value=" + Q_value);

      }

      rs = pstmt.executeQuery();
      int j = 1;
      while (rs.next()) {
        j++;
      }
      list = new ArrayList();
      rs.absolute( -1);
      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);
      list = new ArrayList();
      for (int i = 0; i < pagesize && offset < rowCount + 1; i++, offset++) {
        question = new Question();
        question.setQ_id(rs.getLong("q_id"));
        question.setQ_class(rs.getString("q_class"));
        question.setQ_knowledge(rs.getString("q_knowledge"));
        question.setQ_value(rs.getFloat("q_value"));
        question.setQ_type(rs.getString("q_type"));
        question.setQ_difficulty(rs.getString("q_difficulty"));
        question.setQ_content(rs.getString("q_content"));
        question.setQ_answer(rs.getString("q_answer"));
        question.setQ_standard(rs.getString("q_standard"));
        question.setQ_picture(rs.getString("q_picture"));
        list.add(question);
        rs.next();
        continue;
      }
    }

    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 void addQuestion(Question question) throws SQLException {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(ADD_QUESTIOIN);
      pstmt.setString(1, question.getQ_class());
      pstmt.setString(2, question.getQ_knowledge());
      pstmt.setFloat(3, question.getQ_value());
      pstmt.setString(4, question.getQ_type());
      pstmt.setString(5, question.getQ_difficulty());
      pstmt.setString(6, question.getQ_content());
      pstmt.setString(7, question.getQ_answer());
      pstmt.setString(8, question.getQ_standard());
      pstmt.setString(9, question.getQ_picture());
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
      throw new SQLException("SQLExction on : QuestionDAO.addQuestion()");
    } finally {
      try {
        conn.close();
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  /**
   * remove a row from DB
   * @param question Examinee
   * @throws SQLException
   */
  public void removeQuestion(String[] questionIDList) throws SQLException {
    Statement stmt = null;
    String ids = "";
    for (int i = 0; i < questionIDList.length; i++) {
      ids += "'" + questionIDList[i] + "'";
      if (i != questionIDList.length - 1) {
        ids += ", ";
      }
    }
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate("DELETE FROM ex_question WHERE q_id in (" + ids +
                         ")");

    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SQLException("SQLExction on : QuestionDAO.removeQuestion()");
    } finally {
      try {
        stmt.close();
        stmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }

  }

  public void removeQuestion(Question question) throws SQLException {
    this.removeQuestion(question.getQ_id());
  }

  /**
   * remove a row from DB
   * @param examinee_id String
   * @throws SQLException
   */
  public void removeQuestion(long question_id) throws SQLException {
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      String sql = "DELETE FROM EX_QUESTION WHERE Q_id=" + question_id;
      stmt.executeUpdate(sql);
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SQLException("SQLExction on : QuestionDAO.removeQuestion()");
    } finally {
      try {
        stmt.close();
        stmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  /**
   * update a row
   * @param examinee Examinee
   * @throws SQLException
   */
  public void updateQuestion(Question question) throws SQLException {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(UPDATE_QUESTION);
      pstmt.setString(1, question.getQ_class());
      pstmt.setString(2, question.getQ_knowledge());
      pstmt.setFloat(3, question.getQ_value());
      pstmt.setString(4, question.getQ_type());
      pstmt.setString(5, question.getQ_difficulty());
      pstmt.setString(6, question.getQ_content());
      pstmt.setString(7, question.getQ_answer());
      pstmt.setString(8, question.getQ_standard());
      pstmt.setString(9, question.getQ_picture());
      pstmt.setLong(10, question.getQ_id());
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
    } 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;
    Question question = null;
    Collection list = null;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT * FROM EX_QUESTION");
      list = new ArrayList();
      while (rs.next()) {
        question = new Question();
        question.setQ_id(rs.getLong("q_id"));
        question.setQ_class(rs.getString("q_class"));
        question.setQ_knowledge(rs.getString("q_knowledge"));
        question.setQ_value(rs.getFloat("q_value"));
        question.setQ_type(rs.getString("q_type"));
        question.setQ_difficulty(rs.getString("q_difficulty"));
        question.setQ_content(rs.getString("q_content"));
        question.setQ_answer(rs.getString("q_answer"));
        question.setQ_standard(rs.getString("q_standard"));
        question.setQ_picture(rs.getString("q_picture"));

        list.add(question);

      }
    } 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;
  }
}