JSP网站开发典型模块与实例精讲

package org.mmxbb.exam.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import org.mmxbb.exam.bean.ExaminationPaper; import org.mmxbb.exam.business.program.RandomDistributeExamPaperForm; import org.mmxbb.exam.business.program.SearchActionForm; import org.mmxbb.exam.util.DBConn; import org.mmxbb.exam.util.GetQuestionCount; public class ExaminationPaperDAO { private final static String GETALL_EXAMINATIONPAPER = "select * from EX_EXAMINATIONPAPER"; private final static String GET_SHARCH_RESULT = "select e.e_id as e_id,e.e_name as e_name,e.e_type as e_type,b.b_value as e_typename,date_format(e.e_begin,'%Y-%m-%d') as e_begin,date_format(e.e_end,'%Y-%m-%d') as e_end,e.e_total as e_total,e.e_passvalue as e_passvalue,e.e_timer as e_timer,t.t_state as t_state from ex_examinationpaper e,ex_baseinfo b,ex_testpaper t where b.b_id=e.e_type and e.e_id=t.e_id and t.examinee_id=? and (now())<e.e_end and (now())>e.e_begin and t_state='096' ORDER BY E_ID DESC"; private final static String UPDATE_PAPER = "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_IDLIST=?,E_BEGIN=date_format(?,'%Y-%m-%d'),E_END=date_format(?,'%Y-%m-%d'),E_EXAMINEELIST=?,E_PASSVALUE=?,E_TOTAL=?,E_STATE=? WHERE E_ID=?"; private final static String UPDATE_PAPER2 = "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_IDLIST=?,E_BEGIN=(now()),E_END=(DATE_ADD(now(),INTERVAL 10 DAY )),E_EXAMINEELIST=?,E_PASSVALUE=?,E_TOTAL=?,E_STATE=? WHERE E_ID=?"; private final static String UPDATE_PAPER_BASEINFO = "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_BEGIN=date_format(?,'%Y-%m-%d'),E_END=date_format(?,'%Y-%m-%d'),,E_PASSVALUE=?,E_TOTAL=?,WHERE E_ID=?"; private final static String ADDPAPER = "INSERT INTO EX_EXAMINATIONPAPER (E_ID,E_IDLIST,E_BEGIN,E_END) VALUES ('','@@0@@0@@0@@0',(now()),(DATE_ADD(now(),INTERVAL 1 DAY )))"; private final static String REMOVEEXAMINATIONPAPERBYKEY = "delete from EX_EXAMINATIONPAPER WHERE E_ID=?"; private final static String UPDATE_E_STATE = "update ex_examinationpaper set e_state=? where e_id=?"; private final static String GETBYKEY = "SELECT exam.e_id as e_id,exam.e_name as e_name,exam.e_type as e_type,exam.e_timer as e_timer,exam.e_idlist as e_idlist,date_format(exam.e_begin,'%Y-%m-%d') as e_begin,date_format(exam.e_end,'%Y-%m-%d') as e_end,exam.e_examineeList as e_examineeList,exam.e_passvalue as e_passvalue,exam.e_total as e_total,b2.b_value as e_state FROM EX_EXAMINATIONPAPER exam,EX_BASEINFO b2 WHERE exam.e_state=b2.b_id and exam.e_id=?"; private final static String FINDBYE_STATE = "SELECT E_ID,E_NAME,E_TYPE,E_TIMER,E_IDLIST,date_format(E_BEGIN,'%Y-%m-%d') as E_BEGIN,date_format(E_END,'%Y-%m-%d') as E_END,E_EXAMINEELIST,E_PASSVALUE,E_TOTAL,E_STATE FROM EX_EXAMINATIONPAPER where e_state IS NULL"; private final static String GET_BY_KEY_WHEN_ESTATE_NULL = "select e_id,e_name,e_type,e_timer,date_format(e_begin,'%Y-%m-%d') as e_begin,date_format(e_end,'%Y-%m-%d') as e_end,e_idlist,e_examineelist,e_passvalue,e_total,e_state from ex_examinationpaper where e_id=?"; private final static String GET_IDLIST = "SELECT E_IDLIST FROM EX_EXAMINATIONPAPER WHERE E_ID = ?"; private final static String CLEAR_IDLIST = "UPDATE EX_EXAMINATIONPAPER SET E_IDLIST = ? WHERE E_ID = ?"; private final static String REMOVEEXAMINEE_LIST = "UPDATE EX_EXAMINATIONPAPER SET E_EXAMINEELIST='' WHERE E_ID=?"; private final static String UPDATE_E_STATE_UNVALIDATE_EXAMPAPER = "UPDATE EX_EXAMINATIONPAPER SET e_state='040' where e_end<(now())"; private Connection conn; private DBConn dbconn; private int length = 3; private int rowCount; private int pageCount; private String pagestr; private String conditionStr = ""; //Connect to db!! public ExaminationPaperDAO() { dbconn = new DBConn(); conn = dbconn.getConnection(); } public void setLength(int length) { this.length = length; } public int getLength() { return length; } public String getConditionStr() { return (this.conditionStr); } public void setConditionStr(String conditionStr) { this.conditionStr = conditionStr; } //Add ExaminationPaper! public void addExamintionPaper(ExaminationPaper examinationPaper) { PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(ADDPAPER); pstmt.executeUpdate(); } catch (SQLException ex) { ex.printStackTrace(); } finally { try { pstmt.close(); pstmt = null; conn.close(); conn = null; } catch (SQLException ex1) { ex1.printStackTrace(); } } } public Collection getCanGrade(SearchActionForm searchForm, int ipage) throws SQLException { ArrayList src = (ArrayList)this.getSearch(searchForm, ipage); Collection res = new ArrayList(); ExaminationPaper ePaper = new ExaminationPaper(); for (int i = 0; i < src.size(); i++) { ePaper = (ExaminationPaper) src.get(i); if (ePaper.getE_state().equals("进行中") || ePaper.getE_state().equals("已结束")) { res.add(src.get(i)); } } this.rowCount = res.size(); int pagesize = getLength(); if (getLength() < 1) { pagesize = rowCount; this.pageCount = 1; } else { this.pageCount = rowCount / getLength() + ( (rowCount % getLength()) > 0 ? 1 : 0); } return res; } public String getPagestr(int ipage) { String strPage = ""; if (getLength() > 0) { strPage += "共"; strPage += String.valueOf(rowCount); strPage += "条记录，共"; strPage += String.valueOf(pageCount); strPage += "页，当前是第"; strPage += String.valueOf(ipage); strPage += "页， "; int istart, iend; istart = ipage - 5; if (istart < 0) { istart = 0; } iend = istart + 10; if (iend > pageCount) { iend = pageCount; } istart = iend - 10; if (istart < 0) { istart = 0; } for (int i = istart; i < iend; i++) { strPage += "<a href='searchAction.do?page="; strPage += String.valueOf(i + 1); strPage += conditionStr; strPage += "'>"; strPage += String.valueOf(i + 1); strPage += "</a>"; strPage += " "; } } this.pagestr = strPage; return strPage; } public String getPagestr_(int ipage) { String strPage = ""; if (getLength() > 0) { strPage += "共"; strPage += String.valueOf(rowCount); strPage += "条记录，共"; strPage += String.valueOf(pageCount); strPage += "页，当前是第"; strPage += String.valueOf(ipage); strPage += "页， "; int istart, iend; istart = ipage - 5; if (istart < 0) { istart = 0; } iend = istart + 10; if (iend > pageCount) { iend = pageCount; } istart = iend - 10; if (istart < 0) { istart = 0; } for (int i = istart; i < iend; i++) { strPage += "<a href='examinationPaperListAction.do?page="; strPage += String.valueOf(i + 1); strPage += "'>"; strPage += String.valueOf(i + 1); strPage += "</a>"; strPage += " "; } } this.pagestr = strPage; return strPage; } //Remove Examinationpaper!! public void removeExaminationPaper(ExaminationPaper examinationPaper) { removeExaminationPaper(examinationPaper.getE_id()); } //Remove by key!! public void removeExaminationPaper(long e_id) { PreparedStatement pstmt = null;