coursedao.java

我实习时候的设计

package cn.yxh.dao;

import java.sql.*;
import java.util.ArrayList;

import cn.yxh.util.DBConn;

public class CourseDAO {
	//得到全部的选课信息
	public static ResultSet allCoureseInfo() {
		String sql = "select * from course";
		Connection conn = DBConn.getConn();
		ResultSet rs = null;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	//得到一个学生的选课情况
	public static ResultSet signalStudentCourseInfo(String studentid) {
		String sql = "select c.* ,sc.score from sc sc right join course c on sc.courseid = c.courseid where sc.studentid = ?";
		Connection conn = DBConn.getConn();
		ResultSet rs = null;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentid);
			rs = pstmt.executeQuery();
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	public static ArrayList ShowSC(String teacherid)
	{
		ArrayList a=new ArrayList();
	
		String sql="select sc.*,student.department,student.name from sc,student,course,teacher where sc.studentid=student.studentid and sc.courseid=course.courseid and course.courseid=(select course.courseid from course where course.department=teacher.department and course.teachername=teacher.teachername and teacher.teacherid=?)";
		
		Connection conn=DBConn.getConn();
		ResultSet rs=null;
		try
		{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,teacherid);
			
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				SC s=new SC();
				
				s.setDepartment(rs.getString("department"));
				s.setName(rs.getString("name"));
				
				
				
				s.setCourseid(rs.getString("courseid"));
				s.setStudentid(rs.getString("studentid"));
				s.setScore(rs.getString("score"));
				s.setScid(rs.getInt("scid"));
				a.add(s);
		
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return a;
	}
	
	//学生选择某门课
	public static boolean selectCourse(String studentid,String courseid) {
		boolean flag = false;
		Connection conn = DBConn.getConn();
		String sql = "insert into sc(studentid,courseid) values(?,?)";
		ResultSet rs = null;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentid);
			pstmt.setString(2, courseid);
			pstmt.executeUpdate();
			flag = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		//学生选上一门课后,课程容量应减1
		if(flag) {
			rs = CourseDAO.signalCourseInfo(courseid);
			try {
				if(rs.next()) {
					int number = rs.getInt("number") - 1 ;
					String sql1 = "update course set number = ? where courseid = ?";
					PreparedStatement pstmt = conn.prepareStatement(sql1);
					pstmt.setInt(1, number);
					pstmt.setString(2, courseid);
					pstmt.executeUpdate();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	//学生选课前判断是否已经选择了该课程
	public static boolean checkSelectCourse(String studentid,String courseid) {
		String sql = "select count(*) from sc where studentid = ? and courseid = ?";
		Connection conn = DBConn.getConn();
		boolean flag = false;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentid);
			pstmt.setString(2, courseid);
			ResultSet  rs = pstmt.executeQuery();
			rs.next(); 
			if(rs.getInt(1)>0) {
				flag = true;
				}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	//获得某门课程的信息
	public static ResultSet signalCourseInfo(String courseid) {
		ResultSet rs = null;
		String sql = "select * from course where courseid = ?";
		Connection conn = DBConn.getConn();
		
		try{
			PreparedStatement pstmt =conn.prepareStatement(sql);
			pstmt.setString(1, courseid);
			rs = pstmt.executeQuery();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	//学生删除某门已选课程
	public static boolean studentDeleteCourse(String studentid,String courseid) {
		boolean flag = false;
		Connection conn = DBConn.getConn();
		String sql = "delete from sc where studentid = ? and courseid = ?";
		ResultSet rs = null;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentid);
			pstmt.setString(2, courseid);
			pstmt.executeUpdate();
			flag = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
//		学生删除一门课后,课程容量应加1
		if(flag) {
			rs = CourseDAO.signalCourseInfo(courseid);
			try {
				if(rs.next()) {
					int number = rs.getInt("number") + 1 ;
					String sql1 = "update course set number = ? where courseid = ?";
					PreparedStatement pstmt = conn.prepareStatement(sql1);
					pstmt.setInt(1, number);
					pstmt.setString(2, courseid);
					pstmt.executeUpdate();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	//模糊查询课程信息
	public static ResultSet allCourseByLike(String keyword) {
		String sql = "select * from course where courseid like ? or coursename like ? or department like ? or address like ? or time like ? or teachername like ?";
		Connection conn = DBConn.getConn();
		ResultSet rs = null;
		
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+keyword+"%");
			pstmt.setString(2, "%"+keyword+"%");
			pstmt.setString(3, "%"+keyword+"%");
			pstmt.setString(4, "%"+keyword+"%");
			pstmt.setString(5, "%"+keyword+"%");
			pstmt.setString(6, "%"+keyword+"%");
			rs = pstmt.executeQuery();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return rs;
	}
	public void saveSC(String studentid,String courseid,String score)
	{

		Connection conn=DBConn.getConn();
		String sql="insert into sc(studentid,courseid,score)values(?,?,?)";
	
		try {
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,studentid);
			pstmt.setString(2,courseid);
			pstmt.setString(3,score);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}