JDBC-练习--添加---查询--修改--任意学生信息的提交页面

太过爱你忘了你带给我的痛 2024-03-24 14:39 128阅读 0赞

**目录**

JDBC-练习--添加任意学生信息的提交页面

配置mysql驱动

建立student数据库

建表单（添加数据）

提交判断

数据库收集到的数据

显示所有学生信息的页面

代码

运行结果编辑

查询条件提交显示信息

代码：

运行结果：

修改单条记录页面

代码：

运行结果：

数据库中更新：

根据条件来修改记录（3个jsp页面完成）

代码：

修改记录提交页面

修改编辑页面

修改后重写页面

运行结果：

数据库中：

根据条件来修改记录---添加空值判断和处理（版本）

代码：

页面展示：

--------------------

## JDBC-练习--添加任意学生信息的提交页面 ##

*  ### 配置mysql驱动 ###

把下面的代码复制粘贴到项目的pom,xml文件中，然后刷新maven

<dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.32</version>
        </dependency>

*  ### 建立student数据库 ###

CREATE DATABASE students CHARACTER SET utf8;
    USE students;
    CREATE TABLE stu_info ( id INT, NAME VARCHAR ( 20 ), sex VARCHAR ( 5 ), age INT, weight FLOAT, height FLOAT );

*  ### 建表单（添加数据） ###

<%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/4
      Time: 18:09
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>添加任意学生信息的提交页面</title>
    </head>
    <body>
    <h2 align="center">
        学生身体体质信息管理系统
    </h2>
    <br>
    <form action="insert_stu_2.jsp" method="post">
        <table border="0" width="238" height="252" align="center">
            <tr>
                <td>
                    学号：
                </td>
                <td>
                    <input type="text" name="id">
                </td>
            </tr>
            <tr>
                <td>
                    姓名：
                </td>
                <td>
                    <input type="text" name="name">
                </td>
            </tr>
            <tr>
                <td>
                    性别：
                </td>
                <td>
                    <input type="text" name="sex">
                </td>
            </tr>
            <tr>
                <td>
                    年龄：
                </td>
                <td>
                    <input type="text" name="age">
                </td>
            </tr>
            <tr>
                <td>
                    体重：
                </td>
                <td>
                    <input type="text" name="weight">
                </td>
            </tr>
            <tr>
                <td>
                    身高：
                </td>
                <td>
                    <input type="text" name="height">
                </td>
            </tr>
            <tr align="center">
                <td colspan="2">
                    <input type="submit" value="提交">
                    <input type="reset" value="取消">
                </td>
            </tr>
        </table>
    </form>
    </body>
    </html>

![e06e6037f4ce4833ab43240ada114c54.png][]

*  ### 提交判断 ###

![f1959fe6e2ff4492ae42e8dec88d6a3a.png][]

*  ### 数据库收集到的数据 ###

![2984873b73e146bdb288c59b94571aaa.png][]

*  ## 显示所有学生信息的页面 ##

### 代码 ###

###    运行结果![e2ed1cbb4a2340f68d5c478e3b1c3a0c.png][] ###

对比数据库表：

![db2511c7bef1401a8d64d91507ef81b2.png][]

--------------------

*  ## 查询条件提交显示信息 ##

### 代码： ###

<%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/5
      Time: 10:04
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>查询条件提交页面</title>
    </head>
    <body>
    <div>
      请选择查询条件：
    </div>
    <hr width="100%" size="3">
    <form action="find_stu_3.jsp" method="post">
      性别： 男<input type="radio" value="男" name="sex" checked="checked">
             女<input type="radio" value="女" name="sex">
      <div>
        体重范围：
      </div>
      <p>
      <div>
        最小: <input type="text" name="w1" value="0">
      </div>
      <div>
        最大: <input type="text" name="w2" value="150">
      </div>
      </p>
      <br>
      <div>
        <input type="submit" value="提交">  
        <input type="reset" value="取消">
      </div>
    </form>
    </body>
    </html>

### 运行结果： ###

![73eb69cd07c74193b4aeb347d0528391.png][]

提交后：

![a3c60cdea8484ac0b2ad65832147588f.png][]

--------------------

*  ## 修改单条记录页面 ##

### 代码： ###

#### 运行结果： ####

![fbd80adca346443fa7f1b4169f638625.png][]

### 数据库中更新： ###

![3330ec8984d946b6876d6ddfd726e293.png][]

--------------------

*  ## 根据条件来修改记录（3个jsp页面完成） ##

![b0095bac8e744749b4d8ca4d33d22a82.png][]

### 代码： ###

#### 修改记录提交页面 ####

<%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/5
      Time: 11:49
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>修改记录提交页面</title>
    </head>
    <body>
    <div align="center">
      请选择修改记录所满足的条件：
    </div>
    <hr width="100%" size="3">
    <form action="update_stu_2_edit.jsp" method="post">
      <table align="center">
        <tr>
          <td>
            姓名：
          </td>
          <td>
            <input type="text" name="name">
          </td>
        </tr>
        <tr>
          <td>
            性别：
          </td>
          <td>
            男<input type="radio" value="男" name="sex" checked="checked">
            女<input type="radio" value="女" name="sex">
          </td>
        </tr>
        <tr>
          <td>
            <input type="submit" value="提交">  
          </td>
          <td>
            <input type="reset" value="取消">
          </td>
        </tr>
      </table>
    </form>
    </body>
    </html>

#### 修改编辑页面 ####

<%@ page import="java.sql.Connection" %>
    <%@ page import="java.sql.DriverManager" %>
    <%@ page import="java.sql.PreparedStatement" %>
    <%@ page import="java.sql.ResultSet" %><%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/5
      Time: 11:50
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>修改编辑页面</title>
    </head>
    <body>
    <center>
        <%
            /*注册驱动并建立数据库连接*/
            String url = "jdbc:mysql://localhost:3306/students";
            String username = "root";
            String password = "1222";
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
    
            request.setCharacterEncoding("UTF-8");//设置字符集
    
            String sex = request.getParameter("sex");
            String name = request.getParameter("name");
    
            session.setAttribute("sex", sex);
            session.setAttribute("name", name);
    
            String sql = "select * from stu_info where sex=?and name=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, sex);
            preparedStatement.setString(2, name);
    
            ResultSet resultSet = preparedStatement.executeQuery();
    
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name2 = resultSet.getString("name");
                String sex2 = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                float weight = resultSet.getFloat("weight");
                float height = resultSet.getFloat("height");
    
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
        %>
        <form action="update_stu_2.jsp" method="post">
            <table border="0" width="238" height="252" align="center">
                <tr>
                    <td>
                        学号：
                    </td>
                    <td>
                        <input name="id" value=<%=id%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        姓名：
                    </td>
                    <td>
                        <input name="name2" value=<%=name2%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        性别：
                    </td>
                    <td>
                        <input name="sex2" value=<%=sex2%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        年龄：
                    </td>
                    <td>
                        <input name="age" value=<%=age%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        体重：
                    </td>
                    <td>
                        <input name="weight" value=<%=weight%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        身高：
                    </td>
                    <td>
                        <input name="height" value=<%=height%>>
                    </td>
                </tr>
                <tr align="center">
                    <td colspan="2">
                        <input type="submit" value="提交">   
                        <input type="reset" value="取消">
                    </td>
                </tr>
            </table>
        </form>
    </center>
    <%
    } else {
    %>
    <div align="center">
        没有找到该要修改的记录！请确认信息是否正确！
    </div>
    <%
        }
        if (resultSet != null)
            resultSet.close();
        if (preparedStatement != null)
            preparedStatement.close();
        if (connection != null)
            connection.close();
    %>
    </body>
    </html>

#### 修改后重写页面 ####

#### 运行结果： ####

![f194e3ecd6784bc9a344f564d8e582c6.png][]

\----》》》

![cd836f2c61294dcaba08a38609bef96a.png][]

\-----》》》》

![2f3276a2304f456591437f5278f0698a.png][]

#### 数据库中： ####

![8b78ea5318134b0ca3a44de077d491df.png][]

--------------------

*  ## 根据条件来修改记录---添加空值判断和处理（版本） ##

#### 代码： ####

*  1-

*  #### 2- ####

<%@ page import="java.sql.Connection" %>
    <%@ page import="java.sql.DriverManager" %>
    <%@ page import="java.sql.PreparedStatement" %>
    <%@ page import="java.sql.ResultSet" %><%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/5
      Time: 11:50
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>修改编辑页面</title>
    </head>
    <body>
    <center>
        <%
            /*注册驱动并建立数据库连接*/
            String url = "jdbc:mysql://localhost:3306/students";
            String username = "root";
            String password = "******";
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
    
            request.setCharacterEncoding("UTF-8");//设置字符集
    
            String sex = request.getParameter("sex");
            String name = request.getParameter("name");
    
            session.setAttribute("sex", sex);
            session.setAttribute("name", name);
    
            String sql = "select * from stu_info where sex=?and name=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, sex);
            preparedStatement.setString(2, name);
    
            ResultSet resultSet = preparedStatement.executeQuery();
    
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name2 = resultSet.getString("name");
                String sex2 = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                float weight = resultSet.getFloat("weight");
                float height = resultSet.getFloat("height");
    
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
        %>
        <%--表单--%>
        <form action="update_stu_2.jsp" method="post" name="update-student-2" onsubmit="return validateForm()">
            <table border="0" width="238" height="252" align="center">
                <tr>
                    <td>
                        学号：
                    </td>
                    <td>
                        <input name="id" value=<%=id%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        姓名：
                    </td>
                    <td>
                        <input name="name2" value=<%=name2%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        性别：
                    </td>
                    <td>
                        <input name="sex2" value=<%=sex2%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        年龄：
                    </td>
                    <td>
                        <input name="age" value=<%=age%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        体重：
                    </td>
                    <td>
                        <input name="weight" value=<%=weight%>>
                    </td>
                </tr>
                <tr>
                    <td>
                        身高：
                    </td>
                    <td>
                        <input name="height" value=<%=height%>>
                    </td>
                </tr>
                <tr align="center">
                    <td colspan="2">
                        <input type="submit" value="提交">   
                        <input type="reset" value="取消">
                    </td>
                </tr>
            </table>
        </form>
    </center>
    <%
    } else {
    %>
    <div align="center">
        没有找到该要修改的记录！请确认信息是否正确！
    </div>
    <%
        }
        if (resultSet != null)
            resultSet.close();
        if (preparedStatement != null)
            preparedStatement.close();
        if (connection != null)
            connection.close();
    %>
    </body>
    <script type="text/javascript">
        function validateForm() {
            let nameInput = document.forms["update-student-2"]["name2"];
            let sexInput = document.forms["update-student-2"]["sex2"];
    
            if (nameInput.value.trim() === "") {
                alert("姓名不能为空！");
                nameInput.focus();
                return false;
            }
    
            if (sexInput.value.trim() === "") {
                alert("性别不能为空！");
                sexInput.focus();
                return false;
            }
    
            // 在这里添加更多的数据格式校验，比如年龄应该是一个正整数等等
    
            return true;
        }
    </script>
    </html>

*  #### 3- ####

<%@ page import="java.sql.Connection" %>
    <%@ page import="java.sql.DriverManager" %>
    <%@ page import="java.sql.PreparedStatement" %><%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/5
      Time: 11:50
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>修改后重写页面</title>
    </head>
    <body>
    <%
            /*注册驱动并建立数据库连接*/
            String url = "jdbc:mysql://localhost:3306/students";
            String username = "root";
            String password = "******";
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
    
            String sql="update stu_info set id=?,name=?,sex=?,age=?,weight=?,height=? where name=?and sex=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            request.setCharacterEncoding("UTF-8");//设置字符集
    
    
            String idStr = request.getParameter("id");
            if (idStr == null || idStr.trim().equals("")) {
                    // 处理空值方法1：给定一个默认值
                    idStr = "0";
            }
            int id = Integer.parseInt(idStr);
    
    
            String name2 = request.getParameter("name2");
            if (name2 == null || name2.isBlank()) {
                    // 返回错误信息时，应该保留用户输入的数据，可以在这里将原始值存储到 Session 中
                    session.setAttribute("error", "姓名不能为空");
                    session.setAttribute("name2", name2);
                    response.sendRedirect("D:\\CS\\Code\\IdeaCode\\JDBC-Demo01\\src\\main\\webapp\\update_stu_2_edit.jsp");
                    return;
            }
    
            String sex2 = request.getParameter("sex2");
            if (sex2 == null || sex2.isBlank()) {
                    // 同上，将需要展示的数据存入 Session 中
                    session.setAttribute("error", "性别不能为空");
                    session.setAttribute("sex2", sex2);
                    response.sendRedirect("D:\\CS\\Code\\IdeaCode\\JDBC-Demo01\\src\\main\\webapp\\update_stu_2_edit.jsp");
                    return;
            }
            String ageStr = request.getParameter("age");
            if (ageStr == null || ageStr.trim().equals("")) {
                    ageStr = "0";
            }
            int age = Integer.parseInt(ageStr);
    
            String weightStr = request.getParameter("weight");
            if (weightStr == null || weightStr.trim().equals("")) {
                    weightStr = "0";
            }
            float weight = Float.parseFloat(weightStr);
    
            String heightStr = request.getParameter("height");
            if (heightStr == null || heightStr.trim().equals("")) {
                    heightStr = "0";
            }
            float height = Float.parseFloat(heightStr);
    
            String name=(String) session.getAttribute("name");
            String sex=(String) session.getAttribute("sex");
    
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,name2);
            preparedStatement.setString(3,sex2);
            preparedStatement.setInt(4,age);
            preparedStatement.setFloat(5,weight);
            preparedStatement.setFloat(6,height);
            preparedStatement.setString(7,name);
            preparedStatement.setString(8,sex);
    
            int n=preparedStatement.executeUpdate();
            if (n>=1){
    %>
    <div align="center">
            重写操作成功！
    </div>
    <%
            }
            else {
    %>
    <div align="center">
            重写操作失败！<%=n%>
    </div>
    <%
            }
            if (preparedStatement!=null){preparedStatement.close();}
            if (connection!=null){connection.close();}
    %>
    </body>
    </html>

#### 页面展示： ####

![2451637810184790a3b786dbbfec2412.png][]

[e06e6037f4ce4833ab43240ada114c54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/f82a7efc8e8048e4b8dbce17ca7fb017.png
[f1959fe6e2ff4492ae42e8dec88d6a3a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/462b32c168f54374a79a5c99bac83317.png
[2984873b73e146bdb288c59b94571aaa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/1e31529750fe49e78e9327349aadc630.png
[e2ed1cbb4a2340f68d5c478e3b1c3a0c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/b6984628337c4b1292f1c5c77281aca5.png
[db2511c7bef1401a8d64d91507ef81b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/bd5ce182e7ae4df48bb9defb0f1673d6.png
[73eb69cd07c74193b4aeb347d0528391.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/df92ceb551284e989aa9cc2bac469fa4.png
[a3c60cdea8484ac0b2ad65832147588f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/0a6d2ece7ca74693823679cd391b2b30.png
[fbd80adca346443fa7f1b4169f638625.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/45911a56ae3a4d6698b16702a1df532b.png
[3330ec8984d946b6876d6ddfd726e293.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c95cd686b6e24ec88b959b868f7c97fd.png
[b0095bac8e744749b4d8ca4d33d22a82.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/38137edf4ee84097a304c3868a0ddaea.png
[f194e3ecd6784bc9a344f564d8e582c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/1daff3e3a3c54e4c83d037df6837b7bb.png
[cd836f2c61294dcaba08a38609bef96a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fe8bfcf87b40491b9decc90d50b0729d.png
[2f3276a2304f456591437f5278f0698a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/09e711973abf4bad952e5ab13f8f91d3.png
[8b78ea5318134b0ca3a44de077d491df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/8aa0dc36ccc34f0784bbf74d40001f54.png
[2451637810184790a3b786dbbfec2412.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/09a93f13c47f4caf86766459b7bd8cce.png