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=?"; /** * get a connection from a DB pool * @return Connection */ public TrainPlanDAO() { try { dbconn = new DBConn(); conn = dbconn.getConnection(); } catch (Exception ex) { ex.printStackTrace(); } } /** * add a row into DB * @param examinee Examinee * @throws SQLException */ 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(); //conn.close(); } catch (SQLException ex1) { } } } /** * remove a row from DB * @param trainPlan TrainPlan * @throws SQLException */ public void removeTrainPlan(Trainplan trainPlan) throws SQLException { this.removeTrainPlan(trainPlan.getP_id()); } /** * remove a row from DB * @param p_id int * @throws SQLException */ 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 ex1) { ex1.printStackTrace(); } } } /** * update a row * @param examinee Examinee * @throws SQLException */ public void updateTrainPlan(Trainplan trainPlan) throws SQLException { PreparedStatement pstmt = null; try { // UPDATE_TRAINPLAN = UPDATE_TRAINPLAN + "where p_id =" + trainPlan.getP_id(); //the condition need modification 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 ex) { ex.getStackTrace(); throw new SQLException("SQLExction on : TrainPlanDAO.updateExaminee()"); } */ catch(SQLException sqle){ do { System.err.println("Exception occoured:Message:"+sqle.getMessage()); System.err.println("SQL state:"+sqle.getSQLState()); System.err.println("Vendor code:"+sqle.getErrorCode()+"---------------"); } while((sqle=sqle.getNextException())!=null); }finally { try { conn.close(); } catch (SQLException ex1) { ex1.printStackTrace(); } } } /** * get all record from DB * @throws SQLException * @return Collection */ public Collection getAll() throws SQLException { Statement stmt = null; ResultSet rs = null; Trainplan trainPlan = null; Collection list = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM ks_trainplan"); list = new ArrayList(); while (rs.next()) { trainPlan = new Trainplan(); trainPlan.setP_id(rs.getInt("P_ID")); trainPlan.setP_title(rs.getString("P_TITLE")); trainPlan.setPost_index(rs.getInt("POST_INDEX")); trainPlan.setIssue_time(rs.getString("ISSUE_TIME")); trainPlan.setIssuer(rs.getString("ISSUER")); trainPlan.setP_content(rs.getString("P_CONTENT")); trainPlan.setP_accessory(rs.getString("P_ACCESSORY")); list.add(trainPlan); } } catch (SQLException ex) { } finally { try { conn.close(); } catch (SQLException ex1) { ex1.printStackTrace(); } return list; } } public static String toString(Trainplan trainPlan){ return trainPlan.getP_title(); } /** * for test * @param args String[] */}