web应用技术—第三次课后作业-平芜编程栈

1、复刻注册登录，以及注册人员信息的增删改查

撰写项目文件

sql

CREATE TABLE userinfo2( uid int IDENTITY(1,1) NOT NULL, username varchar(50) NOT NULL, userpwd varchar(50) NOT NULL, sex varchar(10), age int, hobby varchar(200), PRIMARY KEY(uid) ); CREATE UNIQUE INDEX UQ__userinfo__F3DBC57205014886 ON userinfo2("username");

deleteUser.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.sql.*" %> <% request.setCharacterEncoding("UTF-8"); String uidText = request.getParameter("uid"); String msg; if (uidText == null || uidText.trim().isEmpty()) { msg = "删除失败：缺少用户 uid。"; } else { String sql = "DELETE FROM userinfo2 WHERE uid = ?"; try { String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String url = "jdbc:sqlserver://127.0.0.1\\SQLEXPRESS;databaseName=chapter_3;encrypt=true;trustServerCertificate=true"; String dbUser="sa"; String dbPwd="Xujiehhxx1416"; Class.forName(driver); try (Connection conn = DriverManager.getConnection(url, dbUser, dbPwd); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, Integer.parseInt(uidText)); int rows = ps.executeUpdate(); msg = rows > 0 ? "删除成功。" : "删除失败：用户不存在。"; } } catch (Exception e) { msg = "删除失败：" + e.getMessage(); } } %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8" /> <title>删除用户</title> <style> body { text-align: center; margin-top: 80px; font-family: "Microsoft YaHei", sans-serif; } </style> </head> <body> <h2><%= msg %></h2> <p><a href="select.jsp">返回用户列表</a></p> </body> </html>

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>首页</title> <style> body { text-align: center; margin-top: 60px; } a { margin: 0 8px; } </style> </head> <body> <h1>这是主页！！！！！</h1> <% Object loginUser = session.getAttribute("loginUser"); if (loginUser != null) { %> <h1>登录成功！</h1> <p>欢迎您，<%= loginUser %>！</p> <a href="select.jsp">查看所有注册用户信息</a> <a href="logout.jsp">退出登录</a> <% } else { %> <h1>欢迎来到系统</h1> <p>您还没有登录，请先注册或登录：</p> <a href="zhuce.jsp">去注册</a> <a href="login.jsp">去登录</a> <% } %> </body> </html>

login.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <!DOCTYPE html> <html> <head> <title>用户登录</title> <style> form { width: 320px; margin: 60px auto; padding: 20px; border: 1px solid #ddd; border-radius: 8px; background: #fafafa; } .item { margin: 12px 0; } label { display: inline-block; width: 90px; } input { width: 190px; padding: 6px; } button { margin-left: 90px; padding: 6px 18px; } </style> </head> <body> <h2 style="text-align: center">登录页面</h2> <form action="loginCheck.jsp" method="post"> <div class="item"> <label>用户名：</label> <input type="text" name="username" required /> </div> <div class="item"> <label>密码：</label> <input type="password" name="userpwd" required /> </div> <button type="submit">登录</button> </form> </body> </html>

loginCheck.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.sql.*" %> <% request.setCharacterEncoding("UTF-8"); String username = request.getParameter("username"); String userpwd = request.getParameter("userpwd"); boolean loginSuccess = false; String sql = "SELECT uid FROM userinfo2 WHERE username = ? AND userpwd = ?"; try { String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String url = "jdbc:sqlserver://127.0.0.1\\SQLEXPRESS;databaseName=chapter_3;encrypt=true;trustServerCertificate=true"; String dbUser="sa"; String dbPwd="Xujiehhxx1416"; Class.forName(driver); try (Connection conn = DriverManager.getConnection(url, dbUser, dbPwd); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, userpwd); try (ResultSet rs = ps.executeQuery()) { loginSuccess = rs.next(); } } } catch (Exception e) { request.setAttribute("errorMsg", "数据库异常：" + e.getMessage()); } if (loginSuccess) { session.setAttribute("loginUser", username); response.sendRedirect("index.jsp"); return; } %> <!DOCTYPE html> <html> <head> <title>登录结果</title> </head> <body style="text-align: center; margin-top: 60px"> <h2> <%= request.getAttribute("errorMsg") != null ? request.getAttribute("errorMsg") : "登录不成功，用户名或者密码不正确。" %> </h2> <a href="login.jsp">返回登录</a> </body> </html>

logout.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <% session.removeAttribute("loginUser"); session.invalidate(); response.sendRedirect("index.jsp"); %>

select.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.sql.*" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8" /> <title>用户列表 - SQL Server</title> <style> body { margin: 30px; font-family: "Microsoft YaHei", sans-serif; } table { border-collapse: collapse; width: 100%; } th, td { border: 1px solid #ddd; padding: 8px 10px; text-align: center; } th { background: #f5f5f5; } .actions a { margin: 0 6px; } .top-link { margin-bottom: 14px; } </style> </head> <body> <h2>所有注册用户信息</h2> <p class="top-link"><a href="index.jsp">返回首页</a></p> <table> <tr> <th>UID</th> <th>用户名</th> <th>密码</th> <th>性别</th> <th>年龄</th> <th>爱好</th> <th>操作</th> </tr> <% // 查询所有用户，结果用于列表展示 String sql = "SELECT uid, username, userpwd, sex, age, hobby FROM userinfo2 ORDER BY uid DESC"; try { // 加载 SQL Server JDBC 驱动并建立数据库连接 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String uri = "jdbc:sqlserver://localhost:1433;databaseName=2026shangke;encrypt=true;trustServerCertificate=true"; String user = "sa"; String password1 = "123"; // PreparedStatement 执行预编译 SQL，ResultSet 逐行读取结果 try (Connection conn = DriverManager.getConnection(uri, user, password1); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) { while (rs.next()) { %> <tr> <td><%= rs.getInt("uid") %></td> <td><%= rs.getString("username") %></td> <td><%= rs.getString("userpwd") %></td> <td><%= rs.getString("sex") %></td> <td><%= rs.getInt("age") %></td> <td><%= rs.getString("hobby") %></td> <td class="actions"> <a href="updateUser.jsp?uid=<%= rs.getInt("uid") %>">修改</a> <a href="deleteUser.jsp?uid=<%= rs.getInt("uid") %>" onclick="return confirm('确认删除该用户吗？');" >删除</a > </td> </tr> <% } } } catch (Exception e) { %> <tr> <td colspan="7" style="color: red">查询失败：<%= e.getMessage() %></td> </tr> <% } %> </table> </body> </html>

updateUser.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.sql.*" %> <% request.setCharacterEncoding("UTF-8"); String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String url = "jdbc:sqlserver://127.0.0.1\\SQLEXPRESS;databaseName=chapter_3;encrypt=true;trustServerCertificate=true"; String dbUser="sa"; String dbPwd="Xujiehhxx1416"; Class.forName(jdbcDriver); String method = request.getMethod(); String uidText = request.getParameter("uid"); String username = ""; String userpwd = ""; String sex = ""; String ageText = ""; String hobby = ""; String msg = null; if (uidText == null || uidText.trim().isEmpty()) { msg = "缺少用户 uid，无法修改。"; } else { try { int userUid = Integer.parseInt(uidText); try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword)) { if ("POST".equalsIgnoreCase(method)) { username = request.getParameter("username"); userpwd = request.getParameter("userpwd"); sex = request.getParameter("sex"); ageText = request.getParameter("age"); hobby = request.getParameter("hobby"); String updateSql = "UPDATE userinfo2 SET username=?, userpwd=?, sex=?, age=?, hobby=? WHERE uid=?"; try (PreparedStatement ps = conn.prepareStatement(updateSql)) { ps.setString(1, username); ps.setString(2, userpwd); ps.setString(3, sex); ps.setInt(4, Integer.parseInt(ageText)); ps.setString(5, hobby); ps.setInt(6, userUid); int rows = ps.executeUpdate(); msg = rows > 0 ? "修改成功。" : "修改失败：用户不存在。"; } } else { String querySql = "SELECT username, userpwd, sex, age, hobby FROM userinfo2 WHERE uid = ?"; try (PreparedStatement ps = conn.prepareStatement(querySql)) { ps.setInt(1, userUid); try (ResultSet rs = ps.executeQuery()) { if (rs.next()) { username = rs.getString("username"); userpwd = rs.getString("userpwd"); sex = rs.getString("sex"); ageText = String.valueOf(rs.getInt("age")); hobby = rs.getString("hobby"); } else { msg = "未找到该用户。"; } } } } } } catch (NumberFormatException e) { msg = "参数格式错误：" + e.getMessage(); } catch (Exception e) { msg = "操作失败：" + e.getMessage(); } } %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8" /> <title>修改用户</title> <style> body { font-family: "Microsoft YaHei", sans-serif; } form { width: 420px; margin: 40px auto; border: 1px solid #ddd; border-radius: 8px; padding: 20px; background: #fafafa; } .item { margin: 10px 0; } label { display: inline-block; width: 90px; } input { width: 260px; padding: 6px; } button { margin-left: 90px; padding: 6px 18px; } .center { text-align: center; } </style> </head> <body> <h2 class="center">修改用户信息</h2> <% if (msg != null) { %> <p class="center"><strong><%= msg %></strong></p> <% } %> <% if (uidText != null && !uidText.trim().isEmpty() && !"POST".equalsIgnoreCase(method) && msg == null) { %> <form action="updateUser.jsp" method="post"> <input type="hidden" name="uid" value="<%= uidText %>" /> <div class="item"> <label>用户名：</label> <input type="text" name="username" value="<%= username %>" required /> </div> <div class="item"> <label>密码：</label> <input type="text" name="userpwd" value="<%= userpwd %>" required /> </div> <div class="item"> <label>性别：</label> <input type="text" name="sex" value="<%= sex %>" required /> </div> <div class="item"> <label>年龄：</label> <input type="number" name="age" value="<%= ageText %>" required /> </div> <div class="item"> <label>爱好：</label> <input type="text" name="hobby" value="<%= hobby %>" /> </div> <button type="submit">提交修改</button> </form> <% } %> <p class="center"><a href="select.jsp">返回用户列表</a></p> </body> </html>

zhuce.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>用户注册</title> <style> form { width: 380px; margin: 50px auto; padding: 20px; border: 1px solid #ddd; border-radius: 8px; background: #fafafa; } .item { margin: 10px 0; } label { display: inline-block; width: 80px; } input[type="text"], input[type="password"], input[type="number"] { padding: 5px; width: 200px; } button { padding: 6px 20px; margin-left: 80px; } .options { display: inline-block; } .options label { width: auto; margin-right: 10px; } </style> </head> <body> <h1 style="text-align: center">用户注册表单</h1> <%-- 表单提交到zhuceCkeck.jsp，提交方式为post --%> <form action="zhuceCkeck.jsp" method="post"> <div class="item"> <label>用户名：</label> <input type="text" name="username" required /> <%-- required表示必填 --%> </div> <div class="item"> <label>密码：</label> <input type="password" name="userpwd" required /> </div> <div class="item"> <label>性别：</label> <span class="options"> <label ><input type="radio" name="sex" value="男" required /> 男</label > <label><input type="radio" name="sex" value="女" /> 女</label> </span> </div> <div class="item"> <label>年龄：</label> <input type="number" name="age" min="1" max="120" required /> </div> <div class="item"> <label>爱好：</label> <span class="options"> <label ><input type="checkbox" name="hobby" value="篮球" /> 篮球</label > <label ><input type="checkbox" name="hobby" value="音乐" /> 音乐</label > <label ><input type="checkbox" name="hobby" value="阅读" /> 阅读</label > <label ><input type="checkbox" name="hobby" value="旅行" /> 旅行</label > </span> </div> <button type="submit">提交</button> </form> </body> </html>

zhuceCkeck.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.util.Arrays" %> <%@ page import="java.sql.*" %> <html> <head> <title>注册结果</title> <style> body { text-align: center; margin-top: 50px; } .result { display: inline-block; text-align: left; } </style> </head> <body> <% request.setCharacterEncoding("UTF-8"); String username = request.getParameter("username"); String userpwd = request.getParameter("userpwd"); String sex = request.getParameter("sex"); String ageText = request.getParameter("age"); String[] hobbies = request.getParameterValues("hobby"); String hobbyText = (hobbies != null && hobbies.length > 0) ? Arrays.toString(hobbies).replace("[", "").replace("]", "") : "未选择"; int age = Integer.parseInt(ageText); boolean registerSuccess = false; String msg; String checkSql = "SELECT uid FROM userinfo2 WHERE username = ?"; String insertSql = "INSERT INTO userinfo2(username, userpwd, sex, age, hobby) VALUES(?, ?, ?, ?, ?)"; try { String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String url = "jdbc:sqlserver://127.0.0.1;instanceName=SQLEXPRESS;DatabaseName=chapter_3;encrypt=true;trustServerCertificate=true"; String dbUser="sa"; String dbPwd="Xujiehhxx1416"; Class.forName(driver); try (Connection conn = DriverManager.getConnection(url, dbUser, dbPwd); PreparedStatement checkPs = conn.prepareStatement(checkSql)) { checkPs.setString(1, username); try (ResultSet rs = checkPs.executeQuery()) { if (rs.next()) { msg = "注册失败：用户名已存在"; } else { try (PreparedStatement insertPs = conn.prepareStatement(insertSql)) { insertPs.setString(1, username); insertPs.setString(2, userpwd); insertPs.setString(3, sex); insertPs.setInt(4, age); insertPs.setString(5, hobbyText); registerSuccess = insertPs.executeUpdate() > 0; msg = registerSuccess ? "注册信息提交成功！" : "注册失败，请重试"; } } } } } catch (Exception e) { msg = "注册失败：数据库异常 - " + e.getMessage(); } %> <h1><%= msg %></h1> <div class="result"> <p>用户名：<%= username %></p> <p>性别：<%= sex %></p> <p>年龄：<%= ageText %> 岁</p> <p>爱好：<%= hobbyText %></p> <%-- 跳转回表单页面 --%> <a href="zhuce.jsp">返回重新提交</a> &nbsp;&nbsp; <a href="login.jsp">去登录</a> </div> </body> </html>