jsp增删改查实验

JSP（Java Server Pages）是一种基于Java技术的服务器端编程技术，用于生成动态网页内容。增删改查（CRUD）是数据库操作中最基本的四种操作，分别是创建（Create）、读取（Retrieve）、更新（Update）和删除（Delete）。下面我将详细介绍如何在JSP中进行增删改查实验，并提供一些示例代码。

基础概念

JSP：Java Server Pages，是一种用于创建动态网页的技术，允许在HTML或XML文档中嵌入Java代码。
CRUD操作：
- Create（创建）：向数据库中插入新记录。
- Retrieve（读取）：从数据库中查询记录。
- Update（更新）：修改数据库中的现有记录。
- Delete（删除）：从数据库中删除记录。

类型与应用场景

类型：
- 简单JSP页面：用于显示静态内容和简单的动态内容。
- JSP+Servlet：通过Servlet处理业务逻辑，JSP负责页面展示。
- MVC架构：使用Model-View-Controller模式，将业务逻辑、数据和展示层分离。
应用场景：
- Web应用：如电子商务网站、论坛、博客等。
- 企业应用：如CRM系统、ERP系统等。

示例代码

假设我们有一个简单的学生管理系统，包含学生的增删改查功能。

数据库表结构

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

1. 创建（Create）

addStudent.jsp

<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Add Student</title>
</head>
<body>
    <h2>Add Student</h2>
    <form action="addStudentProcess.jsp" method="post">
        Name: <input type="text" name="name"><br>
        Age: <input type="text" name="age"><br>
        Grade: <input type="text" name="grade"><br>
        <input type="submit" value="Add Student">
    </form>
</body>
</html>

addStudentProcess.jsp

<%@ page import="java.sql.*" %>
<%
    String name = request.getParameter("name");
    int age = Integer.parseInt(request.getParameter("age"));
    String grade = request.getParameter("grade");

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
        String sql = "INSERT INTO students (name, age, grade) VALUES (?, ?, ?)";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setInt(2, age);
        pstmt.setString(3, grade);
        pstmt.executeUpdate();
        response.sendRedirect("students.jsp");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
    }
%>

2. 读取（Retrieve）

students.jsp

<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Students List</title>
</head>
<body>
    <h2>Students List</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Age</th>
            <th>Grade</th>
            <th>Actions</th>
        </tr>
        <%
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
                stmt = conn.createStatement();
                rs = stmt.executeQuery("SELECT * FROM students");
                while (rs.next()) {
        %>
        <tr>
            <td><%= rs.getInt("id") %></td>
            <td><%= rs.getString("name") %></td>
            <td><%= rs.getInt("age") %></td>
            <td><%= rs.getString("grade") %></td>
            <td>
                <a href="editStudent.jsp?id=<%= rs.getInt("id") %>">Edit</a>
                <a href="deleteStudent.jsp?id=<%= rs.getInt("id") %>">Delete</a>
            </td>
        </tr>
        <%
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            }
        %>
    </table>
    <a href="addStudent.jsp">Add New Student</a>
</body>
</html>

3. 更新（Update）

editStudent.jsp

<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Edit Student</title>
</head>
<body>
    <h2>Edit Student</h2>
    <%
        int id = Integer.parseInt(request.getParameter("id"));
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
            String sql = "SELECT * FROM students WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
    %>
                <form action="editStudentProcess.jsp" method="post">
                    <input type="hidden" name="id" value="<%= rs.getInt("id") %>">
                    Name: <input type="text" name="name" value="<%= rs.getString("name") %>"><br>
                    Age: <input type="text" name="age" value="<%= rs.getInt("age") %>"><br>
                    Grade: <input type="text" name="grade" value="<%= rs.getString("grade") %>"><br>
                    <input type="submit" value="Update Student">
                </form>
    <%
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    %>
</body>
</html>

editStudentProcess.jsp

<%@ page import="java.sql.*" %>
<%
    int id = Integer.parseInt(request.getParameter("id"));
    String name = request.getParameter("name");
    int age = Integer.parseInt(request.getParameter("age"));
    String grade = request.getParameter("grade");

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
        String sql = "UPDATE students SET name = ?, age = ?, grade = ? WHERE id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setInt(2, age);
        pstmt.setString(3, grade);
        pstmt.setInt(4, id);
        pstmt.executeUpdate();
        response.sendRedirect("students.jsp");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
    }
%>

4. 删除（Delete）

deleteStudent.jsp

<%@ page import="java.sql.*" %>
<%
    int id = Integer.parseInt(request.getParameter("id"));

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
        String sql = "DELETE FROM students WHERE id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        pstmt.executeUpdate();
        response.sendRedirect("students.jsp");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
    }
%>