一、前期准备工作
1、电脑上要有mysql、jdk、navicat、eclipse软件（web开发版本）
2、在eclipse配置服务器apache-tomcat-9.0.11-windows-x64（不赘述）
连接数据库的jar包，例如mysql-connector-j-8.0.33
通过网盘分享的文件：JavaWeb.zip
链接: https://pan.baidu.com/s/1p0-Ya3pXnpBYx_1hgekj9w?pwd=2023 提取码: 2023 复制这段内容后打开百度网盘手机App，操作更方便哦
3、在eclipse建立如图所示的代码结构
注意：连接数据库的jar包 需要右键build to path
plt是Dynamic web project 项目
二、代码部分
1、Java 类代码部分
package plt;
import java.sql.*;
public class JDBCUtil {
// 需要修改：数据库连接信息
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private static final String JDBC_USER = "root";
private static final String JDBC_PASSWORD = "123456";
static {
try {
// 需要修改：如果使用其他数据库需更换驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 执行更新操作（增、删、改）
public static int executeUpdate(String sql, Object... params) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return -1;
} finally {
close(conn, pstmt, null);
}
}
// 执行查询操作
public static ResultSet executeQuery(String sql, Object... params) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
return pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
}
2、jsp部分的代码
add.jsp
<%@ page import="plt.JDBCUtil" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>用户管理系统 - 添加用户</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
.form-container {
max-width: 600px;
margin: 2rem auto;
padding: 2rem;
background: #fff;
border-radius: 8px;
box-shadow: 0 0 15px rgba(0,0,0,0.1);
}
.form-title {
color: #0d6efd;
margin-bottom: 1.5rem;
text-align: center;
}
</style>
</head>
<body class="bg-light">
<div class="container">
<div class="form-container">
<%
if ("POST".equalsIgnoreCase(request.getMethod())) {
// 获取所有表单参数
String name = request.getParameter("name");
String email = request.getParameter("email");
int age = Integer.parseInt(request.getParameter("age"));
String gender = request.getParameter("gender");
String phone = request.getParameter("phone");
try {
// 修改SQL语句包含所有字段
String sql = "INSERT INTO users(name, email, age, gender, phone) VALUES(?, ?, ?, ?, ?)";
int result = JDBCUtil.executeUpdate(sql, name, email, age, gender, phone);
if (result > 0) {
response.sendRedirect("list.jsp");
return;
}
} catch (Exception e) {
out.print("<div class='alert alert-danger mt-3'>添加失败：" + e.getMessage() + "</div>");
}
}
%>
<h2 class="form-title">✍️ 添加新用户</h2>
<form method="post">
<!-- 姓名 -->
<div class="mb-3">
<label class="form-label">姓名：</label>
<input type="text" class="form-control" name="name" required>
</div>
<!-- 邮箱 -->
<div class="mb-3">
<label class="form-label">邮箱：</label>
<input type="email" class="form-control" name="email" required>
</div>
<!-- 年龄 -->
<div class="mb-3">
<label class="form-label">年龄：</label>
<input type="number" class="form-control" name="age" min="1" max="120" required>
</div>
<!-- 性别 -->
<div class="mb-3">
<label class="form-label">性别：</label>
<select class="form-select" name="gender" required>
<option value="">请选择</option>
<option value="男">男</option>
<option value="女">女</option>
</select>
</div>
<!-- 电话 -->
<div class="mb-4">
<label class="form-label">电话：</label>
<input type="tel" class="form-control" name="phone"
pattern="[0-9]{11}" title="请输入11位手机号码" required>
</div>
<!-- 操作按钮 -->
<div class="d-grid gap-2">
<button type="submit" class="btn btn-primary">提交</button>
<a href="list.jsp" class="btn btn-outline-secondary">返回列表</a>
</div>
</form>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
edit.jsp
<%@ page import="plt.JDBCUtil, java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>编辑用户</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
.edit-container {
max-width: 600px;
margin: 2rem auto;
padding: 2rem;
background: white;
border-radius: 8px;
box-shadow: 0 0 15px rgba(0,0,0,0.1);
}
</style>
</head>
<body class="bg-light">
<div class="container">
<div class="edit-container">
<%
String id = request.getParameter("id");
ResultSet rs = null;
if ("POST".equalsIgnoreCase(request.getMethod())) {
String name = request.getParameter("name");
String email = request.getParameter("email");
int age = Integer.parseInt(request.getParameter("age"));
String gender = request.getParameter("gender");
String phone = request.getParameter("phone");
try {
String sql = "UPDATE users SET name=?, email=?, age=?, gender=?, phone=? WHERE id=?";
int result = JDBCUtil.executeUpdate(sql, name, email, age, gender, phone, id);
if (result > 0) {
response.sendRedirect("list.jsp");
return;
}
} catch (Exception e) {
out.print("<div class='alert alert-danger'>更新失败：" + e.getMessage() + "</div>");
}
}
String sql = "SELECT * FROM users WHERE id=?";
rs = JDBCUtil.executeQuery(sql, id);
%>
<% if (rs != null && rs.next()) { %>
<h2 class="text-center mb-4">✏️ 编辑用户</h2>
<form method="post">
<input type="hidden" name="id" value="<%= id %>">
<!-- 姓名 -->
<div class="mb-3">
<label class="form-label">姓名：</label>
<input type="text" class="form-control" name="name"
value="<%= rs.getString("name") %>" required>
</div>
<!-- 邮箱 -->
<div class="mb-3">
<label class="form-label">邮箱：</label>
<input type="email" class="form-control" name="email"
value="<%= rs.getString("email") %>" required>
</div>
<!-- 年龄 -->
<div class="mb-3">
<label class="form-label">年龄：</label>
<input type="number" class="form-control" name="age"
value="<%= rs.getInt("age") %>" min="1" max="120" required>
</div>
<!-- 性别 -->
<div class="mb-3">
<label class="form-label">性别：</label>
<select class="form-select" name="gender" required>
<option value="男" <%= "男".equals(rs.getString("gender")) ? "selected" : "" %>>男</option>
<option value="女" <%= "女".equals(rs.getString("gender")) ? "selected" : "" %>>女</option>
<option value="其他" <%= "其他".equals(rs.getString("gender")) ? "selected" : "" %>>其他</option>
</select>
</div>
<!-- 电话 -->
<div class="mb-4">
<label class="form-label">电话：</label>
<input type="tel" class="form-control" name="phone"
value="<%= rs.getString("phone") %>"
pattern="[0-9]{11}" title="11位手机号" required>
</div>
<!-- 操作按钮 -->
<div class="d-grid gap-2">
<button type="submit" class="btn btn-primary">保存</button>
<a href="list.jsp" class="btn btn-outline-secondary">取消</a>
</div>
</form>
<% } else { %>
<div class="alert alert-warning text-center">用户不存在</div>
<div class="text-center mt-3">
<a href="list.jsp" class="btn btn-primary">返回列表</a>
</div>
<% }
JDBCUtil.close(null, null, rs);
%>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
delete.jsp
<%@ page import="plt.JDBCUtil" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<%
String id = request.getParameter("id");
try {
String sql = "DELETE FROM users WHERE id=?";
int result = JDBCUtil.executeUpdate(sql, id);
if (result > 0) {
response.sendRedirect("list.jsp");
} else {
out.print("<script>alert('删除失败'); window.location='list.jsp';</script>");
}
} catch (Exception e) {
out.print("<script>alert('删除失败：" + e.getMessage() + "'); window.location='list.jsp';</script>");
}
%>
list.jsp
<%@ page import="plt.JDBCUtil, java.sql.*, java.text.SimpleDateFormat" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>用户管理系统</title>
<!-- 修正点：修复了Bootstrap CSS链接的引号问题 -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body { padding: 20px; background-color: #f8f9fa; }
.container { max-width: 1400px; }
.table-responsive { margin-top: 20px; }
.action-buttons a { margin-right: 5px; }
</style>
</head>
<body>
<div class="container">
<h2 class="text-center mb-4">📋 用户列表</h2>
<!-- 搜索表单 -->
<div class="card mb-4">
<div class="card-body">
<form method="get" class="row g-3">
<div class="col-md-8">
<input type="text" name="keyword" class="form-control"
placeholder="输入姓名、邮箱或电话搜索">
</div>
<div class="col-md-4">
<button type="submit" class="btn btn-primary me-2">🔍 查询</button>
<a href="list.jsp" class="btn btn-outline-secondary">🔄 重置</a>
</div>
</form>
</div>
</div>
<!-- 添加用户按钮 -->
<a href="add.jsp" class="btn btn-success mb-3">➕ 添加用户</a>
<!-- 用户表格 -->
<div class="table-responsive">
<table class="table table-striped table-hover">
<thead class="table-dark">
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>邮箱</th>
<th>电话</th>
<th>创建时间</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<%
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
try {
String keyword = request.getParameter("keyword");
String sql = "SELECT * FROM users";
if (keyword != null && !keyword.trim().isEmpty()) {
sql += " WHERE name LIKE ? OR email LIKE ? OR phone LIKE ?";
}
sql += " ORDER BY create_time DESC";
conn = JDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql);
if (keyword != null && !keyword.trim().isEmpty()) {
String likePattern = "%" + keyword + "%";
pstmt.setString(1, likePattern);
pstmt.setString(2, likePattern);
pstmt.setString(3, likePattern);
}
rs = pstmt.executeQuery();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
while (rs.next()) {
Timestamp createTime = rs.getTimestamp("create_time");
String formattedTime = sdf.format(createTime);
%>
<tr>
<td><%= rs.getInt("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getInt("age") %></td>
<td><%= rs.getString("gender") %></td>
<td><%= rs.getString("email") %></td>
<td><%= rs.getString("phone") %></td>
<td><%= formattedTime %></td>
<td class="action-buttons">
<a href="edit.jsp?id=<%= rs.getInt("id") %>" class="btn btn-sm btn-warning">✏️</a>
<a href="delete.jsp?id=<%= rs.getInt("id") %>"
class="btn btn-sm btn-danger"
onclick="return confirm('确定删除吗？')">🗑️</a>
</td>
</tr>
<%
}
} catch (Exception e) {
out.print("<tr><td colspan='8' class='text-danger text-center'>查询出错：" + e.getMessage() + "</td></tr>");
} finally {
JDBCUtil.close(null, pstmt, rs);
}
%>
</tbody>
</table>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
3、web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!-- 配置欢迎页面 -->
<welcome-file-list>
<welcome-file>list.jsp</welcome-file>
</welcome-file-list>
</web-app>
别着急运行
4、sql语句部分
可以一句一句的输入
-- 需要您创建数据库和表（MySQL示例）
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
-- 修改数据库字符集（如需要）
ALTER DATABASE mydb
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
-- 修改表字符集
ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 在users表中添加create_time字段
ALTER TABLE users ADD COLUMN create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- 添加新字段到users表
ALTER TABLE users ADD COLUMN age INT, ADD COLUMN gender VARCHAR(10), ADD COLUMN phone VARCHAR(20), MODIFY COLUMN create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
也可以一次性输入
完整的sql语句
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(10),
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
三、总结语
1、准备工作要做好，一步一步来，配置环境设置要细致准确
2、看好代码结构目录，看清楚路径，连接数据库的信息要填写正确
3、sql语句耐心运行
4、patience is key in life
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
