ExamDao

package com.iflytek.edu.zx.exammng.dao;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.annotation.Resource;import org.apache.commons.lang3.StringUtils;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.stereotype.Repository;import com.iflytek.edu.elp.common.dto.page.PageParam;import com.iflytek.edu.elp.common.util.DaoUtil;import com.iflytek.edu.zx.exammng.model.Exam;import com.iflytek.edu.zx.exammng.model.ExamStatus;import com.iflytek.edu.zx.exammng.model.agent.Grade;import com.iflytek.edu.zx.exammng.model.agent.Phase;import com.iflytek.edu.zx.exammng.model.agent.User;/** * 考试Dao. *  * @author znyin *  */@Repository("examDao")public class ExamDao {/** * NamedParameterJdbcTemplate */@Resourceprivate NamedParameterJdbcTemplate namedParameterJdbcTemplateExammng;/** * JdbcTemplate */@Resourceprivate JdbcTemplate jdbcTemplateExammng;/** * 插入。 *  * @param exam *            考试。 */public void insert(Exam exam) {StringBuilder sql = new StringBuilder();sql.append(" insert into exammng_exam (id,name,phaseCode,phaseName,gradeCode,");sql.append("gradeName,examTime,creatorId,createTime,isDelete)");sql.append(" values (:id,:name,:phaseCode,:phaseName,:gradeCode,");sql.append(":gradeName,:examTime,:creatorId,:createTime,:isDelete)");Map<String, Object> paramMap = new HashMap<String, Object>();paramMap.put("id", exam.getId());paramMap.put("name", exam.getName());paramMap.put("phaseCode", exam.getGrade().getCode());paramMap.put("phaseName", exam.getGrade().getName());paramMap.put("gradeCode", exam.getGrade().getCode());paramMap.put("gradeName", exam.getGrade().getName());paramMap.put("examTime", exam.getExamTime());paramMap.put("creatorId", exam.getCreator().getId());paramMap.put("createTime", exam.getCreateTime());paramMap.put("isDelete", false);namedParameterJdbcTemplateExammng.update(sql.toString(), paramMap);}/** * 更新。 * <p> * 只会更新name、examTime。 *  * @param examId *            考试Id * @param name *            考试名称 * @param examTime *            考试时间 */public void update(String examId, String name, Date examTime) {String sql = "update exammng_exam set name=?,examTime=? where id=?";jdbcTemplateExammng.update(sql, name, examTime, examId);}/** * 查询一考试。 *  * @param examId *            考试Id * @return 考试 */public Exam find(String examId) {StringBuilder sql = new StringBuilder();sql.append(" select id,name,phaseCode,phaseName,gradeCode,");sql.append("gradeName,examTime,creatorId,status,createTime");sql.append(" from exammng_exam where id=?");Exam exam = jdbcTemplateExammng.queryForObject(sql.toString(), new Object[] { examId }, new ExamMapper());return exam;}/** * 查询某次考试的创建人编码。 *  * @param examId *            考试Id * @return 创建人编码 */public String findCreatorCode(String examId) {String sql = "select creatorId from exammng_exam where id=?";return jdbcTemplateExammng.queryForObject(sql, new Object[] { examId }, String.class);}/** * 查询某次考试的状态。 *  * @param examId *            考试Id * @return 考试状态 */public ExamStatus findStatus(String examId) {String sql = "select status from exammng_exam where id=?";String status = jdbcTemplateExammng.queryForObject(sql, new Object[] { examId }, String.class);return ExamStatus.valueOf(status);}/** * 查询考试列表。 * <p> *  * <pre> * 物理分页。 *  * 考试的id、name、grade、examTime会填充。 *  * 默人按createTime降序排列。 * </pre> *  * @param creatorId *            创建人Id。 * @param statusList *            考试状态列表。 * @param pageParam *            分页参数 * @return 考试 */public List<Exam> findExams(String creatorId, List<ExamStatus> statusList, PageParam pageParam) {StringBuilder strStatus=new StringBuilder();strStatus.append("(");for (int i=0;i<statusList.size();i++) {strStatus.append("'");strStatus.append(statusList.get(i).name());strStatus.append("'");if(i<statusList.size()-1){strStatus.append(",");}}strStatus.append(")");StringBuilder sql = new StringBuilder();sql.append(" select id,name,gradeCode,gradeName,examTime");sql.append(" from exammng_exam");sql.append(" where isDelete=false and creatorId=? and status in ");sql.append(strStatus.toString());if (!StringUtils.isEmpty(pageParam.getOrderBy())) {sql.append(" order by ").append(pageParam.getOrderBy());sql.append(pageParam.getOrderDirection() == null ? "ASC" : pageParam.getOrderDirection().name());} else {sql.append(" order by createTime DESC");}sql.append(" limit ?,?");Object[] params = new Object[] { creatorId, pageParam.getStartIndex(), pageParam.getPageSize() };return jdbcTemplateExammng.query(sql.toString(), params, new ExamMapper());}/** * 查询考试数量。 *  * @param creatorId *            创建人Id. * @param statusList *            考试状态列表。 * @return 考试数量 */public int findCount(String creatorId, List<ExamStatus> statusList) {String sql = "select count(1) from exammng_exam where isDelete=false and creatorId=? and status in ";StringBuilder strStatus=new StringBuilder();strStatus.append("(");for (int i=0;i<statusList.size();i++) {strStatus.append("'");strStatus.append(statusList.get(i).name());strStatus.append("'");if(i<statusList.size()-1){strStatus.append(",");}}strStatus.append(")");sql=sql+strStatus.toString();return jdbcTemplateExammng.queryForObject(sql, new Object[] { creatorId }, Integer.class);}/** * 更新考试状态。 *  * @param examId *            考试Id * @param status *            考试状态。 */public void updateStatus(String examId, ExamStatus status) {String sql = "update exammng_exam set status=? where id=?";jdbcTemplateExammng.update(sql, status.name(), examId);}/** * 更新删除标识。 *  * @param examId *            考试Id * @param isDelete *            删除标识，true：删除，false：未删除。 */public void updateIsDelete(String examId, boolean isDelete) {String sql = "update exammng_exam set isDelete=? where id=?";jdbcTemplateExammng.update(sql, isDelete, examId);}/** *  * @author znyin *  */class ExamMapper implements RowMapper<Exam> {@Overridepublic Exam mapRow(ResultSet rs, int rowNum) throws SQLException {Exam exam = new Exam();if (DaoUtil.isColumnExist(rs, "createTime")) {exam.setCreateTime(rs.getDate("createTime"));}if (DaoUtil.isColumnExist(rs, "creatorId")) {User creator = new User();creator.setId(rs.getString("creatorId"));exam.setCreator(creator);}if (DaoUtil.isColumnExist(rs, "examTime")) {exam.setExamTime(rs.getDate("examTime"));}if (DaoUtil.isColumnExist(rs, "gradeCode")) {Grade grade = new Grade();grade.setCode(rs.getString("gradeCode"));grade.setName(rs.getString("gradeName"));exam.setGrade(grade);}if (DaoUtil.isColumnExist(rs, "id")) {exam.setId(rs.getString("id"));}if (DaoUtil.isColumnExist(rs, "name")) {exam.setName(rs.getString("name"));}if (DaoUtil.isColumnExist(rs, "phaseCode")) {Phase phase = new Phase();phase.setCode(rs.getString("phaseCode"));phase.setName(rs.getString("phaseName"));exam.setPhase(phase);}if (DaoUtil.isColumnExist(rs, "phaseCode")) {exam.setStatus(ExamStatus.valueOf(rs.getString("status")));}return exam;}}}