studentdao.java

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

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

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

import com.ccniit.kaoqin.db.DAOBase;
import com.ccniit.kaoqin.db.classes.Classes;
import com.ccniit.kaoqin.db.classes.ClassesDAO;
import com.ccniit.kaoqin.db.course.Course;
import com.ccniit.kaoqin.db.course.CourseDAO;
import com.ccniit.kaoqin.db.selectCourse.SelectCourse;
import com.ccniit.kaoqin.db.selectCourse.SelectCourseDAO;

/**
 * 此类是学生的DAO类 编写小组：邓从伟（张强_李楠） 此类主要放一些对学生操作的代码
 */
public class StudentDAO extends DAOBase {

	/**
	 * 根据班级号获得此班的所有的学生信息
	 * 
	 * @param class_id
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */

	@SuppressWarnings("static-access")
	public ArrayList getStudentByClassId(int class_id)
			throws ClassNotFoundException, SQLException {

		String sqlStr = "select * from student where class_id=?";
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, class_id);
		rs = pst.executeQuery();

		while (rs.next()) {
			student = this.assemble(rs);
			students.add(student);
		}
		this.release();

		return students;

	}

	/**
	 * 根据学生的ID号查询出相对应的全部信息
	 * 
	 * @param student_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Student getStudentById(int student_id)
			throws ClassNotFoundException, SQLException {
		// 对SQL语句进行封装
		String sqlStr = "select * from student where student_id = ?";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 参数指定
		pst.setInt(1, student_id);
		// 执行sql语句
		rs = pst.executeQuery();
		Student student = null;
		while (rs.next()) {
			student = assemble(rs);
		}
		this.release();
		return student;
	}

	/**
	 * 根据学生的ID号查询出相对应的全部信息
	 * 
	 * @param student_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Student getStudentByNo(String student_No)
			throws ClassNotFoundException, SQLException {
		// 对SQL语句进行封装
		String sqlStr = "select * from student where student_NO = ?";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 参数指定
		pst.setString(1, student_No);
		// 执行sql语句
		rs = pst.executeQuery();
		Student student = null;
		while (rs.next()) {
			student = assemble(rs);
		}
		this.release();
		return student;
	}

	/**
	 * 添加一个学生
	 * 
	 * @param student
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */

	public void addStudent(Student student) throws ClassNotFoundException,
			SQLException {

		String sqlStr = "insert into student (class_id,student_NO ,student_name ,student_sex ,student_room ,student_phone ,student_telPhone ,student_email  ,student_password,student_image)values(?,?,?,?,?,?,?,?,?,?)";

		pst = dBMain.getPreparedStatement(sqlStr);

		pst.setInt(1, student.getClass_id());
		pst.setString(2, student.getStudent_NO());
		pst.setString(3, student.getStudent_name());
		pst.setString(4, student.getStudent_sex());
		pst.setString(5, student.getStudent_room());
		pst.setString(6, student.getStudent_phone());
		pst.setString(7, student.getStudent_telPhone());
		pst.setString(8, student.getStudent_email());
		pst.setString(9, student.getStudent_password());
		pst.setString(10, student.getStudent_image());

		// =========//
		pst.executeUpdate();
		this.release();

	}

	/**
	 * 根据学生ID号获得学生的详细信息 (包括学生的基本信息和所选的课程信息) 这个后头在改
	 * 
	 * @param student_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	@SuppressWarnings( { "static-access", "unchecked" })
	public Student getStudentDeatilById(int student_id)
			throws ClassNotFoundException, SQLException {
		String sqlStr = "select * from student s left join class c on s.class_id=c.class_id left join selectCourse e on c.class_id=e.class_id left join course r on e.course_id=r.course_id where student_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, student_id);
		rs = pst.executeQuery();
		Student student = null;
		ArrayList courses = new ArrayList();
		Course course = null;
		ArrayList selectCourses = new ArrayList();
		SelectCourse selectCourse = null;
		Classes classes = null;
		while (rs.next()) {
			course = CourseDAO.assemble(rs);
			student = this.assemble(rs);
			selectCourse = SelectCourseDAO.assemble(rs);
			classes = ClassesDAO.assemble(rs);
			courses.add(course);
			selectCourses.add(selectCourse);
			student.setSelectCourse(selectCourses);
			student.setCourse(courses);
			student.setClasses(classes);
		}
		this.release();
		return student;

	}

	/**
	 * 根据制定的学生ID号删除学生
	 * 
	 * @param student_id
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void deleteStudent(int student_id) throws ClassNotFoundException,
			SQLException {
		// 对SQL语句进行封装
		String sqlStr = "delete from student where student_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, student_id);
		pst.executeUpdate();
		this.release();
	}

	/**
	 * 修改学生信息
	 * 
	 * @param student
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void modifyStudent(Student student) throws ClassNotFoundException,
			SQLException {

		String sqlStr = "update  student  set class_id=?  ,student_NO=? ,student_name=? ,student_sex=? ,student_room=? ,student_phone=? ,student_telPhone=? ,student_email=?  ,student_password=?,student_image=? where student_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, student.getClass_id());
		pst.setString(2, student.getStudent_NO());
		pst.setString(3, student.getStudent_name());
		pst.setString(4, student.getStudent_sex());
		pst.setString(5, student.getStudent_room());
		pst.setString(6, student.getStudent_phone());
		pst.setString(7, student.getStudent_telPhone());
		pst.setString(8, student.getStudent_email());
		pst.setString(9, student.getStudent_password());
		pst.setString(10, student.getStudent_image());
		pst.setInt(11, student.getStudent_id());
		pst.executeUpdate();
		this.release();

	}

	/**
	 * 此方法主要是对Student类进行数据设置从RS中
	 * 
	 * @param rs
	 * @return student
	 * @throws SQLException
	 */

	public static Student assemble(ResultSet rs) throws SQLException {

		Student student = new Student();
		student.setStudent_id(rs.getInt("student_id"));
		student.setClass_id(rs.getInt("class_id"));
		student.setStudent_NO(rs.getString("student_NO"));
		student.setStudent_name(rs.getString("student_name"));
		student.setStudent_sex(rs.getString("student_sex"));
		student.setStudent_room(rs.getString("student_room"));
		student.setStudent_phone(rs.getString("student_phone"));
		student.setStudent_telPhone(rs.getString("student_telPhone"));
		student.setStudent_email(rs.getString("student_email"));
		student.setStudent_password(rs.getString("student_password"));
		student.setStudent_image(rs.getString("student_image"));

		return student;

	}

}