【学生课程实验】数据库及其应用课程设计实验-学生实验报告上传系统

package com.poseiDon.reportupload; import javax.persistence.criteria.CriteriaBuilder; import java.nio.charset.StandardCharsets; import java.sql.*; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; /** * 处理业务逻辑与数据库的请求交互 */ public class sqlQuery { static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/srms?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; static final String USER = "root"; static final String PASSWD = ""; private Connection conn; public void updateStudent(String name, String email, String password, String username) throws Exception { String SQLQuery = "UPDATE student SET student_name=?,student_email=?,student_password=?,student_initialized=1 WHERE student_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, name); preparedStatement.setString(2, email); preparedStatement.setString(3, password); preparedStatement.setString(4, username); preparedStatement.executeUpdate(); } public boolean getSubmitStatus(String username, String projectName, String projectCourse) throws Exception { String SQLQuery = "SELECT * FROM student_project WHERE student_id=? AND EXISTS(SELECT * FROM project WHERE project.project_name=? AND project.project_course=? AND project.project_id=student_project.project_id)"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, username); preparedStatement.setString(2, projectName); preparedStatement.setString(3, projectCourse); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { return resultSet.getBoolean("submit"); } return false; } public void updateTeacherManageID(String teacher_id, int class_id) throws Exception { String SQLQuery = "UPDATE teacher SET teacher_manage_id=? WHERE teacher_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(2, teacher_id); String teacher_manage_id = getTeacherManageID(teacher_id); if (teacher_manage_id.equals("")) { teacher_manage_id = String.valueOf(class_id); } else teacher_manage_id = teacher_manage_id + ";" + class_id; preparedStatement.setString(1, teacher_manage_id); preparedStatement.executeUpdate(); } public void updateStudentProject(String student_id, String project_id) throws Exception { String SQLQuery = "INSERT INTO student_project VALUES(?,?,0)"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, student_id); preparedStatement.setString(2, project_id); preparedStatement.executeUpdate(); } public String getStudentID(String class_id) throws Exception { String SQLQuery = "SELECT * FROM class WHERE class_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, class_id); ResultSet resultSet = preparedStatement.executeQuery(); resultSet.next(); return resultSet.getString("class_student_id"); } public void updateTeacherManageProjectID(String teacher_id) throws Exception { String SQLQuery = "UPDATE teacher SET teacher_project_id=? WHERE teacher_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); String currentTeacherManageProjectID = getTeacherManageProjectID(teacher_id); if (currentTeacherManageProjectID.equals("")) { currentTeacherManageProjectID = String.valueOf(getProjectID()); } else { currentTeacherManageProjectID = currentTeacherManageProjectID + ";" + getProjectID(); } // System.out.println(currentTeacherManageProjectID); preparedStatement.setString(1, currentTeacherManageProjectID); preparedStatement.setString(2, teacher_id); preparedStatement.executeUpdate(); } public String getTeacherManageProjectID(String teacher_id) throws Exception { String SQLQuery = "SELECT * FROM teacher WHERE teacher_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, teacher_id); ResultSet resultSet = preparedStatement.executeQuery(); resultSet.next(); return resultSet.getString("teacher_project_id"); } public String getTeacherManageProjectIDByName(String teacher_name) throws Exception { String SQLQuery = "SELECT * FROM teacher WHERE teacher_name=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, teacher_name); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { return resultSet.getString("teacher_project_id"); } return null; } public int getProjectID() throws Exception { String SQLQuery = "SELECT * FROM project"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); ResultSet resultSet = preparedStatement.executeQuery(); while (!resultSet.next()) { // System.out.println("resultSet.isFirst"); return 0; } resultSet.last(); return resultSet.getInt("project_id"); } public String getProjectID(String project_name) throws Exception { String SQLQuery = "SELECT * FROM project WHERE project_name=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, project_name); ResultSet resultSet = preparedStatement.executeQuery(); resultSet.last(); return resultSet.getString("project_id"); } public void constructNewProject(String Path, String project_course, String project_name, String project_teacher, String project_deadline, String class_id) throws Exception { String SQLQuery = "INSERT INTO project VALUES(?,?,?,?,?,?,?)"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); int project_id = getProjectID(); project_id = project_id + 1; // 新建项目ID String project_url = Path; preparedStatement.setInt(1, project_id); preparedStatement.setString(2, project_name); preparedStatement.setString(3, project_course); preparedStatement.setString(4, project_teacher); preparedStatement.setString(5, project_url); preparedStatement.setString(6, project_deadline); preparedStatement.setString(7, class_id); preparedStatement.executeUpdate(); } public String getTeacherName(String teacher_id) throws Exception { String SQLQuery = "SELECT * FROM teacher WHERE teacher_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setString(1, teacher_id); ResultSet resultSet = preparedStatement.executeQuery(); resultSet.next(); return resultSet.getString("teacher_name"); } public String getTeacherNameFromProject(int project_id) throws Exception { String SQLQuery = "SELECT * FROM project WHERE project_id=?"; PreparedStatement preparedStatement = this.conn.prepareStatement(SQLQuery); preparedStatement.setInt(1, project_id); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { return resultSet.getString("project_teacher"); } return null; } public String getTeacherManageClass(String teacher_id) throws Exception { String SQLQuery = "SELE