基于SSM和MySQL实现的在线考试系统

1.项目简介

1.1 优势

在线考试系统（Exam++）是基于 JAVA 与 MYSQL 开发的网络考试系统。

可以稳定、顺畅的运行在 Windows 与 Linux 平台上
可以通过它快捷方便的创建试题和题库、发布试卷、组织考试、系统自动批改
高度的可配置性和灵活性使得它可以被应用于很多领域

1.2 功能模块

功能模块	子功能	详细介绍
用户功能模块	用户注册登陆	用户可以通过用户名邮箱注册网站，并且通过注册的用户登陆网站。
	随机练习	从题库中随机取出指定数量的题目供学员练习。
	强化练习	按照学员知识分布情况，分类进行练习，每次练习的结果会纳入到学员学习进度中。
	错题练习	学员做错过的题库会记录在错题库中，学员可以从中进行学习。
	模拟考试	学员可以从模拟考试的分类中选择试卷进行考试。
	随机组卷	学员可以从随机组卷的分类中选择试卷进行考试。
	专家试卷	学员可以从专家试卷的分类中选择试卷进行考试。
	统计分析	用图表方式对学员知识体系下所有的题目做统计分析，学员可以清楚的知道自己的知识点掌握情况。
	考试历史	参加过的考试会记录在考试历史中，通过点击可以查看答题情况，得分，和错题解答。
管理员/教师功能模块	题库管理	通过题库管理模块，教师可以增加、修改、删除题目，现在一共有6种题型，包括单选、多选、判断、简答、论述、分析、计算。
	试卷管理	教师可以从题库中选择试题组成试卷供学员考试之用，组卷分为手动和自动两种。组卷完毕后还可以进一步调整试卷的分值，移除或添加试题。
	用户管理	教师或管理员可以管理目前网站的注册用户。

1.3 安装部署

Exam++ 采用了mysql数据库，因此，请安装mysql（5.0以上版本），安装完毕后，请创建一个名为examxx的数据库，并将doc目录下的数据库文件 examxx.sql 导入到数据库
请将examxx.war拷贝到tomcat目录下的webapps目录中
tomcat启动后，war包自动部署到tomcat，打开webapps\examxx\WEB-INF\spring\root-context.xml修改数据库配置，填写你自己的数据库信息，如下：

启动tomcat服务器，输入 http://localhost:8080/examxx 进入到exam++主页面。如果能正常打开，则进度到第下一步，否则，请检查服务器配置或数据库配置是否正确

点击右上角登录按钮，输入用户名admin和密码123456即可登录系统

2.数据库设计

2.1 表结构

试卷表

et_field

班组表

et_knowledge_point

et_news

试题表

et_question_2_point

et_question_2_tag

试题类型表

用户_角色关联表

et_reference表

角色表

用户表

2.2 E-R图

3.项目实现

3.1工具类

拦截器

java @Intercepts({ @Signature(method = "prepare",type = StatementHandler.class,args = { Connection.class }) }) public class MyInterceptor implements Interceptor { private Page<?> page; private static Log log = LogFactory.getLog(MyInterceptor.class); @Override public Object intercept(Invocation invocation) throws Throwable { // TODO Auto-generated method stub try{ RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget(); StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate"); BoundSql boundSql = delegate.getBoundSql(); log.info("拦截sql=" + boundSql.getSql()); //获取sql对应的参数 MapperParamMap<?> mapperParamMap = null; try{ mapperParamMap = (MapperParamMap<?>) boundSql.getParameterObject(); }catch(Exception ex){ } if(mapperParamMap == null){ Object result = invocation.proceed(); return result; } if(mapperParamMap.containsKey("page")){ page = (Page<?>) mapperParamMap.get("page"); //System.out.println(page.isGetAllRecord()); //page为空或者page的isGetAllRecord=true则不修改sql，返回所有的数据 if(page == null){ throw new Exception("page为空，拦截器不处理数据"); } else if(!page.isGetAllRecord()){ MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement"); Connection connection = (Connection) invocation.getArgs()[0]; String strSql = boundSql.getSql(); this.setTotalRecord(boundSql, mappedStatement, connection); StringBuffer sqlBuffer = new StringBuffer(strSql); String pageSql = this.getMySqlPageSql(page, sqlBuffer); ReflectUtil.setFieldValue(boundSql, "sql", pageSql); log.info("修改后的sql=" + pageSql); } } }catch(Exception e){ if(!e.getMessage().equals("page为空，拦截器不处理数据")) e.printStackTrace(); } Object result = invocation.proceed(); return result; } @Override public Object plugin(Object target) { // TODO Auto-generated method stub return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } /** * 更换数据库版本可以通过该方法来处理，这里只处理mysql，该方法暂时不用 * @return */ public String getPageSql(){ return null; } public String getMySqlPageSql(Page<?> page, StringBuffer sqlBuffer) { int offset = (page.getPageNo() - 1) * page.getPageSize(); if (!page.isGetAllRecord()) sqlBuffer.append(" limit ").append(offset).append(",").append(page.getPageSize()); return sqlBuffer.toString(); } public String getCountSql(String sql){ String countSql = "select count(1) from (" + sql + ") sb"; return countSql; } public void setTotalRecord(BoundSql boundSql,MappedStatement mappedStatement,Connection connection){ String sql = boundSql.getSql(); String countSql = this.getCountSql(sql); System.out.println(countSql); BoundSql countBoundSql = new BoundSql( mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); ReflectUtil.setFieldValue(countBoundSql, "sql", sql); ReflectUtil.setFieldValue(countBoundSql, "parameterMappings", boundSql.getParameterMappings()); ReflectUtil.setFieldValue(countBoundSql, "parameterObject", boundSql.getParameterObject()); ReflectUtil.setFieldValue(countBoundSql, "additionalParameters", ReflectUtil.getFieldValue(boundSql, "additionalParameters")); ReflectUtil.setFieldValue(countBoundSql, "metaParameters", ReflectUtil.getFieldValue(boundSql, "metaParameters")); MapperParamMap<?> mapperParamMap = (MapperParamMap<?>) boundSql.getParameterObject(); ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, mapperParamMap, countBoundSql); PreparedStatement pstmt = null; ResultSet rs = null; try{ pstmt = (PreparedStatement) connection.prepareStatement(countSql); parameterHandler.setParameters(pstmt); rs = pstmt.executeQuery(); if(rs.next()){ int totalRecord = rs.getInt(1); page.setTotalRecord(totalRecord); } }catch(Exception e){ e.printStackTrace(); }finally{ } try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); }catch(SQLException e) { e.printStackTrace(); } } }

分页

java /** * 返回anchor类型的分页 * @param currentPageNo * @param maxPageNo * @param parameters * @param url * @return */ public static String getPagelink(int currentPageNo, int maxPageNo, String parameters, String url) { currentPageNo = currentPageNo > maxPageNo ? maxPageNo : currentPageNo; int begainNo = currentPageNo - 5 > 0 ? currentPageNo - 5 : 1; int endNo = begainNo + 9 > maxPageNo ? maxPageNo : begainNo + 9; StringBuffer bf = new StringBuffer(); if (maxPageNo > 1) { bf.append(currentPageNo > 1 ? ("<li><a href = \"" + url + "?page=" + (currentPageNo - 1 > 1 ? currentPageNo - 1 : 1) + parameters + "\">上一页</a></li>") : "<li class=\"disabled\"><a>上一页</a></li>"); for (int i = begainNo; i <= endNo; i++) { if (i == currentPageNo) { bf.append("<li class=\"active\"><a href = \"" + url + "?page=" + i + parameters + "\" >" + i + "</a></li>"); } else bf.append("<li><a href = \"" + url + "?page=" + i + parameters + "\" >" + i + "</a></li>"); } bf.append(currentPageNo < maxPageNo ? ("<li><a href = \"" + url + "?page=" + (currentPageNo + 1 > maxPageNo ? maxPageNo : currentPageNo + 1) + parameters + "\">下一页</a></li>") : "<li class=\"disabled\"><a>下一页</a></li>"); return bf.toString(); } return ""; }

3.2 主要功能

java @RequestMapping(value = "/student/practice-test", method = RequestMethod.GET) public String practiceStartNew(Model model, HttpServletRequest request, @RequestParam(value = "kp", required = false) String knowledgepoint) { String strUrl = "http://" + request.getServerName() // 服务器地址 + ":" + request.getServerPort() + "/"; UserInfo userInfo = (UserInfo) SecurityContextHolder.getContext() .getAuthentication().getPrincipal(); List<Integer> fieldIdList = new ArrayList<Integer>(); fieldIdList.add(userInfo.getFieldId()); List<Integer> typeIdList = new ArrayList<Integer>(); typeIdList.add(1); typeIdList.add(2); typeIdList.add(3); typeIdList.add(4); List<QuestionQueryResult> qqrList = questionService.getQuestionQueryResultListByFieldIdList(fieldIdList,typeIdList, 20); String fieldName = ""; try{ fieldName = qqrList.get(0).getPointName().split(">")[1]; }catch(Exception e){ log.info(e.getMessage()); } int amount = qqrList.size(); StringBuilder sb = new StringBuilder(); for(QuestionQueryResult qqr : qqrList){ QuestionAdapter adapter = new QuestionAdapter(qqr,strUrl); sb.append(adapter.getStringFromXML()); } model.addAttribute("questionStr", sb.toString()); model.addAttribute("amount", amount); model.addAttribute("fieldName", "随机练习"); return "student/practice-improve"; } @RequestMapping(value = "student/practice-random", method = RequestMethod.GET) public String practice(Model model, HttpServletRequest request) { return "student/practice-testing"; } @RequestMapping(value = "student/practice-finished", method = RequestMethod.POST) public @ResponseBody Message practiceFinished(@RequestBody ExamFinishParam efp, HttpServletRequest request) { UserInfo userInfo = (UserInfo) SecurityContextHolder.getContext() .getAuthentication().getPrincipal(); HashMap<Integer, AnswerSheetItem> hm = efp.getAs(); Iterator<Integer> it = hm.keySet().iterator(); List<Integer> idList = new ArrayList<Integer>(); while (it.hasNext()) { int key = it.next(); idList.add(key); AnswerSheetItem as = hm.get(key); System.out.println(key + "=" + as.getPoint()); } PracticePaper practicePaper = new PracticePaper(); practicePaper.setAnswer_sheet(Object2Xml.toXml(hm)); practicePaper.setName("我的练习"); List<QuestionQueryResult> questionList = examService.getQuestionDescribeListByIdList(idList); practicePaper.setContent(Object2Xml.toXml(questionList)); practicePaper.setUserId(userInfo.getUserid()); practiceService.insertPracticePaper(practicePaper); Message message = new Message(); return message; } @RequestMapping(value = "student/practice-finished-page", method = RequestMethod.GET) public String practiceFinishedPage(Model model) { return "student/practice-finish"; } @RequestMapping(value = "student/exam-report", method = RequestMethod.GET) public String examFinishedReportPage(Model model, HttpServletRequest request) { String strUrl = "http://" + request.getServerName() // 服务器地址 + ":" + request.getServerPort() + "/"; UserInfo userInfo = (UserInfo) SecurityContextHolder.getContext() .getAuthentication().getPrincipal(); PracticePaper practicePaper = practiceService .getPracticePaperByUserID(userInfo.getUserid()); List<QuestionQueryResult> questionList = Object2Xml.toBean( practicePaper.getContent(), ArrayList.class); List<Integer> idList = new ArrayList<Integer>(); HashMap<Integer, AnswerSheetItem> hm = Object2Xml.toBean( practicePaper.getAnswer_sheet(), HashMap.class); List<String> htmlStr = new ArrayList<String>(); HashMap<Integer, QuestionQueryResult> questionMap = new HashMap<Integer, QuestionQueryResult>(); for (QuestionQueryResult qqr : questionList) { idList.add(qqr.getQuestionId()); QuestionAdapter adapter = new QuestionAdapter(hm.get(qqr.getQuestionId()),qqr,strUrl); htmlStr.add(adapter.getReportStringFromXML()); } model.addAttribute("htmlStr", htmlStr); return "student/exam-finish-report"; } @RequestMapping(value = "student/finish-exam", method = RequestMethod.GET) public String examFinishedPage(Model model) { UserInfo userInfo = (UserInfo) SecurityContextHolder.getContext() .getAuthentication().getPrincipal(); PracticePaper practicePaper = practiceService .getPracticePaperByUserID(userInfo.getUserid()); List<QuestionQueryResult> questionList = Object2Xml.toBean( practicePaper.getContent(), ArrayList.class); List<Integer> idList = new ArrayList<Integer>(); for (QuestionQueryResult q : questionList) { idList.add(q.getQuestionId()); } HashMap<Integer, AnswerSheetItem> hm = Object2Xml.toBean( practicePaper.getAnswer_sheet(), HashMap.class); int total = questionList.size(); int wrong = 0; int right = 0; HashMap<String, ReportResult> reportResultList = new HashMap<String, ReportResult>(); List<QuestionQueryResult> questionQueryList = examService .getQuestionDescribeListByIdList(idList); HashMap<Integer, Boolean> answer = new HashMap<Integer, Boolean>(); for (QuestionQueryResult q : questionQueryList) { String pointName = q.getPointName().split(">")[1]; if (q.getQuestionTypeId() != 1 && q.getQuestionTypeId() != 2 && q.getQuestionTypeId() != 3) continue; if (hm.get(q.getQuestionId()) != null) { if (q.getAnswer().equals(hm.get(q.getQuestionId()).getAnswer())) { answer.put(q.getQuestionId(), true); right++; if (reportResultList.containsKey(pointName)) { ReportResult r = reportResultList.get(pointName); r.sum++; r.rightTimes++; reportResultList.put(pointName, r); } else { ReportResult r = new ReportResult(); r.sum = 1; r.rightTimes = 1; reportResultList.put(pointName, r); } } else { answer.put(q.getQuestionId(), false); wrong++; if (reportResultList.containsKey(pointName)) { ReportResult r = reportResultList.get(pointName); r.sum++; r.wrongTimes++; reportResultList.put(pointName, r); } else { ReportResult r = new ReportResult(); r.sum = 1; r.wrongTimes = 1; reportResultList.put(pointName, r); } } hm.remove(q.getQuestionId()); } } model.addAttribute("total", total); model.addAttribute("wrong", wrong); model.addAttribute("right", right); model.addAttribute("reportResultList", reportResultList); model.addAttribute("create_time", practicePaper.getCreate_time()); model.addAttribute("answer", answer); model.addAttribute("idList", idList); return "student/exam-finished"; }