一零五六、Jsp+mysql 实现学生选课系统（附源码及数据库）
目录
实现效果
项目代码
数据库
结语
实现效果
login.jsp
index.jsp
course_query.jsp
course_selection.jsp
course_withdraw.jsp
selection_query.jsp
项目代码
checkSelectionStatus.jsp
%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%>
<html>
<head><title>Title</title>
</head>
<body>
<%@ page import="java.sql.*" %>
<%// 查询数据库中是否已选课Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?useSSL=false&serverTimezone=UTC", "root", "123456");pstmt = conn.prepareStatement("SELECT status FROM selection WHERE student_id = "+session.getAttribute("student_id"));rs = pstmt.executeQuery();if (rs.next()) {// 如果已选课，返回1out.print("1");} else {// 如果未选课，返回0out.print("0");}} catch (Exception e) {e.printStackTrace();} finally {try {if (rs != null) {rs.close();}if (pstmt != null) {pstmt.close();}if (conn != null) {conn.close();}} catch (Exception e) {e.printStackTrace();}}
%></body>
</html> course_query.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<html>
<head><title>课程查询</title>
</head>
<body>
<h1>课程查询</h1>
<table><tr><th>课程名称</th><th>授课教师</th><th>学分</th></tr><%// 查询课程信息并展示在页面上Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");stmt = conn.createStatement();rs = stmt.executeQuery("SELECT name, teacher, credit FROM course");while (rs.next()) {out.println("<tr>");out.println("<td>" + rs.getString("name") + "</td>");out.println("<td>" + rs.getString("teacher") + "</td>");out.println("<td>" + rs.getInt("credit") + "</td>");out.println("</tr>");}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {try {rs.close();} catch (Exception e) {}}if (stmt != null) {try {stmt.close();} catch (Exception e) {}}if (conn != null) {try {conn.close();} catch (Exception e) {}}}%>
</table>
<a href="index.jsp">返回主界面</a>
</body>
</html>
course_selection.jsp
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>选课操作</title>
</head>
<body>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>// 获取前端传递的student_idvar studentId = <%= session.getAttribute("student_id") %>;// 发送ajax请求，查询数据库中是否已选课$.ajax({url: "checkSelectionStatus.jsp",type: "POST",data: { student_id: studentId },success: function(result) {if (result == "1") {// 如果已选课，显示提示信息var message = "您已选课，不可重复选课";var messageDiv = document.createElement("div");messageDiv.style.color = "red";messageDiv.innerHTML = message;document.body.appendChild(messageDiv);// 禁用输入框document.getElementsByName("course_id")[0].disabled = true;}}});
</script>
<h1>选课操作</h1>
<form action="course_selection_check.jsp" method="post">课程ID：<select name="course_id"><%Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");stmt = conn.createStatement();String sql = "SELECT id,name FROM course";rs = stmt.executeQuery(sql);out.println("<option value='-请选择-'>-请选择-</option>");while (rs.next()) {int id = rs.getInt("id");String name = rs.getString("name");out.print("<option value='"+ id +"'>" + id +":"+ name + "</option>");}} catch (Exception e) {e.printStackTrace();} finally {try {if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn.close();} catch (Exception e) {e.printStackTrace();}}%></select><br><input type="submit" value="选课">
</form>
<a href="index.jsp">返回主界面</a>
</body>
</html>course_selection_check.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*" %>
<html>
<head><title>Title</title>
</head>
<body><%// 获取前端传递的course_idString courseId = request.getParameter("course_id");// 将选课信息插入数据库中Connection conn = null;PreparedStatement pstmt = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?useSSL=false&serverTimezone=UTC", "root", "123456");pstmt = conn.prepareStatement("update selection set course_id = ?, status=1 where student_id =" + session.getAttribute("student_id"));pstmt.setInt(1, Integer.parseInt(courseId));pstmt.executeUpdate();out.print("选课成功");} catch (Exception e) {e.printStackTrace();out.print("重复选课,选课失败");} finally {try {if (pstmt != null) {pstmt.close();}if (conn != null) {conn.close();}} catch (Exception e) {e.printStackTrace();}}
%>
<a href="index.jsp">返回主界面</a>
</body>
</html>
course_withdraw.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<html>
<head><title>退课操作</title>
</head>
<body>
<h1>退课操作</h1>
<form action="course_withdraw_check.jsp" method="post">课程ID：<select name="course_id"><%Connection conn = null;Statement stmt = null;ResultSet rs = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");stmt = conn.createStatement();String sql = "SELECT distinct(course_id) as dis_cou FROM selection where student_id="+session.getAttribute("student_id");rs = stmt.executeQuery(sql);out.println("<option value='-请选择-'>-请选择-</option>");while (rs.next()) {int id = rs.getInt("dis_cou");out.print("<option value='"+ id +"'>" + id + "</option>");}} catch (Exception e) {e.printStackTrace();} finally {try {if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn.close();} catch (Exception e) {e.printStackTrace();}}%></select><br><input type="submit" value="选课">
</form>
<a href="index.jsp">返回主界面</a>
</body>
</html>course_withdraw_check.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<html>
<head><title>Title</title>
</head>
<body>
<%// 将选课信息更新数据库中Connection conn = null;PreparedStatement pstmt = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?useSSL=false&serverTimezone=UTC", "root", "123456");pstmt = conn.prepareStatement("update selection set course_id = null, status=0 where student_id =" + session.getAttribute("student_id"));pstmt.executeUpdate();out.print("退课成功");} catch (Exception e) {e.printStackTrace();out.print("退课失败");} finally {try {if (pstmt != null) {pstmt.close();}if (conn != null) {conn.close();}} catch (Exception e) {e.printStackTrace();}}
%>
<a href="index.jsp">返回主界面</a>
</body>
</html>
index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%>
<html>
<head><title>学生选课系统</title>
</head>
<body>
<h1>学生选课系统</h1><%Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;String name = "";Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");pstmt = conn.prepareStatement("SELECT name FROM student WHERE id = "+ session.getAttribute("student_id"));rs = pstmt.executeQuery();if (rs.next()) {name = rs.getString(1);}
%><p>欢迎:<font color="red"><%=name%></font>
</p>
<ul><li><a href="course_query.jsp"><i class="fa fa-home fa-fw"></i>课程查询</a></li><li><a href="course_selection.jsp"><i class="fa fa-bar-chart fa-fw"></i>选课操作</a></li><li><a href="course_withdraw.jsp"><i class="fa fa-bar-chart fa-fw"></i>退课操作</a></li><li><a href="selection_query.jsp"><i class="fa fa-bar-chart fa-fw"></i>选课结果查询</a></li><li><a href="login.jsp"><i class="fa fa-sliders fa-fw"></i>登出</a></li>
</ul></body>
</html>
login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head><title>学生选课系统</title>
</head>
<body>
<h1>学生登录</h1>
<form action="login_check.jsp" method="post">学号：<input type="text" name="student_id"><br>密码：<input type="password" name="password"><br><input type="submit" value="登录">
</form>
</body>
</html>login_check.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%><html>
<head><title>Title</title>
</head>
<body>
<%// 获取学生输入的学号和密码String student_id = request.getParameter("student_id");String password = request.getParameter("password");
// 查询数据库中是否存在该学生Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");pstmt = conn.prepareStatement("SELECT * FROM student WHERE id = ? AND password = ?");pstmt.setInt(1, Integer.parseInt(student_id));pstmt.setString(2, password);rs = pstmt.executeQuery();if (rs.next()) {// 学生存在，将其ID存入Session，并重定向到课程查询页面session.setAttribute("student_id", rs.getInt("id"));response.sendRedirect("index.jsp");} else {// 学生不存在或密码错误，返回登录页面out.print("<script>alert('请确认账号密码正确后再进行登录！'); </script>");response.sendRedirect("login.jsp?flag=false");}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {try {rs.close();} catch (Exception e) {}}if (pstmt != null) {try {pstmt.close();} catch (Exception e) {}}if (conn != null) {try {conn.close();} catch (Exception e) {}}}
%></body>
</html>
selection_query.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<html>
<head><title>选课结果查询</title>
</head>
<body>
<h1>选课结果查询</h1>
<table><tr><th>学生学号</th><th>学生姓名</th><th>授课教师</th><th>课程名称</th><th>学分</th><th>选课状态</th></tr><%// 查询选课结果并展示在页面上Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/course_selection?characterEncoding=UTF-8", "root", "123456");pstmt = conn.prepareStatement("SELECT * FROM selection");rs = pstmt.executeQuery();while (rs.next()) {int studentId = rs.getInt("student_id");int courseId = rs.getInt("course_id");int status = rs.getInt("status");// 查询学生姓名PreparedStatement pstmt1 = conn.prepareStatement("SELECT name FROM student WHERE id = ?");pstmt1.setInt(1, studentId);ResultSet rs1 = pstmt1.executeQuery();String studentName = "";if (rs1.next()) {studentName = rs1.getString("name");}rs1.close();pstmt1.close();// 查询课程信息PreparedStatement pstmt2 = conn.prepareStatement("SELECT name, teacher, credit FROM course WHERE id = ?");pstmt2.setInt(1, courseId);ResultSet rs2 = pstmt2.executeQuery();String courseName = "";String teacherName = "";int credit = 0;if (rs2.next()) {courseName = rs2.getString("name");teacherName = rs2.getString("teacher");credit = rs2.getInt("credit");}rs2.close();pstmt2.close();out.println("<tr>");out.println("<td>" + studentId + "</td>");out.println("<td>" + studentName + "</td>");out.println("<td>" + teacherName + "</td>");out.println("<td>" + courseName + "</td>");out.println("<td>" + credit + "</td>");out.println("<td>" + (status == 0 ? "未选课" : (status == 1 ? "已选课" : "未选课")) + "</td>");out.println("</tr>");}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {try {rs.close();} catch (Exception e) {}}if (pstmt != null) {try {pstmt.close();} catch (Exception e) {}}if (conn != null) {try {conn.close();} catch (Exception e) {}}}%>
</table>
<a href="index.jsp">返回主界面</a>
</body>
</html>数据库
/*Navicat Premium Data TransferSource Server : tuomasiSource Server Type : MySQLSource Server Version : 80023Source Host : localhost:3306Source Schema : course_selectionTarget Server Type : MySQLTarget Server Version : 80023File Encoding : 65001Date: 15/04/2023 11:30:47
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (`id` int(0) NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`teacher` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`credit` int(0) NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java程序设计', '张三', 4);
INSERT INTO `course` VALUES (2, 'Web开发', '李四', 3);
INSERT INTO `course` VALUES (3, '数据库原理', '王五', 3);
INSERT INTO `course` VALUES (4, '操作系统', '赵六', 4);
INSERT INTO `course` VALUES (5, '计算机网络', '钱七', 3);-- ----------------------------
-- Table structure for selection
-- ----------------------------
DROP TABLE IF EXISTS `selection`;
CREATE TABLE `selection` (`id` int(0) NOT NULL AUTO_INCREMENT,`student_id` int(0) NOT NULL,`course_id` int(0) NULL DEFAULT NULL,`selection_time` datetime(0) NOT NULL,`status` int(0) NOT NULL DEFAULT 0,PRIMARY KEY (`id`) USING BTREE,INDEX `student_id`(`student_id`) USING BTREE,INDEX `course_id`(`course_id`) USING BTREE,CONSTRAINT `selection_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `selection_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of selection
-- ----------------------------
INSERT INTO `selection` VALUES (1, 1, 1, '2021-09-01 09:30:00', 1);
INSERT INTO `selection` VALUES (2, 2, 2, '2021-09-03 11:30:00', 1);
INSERT INTO `selection` VALUES (3, 3, 3, '2021-09-05 13:30:00', 1);
INSERT INTO `selection` VALUES (4, 4, 4, '2021-09-07 15:30:00', 1);
INSERT INTO `selection` VALUES (5, 5, 5, '2021-09-09 17:30:00', 1);
INSERT INTO `selection` VALUES (6, 6, 1, '2021-09-12 20:30:00', 1);
INSERT INTO `selection` VALUES (7, 7, 2, '2021-09-14 22:30:00', 1);
INSERT INTO `selection` VALUES (8, 8, 3, '2021-09-16 00:30:00', 1);
INSERT INTO `selection` VALUES (9, 9, 4, '2021-09-18 02:30:00', 1);
INSERT INTO `selection` VALUES (10, 10, 5, '2021-09-20 04:30:00', 1);
INSERT INTO `selection` VALUES (11, 11, NULL, '2021-09-22 08:30:00', 0);
INSERT INTO `selection` VALUES (12, 12, NULL, '2021-09-24 09:30:00', 0);
INSERT INTO `selection` VALUES (13, 13, NULL, '2021-09-26 10:30:00', 0);
INSERT INTO `selection` VALUES (14, 14, NULL, '2021-09-28 12:30:00', 0);
INSERT INTO `selection` VALUES (15, 15, NULL, '2021-09-30 08:30:00', 0);
INSERT INTO `selection` VALUES (16, 16, NULL, '2021-10-02 08:50:00', 0);
INSERT INTO `selection` VALUES (17, 17, NULL, '2021-10-04 08:53:00', 0);
INSERT INTO `selection` VALUES (18, 18, NULL, '2021-10-06 08:52:00', 0);
INSERT INTO `selection` VALUES (19, 19, NULL, '2021-10-08 08:50:00', 0);
INSERT INTO `selection` VALUES (20, 20, NULL, '2021-10-10 09:50:00', 0);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`id` int(0) NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`age` int(0) NOT NULL,`contact` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', '男', 19, '13812345678', '123456');
INSERT INTO `student` VALUES (2, '李四', '女', 20, '13987654321', '123456');
INSERT INTO `student` VALUES (3, '王五', '男', 21, '13612345678', '123456');
INSERT INTO `student` VALUES (4, '赵六', '女', 22, '13587654321', '123456');
INSERT INTO `student` VALUES (5, '钱七', '男', 23, '13912345678', '123456');
INSERT INTO `student` VALUES (6, '孙八', '女', 24, '13887654321', '123456');
INSERT INTO `student` VALUES (7, '周九', '男', 25, '13712345678', '123456');
INSERT INTO `student` VALUES (8, '吴十', '女', 26, '13687654321', '123456');
INSERT INTO `student` VALUES (9, '郑一', '男', 27, '13922345678', '123456');
INSERT INTO `student` VALUES (10, '王二', '女', 28, '13977654321', '123456');
INSERT INTO `student` VALUES (11, '李三', '男', 29, '13832345678', '123456');
INSERT INTO `student` VALUES (12, '张四', '女', 30, '13966654321', '123456');
INSERT INTO `student` VALUES (13, '孙五', '男', 31, '13812345698', '123456');
INSERT INTO `student` VALUES (14, '刘六', '女', 32, '13987651121', '123456');
INSERT INTO `student` VALUES (15, '陈七', '男', 33, '13612341178', '123456');
INSERT INTO `student` VALUES (16, '杨八', '女', 34, '13587653321', '123456');
INSERT INTO `student` VALUES (17, '黄九', '男', 35, '13912345628', '123456');
INSERT INTO `student` VALUES (18, '徐十', '女', 36, '13887653321', '123456');
INSERT INTO `student` VALUES (19, '吕一', '男', 37, '13922345628', '123456');
INSERT INTO `student` VALUES (20, '曾二', '女', 38, '13977655521', '123456');SET FOREIGN_KEY_CHECKS = 1;
结语
再者，记得把jdbc驱动包加到lib目录下，启动tomcat，学生选课系统小项目就算完成了