Java实现考试系统问题管理- QuestionDao代码示例

package com.it.dao;

import com.it.domain.ExamQuestion; import com.it.domain.Result; import com.it.utils.JdbcUtil; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder;

import java.sql.PreparedStatement; import java.sql.Statement; import java.util.List; import java.util.Objects;

/**

问题数据访问对象 */ public class ExaminationQuestionDao {

private final JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtil.getDataSource());

/获取问题列表/ public List retrieveQuestionList(String courseName) { String sql; List questionItems; if (courseName == null) { sql = 'select * from question'; questionItems = jdbcTemplate.query(sql, new BeanPropertyRowMapper(ExamQuestion.class)); }else { sql = 'select * from question where course = ?'; questionItems = jdbcTemplate.query(sql, new BeanPropertyRowMapper(ExamQuestion.class), courseName); }
```
 return questionItems;
```
}

/添加问题/ public ExamQuestion createQuestion(ExamQuestion question) { try { String sql = 'insert into question (course,qname,ans1,ans2,ans3,ans4,answer) values (?,?,?,?,?,?,?)'; KeyHolder holder = new GeneratedKeyHolder(); jdbcTemplate.update(connection -> { PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, question.getCourse()); ps.setString(2, question.getQuestionName()); ps.setString(3, question.getOption1()); ps.setString(4, question.getOption2() ); ps.setString(5, question.getOption3() ); ps.setString(6, question.getOption4() ); ps.setString(7, question.getCorrectAnswer()); return ps; }, holder); int id = Objects.requireNonNull(holder.getKey()).intValue(); question.setQuestionNumber(id); return question; } catch (DataAccessException e) { e.printStackTrace(); } return null; }

/查询单个问题/ public ExamQuestion retrieveSingleQuestion(String questionId,String course) { try { String sql = 'select * from question where qno = ? and course = ?'; ExamQuestion question = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(ExamQuestion.class), questionId, course); return question; } catch (DataAccessException ignored) {
```
 }
 return null;
```
}

/修改问题/ public int modifyQuestion(ExamQuestion one) { int i = 0; try { String sql = 'update question set qname = ? ,ans1=? , ans2=? ,ans3=? ,ans4=? , answer = ? where qno = ?'; i = jdbcTemplate.update(sql, one.getQuestionName(), one.getOption1(), one.getOption2(), one.getOption3(), one.getOption4(), one.getCorrectAnswer(),one.getCorrectAnswer()); } catch (DataAccessException ignored) {
```
 }
 return i;
```
}

/删除问题/ public int removeQuestion(String questionId) { int i = 0; try { String sql = 'delete from question where qno = ?'; i = jdbcTemplate.update(sql, questionId); } catch (DataAccessException ignored) {
```
 }
 return i;
```
}

/查询成绩/ public List retrieveExamResults(String courseName) { String sql; List results; if (courseName == null) { sql = 'select * from result'; results = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Result.class)); } else { sql = 'select * from result where course = ?'; results = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Result.class), courseName); } return results; } }