studentdao.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.Student;
import com.yutang.dboption.DBOption;
import com.yutang.dboption.IPreparedStatementSetter;

public class StudentDao {
	Student student = new Student();
	DBOption<Student> db = new DBOption<Student>();
	List<Student> list = new ArrayList<Student>();
	// 检查是否有此用户
	public Student checkUser(final String stuName, final String stuCardID) {	
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql = "select * from Student where StuName = ? and StuCardID = ?";
		student = db.queryOne(sql, student, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, stuName);
				pst.setString(2, stuCardID);
				return pst;
			}
		});
		db.closeCon();
		return student;
	}

	// 根据班级查询该班所有学员信息,根据此信息算出总人数
	public List<Student> selectAllByBanJiID(final String banJiID) {
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql = "select * from Student where BanJiID = ?";
		list = db.queryAll(sql, student, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, banJiID);
				return pst;
			}
		});
		db.closeCon();
		return list;
	};
	//查询学员信息
	public Student selectStudentIDByBanJiID(final String banJiID){
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql="select top 1 * from Student where BanJiID = ? order by StuID desc";
		student = db.queryOne(sql, student, new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1,banJiID);
				return pst;
			}			
		});
		db.closeCon();		
		return student;
	}
	//添加学员信息
	public int insertStudent(final Student student){
		int result = 0;	
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql="insert into Student values(?,?,?,?,?,?,?,?,?,?,?,?)";
		result=db.save(sql, new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, student.getStuID());
				pst.setString(2, student.getStuName());
				pst.setString(3, student.getStuSex());
				pst.setInt(4, student.getStuAge());
				pst.setString(5, student.getStuBirthday());
				pst.setString(6, student.getStuCardID());
				pst.setString(7, student.getStuNation());
				pst.setString(8, student.getStuPhone());
				pst.setString(9, student.getStuAddr());
				pst.setInt(10, student.getStuState());
				pst.setString(11, student.getBanJiID());
				pst.setString(12, student.getRemark());
				return pst;		
			}			
		});
		db.closeCon();
		return result;
	}
	
	//据学生ID号检查是否有此学生用户
	public Student selectStudentByStuID(final String stuID){
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql="select * from Student where stuID=?";
		student=db.queryOne(sql, student,new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, stuID);
				return pst;
			}			
		}	
		);
		db.closeCon();
		return student;
	}
	
	//通过学号修改学生信息
	public int updateStudentByID(final Student student){
		db.setCon(MyConnection.getConnection(StudentDao.class));
		int result = 0;
		String sql = "update Student set StuName = ?,StuSex = ?,StuAge = ?,StuBirthday = ?,StuCardID = ?,StuNation = ?,StuPhone = ?,StuAddr = ?,StuState = ?,BanJiID = ?,Remark = ? where StuID = ?";
		result = db.save(sql, new IPreparedStatementSetter(){
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1,student.getStuName());
				pst.setString(2,student.getStuSex());
				pst.setInt(3,student.getStuAge());
				pst.setString(4,student.getStuBirthday());
				pst.setString(5,student.getStuCardID());
				pst.setString(6,student.getStuNation());
				pst.setString(7, student.getStuPhone());
				pst.setString(8,student.getStuAddr());
				pst.setInt(9,student.getStuState());
				pst.setString(10,student.getBanJiID());
				pst.setString(11,student.getRemark());
				pst.setString(12,student.getStuID());
				return pst;
			}			
		});
		return result;
	}
	
	public List<Student> selectAllStudent(){
		db.setCon(MyConnection.getConnection(StudentDao.class));
		String sql = "select * from Student order by StuID desc";
		list = db.queryAll(sql,student);
		return list;
	}
}