courseschemebean.java

学生信息管理系统 jsp + sql + javabean 实现

package sms.bean;

import sms.db.*;
import java.io.Serializable;
import java.sql.ResultSet;

public class CourseSchemeBean implements Serializable {
    public CourseSchemeBean() {
    }

    /**
     * 退选课程时调用的方法
     * @param studentId 学生编号，courseId 课程编号
     * @return 成功返回ture，失败false
     */
    public boolean undoChooseCourse(String studentId, String courseId) {
        String sql = "delete from selective_course_scheme where stu_id=" +
                     studentId + " and course_id=" + courseId; ;
        DBAccess dba = new DBAccess();
        dba.getConnection();
        int flag = dba.executeSql(sql);
        if (flag > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 选课时调用的方法
     * @param studentId 学生编号，courseId 课程编号
     * @return 成功返回1，异常时返回－2，已经选择该课程时返回0，失败返回－1,该时间有必修课时返回-3；
     */
    public int chooseCourse(String courseId, String studentId, String classId) {
        String sql =
                "select * from selective_course_scheme where stu_id=" +
                studentId + " and course_id=" + courseId;
        String sql2 = "select a.course_id,b.class_id from course_info a,required_course_scheme b,course_info";
        sql2 += ",(select term,week,period from course_info where course_id='" +
                courseId + "') c";
        sql2 += " where a.course_id=b.course_id and b.class_id='" + classId +
                "' and a.week=c.week";
        sql2 += " and a.period=c.period and a.term=c.term";
        String sql3 =
                "insert into selective_course_scheme (stu_id,course_id) values('" +
                studentId + "','" + courseId + "')";
        DBAccess dba = new DBAccess();
        try {
            dba.getConnection();
            ResultSet rs = null;
            rs = dba.query(sql2); //查询该时间段内是否有必修课
            if (rs != null && rs.next()) {
                return -3;
            }
            rs = null;
            rs = dba.query(sql); //查询是否已经选择了此课程
            if (rs != null && rs.next()) {
                return 0; //已经选择了该课程，直接返回
            }
            int flag = dba.executeSql(sql3); //将此课程添加到选课表中
            if (flag > 0) {
                return 1; //选课成功返回1
            } else {
                return -1; //选课失败返回－1
            }
        } catch (Exception ex) {
            return -2;
        } finally {
            dba.closeConnection();
        }
    }

    public static void main(String[] args) {
        CourseSchemeBean courseschemebean = new CourseSchemeBean();
    }
}