web应用技术03-JDBC数据库操作
一、以下是注册登录的小项目。
如果是没有登录的状态访问index.jsp则显示欢迎，并提供注册或者登录的链接。如果已登录状态访问主页，则显示退出登录，和显示所有所有注册用户的链接。
在显示所有注册用户的链接页面，可以对前面所注册的用户进行删除和修改。
二、撰写项目文件
1、新建项目，并把之前的表单文件拷贝到新项目相应的文件夹下面
2、添加新的增删改查的文件
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> <% // 从 session 中读取当前登录用户，用于控制页面显示内容 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="password" 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 password = request.getParameter("password"); boolean loginSuccess = false; // 使用占位符避免 SQL 注入 String sql = "SELECT id FROM t_user WHERE username = ? AND password = ?"; try { // 加载 JDBC 驱动并建立数据库连接 Class.forName("com.mysql.jdbc.Driver"); String uri = "jdbc:mysql://127.0.0.1/2026shangke?useUnicode=true&characterEncoding=UTF-8"; String user = "root"; String password1 = "123"; // PreparedStatement 负责预编译 SQL 并安全绑定参数 try (Connection conn = DriverManager.getConnection(uri, user, password1); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, password); try (ResultSet rs = ps.executeQuery()) { loginSuccess = rs.next(); } } } catch (Exception e) { request.setAttribute("errorMsg", "数据库异常：" + e.getMessage()); } if (loginSuccess) { // 登录成功后写入 session，后续页面可据此判断登录状态 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，完成退出登录 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>用户列表</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>ID</th> <th>用户名</th> <th>密码</th> <th>性别</th> <th>年龄</th> <th>爱好</th> <th>操作</th> </tr> <% // 查询所有用户，结果用于列表展示 String sql = "SELECT id, username, password, gender, age, hobby FROM t_user ORDER BY id DESC"; try { // 加载 JDBC 驱动并建立数据库连接 Class.forName("com.mysql.jdbc.Driver"); String uri = "jdbc:mysql://127.0.0.1/2026shangke?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8"; String user = "root"; 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("id") %></td> <td><%= rs.getString("username") %></td> <td><%= rs.getString("password") %></td> <td><%= rs.getString("gender") %></td> <td><%= rs.getInt("age") %></td> <td><%= rs.getString("hobby") %></td> <td class="actions"> <a href="updateUser.jsp?id=<%= rs.getInt("id") %>">修改</a> <a href="deleteUser.jsp?id=<%= rs.getInt("id") %>" 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 jdbcDriver = "com.mysql.jdbc.Driver"; String jdbcUrl = "jdbc:mysql://127.0.0.1/2026shangke?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8"; String jdbcUser = "root"; String jdbcPassword = "123"; // 加载 JDBC 驱动，后续通过 DriverManager 建立连接 Class.forName(jdbcDriver); String method = request.getMethod(); String idText = request.getParameter("id"); String username = ""; String password = ""; String gender = ""; String ageText = ""; String hobby = ""; String msg = null; if (idText == null || idText.trim().isEmpty()) { msg = "缺少用户 id，无法修改。"; } else { try { int userId = Integer.parseInt(idText); // 共享同一个连接：POST 执行更新，GET 执行回显查询 try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword)) { if ("POST".equalsIgnoreCase(method)) { username = request.getParameter("username"); password = request.getParameter("password"); gender = request.getParameter("gender"); ageText = request.getParameter("age"); hobby = request.getParameter("hobby"); String updateSql = "UPDATE t_user SET username=?, password=?, gender=?, age=?, hobby=? WHERE id=?"; // 预处理语句绑定参数并提交更新 try (PreparedStatement ps = conn.prepareStatement(updateSql)) { ps.setString(1, username); ps.setString(2, password); ps.setString(3, gender); ps.setInt(4, Integer.parseInt(ageText)); ps.setString(5, hobby); ps.setInt(6, userId); int rows = ps.executeUpdate(); msg = rows > 0 ? "修改成功。" : "修改失败：用户不存在。"; } } else { String querySql = "SELECT username, password, gender, age, hobby FROM t_user WHERE id = ?"; // 预处理语句按 id 查询，用于表单回显 try (PreparedStatement ps = conn.prepareStatement(querySql)) { ps.setInt(1, userId); try (ResultSet rs = ps.executeQuery()) { if (rs.next()) { username = rs.getString("username"); password = rs.getString("password"); gender = rs.getString("gender"); 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 (idText != null && !idText.trim().isEmpty() && !"POST".equalsIgnoreCase(method) && msg == null) { %> <form action="updateUser.jsp" method="post"> <input type="hidden" name="id" value="<%= idText %>" /> <div class="item"> <label>用户名：</label> <input type="text" name="username" value="<%= username %>" required /> </div> <div class="item"> <label>密码：</label> <input type="text" name="password" value="<%= password %>" required /> </div> <div class="item"> <label>性别：</label> <input type="text" name="gender" value="<%= gender %>" 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="password" required /> </div> <div class="item"> <label>性别：</label> <span class="options"> <label ><input type="radio" name="gender" value="男" required /> 男</label > <label><input type="radio" name="gender" 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 password = request.getParameter("password"); String gender = request.getParameter("gender"); 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 id FROM t_user WHERE username = ?"; String insertSql = "INSERT INTO t_user(username, password, gender, age, hobby) VALUES(?, ?, ?, ?, ?)"; try { // 加载 JDBC 驱动并连接数据库 Class.forName("com.mysql.jdbc.Driver"); String uri = "jdbc:mysql://127.0.0.1/2026shangke?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8"; String user = "root"; String password1 = "123"; // 先用预处理语句校验用户名是否已存在 try (Connection conn = DriverManager.getConnection(uri, user, password1); 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, password); insertPs.setString(3, gender); 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>性别：<%= gender %></p> <p>年龄：<%= ageText %> 岁</p> <p>爱好：<%= hobbyText %></p> <%-- 跳转回表单页面 --%> <a href="zhuce.jsp">返回重新提交</a> <a href="login.jsp">去登录</a> </div> </body> </html>deleteUser.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="java.sql.*" %> <% request.setCharacterEncoding("UTF-8"); String idText = request.getParameter("id"); String msg; if (idText == null || idText.trim().isEmpty()) { msg = "删除失败：缺少用户 id。"; } else { // 按 id 删除用户，使用占位符避免 SQL 注入 String sql = "DELETE FROM t_user WHERE id = ?"; try { // 加载驱动并连接数据库 Class.forName("com.mysql.jdbc.Driver"); String uri = "jdbc:mysql://127.0.0.1/2026shangke?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8"; String user = "root"; String password1 = "123"; // PreparedStatement 绑定 id 参数后执行删除 try (Connection conn = DriverManager.getConnection(uri, user, password1); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, Integer.parseInt(idText)); 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>pom.xml
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency>三、项目的部署
1、可以按照vs code中常规的，先打包，在部署到tomcat中
mvn clean package 打包成demo.war的文件
run on server 会自动将war文件拷贝到tomcat的webapps目录下面。
然后就可以war包的名字作为路径来访问。
http://localhost:8080/demo/index.jsp
2、只要改动了文件，就要重新打包部署，非常的麻烦。可以把所编辑的源文件直接拷贝到webapps相应的目录里面去。这样就不用再打包部署。每次修改了源代码，拷贝替代即可。
3、每次拷贝替代也非常的麻烦，可以修改tomcat的配置文件server.xml，让tomcat可以直接访问我们所编辑的文件夹。
以下代码，改成自己的路径和源代码的地址。
<Context path="/zhucedenglu11" docBase="D:/2026workspace/zhucedenglu/demo/src/main/webapp" reloadable="true" />设置好了以后，tomcat重启，这样就能访问了
但是，如果进这样操作，发现一般的页面可以正常访问，但是只要有数据库操作的页面都会报错。这是因为，数据库的操作，需要额外的java包。目前这个目录中没有。
进入到之前vs code部署的项目文件中，找到lib，拷贝
粘贴到源文件的目录中
再次重启tomcat，数据库的所有操作都能成功完成了。
这个lib包中，就是jsp操作数据库的jdbc的java包。
四、如果是sql server数据库的话，需要做的修改
pom.xml
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency>select-sqlserver.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>ID</th> <th>用户名</th> <th>密码</th> <th>性别</th> <th>年龄</th> <th>爱好</th> <th>操作</th> </tr> <% // 查询所有用户，结果用于列表展示 String sql = "SELECT id, username, password, gender, age, hobby FROM t_user ORDER BY id 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("id") %></td> <td><%= rs.getString("username") %></td> <td><%= rs.getString("password") %></td> <td><%= rs.getString("gender") %></td> <td><%= rs.getInt("age") %></td> <td><%= rs.getString("hobby") %></td> <td class="actions"> <a href="updateUser.jsp?id=<%= rs.getInt("id") %>">修改</a> <a href="deleteUser.jsp?id=<%= rs.getInt("id") %>" onclick="return confirm('确认删除该用户吗？');" >删除</a > </td> </tr> <% } } } catch (Exception e) { %> <tr> <td colspan="7" style="color: red">查询失败：<%= e.getMessage() %></td> </tr> <% } %> </table> </body> </html>换不同的数据库，代码部分需要改的，只有加载驱动和建立连接这两句，其他的地方没有任何变化。