Struts 文件上傳實戰 (七)：TrainPlanDAO 數據訪問層完整實現

DAO 類職責與設計模式

TrainPlanDAO 封裝 KS_TRAINPLAN 表的所有 CRUD 操作，採用 Template Method 風味的固定 SQL 模式，連接管理採用簡易單例模式（構造函數獲取連接，方法內複用）。

完整代碼解析（關鍵片段）

package tester.business.maitain;

import tclcc.tester.business.maitain.Trainplan;
import tclcc.tester.util.DBConn;
import java.sql.*;
import java.util.*;

public class TrainPlanDAO {
    private DBConn dbconn = null;
    private Connection conn = null;

    // SQL 常量（使用 PreparedStatement 佔位符 ? 防注入）
    private static final String ADD_TRAINPLAN =
        "INSERT INTO KS_TRAINPLAN (p_id, p_title, issue_time, issuer, p_content, p_accessory) " +
        "VALUES (lpad(SEQ_P_ID.nextVal, 10, '0'), ?, ?, to_date(?, 'yyyy-mm-dd'), ?, ?)";
    
    private static final String UPDATE_TRAINPLAN =
        "UPDATE KS_TRAINPLAN SET p_title=?, post_index=?, issue_time=to_date(?, 'yyyy-mm-dd'), " +
        "issuer=?, p_content=?, p_accessory=? WHERE p_id=?";

    // 構造函數獲取連接（簡易單例，實際應用連接池）
    public TrainPlanDAO() {
        try {
            dbconn = new DBConn();
            conn = dbconn.getConnection();
        } catch (Exception ex) { ex.printStackTrace(); }
    }

    // 新增
    public void addTrainPlan(Trainplan trainPlan) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(ADD_TRAINPLAN);
            pstmt.setString(1, trainPlan.getP_title());
            pstmt.setString(2, trainPlan.getIssue_time());
            pstmt.setString(3, trainPlan.getIssuer());
            pstmt.setString(4, trainPlan.getP_content());
            pstmt.setString(5, trainPlan.getP_accessory());
            pstmt.executeUpdate();
        } catch (SQLException ex) { ex.printStackTrace(); }
        finally { closeQuietly(pstmt); /* 不關閉 conn，複用 */ }
    }

    // 刪除（重載）
    public void removeTrainPlan(int p_id) throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM ks_trainplan WHERE p_id=" + p_id); // 風險：SQL 注入
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new SQLException("SQLExction on: TrainPlanDAO.removeTrainPlan()");
        } finally {
            closeQuietly(stmt);
            closeQuietly(conn); // 刪除後關閉連接，導致後續方法無法復用
        }
    }

    // 更新（異常處理較完善：鏈式 SQLException 遍歷）
    public void updateTrainPlan(Trainplan trainPlan) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(UPDATE_TRAINPLAN);
            pstmt.setString(1, trainPlan.getP_title());
            pstmt.setInt(2, trainPlan.getPost_index());
            pstmt.setString(3, trainPlan.getIssue_time());
            pstmt.setString(4, trainPlan.getIssuer());
            pstmt.setString(5, trainPlan.getP_content());
            pstmt.setString(6, trainPlan.getP_accessory());
            pstmt.setInt(7, trainPlan.getP_id());
            pstmt.executeUpdate();
        } catch (SQLException sqle) {
            do {
                System.err.println("Message: " + sqle.getMessage());
                System.err.println("SQLState: " + sqle.getSQLState());
                System.err.println("ErrorCode: " + sqle.getErrorCode());
            } while ((sqle = sqle.getNextException()) != null);
        } finally {
            closeQuietly(pstmt);
            closeQuietly(conn);
        }
    }

    // 查詢所有
    public Collection getAll() throws SQLException {
        Statement stmt = null; ResultSet rs = null;
        Collection list = new ArrayList();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM ks_trainplan");
            while (rs.next()) {
                Trainplan tp = new Trainplan();
                tp.setP_id(rs.getInt("P_ID"));
                tp.setP_title(rs.getString("P_TITLE"));
                tp.setPost_index(rs.getInt("POST_INDEX"));
                tp.setIssue_time(rs.getString("ISSUE_TIME"));
                tp.setIssuer(rs.getString("ISSUER"));
                tp.setP_content(rs.getString("P_CONTENT"));
                tp.setP_accessory(rs.getString("P_ACCESSORY"));
                list.add(tp);
            }
        } catch (SQLException ex) { /* 吞沒異常 */ }
        finally {
            closeQuietly(rs); closeQuietly(stmt); closeQuietly(conn);
            return list;
        }
    }

    // 輔助關閉方法（建議提取至基類/工具類）
    private void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignore) {}
    }
}

核心缺陷與重構要點

現代化對比：Spring JDBC Template 版

@Repository
public class TrainPlanJdbcDao {
    @Autowired private JdbcTemplate jdbcTemplate;
    @Autowired private GeneratedKeyHolder keyHolder; // Oracle Sequence 需特殊處理

    public Trainplan insert(Trainplan plan) {
        String sql = "INSERT INTO KS_TRAINPLAN (p_id, p_title, issue_time, issuer, p_content, p_accessory) " +
                     "VALUES (SEQ_P_ID.nextVal, ?, ?, to_date(?, 'yyyy-mm-dd'), ?, ?)";
        jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, new String[]{"p_id"});
            ps.setString(1, plan.getP_title());
            ps.setString(2, plan.getIssue_time());
            ps.setString(3, plan.getIssuer());
            ps.setString(4, plan.getP_content());
            ps.setString(5, plan.getP_accessory());
            return ps;
        }, keyHolder);
        plan.setP_id(keyHolder.getKey().intValue());
        return plan;
    }

    public List findAll(Pageable pageable) {
        String sql = "SELECT * FROM KS_TRAINPLAN ORDER BY p_id DESC";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Trainplan t = new Trainplan();
            t.setP_id(rs.getInt("P_ID"));
            t.setP_title(rs.getString("P_TITLE"));
            // ... 其他字段
            return t;
        }, pageable);
    }
}

經典 JDBC 代碼是理解框架底層的最佳教材；實際項目請擁抱成熟框架，專注業務創新。