高校毕业生问卷调查系统Java.zip

package com.qog.service; import com.google.gson.Gson; import com.google.gson.reflect.TypeToken; import com.qog.util.Constant; import com.qog.util.Service; import com.qog.model.Question; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.transaction.support.TransactionTemplate; import java.sql.Statement; import java.util.HashMap; import java.util.List; import java.util.Map; @SuppressWarnings("unused") public class SurveyService implements Service { private static final Log logger = LogFactory.getLog(SurveyService.class); private JdbcTemplate jdao; private TransactionTemplate jtx; public SurveyService() { } public SurveyService(JdbcTemplate jdao, TransactionTemplate jtx) { this.jdao = jdao; this.jtx = jtx; } /** * 判断问卷表标题是否重复 */ private boolean hasDuplicate(String title) { return jdao.queryForObject(" SELECT COUNT(*) FROM survey WHERE title = '" + title + "'", Integer.class) > 0; } /** * 插入时判断问卷表标题是否重复 */ private boolean hasOtherDuplicate(String title, int id) { return jdao.queryForObject(" SELECT COUNT(*) FROM survey WHERE title = '" + title + "' AND id <>" + id, Integer.class) > 0; } /** * 插入时判断问卷表标题是否重复 */ public int updateHit(int surveyid) { String strSql = "UPDATE survey SET hit=hit+1 WHERE id=?"; return jdao.update(conn -> { int i = 0; java.sql.PreparedStatement ps = conn.prepareStatement(strSql); ps.setInt(++i, surveyid); return ps; }); } /** * 判断问题题号是否重复 */ private boolean hasDuplicate(int number, int surveyid) { String tb_questions = "(SELECT * FROM question WHERE surveyid=" + surveyid + ") AS tb_questions"; return jdao.queryForObject("SELECT COUNT(*) FROM " + tb_questions + " WHERE `number` = " + number, Integer.class) > 0; } /** * 插入数据并返回ID */ public int insertAndReturnID(String title, String description, String image, String type, int userid) { KeyHolder keyHolder = new GeneratedKeyHolder(); String strSql = "insert into survey(title,description,createtime,isable,image,userid,`type`) values(?,?,NOW(),?,?,?,?)"; if (!hasDuplicate(title)) { jdao.update(conn -> { String isable = "启用"; int i = 0; java.sql.PreparedStatement ps; ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS); ps.setString(++i, title); ps.setString(++i, description); ps.setString(++i, isable); ps.setString(++i, image); ps.setInt(++i, userid); ps.setString(++i, type); return ps; }, keyHolder); return keyHolder.getKey().intValue(); } else { return -1; } } /** * 插入问卷与题目 */ public int insertSurveyAndQuestions(String title, String description, String image, String type, int userid, String savestring) { KeyHolder keyHolder = new GeneratedKeyHolder(); String strSql = "insert into survey(title,description,createtime,isable,image,userid,`type`,publish) values(?,?,NOW(),?,?,?,?,'可编辑')"; // 执行事务 return jtx.execute(status -> { try { jdao.update(conn -> { String isable = "启用"; int i = 0; java.sql.PreparedStatement ps; ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS); ps.setString(++i, title); ps.setString(++i, description); ps.setString(++i, isable); ps.setString(++i, image); ps.setInt(++i, userid); ps.setString(++i, type); return ps; }, keyHolder); int surveyid = keyHolder.getKey().intValue(); Gson gson = new Gson(); List<Question> retList = gson.fromJson(savestring, new TypeToken<List<Question>>() { }.getType()); int affectrows = 0; for (Question ques : retList) { String sqlsString; int number = ques.getNumber(); if (hasDuplicate(number, surveyid)) { sqlsString = "UPDATE question SET title=?,description=?,`type`=?,surveyid=?,`number`=?,opA=?,opB=?,opC=?,opD=?,opE=?,opF=?,opG=?,opH=?,opI=? WHERE surveyid=? AND `number`=?"; jdao.update(conn -> { int i = 0; java.sql.PreparedStatement ps; ps = conn.prepareStatement(sqlsString); ps.setString(++i, ques.getTitle()); ps.setString(++i, ques.getDescription()); ps.setString(++i, ques.getType()); ps.setInt(++i, surveyid); ps.setInt(++i, ques.getNumber()); ps.setString(++i, ques.getOpA()); ps.setString(++i, ques.getOpB()); ps.setString(++i, ques.getOpC()); ps.setString(++i, ques.getOpD()); ps.setString(++i, ques.getOpE()); ps.setString(++i, ques.getOpF()); ps.setString(++i, ques.getOpG()); ps.setString(++i, ques.getOpH()); ps.setString(++i, ques.getOpI()); ps.setInt(++i, surveyid); ps.setInt(++i, ques.getNumber()); return ps; }); } else { KeyHolder keyHolder1 = new GeneratedKeyHolder(); sqlsString = "INSERT INTO question(title,description,`type`,surveyid,`number`,opA,opB,opC,opD,opE,opF,opG,opH,opI) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; jdao.update(conn -> { int i = 0; java.sql.PreparedStatement ps; ps = conn.prepareStatement(sqlsString, Statement.RETURN_GENERATED_KEYS); ps.setString(++i, ques.getTitle()); ps.setString(++i, ques.getDescription()); ps.setString(++i, ques.getType()); ps.setInt(++i, surveyid); ps.setInt(++i, ques.getNumber()); ps.setString(++i, ques.getOpA()); ps.setString(++i, ques.getOpB()); ps.setString(++i, ques.getOpC()); ps.setString(++i, ques.getOpD()); ps.setString(++i, ques.getOpE()); ps.setString(++i, ques.getOpF()); ps.setString(++i, ques.getOpG()); ps.setString(++i, ques.getOpH()); ps.setString(++i, ques.getOpI()); return ps; }, keyHolder1); } } jdao.update("DELETE FROM question WHERE surveyid=" + surveyid + " AND `number`>" + retList.size()); return Constant.STATUS_CODE_SUCCESS; } catch (Exception e) { e.print