0
0

一零五六、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()) {
            // 如果已选课，返回1
            out.print("1");
        } else {
            // 如果未选课，返回0
            out.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_id
    var 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_id
    String 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 Transfer

 Source Server         : tuomasi
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3306
 Source Schema         : course_selection

 Target Server Type    : MySQL
 Target Server Version : 80023
 File Encoding         : 65001

 Date: 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，学生选课系统小项目就算完成了

标签： mysql 数据库
本文转载自: https://blog.csdn.net/m0_54925305/article/details/130253213
版权归原作者 托马斯-酷涛 所有， 如有侵权，请联系我们删除。
登录后发布评论

“一零五六、Jsp+mysql 实现学生选课系统（附源码及数据库）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

IDE Eval Resetter 安装和配置指南

docker compose入门7—详解build命令

推送到 Git 返回错误代码 403 致命：HTTP 请求失败

大数据毕业设计python基于Hadoop的租房数据分析系统

Flink流处理与网络安全

云服务器3——安装及配置Hadoop

Kafka+RabbitMQ+ActiveMQ看看消息队列设计精要14

文章导航