coursedao.java

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

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

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

import com.ccniit.kaoqin.db.DAOBase;
import com.ccniit.kaoqin.db.selectCourse.SelectCourse;
import com.ccniit.kaoqin.db.teacher.Teacher;
import com.ccniit.kaoqin.db.teacher.TeacherDAO;

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

	/**
	 * 根据课程ID得到详细信息（包括代课老师的信息）
	 * 
	 * @param course_id
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */

	@SuppressWarnings( { "static-access", "unchecked" })
	public Course getCourseDetailById(int course_id)
			throws ClassNotFoundException, SQLException {
		String sqlStr = "select * from course" + " c left join teachCourse "
				+ " t on c.course_id=t.course_id  "
				+ " left join teacher  te on  " + "te.teacher_id=t.teacher_id "
				+ "where c.course_id=?";

		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, course_id);
		rs = pst.executeQuery();
		ArrayList teachers = new ArrayList();
		Teacher teacher = null;
		Course course = null;
		ArrayList courses = new ArrayList();

		while (rs.next()) {

			course = this.assemble(rs);
			courses.add(course);
			teacher = TeacherDAO.assemble(rs);
			teachers.add(teacher);
			course.setTeacher(teachers);

		}

		this.release();
		return course;
	}
	
	
@SuppressWarnings("static-access")
public Course getCourseByNo(String No) throws ClassNotFoundException, SQLException{
	
	String sqlStr = "select *  from  course where course_NO=?";
	
	pst = dBMain.getPreparedStatement(sqlStr);

	pst.setString(1, No);
	Course course = null;
	rs = pst.executeQuery();
	while(rs.next()){
		course = this.assemble(rs);
	}
	
	
	return course;
	
	
	
	
}
	
	
	
	
	
	
	
	
	

	// 根据教师ID获得该教师的课程
	@SuppressWarnings( { "static-access", "unchecked" })
	public ArrayList getCourseByTeacherId(int teacher_id) throws SQLException,
			ClassNotFoundException {

		String sqlStr = "select *  from  course,teachCourse where course.course_id=teachCourse.course_id and teacher_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);

		pst.setInt(1, teacher_id);

		rs = pst.executeQuery();

		ArrayList courses = new ArrayList();
		Course course = null;
		while (rs.next()) {
			course = this.assemble(rs);
			courses.add(course);

		}
		this.release();
		return courses;

	}

	/**
	 * courses根据班级ID获得该班的课程
	 * 
	 * @param class_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	@SuppressWarnings( { "static-access", "unchecked" })
	public ArrayList getCourseByClassId(int class_id)
			throws ClassNotFoundException, SQLException {
		String sqlStr = "select * from  course ,selectCourse where course.course_id=selectCourse.course_id  and class_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);

		pst.setInt(1, class_id);

		rs = pst.executeQuery();

		ArrayList courses = new ArrayList();

		Course course = null;
		while (rs.next()) {

			course = this.assemble(rs);
			courses.add(course);
		}

		return courses;

	}

	/**
	 * 得到所有的课程信息
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */

	@SuppressWarnings("static-access")
	public ArrayList getAllCourse() throws ClassNotFoundException, SQLException {

		String sqlStr = "select * from course";
		pst = dBMain.getPreparedStatement(sqlStr);
		rs = pst.executeQuery();
		ArrayList<Course> courses = new ArrayList<Course>();
		Course course = null;
		while (rs.next()) {

			course = this.assemble(rs);
			courses.add(course);

		}
		this.release();

		return courses;

	}

	/**
	 * 根据课程ID删除课程
	 * 
	 * @param course_id
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void deleteCourse(int course_id) throws ClassNotFoundException,
			SQLException {

		String sqlStr = "delete from course where course_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setInt(1, course_id);
		pst.executeUpdate();

		this.release();

	}

	/**
	 * 修改课程
	 * 
	 * @param course
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void modifyCourse(Course course) throws ClassNotFoundException,
			SQLException {
		String sqlStr = "update course set course_NO=?,course_name=? where course_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		pst.setString(1, course.getCourse_NO());
		pst.setString(2, course.getCourse_name());
		pst.setInt(3, course.getCourse_id());
		pst.executeUpdate();
		this.release();
	}

	
	
	
	
	
	
	
	
	
	
	/**
	 * 添加一个课程
	 * 
	 * @param course
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void addCourse(Course course) throws ClassNotFoundException,
			SQLException {

		String sqlStr = "insert into course (course_NO,course_name)values (?,?)";

		pst = dBMain.getPreparedStatement(sqlStr);

		pst.setString(1, course.getCourse_NO());
		pst.setString(2, course.getCourse_name());

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

	}

	/**
	 * 根据课程ID号查询出一条相对应的信息
	 * 
	 * @param course_id
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */

	public Course getCourseById(int course_id) throws ClassNotFoundException,
			SQLException {

		// 对SQL语句进行封装
		String sqlStr = "select * from course where course_id = ?";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 参数指定
		pst.setInt(1, course_id);
		// 执行sql语句
		rs = pst.executeQuery();
		Course course = null;
		while (rs.next()) {
			course = assemble(rs);

		}

		this.release();
		return course;

	}
/**
 * 根据班级和课程ID返回课程的信息
 * @param course_id
 * @param class_id
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 */
	public Course getCourseByIdClassandCourseId(int course_id, int class_id)
			throws ClassNotFoundException, SQLException {

		// 对SQL语句进行封装
		String sqlStr = "select * from course,selectCourse "
				+ "where course.course_id=selectCourse.course_id and"
				+ " course.course_id=? and selectCourse.class_id=?";
		pst = dBMain.getPreparedStatement(sqlStr);
		// 参数指定
		pst.setInt(1, course_id);
		pst.setInt(2, class_id);
		// 执行sql语句
		rs = pst.executeQuery();
		Course course = null;
		SelectCourse selectCourse=null;
		
		while (rs.next()) {
			course = assemble(rs);
			selectCourse=com.ccniit.kaoqin.db.selectCourse.SelectCourseDAO.assemble(rs);
			course.setSelectCourse(selectCourse);

		}

		this.release();
		return course;

	}

	/**
	 * 将当前的课程 封装在一个assemble里面供 使用
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */

	public static Course assemble(java.sql.ResultSet rs) throws SQLException {
		Course course = new Course();

		course.setCourse_id(rs.getInt("course_id"));
		course.setCourse_NO(rs.getString("course_NO"));
		course.setCourse_name(rs.getString("course_name"));

		return course;

	}
}