Web基础学生成绩管理系统

package com.ischoolbar.programmer.servlet; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.fileupload.FileUploadException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import com.ischoolbar.programmer.dao.CourseDao; import com.ischoolbar.programmer.dao.ScoreDao; import com.ischoolbar.programmer.dao.SelectedCourseDao; import com.ischoolbar.programmer.dao.StudentDao; import com.ischoolbar.programmer.model.Course; import com.ischoolbar.programmer.model.Page; import com.ischoolbar.programmer.model.Score; import com.ischoolbar.programmer.model.Student; import com.lizhou.exception.FileFormatException; import com.lizhou.exception.NullFileException; import com.lizhou.exception.ProtocolException; import com.lizhou.exception.SizeException; import com.lizhou.fileload.FileUpload; /** * Servlet implementation class ScoreServlet */ public class ScoreServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public ScoreServlet() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String method = request.getParameter("method"); if("toScoreListView".equals(method)){ request.getRequestDispatcher("view/scoreList.jsp").forward(request, response); }else if("toScoreStatsView".equals(method)){ request.getRequestDispatcher("view/scoreStats.jsp").forward(request, response); }else if("AddScore".equals(method)){ addScore(request,response); }else if("ScoreList".equals(method)){ getScoreList(request,response); }else if("EditScore".equals(method)){ editScore(request,response); }else if("DeleteScore".equals(method)){ deleteScore(request,response); }else if("ImportScore".equals(method)){ importScore(request,response); }else if("ExportScoreList".equals(method)){ exportScore(request,response); }else if("getStatsList".equals(method)){ getStatsList(request,response); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } //添加成绩信息 private void addScore(HttpServletRequest request,HttpServletResponse response) { // TODO Auto-generated method stub int studentId = request.getParameter("studentid") == null ? 0 : Integer.parseInt(request.getParameter("studentid").toString()); int courseId = request.getParameter("courseid") == null ? 0 : Integer.parseInt(request.getParameter("courseid").toString()); Double scoreNum = Double.parseDouble(request.getParameter("score")); String remark = request.getParameter("remark"); Score score = new Score(); score.setCourseId(courseId); score.setStudentId(studentId); score.setScore(scoreNum); score.setRemark(remark); ScoreDao scoreDao = new ScoreDao(); if(scoreDao.isAdd(studentId, courseId)){ try { response.getWriter().write("added"); scoreDao.closeCon(); return; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } String ret = "success"; if(!scoreDao.addScore(score)){ ret = "error"; } try { response.getWriter().write(ret); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //修改学生成绩 private void editScore(HttpServletRequest request,HttpServletResponse response) { // TODO Auto-generated method stub int id = Integer.parseInt(request.getParameter("id")); int studentId = request.getParameter("studentid") == null ? 0 : Integer.parseInt(request.getParameter("studentid").toString()); int courseId = request.getParameter("courseid") == null ? 0 : Integer.parseInt(request.getParameter("courseid").toString()); Double scoreNum = Double.parseDouble(request.getParameter("score")); String remark = request.getParameter("remark"); Score score = new Score(); score.setId(id); score.setCourseId(courseId); score.setStudentId(studentId); score.setScore(scoreNum); score.setRemark(remark); ScoreDao scoreDao = new ScoreDao(); String ret = "success"; if(!scoreDao.editScore(score)){ ret = "error"; } try { response.getWriter().write(ret); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //删除学生成绩 private void deleteScore(HttpServletRequest request,HttpServletResponse response) { // TODO Auto-generated method stub int id = Integer.parseInt(request.getParameter("id")); ScoreDao scoreDao = new ScoreDao(); String msg = "success"; if(!scoreDao.deleteScore(id)){ msg = "error"; } scoreDao.closeCon(); try { response.getWriter().write(msg); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //导入学生成绩 private void importScore(HttpServletRequest request,HttpServletResponse response) { // TODO Auto-generated method stub FileUpload fileUpload = new FileUpload(request); fileUpload.setFileFormat("xls"); fileUpload.setFileFormat("xlsx"); fileUpload.setFileSize(2048); response.setCharacterEncoding("UTF-8"); try { InputStream uploadInputStream = fileUpload.getUploadInputStream(); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(uploadInputStream); HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0); int count = 0; String errorMsg = ""; StudentDao studentDao = new StudentDao(); CourseDao courseDao = new CourseDao(); ScoreDao scoreDao = new ScoreDao(); SelectedCourseDao selectedCourseDao = new SelectedCourseDao(); for(int rowNum = 1; rowNum <= sheetAt.getLastRowNum(); rowNum++){ HSSFRow row = sheetAt.getRow(rowNum); HSSFCell cell = row.getCell(0); //获取第0列，学生id if(cell == null){ errorMsg += "第" + rowNum + "行学生id缺失！\n"; continue; } if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC){ errorMsg += "第" + rowNum + "行学生id类型不是整数！\n"; continue; } int studentId = new Double(cell.getNumericCellValue()).intValue(); //获取第1列，课程id cell = row.getCell(1); if(cell == null){ errorMsg += "第" + rowNum + "行课程id缺失！\n"; continue; } if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC){ errorMsg += "第" + rowNum + "行课程id不是整数！\n"; continue; } int courseId = new Double(cell.getNumericCellValue()).intValue(); //获取第2列，成绩 cell = row.getCell(2); if(cell == null){ errorMsg += "第" + rowNum + "行成绩缺失！\n"; continue; } if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC){ errorMsg += "第" + rowNum + "行成绩类型不是数字！\n"; continue; } double scoreValue = cell.getNumericCellValue(); //获取第3列，备注 cell = row.getCell(3); String remark = null; if(cell != null){ remark = cell.getStringCellValue(); } Student student = studentDao.getStudent(studentId); if(student == null){ errorMsg += "第" + rowNum + "行学生id不存在！\n"; continue; } Course course = courseDao.getCourse(courseId); if(course == null){ errorMsg += "