Java Web Servlet 實作 Oracle CRUD 完整教學：從資料庫建立到部署

前言

本文透過完整的 Java Web 專案實例，示範如何使用 Servlet + JDBC 實作 Oracle 資料庫的基本增刪改查（CRUD）功能。涵蓋資料庫建立、專案結構、Servlet 實作、JSP 頁面、web.xml 配置等完整流程，適合初學者從零建立第一個資料庫 Web 應用。

開發環境與資料庫準備

資料表建立腳本

CREATE TABLE student (
    id VARCHAR2(50) PRIMARY KEY NOT NULL,
    name VARCHAR2(50) NOT NULL,
    class_grade VARCHAR2(50) NOT NULL,
    result VARCHAR2(12) NOT NULL
);

CREATE SEQUENCE perseq;

INSERT INTO student (id, name, class_grade, result)
VALUES (perseq.nextval, '張三', '33', '98');

專案結構

src/
└── org.lsy.servlet/
    ├── MMC_01.java      // 主頁面：列表、刪除連結、新增連結
    ├── AddPage.java     // 新增頁面表單
    ├── Add.java         // 新增處理
    ├── UpdatePage.java  // 修改頁面表單（帶入原資料）
    ├── Update.java      // 修改處理
    └── Delete.java      // 刪除處理
WebContent/
├── WEB-INF/
│   └── web.xml
└── index.jsp            // 歡迎頁面

核心 Servlet 實作

1. 資料庫連線常數（建議抽取至基底類別或配置檔）

public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:LIUSY";
public static final String DB_USER = "scott";
public static final String DB_PASS = "tiger";

2. MMC_01.java - 主列表頁面

@WebServlet("/MMC_01")
public class MMC_01 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out = resp.getWriter();

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM student")) {

            out.println("");
            out.println("");

            while (rs.next()) {
                String id = rs.getString("id");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
            }
            out.println("");
            out.println("
姓名
班級
成績
操作
" + rs.getString("name") + "
" + rs.getString("class_grade") + "
" + rs.getString("result") + "
");
                out.println("修改 | ");
                out.println("刪除");
                out.println("
新增學生
");

        } catch (Exception e) {
            e.printStackTrace();
            out.println("系統錯誤：" + e.getMessage());
        }
    }
}

3. AddPage.java / Add.java - 新增功能

// AddPage.java - 顯示新增表單
@WebServlet("/AddPage")
public class AddPage extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out = resp.getWriter();
        out.println("");
        out.println("
");
        out.println("姓名：
");
        out.println("班級：
");
        out.println("成績：
");
        out.println("");
        out.println("
");
    }
}

// Add.java - 處理新增
@WebServlet("/Add")
public class Add extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        req.setCharacterEncoding("UTF-8");
        String name = req.getParameter("name");
        String grade = req.getParameter("class_grade");
        String result = req.getParameter("result");

        String sql = "INSERT INTO student (id, name, class_grade, result) " +
                     "VALUES (perseq.nextval, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, grade);
            ps.setString(3, result);
            int rows = ps.executeUpdate();
            resp.getWriter().println(rows > 0 ? "新增成功 返回列表" : "新增失敗");
        } catch (Exception e) {
            e.printStackTrace();
            resp.getWriter().println("錯誤：" + e.getMessage());
        }
    }
}

4. UpdatePage.java / Update.java - 修改功能

// UpdatePage.java - 顯示修改表單（帶入原資料）
@WebServlet("/UpdatePage")
public class UpdatePage extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String id = req.getParameter("id");
        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out = resp.getWriter();

        String sql = "SELECT * FROM student WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                out.println("
");
                out.println("姓名：
");
                out.println("班級：
");
                out.println("成績：
");
                out.println("");
                out.println("");
                out.println("
");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

// Update.java - 處理修改
@WebServlet("/Update")
public class Update extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        req.setCharacterEncoding("UTF-8");
        String id = req.getParameter("id");
        String name = req.getParameter("name");
        String grade = req.getParameter("class_grade");
        String result = req.getParameter("result");

        String sql = "UPDATE student SET name=?, class_grade=?, result=? WHERE id=?";
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, grade);
            ps.setString(3, result);
            ps.setString(4, id);
            int rows = ps.executeUpdate();
            resp.getWriter().println(rows > 0 ? "修改成功 返回列表" : "修改失敗");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5. Delete.java - 刪除功能

@WebServlet("/Delete")
public class Delete extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String id = req.getParameter("id");
        String sql = "DELETE FROM student WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, id);
            int rows = ps.executeUpdate();
            resp.sendRedirect("MMC_01"); // 重導向列表頁
        } catch (Exception e) {
            e.printStackTrace();
            resp.getWriter().println("刪除失敗：" + e.getMessage());
        }
    }
}

web.xml 配置（Servlet 3.0+ 可用 @WebServlet 註解替代）

    
        index.jsp
    
    

關鍵改進建議（生產環境必做）

1. 連線池：使用 HikariCP / Druid / DBCP 替代 DriverManager.getConnection
2. DAO 層分離：將 SQL 操作封裝於 StudentDAO，Servlet 僅處理請求回應
3. PreparedStatement 防注入：所有參數化查詢，絕不拼接 SQL 字串
4. 異常統一處理：@ControllerAdvice / Filter 捕獲異常，回傳友善錯誤頁
5. 編碼過濾器：CharacterEncodingFilter 統一 UTF-8
6. JSP 替代印出 HTML：使用 JSTL + EL 表達式渲染頁面
7. 資源關閉：try-with-resources 確保連線、Statement、ResultSet 正確關閉

效果展示

總結

本文提供了 Servlet + JDBC 實作 Oracle CRUD 的完整可執行範例。雖然現代開發多採用 Spring Boot + MyBatis/JPA，但理解 Servlet 原生寫法有助於掌握 Web 核心機制、除錯框架問題、維護舊系統。建議學習者以此為基礎，逐步引入連線池、DAO 模式、MVC 分層，演進至現代框架開發模式。