teacherdao.java

学生考勤管理系统————分为课程选择

/**
 * 
 */
package com.ccniit.kaoqin.db.teacher;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ccniit.kaoqin.db.DAOBase;
import com.ccniit.kaoqin.db.course.Course;
import com.ccniit.kaoqin.db.course.CourseDAO;
import com.ccniit.kaoqin.db.teachCourse.TeachCourse;
import com.ccniit.kaoqin.db.teachCourse.TeachCourseDAO;


/**
 * 
 * 编写小组：邓从伟（张强_李楠）
 *
 */
public class TeacherDAO extends DAOBase{
	

	/**
	 * 根据教师ID号查询一条相对应的教师信息
	 * 
	 * @param teacher_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Teacher getTeacherById(int teacher_id)
			throws ClassNotFoundException, SQLException {
		// 对SQL语句进行封装
		String sqlStr = "select * from teacher where teacher_id = ?";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 参数指定
		pst.setInt(1, teacher_id);
		// 执行sql语句
		rs = pst.executeQuery();
		Teacher teacher = null;
		while (rs.next()) {
			teacher = assemble(rs);

		}
		this.release();
		return teacher;

	}
	
	
	public int getTeacherCout() throws ClassNotFoundException, SQLException {

		// 对SQL语句进行封装
		String sqlStr = "select * from teacher ";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 执行sql语句
		rs = pst.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getRow();

		}

		this.release();
		return count;
	}

	/**
	 * 得到总共页数
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int getTeacherYeShu() throws ClassNotFoundException, SQLException {

		int count = 0;
		count = (getTeacherCout() + 1) / 20;
		return count;
	}

	
	
	
	
	
	
	@SuppressWarnings("static-access")
	public ArrayList getFenyeTeacher(int pages) throws ClassNotFoundException, SQLException{
		
		
		
		String sqlStr="select top 20  * from teacher where teacher_id not in" +
				"(select top "+(20*(pages-1))+" teacher_id from teacher order by teacher_id ) ";
		pst=dBMain.getPreparedStatement(sqlStr) ;
		rs=pst.executeQuery();

		ArrayList<Teacher> teachers=new ArrayList<Teacher>();
		Teacher teacher=null;
		while(rs.next()){
			
			teacher=this.assemble(rs);
			teachers.add(teacher);
			
		}
		this.release();
		
		return teachers;
		
	}	
	
/**
 * 得到所有的老师
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 */	
@SuppressWarnings("static-access")
public ArrayList	getAllTeacher() throws ClassNotFoundException, SQLException{
	
	
	
	String sqlStr="select * from teacher";
	pst=dBMain.getPreparedStatement(sqlStr) ;
	rs=pst.executeQuery();

	ArrayList<Teacher> teachers=new ArrayList<Teacher>();
	Teacher teacher=null;
	while(rs.next()){
		
		teacher=this.assemble(rs);
		teachers.add(teacher);
		
	}
	this.release();
	
	return teachers;
	
}

/**
 * 根据老师ID号删除老师
 * @param teacher_id
 * @throws ClassNotFoundException
 * @throws SQLException
 */
public void deleteTeacher(int teacher_id) throws ClassNotFoundException, SQLException{
	
	String sqlStr="delete from teacher where teacher_id=?";
	
	pst=dBMain.getPreparedStatement(sqlStr);
	pst.setInt(1, teacher_id);
	pst.executeUpdate();
	this.release();
	
	 
	
	
}



/**
 *   修改老师信息
 * @param teacher
 * @throws ClassNotFoundException
 * @throws SQLException
 */
public void   modifyTeacher(Teacher teacher) throws ClassNotFoundException, SQLException{
	
	
	String sqlStr = "update teacher set teacher_NO=?,teacher_name=?,teacher_sex=?,teacher_office=?,teacher_phone=?,teacher_telPhone=?,teacher_email=?,teacher_password=? where teacher_id=?";
	pst = dBMain.getPreparedStatement(sqlStr);

	pst.setString(1, teacher.getTeacher_NO());
	pst.setString(2, teacher.getTeacher_name());
	pst.setString(3, teacher.getTeacher_sex());
	pst.setString(4, teacher.getTeacher_office());
	pst.setString(5, teacher.getTeacher_phone());
	pst.setString(6, teacher.getTeacher_telPhone());
	pst.setString(7, teacher.getTeacher_email());
	pst.setString(8, teacher.getTeacher_password());
	pst.setInt(9, teacher.getTeacher_id());

	pst.executeUpdate();
	this.release();
	
	
}



/**
 * 根据学生ID获得老师详细信息(包括教师的基本信息和所代课程)
 * @param teacher_id
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 */
@SuppressWarnings({ "static-access", "unchecked" })
public Teacher getTeacherDetailById(int teacher_id) throws ClassNotFoundException, SQLException{
	
	
	String sqlStr = "select * from teacher t left join teachCourse c on t.teacher_id=c.teacher_id left join course s on c.course_id=s.course_id where t.teacher_id=?";
	pst = dBMain.getPreparedStatement(sqlStr);
	pst.setInt(1, teacher_id);
	rs = pst.executeQuery();
	Teacher teacher =null;
	ArrayList courses = new ArrayList();
	Course course=null;
	ArrayList teachCourses = new ArrayList();
	TeachCourse teachCourse=null;
	while (rs.next()) {
		course = CourseDAO.assemble(rs);
		teacher = this.assemble(rs);
		teachCourse=TeachCourseDAO.assemble(rs);
		courses.add(course);
		teachCourses.add(teachCourse);
		teacher.setTeachCourse(teachCourses);
		teacher.setCourse(courses);
	}
	this.release();
	return teacher;

	
}



/**
 * 添加老师
 * @param teacher
 * @throws ClassNotFoundException
 * @throws SQLException
 */
public void addTeacher(Teacher teacher) throws ClassNotFoundException, SQLException{
	
	
	String sqlStr="insert into teacher (teacher_NO,teacher_name,teacher_sex,teacher_office,teacher_phone,teacher_telPhone,teacher_email,teacher_password)values (?,?,?,?,?,?,?,?)";
	pst=dBMain.getPreparedStatement(sqlStr);
	
	pst.setString(1,teacher.getTeacher_NO());
	pst.setString(2,teacher.getTeacher_name());
	pst.setString(3,teacher.getTeacher_sex());
	pst.setString(4,teacher.getTeacher_office());
	pst.setString(5,teacher.getTeacher_phone());
	pst.setString(6,teacher.getTeacher_telPhone());
	pst.setString(7,teacher.getTeacher_email());
	pst.setString(8,teacher.getTeacher_password());

	pst.executeUpdate();
	this.release();
	
	
	
	
}


/**
 * 根据邮件号获得老师信息
 * @param teacher_email
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 */
@SuppressWarnings("static-access")
public Teacher getTeacherByEmail(String teacher_email) throws ClassNotFoundException, SQLException{
	
	String sqlStr="select * from teacher where teacher_email=?";
	pst=dBMain.getPreparedStatement(sqlStr);
	pst.setString(1, teacher_email);
	rs=pst.executeQuery();
	Teacher teacher=null;
	while(rs.next()){
		
		teacher=this.assemble(rs);

		
	}
	this.release();
	
	return teacher;
	
}









/**
 * 把RS结果集中的数据放入到Bean里
 * @param rs
 * @return
 * @throws SQLException 
 */
public static Teacher assemble(ResultSet rs) throws SQLException{
	
	Teacher teacher=new Teacher();
	
	
	teacher.setTeacher_id(rs.getInt("teacher_id"));
	teacher.setTeacher_NO(rs.getString("teacher_NO"));
	teacher.setTeacher_name(rs.getString("teacher_name"));
	teacher.setTeacher_sex(rs.getString("teacher_sex"));
	teacher.setTeacher_office(rs.getString("teacher_office"));
	teacher.setTeacher_phone(rs.getString("teacher_phone"));
	teacher.setTeacher_telPhone(rs.getString("teacher_telPhone"));
	teacher.setTeacher_email(rs.getString("teacher_email"));
	teacher.setTeacher_password(rs.getString("teacher_password"));
	return teacher;
	 
}
}