#_ssm_023_mysql_协同过滤算法的离散数学题推荐系统_.zip

package exam.service.impl; import exam.dao.ExamDao; import exam.dao.base.BaseDao; import exam.dao.base.GenerateKeyCallback; import exam.model.*; import exam.model.page.PageBean; import exam.service.ExamService; import exam.service.QuestionService; import exam.service.base.BaseServiceImpl; import exam.util.DataUtil; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.CallableStatementCreator; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.math.BigInteger; import java.sql.*; import java.util.ArrayList; import java.util.Calendar; import java.util.List; @Service("examService") public class ExamServiceImpl extends BaseServiceImpl<Exam> implements ExamService { private ExamDao examDao; @Resource private QuestionService questionService; @Resource(name = "examDao") @Override protected void setBaseDao(BaseDao<Exam> baseDao) { super.baseDao = baseDao; this.examDao = (ExamDao) baseDao; } @Override public void saveOrUpdate(Exam entity) { if (entity.getId() <= 0) { //首先保存exam,需要获取新插入的记录的主键 int examId = saveExam(entity); List<Integer> questionIds = saveQuestions(entity); //设置试卷和班级的关联关系 saveExamClassRelationships(entity.getClazzs(), examId); //设置题目和试卷的关联关系 saveExamQuestionRelationships(questionIds, examId); } else { String sql = "update exam set title = ?, timelimit = ? where id = ?"; examDao.executeSql(sql, new Object[] {entity.getTitle(), entity.getLimit(), entity.getId()}); } } /** * 辅助保存试卷 * @param entity * @return */ private int saveExam(Exam entity) { String sql = "insert into exam values(null,?,?,?,?,?,?,?,?,?)"; return examDao.getKeyHelper(sql, (ps, param) -> { Exam exam = (Exam) param; ps.setString(1, exam.getTitle()); ps.setInt(2, exam.getLimit()); ps.setTimestamp(3, exam.getEndTime() != null ? new Timestamp(exam.getEndTime().getTime()) : null); ps.setString(4, exam.getStatus().name()); ps.setInt(5, exam.getPoints()); ps.setInt(6, exam.getSinglePoints()); ps.setInt(7, exam.getMultiPoints()); ps.setInt(8, exam.getJudgePoints()); ps.setString(9, exam.getTeacher().getId()); }, entity); } /** * 辅助保存题目，并返回主键 * @param entity 试卷 * @return */ private List<Integer> saveQuestions(Exam entity) { //保存所有新插入的问题的id List<Integer> questionIds = new ArrayList<>(); //保存题目，同时返回题目的id String sql = "insert into question values(null,?,?,?,?,?,?,?,?,?)"; QuestionGenerateKeyCallback questionGenerateKeyCallback = new QuestionGenerateKeyCallback(); for (final Question question : entity.getSingleQuestions()) { questionIds.add(question.getId() > 0 ? question.getId() : examDao.getKeyHelper(sql, questionGenerateKeyCallback, question)); } for (final Question question : entity.getMultiQuestions()) { questionIds.add(question.getId() > 0 ? question.getId() : examDao.getKeyHelper(sql, questionGenerateKeyCallback, question)); } for (final Question question : entity.getJudgeQuestions()) { questionIds.add(question.getId() > 0 ? question.getId() : examDao.getKeyHelper(sql, questionGenerateKeyCallback, question)); } return questionIds; } /** * 辅助建立班级和试卷的关系 * @param classes 适用的班级 * @param examId */ private void saveExamClassRelationships(List<Clazz> classes, int examId) { StringBuilder sb = new StringBuilder("insert into exam_class values"); for (Clazz clazz : classes) { sb.append("(null,").append(examId).append(",").append(clazz.getId()).append("),"); } sb.deleteCharAt(sb.length() - 1); examDao.executeSql(sb.toString()); } /** * 辅助建立试卷和题目的关联关系 * @param questionIds 问题ids * @param examId */ private void saveExamQuestionRelationships(List<Integer> questionIds, int examId) { StringBuilder sb = new StringBuilder("insert into exam_question values"); for (Integer qid : questionIds) { sb.append("(null,").append(examId).append(",").append(qid).append("),"); } examDao.executeSql(sb.deleteCharAt(sb.length() - 1).toString()); } @Override public void switchStatus(int examId, String status, Integer days) { String sql = "update exam set status = ?"; List<Object> params = new ArrayList<>(3); params.add(status); if (DataUtil.isValid(days)) { Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.DATE, days); sql += ",endtime = ?"; params.add(new Timestamp(calendar.getTime().getTime())); } sql += " where id = ?"; params.add(examId); examDao.executeSql(sql, params.toArray()); } @Override public void delete(Object id) { int examId = (Integer) id; //注意一个问题:delete语句里面不能用别名 String[] sqls = { //删除试卷和班级的关联关系 "delete from exam_class where eid = " + examId, //删除试卷和题目的关联关系 "delete from exam_question where eid = " + examId, //删除examinationresult_question表的内容 "delete from examinationresult_question where erid in (select er.id from examinationresult er where er.eid = " + examId + ")", //删除examinationresult表的记录 "delete from examinationresult where eid = " + examId, //删除试卷 "delete from exam where id = " + examId }; examDao.batchUpdate(sqls); } @Override public Exam findWithQuestions(Exam exam) { //先查出试题 Exam result = getById(exam.getId()); List<Question> questions = questionService.findByExam(result.getId()); return filterQuestions(result, questions); } /** * 从全部题目中筛选出单选题、多选题以及判断题并且设置到exam中去 * @param exam * @param questions */ private Exam filterQuestions(Exam exam, List<Question> questions) { questions.stream().forEach(question -> { if (question.getType() == QuestionType.SINGLE) { exam.addSingleQuestion(question); } else if (question.getType() == QuestionType.MULTI) { exam.addMultiQuestion(question); } else { exam.addJudgeQuestion(question); } }); return exam; } @Override public boolean hasJoined(int eid, String sid) { String sql = "select count(id) from examinationresult where eid = " + eid + " and sid = '" + sid + "'"; BigInteger count = (BigInteger) examDao.queryForObject(sql, BigInteger.class); return count.intValue() > 0; } @Override public boolean isUseful(int eid) { String sql = "select count(id) from exam where id = " + eid +" and status = 'RUNNING'"; BigInteger result = (BigInteger) examDao.queryForObject(sql, BigInteger.class); return result.intValue() > 0; } @Override public Exam getById(int eid) { List<Exam> list = examDao.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { String sql = "{call getExamById(?)}"; CallableStatement cs = con.prepareCall(sql); //设置存储过程的参数 cs.setInt(1, eid); return cs; } }, ExamCallableSta