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[]
*/
}
