当前位置：首页 > 行业动态 > 正文

html调用jsp操作数据库

admin
行业动态
2025-05-01
2310

HTML表单提交数据至JSP，通过JDBC驱动连接数据库，执行SQL语句实现

HTML调用JSP操作数据库实现流程

环境准备

组件	版本/要求	说明
应用服务器	Apache Tomcat 9+	支持JSP/Servlet规范
数据库	MySQL 8.0+	示例使用关系型数据库
JDBC驱动	mysql-connector-java 8.0+	放入WEB-INF/lib目录
开发工具	Eclipse/IntelliJ IDEA	支持Java Web开发的IDE
项目结构	Dynamic Web Project	包含WebContent/WEB-INF目录

实现步骤

创建数据库表

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

配置JDBC连接

// DBUtil.java
public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); // 加载驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

创建数据访问对象(DAO)

// UserDAO.java
public class UserDAO {
    public void addUser(User user) {
        String sql = "INSERT INTO users(username,password,email) VALUES(?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT  FROM users";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
}

创建实体类

// User.java
public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    // getters and setters
}

创建Servlet处理请求

// UserServlet.java
@WebServlet("/user")
public class UserServlet extends HttpServlet {
    private UserDAO userDAO = new UserDAO();
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
        throws ServletException, IOException {
        // 设置编码
        request.setCharacterEncoding("UTF-8");
        // 获取表单参数
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String email = request.getParameter("email");
        // 创建用户对象
        User user = new User();
        user.setUsername(username);
        user.setPassword(password);
        user.setEmail(email);
        // 调用DAO保存数据
        userDAO.addUser(user);
        // 转发到成功页面
        request.setAttribute("msg", "用户添加成功");
        request.getRequestDispatcher("/success.jsp").forward(request, response);
    }
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
        throws ServletException, IOException {
        List<User> users = userDAO.getAllUsers();
        request.setAttribute("users", users);
        request.getRequestDispatcher("/list.jsp").forward(request, response);
    }
}

创建JSP视图页面

<!-add_user.jsp -->
<html>
<head><title>添加用户</title></head>
<body>
    <h2>用户注册</h2>
    <form action="user" method="post">
        用户名: <input type="text" name="username"><br>
        密码: <input type="password" name="password"><br>
        邮箱: <input type="email" name="email"><br>
        <input type="submit" value="提交">
    </form>
</body>
</html>

<!-list.jsp -->
<html>
<head><title>用户列表</title></head>
<body>
    <h2>用户列表</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>用户名</th>
            <th>邮箱</th>
        </tr>
        <c:forEach var="user" items="${users}">
            <tr>
                <td>${user.id}</td>
                <td>${user.username}</td>
                <td>${user.email}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

关键技术点说明

技术点	说明
请求转发	使用`request.getRequestDispatcher().forward()`实现零跳转
字符编码	必须设置`request.setCharacterEncoding("UTF-8")`防止中文乱码
预编译语句	使用`PreparedStatement`防止SQL注入
自动资源管理	使用try-with-resources确保数据库连接自动关闭
JSP标准标签库	使用JSTL标签(`<c:forEach>`)简化集合遍历
MVC分层架构	JSP仅负责视图展示，业务逻辑通过Servlet处理，数据访问通过DAO完成