Java Servlet + JDBC + JSP 實現資料庫 CRUD 完整範例：MySQL 資料顯示與即時更新

架構分層設計

┌─────────────┐     ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   JSP 頁面   │ ←→  │  Servlet    │ ←→  │  Service/DAO │ ←→  │  MySQL DB   │
│  (View)      │     │ (Controller)│     │  (Model)     │     │  (Data)     │
└─────────────┘     └─────────────┘     └─────────────┘     └─────────────┘

核心元件程式碼

1. 實體類

public class User {
    private int id;
    private String username;
    private String email;
    // Getter/Setter/建構子/toString 省略
}

2. DAO 介面與實作

public interface UserDao {
    List findAll();
    boolean update(User user);
}

public class UserDaoImpl implements UserDao {
    private DataSource dataSource; // 注入連線池
    
    @Override
    public List findAll() {
        String sql = "SELECT id, username, email FROM users";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            List list = new ArrayList<>();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setUsername(rs.getString("username"));
                u.setEmail(rs.getString("email"));
                list.add(u);
            }
            return list;
        } catch (SQLException e) { throw new RuntimeException(e); }
    }
    
    @Override
    public boolean update(User user) {
        String sql = "UPDATE users SET username=?, email=? WHERE id=?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
            ps.setInt(3, user.getId());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) { throw new RuntimeException(e); }
    }
}

3. Servlet 控制器

@WebServlet("/users")
public class UserServlet extends HttpServlet {
    @Inject UserDao userDao; // 或手動 new UserDaoImpl()
    
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        String action = req.getParameter("action");
        if ("edit".equals(action)) {
            // 轉發到編輯頁
            req.setAttribute("user", userDao.findById(Integer.parseInt(req.getParameter("id"))));
            req.getRequestDispatcher("/edit.jsp").forward(req, resp);
        } else {
            // 列表頁
            req.setAttribute("users", userDao.findAll());
            req.getRequestDispatcher("/list.jsp").forward(req, resp);
        }
    }
    
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        User user = new User();
        user.setId(Integer.parseInt(req.getParameter("id")));
        user.setUsername(req.getParameter("username"));
        user.setEmail(req.getParameter("email"));
        
        boolean ok = userDao.update(user);
        resp.sendRedirect(req.getContextPath() + "/users?msg=" + (ok ? "success" : "fail"));
    }
}

4. JSP 列表頁

<%@ taglib prefix="c" uri="https://java.sun.com/jsp/jstl/core" %>
<table border="1">
  <tr><th>ID</th><th>用戶名</th><th>Email</th><th>操作</th></tr>
  <c:forEach items="${users}" var="u">
  <tr>
    <td>${u.id}</td>
    <td>${u.username}</td>
    <td>${u.email}</td>
    <td>
      <a href="users?action=edit&id=${u.id}">編輯</a>
    </td>
  </tr>
  </c:forEach>
</table>

5. JSP 編輯頁

<form action="users" method="post">
  <input type="hidden" name="id" value="${user.id}">
  用戶名: <input name="username" value="${user.username}"><br>
  Email: <input name="email" value="${user.email}"><br>
  <button type="submit">更新</button>
</form>

關鍵最佳實踐

• 連線池：務必使用 HikariCP/Druid，禁用 DriverManager.getConnection。
• 預處理語句：防 SQL 注入、提升效能。
• Try-with-resources：JDK 7+ 自動關閉資源，避免洩漏。
• 字元編碼：req.setCharacterEncoding("UTF-8") + 資料庫 utf8mb4。
• 事務控制：更新多張表時，在 Service 層開啟事務。

可執行專案下載

完整 Maven 專案 (含 SQL 腳本、Tomcat 插件) 參考：CSDN 下載鏈結