mysql 计算学生成绩的中位数 mysql计算总成绩

转载

mob6454cc6a8ab0 2024-07-04 15:48:26

文章标签 mysql 计算学生成绩的中位数 mysql java servlet 上传 文章分类 MySQL 数据库

基于javaweb+mysql的jsp+servlet学生成绩管理系统(java+servlet+jsp+mysql+c3p0)

运行环境

Java≥8、MySQL≥5.7、Tomcat≥8

开发工具

eclipse/idea/myeclipse/sts等均可配置运行

适用

课程设计，大作业，毕业设计，项目练习，学习演示等

功能说明

基于javaweb的JSP+Servlet学生成绩管理系统(java+servlet+jsp+mysql+c3p0)

管理员：

admin 123456

老师：

2001 123456

2002 123456

2003 123456

学生：

202801001 123456

202801002 123456

202801003 123456

202801004 123456

202801005 123456

202801006 123456

202801007 123456

headers[index++] = course.getName();
		}
		
		if(exam.getType() == Exam.EXAM_GRADE_TYPE){
			headers[len-1] = "总分";
		}
		
		ExcelTool et = new ExcelTool<>();
		//导出
		try {
			et.exportMapExcel(headers, list, response.getOutputStream());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置成绩
	 * @param score id_score 形式
	 */
	public void setScore(String[] score) {
		Object[][] param = new Object[score.length][2];
		
		for(int i = 0;i < score.length;i++){
			String[] id_score = score[i].split("_");
			int id = Integer.parseInt(id_score[0]);
			param[i][1] = id;
			if(id_score.length == 1){
				param[i][0] = 0;
			} else {
				int sco = Integer.parseInt(id_score[1]);
				param[i][0] = sco;
			}
		}
		
		dao.updateBatch("UPDATE escore SET score=? WHERE id=?", param);
		
	}
	
}

/**
 * 教师类Servlet
 *
 */
public class TeacherServlet extends HttpServlet {
	
	//创建服务层对象
	private TeacherService service = new TeacherService();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		if("toTeacherListView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/teacherList.jsp").forward(request, response);
		} else if("toTeacherNoteListView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/teacherNoteList.jsp").forward(request, response);
		} else if("toExamTeacherView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/examTeacherList.jsp").forward(request, response);
		} else if("toTeacherPersonalView".equalsIgnoreCase(method)){ //转发到教师列表页
			toPersonal(request, response);
		}  
	}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		//请求分发
		if("TeacherList".equalsIgnoreCase(method)){ //获取所有教师数据
			teacherList(request, response);
		} else if("AddTeacher".equalsIgnoreCase(method)){ //添加教师
			addTeacher(request, response);
		} else if("DeleteTeacher".equalsIgnoreCase(method)){ //删除教师
			deleteTeacher(request, response);
		} else if("EditTeacher".equalsIgnoreCase(method)){ //修改教师信息
			editTeacher(request, response);
		} else if("GetTeacher".equalsIgnoreCase(method)){ //获取某个教师信息
			getTeacher(request, response);
		} else if("GetExamClazz".equalsIgnoreCase(method)){ //获取某次考试老师的班级
			getExamClazz(request, response);
		} else if("GetExamCourse".equalsIgnoreCase(method)){ //获取某次考试老师的课程
			getExamCourse(request, response);
		} else if("EditTeacherPersonal".equalsIgnoreCase(method)){ //修改个人信息
			editTeacherPersonal(request, response);
		}

/**
 * 成绩类Servlet
 *
 */
public class ScoreServlet extends HttpServlet {
	
	//创建服务层对象
	private ScoreService service = new ScoreService();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		//请求分发
		if("ExportScore".equalsIgnoreCase(method)){ //导出成绩
			exportScore(request, response);
		}
	}

/**
 * 上传照片
 *
 */
public class PhotoService {
	
	PhotoDaoInter dao = new PhotoDaoImpl();
	
	/**
	 * 设置照片
	 * @param user 
	 * @param request
	 * @return
	 * @throws IOException
	 */
	public String setPhoto(User user, HttpServletRequest request) {
		FileUpload upload = new FileUpload(request);
		//设置格式
		upload.setFileFormat("jpg");
		upload.setFileFormat("jpeg");
		upload.setFileFormat("png");
		//设置上传文件大小
		upload.setFileSize(1000);
		//返回信息
		String msg = "";
		try {
			//获取上传文件输入流
			InputStream is = upload.getUploadInputStream();
			//更新数据库
			dao.setPhoto(user, is);
			
			msg = "<div id='message'>上传成功!</div>";

/**
	 * 导出成绩列表
	 * @param response
	 * @param exam
	 */
	public void exportScore(HttpServletResponse response, Exam exam) {
		//获取需要导出的数据
		List<Map<String, Object>> list = dao.getScoreList(exam);
		//获取考试信息
		Exam em = (Exam) dao.getObject(Exam.class, "SELECT name, time FROM exam WHERE id=?", new Object[]{exam.getId()});
		//设置文件名
		String fileName = em.getName()+".xls";
		//定义输出类型
		response.setContentType("application/msexcel;charset=utf-8");
		//设定输出文件头
		try {
			response.setHeader("Content-Disposition", "attachment; filename="+URLEncoder.encode(fileName, "UTF-8"));
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		
		//获取导出的课程
		List<Object> courseList = getColumn(exam);
		
		//表头长度
		int len = 2 + courseList.size();
		if(exam.getType() == Exam.EXAM_GRADE_TYPE){
			len += 1;
		}
		//设置excel的列名
		String[] headers = new String[len];
		headers[0] = "姓名";
		headers[1] = "学号";
		
		int index = 2;
		for(Object obj : courseList){
			Course course = (Course) obj;
			headers[index++] = course.getName();
		}
		
		if(exam.getType() == Exam.EXAM_GRADE_TYPE){
			headers[len-1] = "总分";
		}
		
		ExcelTool et = new ExcelTool<>();

/**
 * 教师类Servlet
 *
 */
public class TeacherServlet extends HttpServlet {
	
	//创建服务层对象
	private TeacherService service = new TeacherService();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		if("toTeacherListView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/teacherList.jsp").forward(request, response);
		} else if("toTeacherNoteListView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/teacherNoteList.jsp").forward(request, response);
		} else if("toExamTeacherView".equalsIgnoreCase(method)){ //转发到教师列表页
			request.getRequestDispatcher("/WEB-INF/view/teacher/examTeacherList.jsp").forward(request, response);
		} else if("toTeacherPersonalView".equalsIgnoreCase(method)){ //转发到教师列表页
			toPersonal(request, response);
		}  
	}

//获取参数名
		Enumeration<String> pNames = request.getParameterNames();
		Teacher teacher = new Teacher();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				if("course[]".equals(pName)){//设置所选课程
					BeanUtils.setProperty(teacher, "course", request.getParameterValues("course[]"));
				} else{
					BeanUtils.setProperty(teacher, pName, value);
				}
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		try {
			service.editTeacher(teacher);
			response.getWriter().write("success");
		} catch (Exception e) {
			response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

	private void deleteTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取要删除的学号
		String[] ids = request.getParameterValues("ids[]");
		String[] numbers = request.getParameterValues("numbers[]");
		try {
			service.deleteTeacher(ids, numbers);
			response.getWriter().write("success");
		} catch (Exception e) {
			response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

	private void addTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取参数名
		Enumeration<String> pNames = request.getParameterNames();
		Teacher teacher = new Teacher();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				if("course[]".equals(pName)){//设置所选课程
					BeanUtils.setProperty(teacher, "course", request.getParameterValues("course[]"));
				} else{
					BeanUtils.setProperty(teacher, pName, value);
				}
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}

PhotoDaoInter dao = new PhotoDaoImpl();
	
	/**
	 * 设置照片
	 * @param user 
	 * @param request
	 * @return
	 * @throws IOException
	 */
	public String setPhoto(User user, HttpServletRequest request) {
		FileUpload upload = new FileUpload(request);
		//设置格式
		upload.setFileFormat("jpg");
		upload.setFileFormat("jpeg");
		upload.setFileFormat("png");
		//设置上传文件大小
		upload.setFileSize(1000);
		//返回信息
		String msg = "";
		try {
			//获取上传文件输入流
			InputStream is = upload.getUploadInputStream();
			//更新数据库
			dao.setPhoto(user, is);
			
			msg = "<div id='message'>上传成功!</div>";
		} catch (ProtocolException e) {
			msg = "<div id='message'>请以MIME协议上传文件<br/>您可以为form表单添加如下属性：enctype=\"multipart/form-data\"</div>";
			e.printStackTrace();
		} catch (NullFileException e) {
			msg = "<div id='message'>上传的文件为空</div>";
			e.printStackTrace();
		} catch (SizeException e) {
			msg = "<div id='message'>请上传小于 "+upload.getFileSize()+"k的文件</div>";
			e.printStackTrace();
		} catch (FileFormatException e) {
			msg = "<div id='message'>请上传 "+upload.getFileFormat()+" 格式的文件</div>";
			e.printStackTrace();
		} catch (Exception e) {
			msg = "<div id='message'>上传出错!</div>";
			e.printStackTrace();
		}
		return msg;
	}

	/**
	 * 获取照片

/**
 * 成绩类服务层
 *
 */
public class ScoreService {
	
	private ScoreDaoInter dao;
	
	public ScoreService(){

* 获取验证码
	 * @param request
	 * @param response
	 * @throws IOException 
	 */
	private void getVCode(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//创建验证码生成器对象
		VCodeGenerator vcGenerator = new VCodeGenerator();
		//生成验证码
		String vcode = vcGenerator.generatorVCode();
		//将验证码保存在session域中,以便判断验证码是否正确
		request.getSession().setAttribute("vcode", vcode);
		//生成验证码图片
		BufferedImage vImg = vcGenerator.generatorRotateVCodeImage(vcode, true);
		//输出图像
		ImageIO.write(vImg, "gif", response.getOutputStream());
	}
	
}

public class ClazzServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
      
	private ClazzService service = new ClazzService();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		if("toClazzListView".equalsIgnoreCase(method)){ //转发到课程列表页
			request.getRequestDispatcher("/WEB-INF/view/other/clazzList.jsp").forward(request, response);
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");

/**
	 * 获取数据栏的列名
	 * @param exam
	 * @return
	 */
	public String columnList(Exam exam) {
		List<Object> list = getColumn(exam);
		
		return JSONArray.fromObject(list).toString();
	}
	
	private List<Object> getColumn(Exam exam){
		List<Object> list = null;
		if(exam.getType() == Exam.EXAM_GRADE_TYPE){ //年级考试
			//获取考试的科目
			list = dao.getList(Course.class, 
					"SELECT c.id id, c.name name FROM course c, grade_course gc WHERE c.id=gc.courseid AND gc.gradeid=?", 
					new Object[]{exam.getGradeid()});
		} else{
			//获取某科
			list =  dao.getList(Course.class, 
					"SELECT * FROM course WHERE id=?", new Object[]{exam.getCourseid()});
			
		}
		return list;
	}
	

	/**
	 * 导出成绩列表
	 * @param response
	 * @param exam
	 */
	public void exportScore(HttpServletResponse response, Exam exam) {
		//获取需要导出的数据
		List<Map<String, Object>> list = dao.getScoreList(exam);
		//获取考试信息
		Exam em = (Exam) dao.getObject(Exam.class, "SELECT name, time FROM exam WHERE id=?", new Object[]{exam.getId()});
		//设置文件名
		String fileName = em.getName()+".xls";
		//定义输出类型
		response.setContentType("application/msexcel;charset=utf-8");
		//设定输出文件头
		try {

response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

	private void addStudent(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取参数名
		Enumeration<String> pNames = request.getParameterNames();
		Student student = new Student();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				BeanUtils.setProperty(student, pName, value);
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		service.addStudent(student);
		response.getWriter().write("success");
	}

	private void studentList(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//年级ID
		String gradeid = request.getParameter("gradeid");
		//班级ID
		String clazzid = request.getParameter("clazzid");
		//获取分页参数
		int page = Integer.parseInt(request.getParameter("page"));
		int rows = Integer.parseInt(request.getParameter("rows"));
		
		//封装参数
		Student student = new Student();
		
		if(!StringTool.isEmpty(gradeid)){
			student.setGradeid(Integer.parseInt(gradeid));
		}
		if(!StringTool.isEmpty(clazzid)){
			student.setClazzid(Integer.parseInt(clazzid));
		}

/**
 * 上传照片Servlet
 *
 */
public class PhotoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
    
	PhotoService service = new PhotoService();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		
		if("GetPhoto".equals(method)){ //设置照片
			getPhoto(request, response);
		} 
	}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//获取请求的方法
		String method = request.getParameter("method");
		
		if("SetPhoto".equals(method)){ //设置照片
			setPhoto(request, response);
		}

private void addStudent(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取参数名
		Enumeration<String> pNames = request.getParameterNames();
		Student student = new Student();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				BeanUtils.setProperty(student, pName, value);
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		service.addStudent(student);
		response.getWriter().write("success");
	}

	private void studentList(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//年级ID
		String gradeid = request.getParameter("gradeid");
		//班级ID
		String clazzid = request.getParameter("clazzid");
		//获取分页参数
		int page = Integer.parseInt(request.getParameter("page"));
		int rows = Integer.parseInt(request.getParameter("rows"));
		
		//封装参数
		Student student = new Student();
		
		if(!StringTool.isEmpty(gradeid)){
			student.setGradeid(Integer.parseInt(gradeid));
		}
		if(!StringTool.isEmpty(clazzid)){
			student.setClazzid(Integer.parseInt(clazzid));
		}
		
		//获取数据
		String result = service.getStudentList(student, new Page(page, rows));
		//返回数据
        response.getWriter().write(result);
	}

String method = request.getParameter("method");
		//请求分发
		if("ExamList".equalsIgnoreCase(method)){ //获取所有考试数据
			examList(request, response);
		} else if("AddExam".equalsIgnoreCase(method)){ //添加考试
			addExam(request, response);
		} else if("DeleteExam".equalsIgnoreCase(method)){ //删除考试信息
			deleteExam(request, response);
		} else if("TeacherExamList".equalsIgnoreCase(method)){ //获取属于某个老师的考试
			teacherExamList(request, response);
		} else if("StudentExamList".equalsIgnoreCase(method)){ //获取属于某个学生的考试
			studentExamList(request, response);
		}
		
		
		
	}
	
	private void studentExamList(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取当前用户
		User user = (User) request.getSession().getAttribute("user");
		String number = user.getAccount();
		
		String result = service.studentExamList(number);
		response.getWriter().write(result);
	}

	private void teacherExamList(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取当前用户
		User user = (User) request.getSession().getAttribute("user");
		String number = user.getAccount();
		String result = service.teacherExamList(number);
		response.getWriter().write(result);
	}

	private void deleteExam(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取要删除的id
		int id = Integer.parseInt(request.getParameter("id"));
		try {
			service.deleteExam(id);
			response.getWriter().write("success");
		} catch (Exception e) {
			response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

}

	private void getTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取当前用户
		User user = (User) request.getSession().getAttribute("user");
		String number = user.getAccount();
		String result = service.getTeacherResult(number);
		response.getWriter().write(result);
	}

	private void editTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取参数名
		Enumeration<String> pNames = request.getParameterNames();
		Teacher teacher = new Teacher();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				if("course[]".equals(pName)){//设置所选课程
					BeanUtils.setProperty(teacher, "course", request.getParameterValues("course[]"));
				} else{
					BeanUtils.setProperty(teacher, pName, value);
				}
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		try {
			service.editTeacher(teacher);
			response.getWriter().write("success");
		} catch (Exception e) {
			response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

	private void deleteTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//获取要删除的学号
		String[] ids = request.getParameterValues("ids[]");
		String[] numbers = request.getParameterValues("numbers[]");
		try {
			service.deleteTeacher(ids, numbers);
			response.getWriter().write("success");
		} catch (Exception e) {
			response.getWriter().write("fail");
			e.printStackTrace();
		}
	}

	private void addTeacher(HttpServletRequest request, HttpServletResponse response) throws IOException {

}
		}
		
		//获取数据
		String result = service.columnList(exam);
		//返回数据
        response.getWriter().write(result);
	}
	
	private void scoreList(HttpServletRequest request, HttpServletResponse response) throws IOException {
		Enumeration<String> pNames = request.getParameterNames();
		Exam exam = new Exam();
		while(pNames.hasMoreElements()){
			String pName = pNames.nextElement();
			String value = request.getParameter(pName);
			try {
				BeanUtils.setProperty(exam, pName, value);
			} catch (IllegalAccessException | InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		
		//获取数据
		String result = service.getScoreList(exam);
		//返回数据
        response.getWriter().write(result);
	}
	
}

msg = "student";
				} else if(User.USER_TEACHER == type){
					msg = "teacher";
				}
				//将该用户名保存到session中
				request.getSession().setAttribute("user", loginUser);
			}
		}
		//返回登录信息
		response.getWriter().write(msg);
	}
	
	/**
	 * 获取验证码
	 * @param request
	 * @param response
	 * @throws IOException 
	 */
	private void getVCode(HttpServletRequest request, HttpServletResponse response) throws IOException {
		//创建验证码生成器对象
		VCodeGenerator vcGenerator = new VCodeGenerator();
		//生成验证码
		String vcode = vcGenerator.generatorVCode();
		//将验证码保存在session域中,以便判断验证码是否正确
		request.getSession().setAttribute("vcode", vcode);
		//生成验证码图片
		BufferedImage vImg = vcGenerator.generatorRotateVCodeImage(vcode, true);
		//输出图像
		ImageIO.write(vImg, "gif", response.getOutputStream());
	}
	
}

public class ClazzServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

mysql 计算学生成绩的中位数 mysql计算总成绩_java