/*
 Navicat Premium Data Transfer
 Source Server         : MYSQL
 Source Server Type    : MySQL
 Source Server Version : 80013
 Source Host           : localhost:3306
 Source Schema         : jsp_select_topic
 Target Server Type    : MySQL
 Target Server Version : 80013
 File Encoding         : 65001
 Date: 12/04/2022 09:04:43
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for msg
-- ----------------------------
DROP TABLE IF EXISTS `msg`;
CREATE TABLE `msg`  (
  `title` varchar(100) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
  `text` varchar(1000) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`title`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of msg
-- ----------------------------
INSERT INTO `msg` VALUES ('毕业设计选题系统初步完成', '整个系统流程大致为：老师发布课题，管理员审核\r\n\r\n课题，学生选题，学生完成设计');
INSERT INTO `msg` VALUES ('学生相关功能', '请各位学生登录系统，给自己选择一个课题！');
-- ----------------------------
-- Table structure for student_info
-- ----------------------------
DROP TABLE IF EXISTS `student_info`;
CREATE TABLE `student_info`  (
  `sno` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `sname` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `ssex` varchar(2) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `sclass` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `stel` varchar(11) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `semail` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `sgroup` varchar(1) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `spassword` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '6-10位字符',
  PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student_info
-- ----------------------------
INSERT INTO `student_info` VALUES ('1001', '张三', '男', '4', '15523457667', '336688@qq.com', '2', '123456');
INSERT INTO `student_info` VALUES ('1002', '李四', '男', '2', '14456789876', '6699334@qq.com', '2', '123456');
INSERT INTO `student_info` VALUES ('1003', '王五', '男', '3', '14456789876', '6699334@qq.com', '3', '123456');
INSERT INTO `student_info` VALUES ('1004', '赵六', '男', '3', '14456789876', '6699334@qq.com', '3', 'admin');
-- ----------------------------
-- Table structure for teacher_info
-- ----------------------------
DROP TABLE IF EXISTS `teacher_info`;
CREATE TABLE `teacher_info`  (
  `tno` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `tname` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `tsex` varchar(2) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `tdept` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `tdegree` varchar(8) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `ttitle` varchar(4) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `tright` bit(1) NOT NULL,
  `ttel` varchar(11) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `temail` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `tgroup` varchar(1) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `tpassword` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher_info
-- ----------------------------
INSERT INTO `teacher_info` VALUES ('101', 'admin', '男', '计算机科学与技术系', '硕士', '教授', b'0', '15315614829', '12345678@qq.com', '4', 'admin');
INSERT INTO `teacher_info` VALUES ('102', '王老师', '男', '计算机科学与技术系', '硕士', '教授', b'1', '15315614829', '1234567844@qq.com', '4', '123456');
INSERT INTO `teacher_info` VALUES ('103', '李老师', '男', '计算机科学与技术系', '硕士', '助教', b'1', '13456789876', '1234567844@qq.com', '4', 'admin');
-- ----------------------------
-- Table structure for topic_info
-- ----------------------------
DROP TABLE IF EXISTS `topic_info`;
CREATE TABLE `topic_info`  (
  `hno` varchar(5) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hname` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `htype` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hkind` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hsource` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hstatus` varchar(5) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hcontent` varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `hresult` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `direction` varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `tno` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `sno` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`hno`) USING BTREE,
  INDEX `FK_topicinfo`(`sno`) USING BTREE,
  INDEX `FK_topicinfo1`(`tno`) USING BTREE,
  CONSTRAINT `FK_topicinfo` FOREIGN KEY (`sno`) REFERENCES `student_info` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_topicinfo1` FOREIGN KEY (`tno`) REFERENCES `teacher_info` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of topic_info
-- ----------------------------
INSERT INTO `topic_info` VALUES ('10001', '计算机技术体系研究', '毕业设计', '理论研究', '结合科研', 'true', '计算机技术体系研究', 'SCI发表相应论文', '无', '102', '1001');
INSERT INTO `topic_info` VALUES ('10002', '软件工程体系研究', '毕业设计', '软件研究', '结合科研', 'true', '软件工程体系研究', 'SCI发表论文', '无', '102', '1002');
SET FOREIGN_KEY_CHECKS = 1;

package com.sjsq.service;
import com.sjsq.model.Student;
import com.sjsq.util.DB;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentManager {
    /**
     * 添加新的单个Student信息到数据库中
     * @param stu 存有学生信息的Student对象
     * @return 保存成功返回true否则返回false
     */
    public static boolean save(Student stu) {
        Connection conn = DB.getConn();
        String sql = null;
        boolean b = false;
        sql = "insert into student_info values (?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = DB.prepare(conn, sql);//预编译插入语句
        try {
            /*
             * 将stu中保存的信息分别放到数据库中对应表的对应列上
             */
            pstmt.setString(1, stu.getNumber());
            pstmt.setString(2, stu.getName());
            pstmt.setString(3, stu.getSex());
            pstmt.setString(4, stu.getSclass());
            pstmt.setString(5, stu.getPhone());
            pstmt.setString(6, stu.getEmail());
            pstmt.setString(7, stu.getGroup());
            pstmt.setString(8, stu.getPassword());
            pstmt.execute();
            b = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(pstmt);
            DB.close(conn);
        }
        return b;
    }
    /**
     * 获取所以学生信息
     * @param students 存放所以学生信息
     * @param pageNo 分页时的，当前页号
     * @param pageSize  每页显示学生的个数
     * @return 返回一共有多少页
     */
    public static int getStudents(List<Student> students, int pageNo,
                                  int pageSize) {
        int totalRecords = -1;
        Connection conn = DB.getConn();
        String sql = null;
        sql = "select * from student_info limit " + (pageNo - 1) * pageSize
                + "," + pageSize;
        Statement stmt = DB.getStatement(conn);
        ResultSet rs = DB.getResultSet(stmt, sql);
        Statement stmtCount = DB.getStatement(conn);
        ResultSet rsCount = null;
        rsCount = DB.getResultSet(stmtCount,
                "select count(*) from student_info");
        try {
            rsCount.next();
            totalRecords = rsCount.getInt(1);//得到结果集中一共有多少条记录
            while (rs.next()) {
                Student student = new Student();
                student.setEmail(rs.getString("semail"));
                student.setGroup(rs.getString("sgroup"));
                student.setName(rs.getString("sname"));
                student.setNumber(rs.getString("sno"));
                student.setPassword(rs.getString("spassword"));
                student.setPhone(rs.getString("stel"));
                student.setSclass(rs.getString("sclass"));
                student.setSex(rs.getString("ssex"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(rsCount);
            DB.close(stmtCount);
            DB.close(rs);
            DB.close(stmt);
            DB.close(conn);
        }
        return totalRecords;
    }
    /**
     * 按学号删除学生
     * @param id 要删除学生的学号
     * @return 删除成功返回true否则返回false
     */
    public static boolean deleteBySno(String id) {
        boolean b = false;
        Connection conn = DB.getConn();
        String sql = null;
        sql = "delete from student_info where sno = '" + id + "'";
        Statement stmt = DB.getStatement(conn);
        try {
            DB.executeUpdate(stmt, sql);
            b = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DB.close(stmt);
            DB.close(conn);
        }
        return b;
    }
    /**
     * 检查登录时学号和密码是否真确
     * @param num 学号
     * @param password 密码
     * @return 正确返回true否则返回false
     * @throws UserNotFoundException 抛出学号不存在的异常
     * @throws PasswordNotCorrectException 抛出密码错误的异常
     */
    public static Student check(String num, String password)
            throws UserNotFoundException, PasswordNotCorrectException {
        Student student = null;
        Connection conn = DB.getConn();
        String sql = null;
        sql = "select * from student_info where sno = '" + num + "'";
        Statement stmt = DB.getStatement(conn);
        ResultSet rs = DB.getResultSet(stmt, sql);
        try {
            if (!rs.next()) {
                throw new UserNotFoundException("用户不存在:" + num);
            } else {
                if (!password.equals(rs.getString("spassword"))) {
                    throw new PasswordNotCorrectException("密码不正确!");
                }
            }
            student = new Student();
            student.setEmail(rs.getString("semail"));
            student.setGroup(rs.getString("sgroup"));
            student.setName(rs.getString("sname"));
            student.setNumber(rs.getString("sno"));
            student.setPassword(rs.getString("spassword"));
            student.setPhone(rs.getString("stel"));
            student.setSclass(rs.getString("sclass"));
            student.setSex(rs.getString("ssex"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(rs);
            DB.close(stmt);
            DB.close(conn);
        }
        return student;
    }
    /**
     * 按学号获得学生信息
     * @param num 学号
     * @return 返回该学号的学生对象
     */
    public static Student getBySno(String num) {
        Connection conn = DB.getConn();
        String sql = null;
        sql = "select * from student_info where sno='" + num + "'";
        Statement stmt = DB.getStatement(conn);
        ResultSet rs = DB.getResultSet(stmt, sql);
        Student student = null;
        try {
            if (rs.next()) {
                student = new Student();
                student.setEmail(rs.getString("semail"));
                student.setGroup(rs.getString("sgroup"));
                student.setName(rs.getString("sname"));
                student.setNumber(rs.getString("sno"));
                student.setPassword(rs.getString("spassword"));
                student.setPhone(rs.getString("stel"));
                student.setSclass(rs.getString("sclass"));
                student.setSex(rs.getString("ssex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(rs);
            DB.close(conn);
        }
        return student;
    }
    /**
     * 更新学生stu的相关信息（学号不能更新）
     * @param stu 存有新信息的学生对象
     * @return 更新成功返回true否则返回false
     */
    public static boolean update(Student stu) {
        Connection conn = DB.getConn();
        String sql = null;
        boolean b = false;
        sql = "update student_info set spassword=?, sgroup=?, sname=?, ssex=?, stel=?, semail=?, sclass=?  where sno = ?";
        PreparedStatement pstmt = DB.prepare(conn, sql);
        try {
            pstmt.setString(1, stu.getPassword());
            pstmt.setString(2, stu.getGroup());
            pstmt.setString(3, stu.getName());
            pstmt.setString(4, stu.getSex());
            pstmt.setString(5, stu.getPhone());
            pstmt.setString(6, stu.getEmail());
            pstmt.setString(7, stu.getSclass());
            pstmt.setString(8, stu.getNumber());
            pstmt.executeUpdate();
            b = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(pstmt);
            DB.close(conn);
        }
        return b;
    }
    /**
     * 按指定的条件获得所有符合条件的学生信息
     * @param students 存放符合条件的学生信息的List
     * @param pageNo 分页显示时，当前的页号
     * @param pageSize 每页显示学生信息的数量
     * @param sclass 学生所在的班级
     * @param num 学号
     * @param name 学生姓名
     * @return 返回符合条件的学生列表
     */
    public static int query(List<Student> students, int pageNo, int pageSize,
                            String sclass, String num, String name) {
        int totalRecords = -1;
        Connection conn = DB.getConn();
        String sql = null;
        /*
         * 如何有些条件为空的进行如下处理，否则模糊查询时可能会报空指针异常
         */
        if (sclass == null)
            sclass = "";
        if (name == null)
            name = "";
        if (num == null)
            num = "";
        /*
         * 模糊查寻
         */
        sql = "select * from student_info where sclass like '%" + sclass
                + "%' and sname like '%" + name + "%' and sno like '%" + num
                + "%'";
        sql += "limit " + (pageNo - 1) * pageSize + "," + pageSize;
        Statement stmt = DB.getStatement(conn);
        ResultSet rs = DB.getResultSet(stmt, sql);
        Statement stmtCount = DB.getStatement(conn);
        ResultSet rsCount = null;
        rsCount = DB.getResultSet(stmtCount,
                "select count(*) from student_info where sclass like '%"
                        + sclass + "%' and sname like '%" + name
                        + "%' and sno like '%" + num + "%'");
        try {
            rsCount.next();
            totalRecords = rsCount.getInt(1);
            while (rs.next()) {
                Student student = new Student();
                student.setEmail(rs.getString("semail"));
                student.setGroup(rs.getString("sgroup"));
                student.setName(rs.getString("sname"));
                student.setNumber(rs.getString("sno"));
                student.setPassword(rs.getString("spassword"));
                student.setPhone(rs.getString("stel"));
                student.setSclass(rs.getString("sclass"));
                student.setSex(rs.getString("ssex"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(rsCount);
            DB.close(stmtCount);
            DB.close(rs);
            DB.close(stmt);
            DB.close(conn);
        }
        return totalRecords;
    }
    /**
     * 批量导入excel表中学生的信息到数据库中（用到组件jxl.jar)
     * @param file excel表所在的相对路径或绝对路径（包括文件完整的名字）
     * @return 返回那些学号在数据库中已经存在的学号
     */
    public static List<String> adds(String file) {
        Connection conn = DB.getConn();
        List<String> snumber = new ArrayList<String>();
        Workbook info;//定义工作簿
        String sql = "insert into student_info values (?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            info = Workbook.getWorkbook(new FileInputStream(file));
            Sheet   sheet   =   info.getSheet(0);//获得工作薄中表单对象
            int   size   =   sheet.getRows(); //获得表单的行数
            for(int   i   =   1;i   <   size;i++){
                Cell c = sheet.getCell(0,i);//获得表单的i行0列的单元格即学号
                /*
                 * 查看学号是否已经存在
                 */
                if(StudentManager.getBySno(c.getContents().trim())!=null) {
                    snumber.add(c.getContents().trim());
                    continue;
                }
                PreparedStatement pstmt = DB.prepare(conn, sql);
                for(int j=0; j<8; j++) {
                    c   =   sheet.getCell(j,i);
                    pstmt.setString(j+1, c.getContents().trim());
                }
                pstmt.execute();
            }
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IndexOutOfBoundsException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return snumber;
    }
}