Struts 文件上傳實戰（七）：TrainPlanDAO 數據訪問層實現

TrainPlanDAO.java 完整代碼

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;
    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 final static String UPDATE_TRAINPLAN =
        "UPDATE KS_TRAINPLAN set p_id=?,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 { try { pstmt.close(); } catch (SQLException ignored) {} }
    }

    public void removeTrainPlan(Trainplan trainPlan) throws SQLException {
        removeTrainPlan(trainPlan.getP_id());
    }
    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);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new SQLException("SQLExction on: TrainPlanDAO.removeExaminee()");
        } finally { try { conn.close(); } catch (SQLException ignored) {} }
    }

    public void updateTrainPlan(Trainplan trainPlan) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(UPDATE_TRAINPLAN);
            pstmt.setInt(1, trainPlan.getP_id());
            pstmt.setString(2, trainPlan.getP_title());
            pstmt.setInt(3, trainPlan.getPost_index());
            pstmt.setString(4, trainPlan.getIssue_time());
            pstmt.setString(5, trainPlan.getIssuer());
            pstmt.setString(6, trainPlan.getP_content());
            pstmt.setString(7, trainPlan.getP_accessory());
            pstmt.setInt(8, trainPlan.getP_id());
            pstmt.executeUpdate();
        } catch (SQLException sqle) {
            do {
                System.err.println("Message: " + sqle.getMessage());
                System.err.println("SQLState: " + sqle.getSQLState());
                System.err.println("VendorCode: " + sqle.getErrorCode());
            } while ((sqle = sqle.getNextException()) != null);
        } finally { try { conn.close(); } catch (SQLException ignored) {} }
    }

    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);
            }
        } finally {
            try { conn.close(); } catch (SQLException ignored) {}
            return list;
        }
    }

    public static String toString(Trainplan tp) { return tp.getP_title(); }
}

設計要點

• 使用序列 SEQ_P_ID.nextVal 生成主鍵，配合 lpad 補零。
• 日期轉換 to_date(?,'yyyy-mm-dd') 確保格式一致。
• 異常處理：update 方法遍歷 SQLException 鏈打印詳細錯誤碼。
• 連接管理：構造函數獲取連接，各方法執行後關閉（簡易模式，生產建議使用連接池）。