Javaweb 实验9 JSP访问数据库（二）

于 2024-11-20 15:12:37 发布
阅读量591 收藏 9
点赞数 9
CC 4.0 BY-SA版权
分类专栏： JavaWeb 文章标签： 数据库 jsp javaweb sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CH3CH2CH4/article/details/143912851

我发现了有些人喜欢静静看博客不聊天呐，

但是ta会点赞。

这样的人呢帅气低调有内涵，

美丽大方很优雅。

说的就是你，

不用再怀疑哦

目的：

1、熟悉JDBC的数据库访问模式。

2、掌握预处理语句的使用

实验要求：

  1. 使用Tomcat作为Web服务器
  2. 通过JDBC访问数据库，实现增删改查功能的实现
  3. 要求提交实验报告，将代码和实验结果页面截图放入报告中

实验内容：

编写一个网页，实现根据输入学生姓名的模糊查询，如果查找不到就显示“查无此人”

这个代码不能通过姓氏查询名字，在别的电脑却可以

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head>
  <title>Title</title>
</head>
<body>
<form method="post">
  <label>查找的姓名：</label>
  <input type="text" name="name">
  <input type="submit" value="查询">
</form>
<h1>查询结果</h1>
<table border="1">
  <tr>
    <th>编号</th>
    <th>姓名</th>
    <th>性别</th>
  </tr>
  <%
    request.setCharacterEncoding("UTF-8");
    // 数据库连接信息
    String url = "jdbc:mysql://localhost:3306/dbjsp?useUnicode=true&characterEncoding=utf8";
    String user = "root";
    String password = "";
    String name = request.getParameter("name");
    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection conn = DriverManager.getConnection(url, user, password);

      String sql = "SELECT * FROM student WHERE name = ?";
      PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, name);
      //out.println(pst);
      ResultSet rs = pst.executeQuery();

      // 检查结果集是否有数据
      if (rs.next()) {
          int id = rs.getInt("id");
          String dbname = rs.getString("name");
          String sex = rs.getString("sex");
  %>
  <tr>
    <td><%= id %></td>
    <td><%= dbname %></td>
    <td><%= sex %></td>
  </tr>
  <%
      } else {
        out.println("查无此人");
      }
      // 关闭资源
      rs.close();
      pst.close();
      conn.close();
    } catch (Exception e) {
      out.println("查询出错：" + e.getMessage());
    }
  %>
</table>
</body>
</html>

实现学生注册和登录功能，输入学生学号（stuId）和密码(stuPwd)，如果学号不存在，跳转到注册界面，完成注册功能；如果学号存在，密码匹配显示“登录成功”，否则显示“登录失败”。注：要求用PreparedStatement实现。

2.1.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>学生登录</title>
    <style>
        .center{
            text-align: center;
        }
        .login-btn {
            background-color: #4CAF50; /* 添加你想要的颜色 */
            color: white;
            padding: 10px 20px;
            border: none;
            cursor: pointer;
            border-radius: 5px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin-top: 10px; /* 如果需要，可以调整按钮与输入框之间的间距 */
        }
    </style>
</head>
<body>
<div class="center" >

    <h2>学生登录</h2>
    <form  method="post">
        学号：<input type="text" name="stuId"><br>
        密码：<input type="password" name="stuPwd"><br>
        <input type="submit" value="登录"  class="login-btn">
    </form>
</div>
<%
    try {
        request.setCharacterEncoding("UTF-8");
        String stuId = request.getParameter("stuId");
        String stuPwd = request.getParameter("stuPwd");
        // 数据库连接信息
        if (stuId != null && stuPwd != null) {
            String url = "jdbc:mysql://localhost:3306/dbjsp?serverTimezone=UTC";
            String user = "root";
            String password = "";
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);

            String sql = "SELECT * FROM student2 WHERE id = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, stuId);
            ResultSet rs = pst.executeQuery();
            // 输出查询结果
            if (rs.next()) {
                // 如果学号存在，检查密码是否匹配
                String dbPwd = rs.getString("password");
                if (dbPwd.equals(stuPwd)) {
                    // 密码匹配，登录成功
                    %>
                    <script>
                        alert("登录成功");
                    </script>
                    <%
                    } else {
                        // 密码不匹配，登录失败
                        %>
                        <script>
                            alert("登录失败");
                        </script>
                        <%
                    }
            } else{
                // 学号不存在，重定向到注册页面
                response.sendRedirect("register.jsp");
            }

            // 关闭资源
            if (rs != null)
                rs.close();
            if (pst != null)
                pst.close();
            if (conn != null)
                conn.close();
        }
    } catch (Exception e) {
        out.println(e.getMessage());
    }
%>
</body>
</html>

register.jsp：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>学生登录</title>
    <style>
        .center{
            text-align: center;
        }
        .login-btn {
            padding: 10px 20px;
            border: none;
            cursor: pointer;
            border-radius: 5px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin-top: 10px; /* 如果需要，可以调整按钮与输入框之间的间距 */
        }
    </style>
</head>
<body>
<div class="center" >
    <h2>学生注册</h2>
    <form  method="post">
        学号：<input type="text" name="stuId"><br>
        密码：<input type="password" name="stuPwd"><br>
        <input type="submit" value="注册"  class="login-btn">
    </form>
    <a href="2.1.jsp">返回登录页面</a>
</div>
<%
    try {
        request.setCharacterEncoding("UTF-8");
        String stuId = request.getParameter("stuId");
        String stuPwd = request.getParameter("stuPwd");
        // 数据库连接信息
        if (stuId != null && stuPwd != null)
        {
            String url = "jdbc:mysql://localhost:3306/dbjsp?serverTimezone=UTC";
            String user = "root";
            String password = "";
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);

            String sql = "INSERT INTO student2 (id, password) VALUES (?, ?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, stuId);
            pst.setString(2, stuPwd);
            pst.execute();

%>
<script>
    alert("注册成功");
</script>
<%
        }
        //response.sendRedirect("2.1.jsp");

    } catch (Exception e) {
        out.println(e.getMessage());
    }
%>
</body>
</html>

运行结果

（选做）参考学习平台上“JDBC操作_增删改”的视频，使用表格展示数据库中stuinfo表中的个人信息，每行一条个人信息，表格新增两列，分别提供“修改”和“删除”两个链接。点击“修改”链接时，跳转到新页面可供用户修改和保存；点击“删除”时，跳转到新页面完成删除操作并提示操作结果。

T3.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1>学生信息展示</h1>
<table>
    <tr>
        <th>学号</th>
        <th>姓名</th>
        <th>性别</th>
        <th><a href="Add.jsp">添加</a></th>
    </tr>
    <%
        request.setCharacterEncoding("UTF-8");
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/dbjsp";
        String user = "root";
        String password = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);

            String sql = "SELECT * FROM student";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
    %>
    <tr>
        <td><%= id %></td>
        <td><%= name %></td>
        <td><%= sex %></td>
        <td><a href="update.jsp?id=<%=id%>">修改</a></td>
        <td><a href="delete.jsp?id=<%=id%>" onclick="if(confirm('确定删除？') == false) return false">删除</a></td>
    </tr>
    <%
            }
            // 关闭资源
            rs.close();
            pst.close();
            conn.close();
        } catch (Exception e) {
            out.println("查询出错：" + e.getMessage());
        }
    %>
</table>
</body>
</html>

Add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1>添加学生</h1>
<form action="Add.jsp" method="post">
    姓名:<input type="text" name="name"><br>
    性别:<input type="radio" name="sex" value="男">男
        <input type="radio" name="sex" value="女">女<br>
    <input type="submit" value="添加">
</form>
    <%
        request.setCharacterEncoding("UTF-8");
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/dbjsp?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "";

        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        if(name != null && sex != null) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection(url, user, password);
                //找出最后的id
                String sql = "select id from student order by id desc limit 1";
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet rs = pst.executeQuery();
                int id = 0; //初始化
                while (rs.next()) {
                    id = rs.getInt("id");
                }
//                out.println(id);
                sql = "Insert into student(id, name, sex) values(?,?,?)";
                pst = conn.prepareStatement(sql);
                pst.setString(1, String.valueOf(id + 1)); //转字符
                pst.setString(2, name);
                pst.setString(3, sex);
                int i = pst.executeUpdate();
                out.println(i + "条记录插入成功");
                pst.close();
                conn.close();
            } catch (Exception e) {
                out.println("出错：" + e.getMessage());
            }
        }
    %>
</body>
</html>

delete.jsp

<%--
  Created by IntelliJ IDEA.
  User: a1830
  Date: 2024/11/19
  Time: 22:58
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<html>
<head>
    <title>更新成功</title>
</head>
<body>
<%
    request.setCharacterEncoding("UTF-8");
    // 数据库连接信息
    String url = "jdbc:mysql://localhost:3306/dbjsp";
    String user = "root";
    String password = "";
    String id = request.getParameter("id");
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            //根据id删除数据
            String sql = "delete from student where id = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            int i = pst.executeUpdate();
            if(i > 0) {
                out.println("删除成功");
                response.sendRedirect("T3.jsp"); //删除成功后跳转到显示信息页面
            }
            else {
                out.println("删除失败");
            }
            pst.close();
            conn.close();
        } catch (Exception e) {
            out.println("出错：" + e.getMessage());
        }
%>
</body>
</html>

update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1>修改学生信息</h1>

<%
    request.setCharacterEncoding("UTF-8");
    int id = Integer.parseInt(request.getParameter("id"));
    // 数据库连接信息
    String url = "jdbc:mysql://localhost:3306/dbjsp?useUnicode=true&characterEncoding=utf8";
    String user = "root";
    String password = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "select name, sex from student where id=" + id;
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            if(rs.next()) {
                String dbname = rs.getString("name");
                String dbsex = rs.getString("sex");
        %>
                <form action="updateDo.jsp" method="post">
                    <input type="hidden" name="id" value=<%=id%>>
                    姓名:<input type="text" name="name" value=<%=dbname%>><br>
                    性别:<input type="radio" name="sex" value="男" <%="男".equals(dbsex) ? "checked" : ""%>>男
                    <input type="radio" name="sex" value="女" <%="女".equals(dbsex) ? "checked" : ""%>>女<br>
                    <input type="submit" value="更新">
                </form>
<%
        }
        pst.close();
        conn.close();
    } catch (Exception e) {
        out.println("出错：" + e.getMessage());
    }
%>
</body>
</html>

updateDo.jsp

<%--
  Created by IntelliJ IDEA.
  User: a1830
  Date: 2024/11/19
  Time: 22:58
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<html>
<head>
    <title>更新成功</title>
</head>
<body>
<%
    request.setCharacterEncoding("UTF-8");
    // 数据库连接信息
    String url = "jdbc:mysql://localhost:3306/dbjsp?useUnicode=true&characterEncoding=utf8";
    String user = "root";
    String password = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            //更新数据
            String sql = "update student set name = ?, sex = ? where id = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, request.getParameter("name"));
            pst.setString(2, request.getParameter("sex"));
            pst.setString(3, request.getParameter("id"));
            int i = pst.executeUpdate();
            if(i > 0) {
                out.println("更新成功");
            }
            pst.close();
            conn.close();
        } catch (Exception e) {
            out.println("出错：" + e.getMessage());
        }
%>
</body>
</html>

运行结果

数据库：

添加功能

修改学生信息：

删除学生：

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值