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

public class TeacherDao {
	List<Teacher> list = new ArrayList<Teacher>();
	Teacher teacher = new Teacher();
	DBOption<Teacher> db = new DBOption<Teacher>();

	// 根据教师类型查找相应的教师信息
	public List<Teacher> selectAllBySort(final int teaSort) {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select * from Teacher where TeaSort = ?";
		list = db.queryAll(sql, teacher, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setInt(1, teaSort);
				return pst;
			}
		});
		db.closeCon();
		return list;
	}

	// 检查是否有此用户
	public Teacher checkUser(final String teaName, final String teaCardID) {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select * from Teacher where TeaName = ? and TeaCardID = ?";
		teacher = db.queryOne(sql, teacher, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teaName);
				pst.setString(2, teaCardID);
				return pst;
			}
		});
		db.closeCon();
		return teacher;
	}

	// 根据教师编号查找教师姓名
	public Teacher selectTeaNameByTeaID(final String teaID) {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select * from Teacher where TeaID = ?";
		teacher = db.queryOne(sql, teacher, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teaID);
				return pst;
			}
		});
		db.closeCon();
		return teacher;
	}

	// 根据姓名找编号
	public Teacher selectTeaIDByTeaName(final String teaName) {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select * from Teacher where TeaName = ?";
		teacher = db.queryOne(sql, teacher, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teaName);
				return pst;
			}
		});
		db.closeCon();
		return teacher;
	}

	// 添加教师信息
	public int insertTeacher(final Teacher teacher) {
		int result = 0;
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "insert into Teacher values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		result = db.save(sql, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teacher.getTeaID());
				pst.setString(2, teacher.getTeaName());
				pst.setString(3, teacher.getTeaSex());
				pst.setInt(4, teacher.getTeaAge());
				pst.setString(5, teacher.getTeaBirthday());
				pst.setString(6, teacher.getTeaCardID());
				pst.setString(7, teacher.getTeaNation());
				pst.setString(8, teacher.getTeaPhone());
				pst.setString(9, teacher.getTeaAddr());
				pst.setInt(10, teacher.getTeaSort());
				pst.setInt(11, teacher.getTeaState());
				pst.setInt(12, teacher.getCouID());
				pst.setString(13, teacher.getRemark());
				return pst;
			}
		});
		db.closeCon();
		return result;
	}

	public int insertTeacherNoCou(final Teacher teacher) {
		int result = 0;
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "insert into Teacher(TeaID,TeaName,TeaSex,TeaAge,TeaBirthday,TeaCardID,TeaNation,TeaPhone,TeaAddr,TeaSort,TeaState,Remark) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		result = db.save(sql, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teacher.getTeaID());
				pst.setString(2, teacher.getTeaName());
				pst.setString(3, teacher.getTeaSex());
				pst.setInt(4, teacher.getTeaAge());
				pst.setString(5, teacher.getTeaBirthday());
				pst.setString(6, teacher.getTeaCardID());
				pst.setString(7, teacher.getTeaNation());
				pst.setString(8, teacher.getTeaPhone());
				pst.setString(9, teacher.getTeaAddr());
				pst.setInt(10, teacher.getTeaSort());
				pst.setInt(11, teacher.getTeaState());
				pst.setString(12, teacher.getRemark());
				return pst;
			}
		});
		db.closeCon();
		return result;
	}

	// 选择最后一个教师的编号
	public Teacher selectTeacherLastID() {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select Top 1 * from Teacher order by TeaID desc";
		teacher = db.queryOne(sql, teacher);
		return teacher;
	}

	// 根据教师编号修改信息
	public int updateTeacherByID(final Teacher teacher) {
		int result = 0;
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "update Teacher set TeaName = ?,TeaSex = ?,TeaAge = ?,TeaBirthday = ?,TeaCardID = ?,TeaNation = ?,TeaPhone = ?,TeaAddr = ?,TeaSort = ?,TeaState = ?,CouID = ?,Remark = ? where TeaID = ?";
		result = db.save(sql, new IPreparedStatementSetter() {
			public PreparedStatement setValues(PreparedStatement pst)
					throws SQLException {
				pst.setString(1, teacher.getTeaName());
				pst.setString(2, teacher.getTeaSex());
				pst.setInt(3, teacher.getTeaAge());
				pst.setString(4, teacher.getTeaBirthday());
				pst.setString(5, teacher.getTeaCardID());
				pst.setString(6, teacher.getTeaNation());
				pst.setString(7, teacher.getTeaPhone());
				pst.setString(8, teacher.getTeaAddr());
				pst.setInt(9, teacher.getTeaSort());
				pst.setInt(10, teacher.getTeaState());
				pst.setInt(11, teacher.getCouID());
				pst.setString(12, teacher.getRemark());
				pst.setString(13, teacher.getTeaID());
				return pst;
			}
		});
		return result;
	}

	public List<Teacher> selectAllTeacher() {
		db.setCon(MyConnection.getConnection(TeacherDao.class));
		String sql = "select * from Teacher";
		list = db.queryAll(sql, teacher);
		return list;
	}
}