学生选课系统v1.0

package dao;

import java.sql.connection;
import java.sql.preparedstatement;
import java.sql.resultset;
import java.sql.sqlexception;
import java.sql.statement;
import java.util.arraylist;
import java.util.list;

import javax.servlet.http.httpservletrequest;

import util.dbutil;
import bean.de;
import bean.lesson;
import bean.student;
import bean.teacher;

public class function {

    // 验证用户是否存在
    public static boolean search(string stunumber, string code, string id, httpservletrequest request) {
        int temp = 0;
        connection conn = dbutil.getconn();
        string sql = null;
        statement sta = null;
        if (id.contentequals("老师")) {
            sql = "select * from teacher";
        } else {
            sql = "select * from student";
        }

        try {
            sta = conn.createstatement();
            resultset res = sta.executequery(sql);
            while (res.next()) {
                string pn = res.getstring("num");
                string name = res.getstring("name");
                request.setattribute("na", name);
                if (stunumber.equals(pn) && code.equals("123")) {
                    temp = 1;
                    break;
                }
            }
            res.close();
        } catch (exception e) {
            e.printstacktrace();
        } finally {
            dbutil.close(sta, conn);
        }
        if (temp == 0)
            return false;
        else
            return true;
    }

    // 增加老师数据
    public static void addt(string stunumber, string name, string sex, string college, string department)
            throws sqlexception {
        string sql = "insert into teacher(num,name,sex,college,job) values('" + stunumber + "','" + name + "','" + sex
                + "','" + college + "','" + department + "')";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    // 增加学生数据
    public static void adds(string stunumber, string name, string sex, string college, string department)
            throws sqlexception {
        string sql = "insert into student(num,name,sex,class,major) values('" + stunumber + "','" + name + "','" + sex
                + "','" + college + "','" + department + "')";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    // 增加课程信息
    public static void addc(string stunumber, string name, string sex, string college) throws sqlexception {
        string sql = "insert into kecheng(number,name,people,teacher) values('" + stunumber + "','" + name + "','" + sex
                + "','" + college + "')";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    // 修改老师信息
    public static void updatat(string name, string sex, string college, string de) throws sqlexception {

        string sql = "update teacher set sex='" + sex + "',college='" + college + "',job='" + de + "' where name='"
                + name + "'";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    // 修改学生数据
    public static void updatas(string name, string sex, string college, string de) throws sqlexception {

        string sql = "update student set sex='" + sex + "',class='" + college + "',major='" + de + "' where name='"
                + name + "'";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    // 返回全部课程信息
    public static list<de> getde_1() throws sqlexception {
        list<de> list = new arraylist<de>();
        string sql = "select * from kecheng ";
        connection conn = dbutil.getconn();
        try {
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            while (rs.next()) {
                de vol = new de();
                vol.setnumber(rs.getstring("number"));
                vol.setname(rs.getstring("name"));
                vol.setpeople(rs.getint("people"));
                vol.setteacher(rs.getstring("teacher"));
                list.add(vol);
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    // 返回可选课程信息
    public static list<de> getde() throws sqlexception {
        list<de> list = new arraylist<de>();
        string sql = "select * from kecheng where status='0' ";
        connection conn = dbutil.getconn();
        try {
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            while (rs.next()) {
                de vol = new de();
                vol.setnumber(rs.getstring("number"));
                vol.setname(rs.getstring("name"));
                vol.setpeople(rs.getint("people"));
                vol.setteacher(rs.getstring("teacher"));
                list.add(vol);
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    // 返回老师详细信息
    public static teacher getteacheer(string name) throws sqlexception {
        teacher te = new teacher();
        string sql = "select * from teacher where name='" + name + "' ";
        connection conn = dbutil.getconn();
        try {
            statement st = conn.createstatement();
            resultset rs = st.executequery(sql);
            if (rs.next()) {
                te.setnum(rs.getstring("num"));
                te.setname(rs.getstring("name"));
                te.setsex(rs.getstring("sex"));
                te.setcollege(rs.getstring("college"));
                te.setjob(rs.getstring("job"));
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return te;
    }

    // 学生选课信息
    public static void stu_lesson(string s_name, string le_number) throws sqlexception {
        string lesson_number = le_number;
        string lesson_name = null;
        string teacher_name = null;
        string teacher_number = null;
        string stu_name = s_name;
        string stu_num = null;
        connection conn = null;
        int a = 0;
        int count = 0;
        boolean temp = true;
        try {
            string sql = "select * from chooseke  where lesson_num='" + le_number + "' and stu_name='" + s_name + "' ";
            conn = dbutil.getconn();
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            if (rs.next()) {
                temp = false;
            }
            if (temp == true) {
                sql = "select * from kecheng where number='" + le_number + "' ";
                rs = st.executequery(sql);
                if (rs.next()) {
                    lesson_name = rs.getstring("name");
                    teacher_name = rs.getstring("teacher");
                }

                sql = "select * from teacher where name='" + teacher_name + "' ";
                rs = st.executequery(sql);
                // 遍历结果集对象
                if (rs.next()) {
                    teacher_number = rs.getstring("num");
                }

                sql = "select * from student where name='" + stu_name + "' ";
                rs = st.executequery(sql);
                // 遍历结果集对象
                if (rs.next()) {
                    stu_num = rs.getstring("num");
                }

                sql = "select * from kecheng where name='" + lesson_name + "' ";
                rs = st.executequery(sql);
                if (rs.next()) {
                    a = rs.getint("people");
                }

                sql = "select * from chooseke where lesson_name='" + lesson_name + "' ";
                rs = st.executequery(sql);
                while (rs.next()) {
                    count++;
                    system.out.println(count);
                }

                if (count < a) {
                    sql = "insert into chooseke(lesson_num,lesson_name,teacher_num,teacher_name,stu_number,stu_name) values('"
                            + lesson_number + "','" + lesson_name + "','" + teacher_number + "','" + teacher_name
                            + "','" + stu_num + "','" + stu_name + "')";
                    conn.setautocommit(false);
                    preparedstatement preparedstatement = conn.preparestatement(sql);
                    preparedstatement.executeupdate();
                    conn.commit();
                    if ((a - count) == 1) {
                        sql = "update kecheng set status='-1' where name='" + lesson_name + "' ";
                        conn.setautocommit(false);
                        preparedstatement = conn.preparestatement(sql);
                        preparedstatement.executeupdate();
                        conn.commit();
                    }
                } else {
                    system.out.println("人数已达上限，不可选");
                }
            }
            else {
                system.out.println("与已选课冲突");
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    // 返回课程详细信息
    public static void getlesson(string le_name, httpservletrequest req) throws sqlexception {

        string lesson_name = le_name;
        string lesson_number = null;
        string teacher_name = null;
        string teacher_number = null;
        connection conn = null;
        lesson les = new lesson();
        int a = 0;
        int count = 0;
        try {

            string sql = "select * from kecheng where name='" + le_name + "' ";
            conn = dbutil.getconn();
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            if (rs.next()) {
                lesson_number = rs.getstring("number");
                teacher_name = rs.getstring("teacher");
                a = rs.getint("people");
            }

            sql = "select * from teacher where name='" + teacher_name + "' ";
            rs = st.executequery(sql);
            if (rs.next()) {
                teacher_number = rs.getstring("num");
            }

            sql = "select * from chooseke where lesson_num='" + lesson_number + "' ";
            rs = st.executequery(sql);
            while (rs.next()) {
                count++;
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        les.setlesson_number(lesson_number);
        les.setlesson_name(lesson_name);
        les.setteacher_number(teacher_number);
        les.setteacher_name(teacher_name);
        les.setpe_num(count);
        les.setmessage(a);
        req.setattribute("les", les);
    }

    // 返回老师对应的课程
    public static list<de> getteacher_lesson(string name) throws sqlexception {
        list<de> list = new arraylist<de>();
        string sql = "select * from kecheng where teacher='" + name + "' ";
        connection conn = dbutil.getconn();
        try {
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            while (rs.next()) {
                de vol = new de();
                vol.setnumber(rs.getstring("number"));
                vol.setname(rs.getstring("name"));
                list.add(vol);
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    // 返回选择该课程学生的信息
    public static list<student> getstu(string number) throws sqlexception {
        list<student> list = new arraylist<student>();
        string sql = "select * from chooseke where lesson_num= '" + number + "'";
        connection conn = dbutil.getconn();
        try {
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            while (rs.next()) {
                student sd = new student();
                sd.setname(rs.getstring("stu_name"));
                sd.setnum(rs.getstring("stu_number"));
                list.add(sd);
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    // 返回单个学生的详细信息
    public static student get_stu(string number) throws sqlexception {
        student sd = new student();
        string sql = "select * from student where num= '" + number + "'";
        connection conn = dbutil.getconn();
        try {
            // 连接对象coon调用createstatement()方法，创建一个执行sql语句的对象st
            statement st = conn.createstatement();
            // 执行sql语句中的对象st调用executequery()方法，执行查询语句，将查询到的结果返回到一个结果集中
            resultset rs = st.executequery(sql);
            // 遍历结果集对象
            if (rs.next()) {
                sd.setnum(rs.getstring("num"));
                sd.setname(rs.getstring("name"));
                sd.setsex(rs.getstring("sex"));
                sd.setcla(rs.getstring("class"));
                sd.setmajor(rs.getstring("major"));
            }
        } catch (exception e) {
            system.out.println(e.getmessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return sd;
    }

    /**
     * 删除数据
     */
    public static void delete(string stunumber) throws sqlexception {
        string sql = "delete from stumanage where stumanage.stunumber = '" + stunumber + "'";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        preparedstatement.executeupdate();
        conn.commit();
        conn.close();
    }

    /**
     * 查找数据
     */
    public static void query() throws sqlexception {
        string sql = "select * from stumanage";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        // 执行查询语句并返回结果集
        resultset resultset = preparedstatement.executequery();
        while (resultset.next()) {
            // 注意：这里要与数据库里的字段对应
            string stunumber = resultset.getstring("stunumber");
            string code = resultset.getstring("code");
            system.out.println(stunumber + " " + code);
        }
        conn.commit();
        conn.close();
    }

    /**
     * 查找特定数据
     */
    public static void query(string stu) throws sqlexception {
        string sql = "select * from stumanage";
        connection conn = dbutil.getconn();
        conn.setautocommit(false);
        preparedstatement preparedstatement = conn.preparestatement(sql);
        // 执行查询语句并返回结果集
        resultset resultset = preparedstatement.executequery();
        while (resultset.next()) {
            // 注意：这里要与数据库里的字段对应
            string stunumber = resultset.getstring("stunumber");
            if (stu.contentequals(stunumber)) {

            }
        }
        conn.commit();
        conn.close();
    }
}