examdbdao.java

accp s1毕业项目 考试管理系统

package com.exam.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.exam.db.bean.ExamDB;
import com.yutang.dboption.DBOption;
import com.yutang.dboption.IPreparedStatementSetter;

public class ExamDBDao {
	List<ExamDB> list = new ArrayList<ExamDB>();
	DBOption<ExamDB> db = new DBOption<ExamDB>();
	ExamDB examDB = new ExamDB();
	
	public int insertExamDB(final ExamDB examDB) {
		int result = 0;
		db.setCon(MyConnection.getConnection(ExamDBDao.class));
		String sql = "insert into ExamDB values(?,?,?,?,?,getDate())";
		result = db.save(sql, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setInt(1, examDB.getCouID());
				pst.setString(2, examDB.getStuID());
				pst.setString(3, examDB.getQuestion());
				pst.setString(4, examDB.getAnswer());
				pst.setInt(5, examDB.getScore());
				return pst;
			}
		});
		return result;
	}

	public List<ExamDB> selectExamDBByStuID(final String stuID) {		
		db.setCon(MyConnection.getConnection(ExamDBDao.class));
		String sql = "select * from ExamDB where StuID = ?";
		list = db.queryAll(sql, examDB, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, stuID);
				return pst;
			}
		});
		return list;
	}
	
	//根据班级,科目选择平均分
	public List<ExamDB> selectAvrScoreByStuIDAndCouID(String stuID,int couID){
		db.setCon(MyConnection.getConnection(ExamDBDao.class));
		String sql = "select * from ExamDB where StuID like '"+stuID+"' and CouID = "+couID;
		list = db.queryAll(sql,examDB);
		return list;
	}
	
	//根据学号，科目选择考试数据
	public List<ExamDB> selectExamDBByStuIDAndCouID(final String stuID,final int couID){
		db.setCon(MyConnection.getConnection(ExamDBDao.class));
		String sql = "select * from ExamDB where StuID = ? and CouID = ? order by ExamTime desc";
		list = db.queryAll(sql, examDB, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, stuID);
				pst.setInt(2, couID);
				return pst;
			}
		});
		return list;
	}
}