package comitdao;import comitdomainExamQuestion;import comitdomainResult;import comitutilsjdbcUtil;import orgspringframeworkdaoDataAccessException;import orgspringframeworkjdbccoreBeanPropertyRowMappe

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 QuestionDao {

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

/*获取问题列表*/
public List<ExamQuestion> getQuestionList(String courseName) {
    String sql;
    List<ExamQuestion> questions;
    if (courseName == null) {
        sql = "select * from question";
        questions = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ExamQuestion>(ExamQuestion.class));
    }else {
        sql = "select * from question where course = ?";
        questions = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ExamQuestion>(ExamQuestion.class), courseName);
    }

    return questions;
}

/*添加问题*/
public ExamQuestion addQuestion(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 getSingleQuestion(String qno,String course) {
    try {
        String sql = "select * from question where qno = ? and course = ?";
        ExamQuestion question = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<ExamQuestion>(ExamQuestion.class), qno, course);
        return question;
    } catch (DataAccessException ignored) {

    }
    return null;
}

/*修改问题*/
public int updateQuestion(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 deleteQuestion(String qno) {
    int i = 0;
    try {
        String sql = "delete from question where qno = ?";
        i = jdbcTemplate.update(sql, qno);
    } catch (DataAccessException ignored) {

    }
    return i;
}

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