/** * @author 李佩 *  * @version v1 *  * @time 2016/12/5 19:20 *  * @program  课程安排信息与数据库进行操作的类 * */package com.csms.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.csms.DBLink;import com.csms.entity.CourseArrange;import com.csms.entity.SelectCourse;public class CourseArrangeDAO {	// 查询课程信息的所有信息	public List<CourseArrange> searchCourseInformation() {		// 创建一个课程安排的对象		CourseArrange arrange = null;		// 创建一个List容器保存数据对象		List<CourseArrange> arrangeList = new ArrayList<CourseArrange>();		// 1. 取得数据库连接		Connection conn = DBLink.getConn();		// 2. 写出数据库查询语句		String sql = "SELECT * FROM coursearrange";		// 3. 设置查询变量		PreparedStatement ptm = null;		ResultSet rs = null;		try {			// 4. 将数据库语句送入数据库，执行查询			ptm = conn.prepareStatement(sql);			rs = ptm.executeQuery();			while (rs.next()) {				arrange = new CourseArrange();				arrange.setCourseID(rs.getString("courseID"));				arrange.setCourseTeacher(rs.getString("courseTeacher"));				arrange.setCourseName(rs.getString("courseName"));				arrange.setCourseGridits(rs.getInt("courseCridits"));				arrange.setCourseTimes(rs.getInt("courseTime"));				arrange.setCourseType(rs.getString("courseType"));				arrange.setCourseNumber(rs.getInt("courseNumber"));				arrange.setCourseSelectNumber(rs.getInt("courseSelectNumber"));				arrange.setCourseArrange(rs.getString("courseArrage"));				arrangeList.add(arrange);			}			rs.close();		} catch (SQLException e) {			e.printStackTrace();		} finally {			try {				if (ptm != null)					ptm.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		return arrangeList;	}	// 联合课程安排表和课程已选表查询学生的课程，任课老师，学生的学分，成绩和上课安排	public List<Object> searchSpecificInformation(String studentID) {		// 设置安排课程对象		CourseArrange ca = null;		// 设置已选课程的对象		SelectCourse sc = null;		// 创建一个List容器保存数据对象		List<Object> list = new ArrayList<Object>();		System.out.println("studentID: " + studentID);		// 1. 取得数据库连接对象		Connection conn = DBLink.getConn();		// 2. 写出查询sql语句段		String sql = "SELECT CA.courseName,CA.courseTeacher,CA.courseCridits,SC.courseGrade,CA.courseArrage FROM coursearrange CA,selectcourse SC WHERE SC.courseID=CA.courseID AND SC.studentID=?";		// 设置查询变量		PreparedStatement ptm = null;		ResultSet rs = null;		try {			// 3. 将数据库语句插入，执行查询			ptm = conn.prepareStatement(sql);			ptm.setString(1, studentID);			rs = ptm.executeQuery();			while (rs.next()) {				ca = new CourseArrange();				sc = new SelectCourse();				ca.setCourseName(rs.getString("courseName"));				ca.setCourseTeacher(rs.getString("courseTeacher"));				ca.setCourseGridits(rs.getInt("courseCridits"));				ca.setCourseArrange(rs.getString("courseArrage"));				sc.setStudentGrade(rs.getDouble("courseGrade"));				list.add(ca);				list.add(sc);			}			rs.close();		} catch (SQLException e) {			e.printStackTrace();		} finally {			try {				if (ptm != null)					ptm.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		System.out.println("List size: " + list.size());		return list;	}	// 查询课程名和课程id	public List<CourseArrange> selectNameAndIDForCourse(String studentID) {		List<CourseArrange> list = new ArrayList<CourseArrange>();		CourseArrange ca = null;		Connection conn = DBLink.getConn();		String sql = "SELECT CA.courseName,CA.courseID,CA.courseArrage,CA.courseCridits FROM coursearrange CA,selectcourse SC WHERE SC.courseID=CA.courseID AND SC.studentID=?";		// 设置查询变量		PreparedStatement ptm = null;		ResultSet rs = null;		try {			// 3. 将数据库语句插入，执行查询			ptm = conn.prepareStatement(sql);			ptm.setString(1, studentID);			rs = ptm.executeQuery();			while (rs.next()) {				ca = new CourseArrange();				ca.setCourseName(rs.getString("courseName"));				ca.setCourseID(rs.getString("courseID"));				ca.setCourseArrange(rs.getString("courseArrage"));				ca.setCourseGridits(rs.getInt("courseCridits"));				list.add(ca);			}			rs.close();		} catch (SQLException e) {			e.printStackTrace();		} finally {			try {				if (ptm != null)					ptm.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		return list;	}	// 插入课程安排信息	public void insertCourseArrange(CourseArrange ca){		Connection conn = DBLink.getConn();		String sql ="INSERT INTO coursearrange (courseID,courseTeacher,courseName,courseCridits,courseTime,courseType,courseNumber,courseSelectNumber,courseArrage) VALUES 	(?,?,?,?,?,?,?,?,?)";		PreparedStatement ptm = null;		try{			ptm = conn.prepareStatement(sql);			ptm.setString(1, ca.getCourseID());			ptm.setString(2, ca.getCourseTeacher());			ptm.setString(3, ca.getCourseName());			ptm.setInt(4, ca.getCourseGridits());			ptm.setInt(5, ca.getCourseTimes());			ptm.setString(6, ca.getCourseType());			ptm.setInt(7, ca.getCourseNumber());			ptm.setInt(8, ca.getCourseSelectNumber());			ptm.setString(9, ca.getCourseArrange());			ptm.execute();		}catch(SQLException e){			e.printStackTrace();		}finally{			try{				if(ptm!=null)					ptm.close();			}catch(SQLException e){				e.printStackTrace();			}		}	}	// 删除排课信息	public void deleteArrageCourseInformation(String id){		Connection conn = DBLink.getConn();		String sql = "DELETE FROM coursearrange WHERE courseID=?";		PreparedStatement ptm = null;		try{			ptm = conn.prepareStatement(sql);			ptm.setString(1, id);			ptm.execute();		}catch(SQLException e){			e.printStackTrace();		}finally{			try{				if(ptm != null){					ptm.close();				}			}catch(SQLException e){				e.printStackTrace();			}		}	}		// 查看所有课程信息	public List<CourseArrange> searchAllCourseArrange(){		List<CourseArrange> list = new ArrayList<CourseArrange>();		CourseArrange ca = null;		Connection conn = DBLink.getConn();		String sql = "SELECT courseID,courseTeacher,courseName,courseCridits,courseTime,courseType,courseNumber,courseSelectNumber,courseArrage FROM coursearrange";		PreparedStatement ptm = null;		ResultSet rs = null;		try{			ptm = conn.prepareStatement(sql);			rs = ptm.executeQuery();			while(rs.next()){				ca = new CourseArrange();				ca.setCourseID(rs.getString("courseID"));				ca.setCourseTeacher(rs.getString("courseTeacher"));				ca.setCourseName(rs.getString("courseName"));				ca.setCourseGridits(rs.getInt("courseCridits"));				ca.setCourseTimes(rs.getInt("courseTime"));				ca.setCourseType(rs.getString("courseType"));				ca.setCourseNumber(rs.getInt("courseNumber"));				ca.setCourseSelectNumber(rs.getInt("courseSelectNumber"));				ca.setCourseArrange(rs.getString("courseArrage"));				list.add(ca);			}			rs.close();		}catch(SQLException e){			e.printStackTrace();		}finally{			try{				if(ptm!=null){					ptm.close();				}			}catch(SQLException e){				e.printStackTrace();			}		}		return list;	}}