/**
* Method declaration
*
* @param con
* @param questionContainerPK
* @throws SQLException
* @see
*/
public static void openQuestionContainer(Connection con, QuestionContainerPK questionContainerPK)
throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.openQuestionContainer()",
"root.MSG_GEN_ENTER_METHOD",
"questionContainerPK = " + questionContainerPK);
String updateStatement =
"update "
+ questionContainerPK.getTableName()
+ " set qcIsClosed = 0 , instanceId = ?"
+ " where qcId = ? ";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setString(1, questionContainerPK.getComponentName());
prepStmt.setInt(2, Integer.parseInt(questionContainerPK.getId()));
prepStmt.executeUpdate();
} finally {
DBUtil.close(prepStmt);
}
}
/**
* Method declaration
*
* @param con
* @param questionContainerPK
* @return
* @throws SQLException
* @see
*/
public static QuestionContainerHeader getQuestionContainerHeader(
Connection con, QuestionContainerPK questionContainerPK) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.getQuestionContainerHeader()",
"root.MSG_GEN_ENTER_METHOD",
"questionContainerPK = " + questionContainerPK);
ResultSet rs = null;
QuestionContainerHeader questionContainerHeader = null;
String selectStatement =
"select "
+ QUESTIONCONTAINERCOLUMNNAMES
+ " from "
+ questionContainerPK.getTableName()
+ " where qcId = ? ";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setInt(1, Integer.parseInt(questionContainerPK.getId()));
rs = prepStmt.executeQuery();
if (rs.next()) {
questionContainerHeader = getQuestionContainerHeaderFromResultSet(rs, questionContainerPK);
}
} finally {
DBUtil.close(rs, prepStmt);
}
return questionContainerHeader;
}
/**
* Method declaration
*
* @param con
* @param qcPK
* @return
* @throws SQLException
* @see
*/
public static Collection<QuestionContainerHeader> getInWaitQuestionContainers(
Connection con, QuestionContainerPK qcPK) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.getInWaitQuestionContainers()",
"root.MSG_GEN_ENTER_METHOD",
"qcPK = " + qcPK);
ResultSet rs = null;
QuestionContainerHeader header = null;
String selectStatement =
"select "
+ QUESTIONCONTAINERCOLUMNNAMES
+ " from "
+ qcPK.getTableName()
+ " where ? < qcBeginDate and instanceId = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, formatter.format(new java.util.Date()));
prepStmt.setString(2, qcPK.getComponentName());
rs = prepStmt.executeQuery();
List<QuestionContainerHeader> list = new ArrayList<QuestionContainerHeader>();
while (rs.next()) {
header = getQuestionContainerHeaderFromResultSet(rs, qcPK);
list.add(header);
}
return list;
} finally {
DBUtil.close(rs, prepStmt);
}
}
public static Collection<QuestionContainerHeader> getQuestionContainers(
Connection con, List<QuestionContainerPK> pks) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.getQuestionContainers()",
"root.MSG_GEN_ENTER_METHOD",
"pks = " + pks.toString());
ResultSet rs = null;
QuestionContainerHeader questionContainerHeader = null;
QuestionContainerPK questionContainerPK = new QuestionContainerPK("unknown");
List<QuestionContainerHeader> list = new ArrayList<QuestionContainerHeader>();
StringBuffer whereClause = new StringBuffer();
if (pks != null && !pks.isEmpty()) {
Iterator<QuestionContainerPK> it = pks.iterator();
QuestionContainerPK pk = null;
whereClause.append("(");
while (it.hasNext()) {
pk = it.next();
whereClause.append(" qcId = ").append(pk.getId());
if (it.hasNext()) {
whereClause.append(" or ");
} else {
whereClause.append(" ) ");
}
}
String selectStatement =
"select "
+ QUESTIONCONTAINERCOLUMNNAMES
+ " from "
+ questionContainerPK.getTableName()
+ " where "
+ whereClause.toString()
+ " and instanceId = '"
+ pk.getComponentName()
+ "' order by qcBeginDate DESC, qcEndDate DESC";
Statement stmt = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(selectStatement);
while (rs.next()) {
questionContainerHeader = getQuestionContainerHeaderFromResultSet(rs, pk);
list.add(questionContainerHeader);
}
} finally {
DBUtil.close(rs, stmt);
}
}
return list;
}
/**
* Method declaration
*
* @param con
* @param comment
* @throws SQLException
* @see
*/
public static void addComment(Connection con, Comment comment) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.addComment()",
"root.MSG_GEN_ENTER_METHOD",
"comment = " + comment);
QuestionContainerPK questionContainerPK = comment.getQuestionContainerPK();
CommentPK commentPK = new CommentPK("unknown", questionContainerPK);
int newId = 0;
String insertStatement =
"insert into " + commentPK.getTableName() + " values(?, ?, ?, ?, ?, ?) ";
try {
/* Recherche de la nouvelle PK de la table */
newId = DBUtil.getNextId(commentPK.getTableName(), "commentId");
} catch (Exception e) {
throw new QuestionContainerRuntimeException(
"QuestionContainerDAO.addComment()",
SilverpeasRuntimeException.ERROR,
"root.EX_GET_NEXTID_FAILED",
e);
}
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(insertStatement);
prepStmt.setInt(1, newId);
prepStmt.setInt(2, Integer.parseInt(questionContainerPK.getId()));
prepStmt.setString(3, comment.getUserId());
prepStmt.setString(4, comment.getComment());
if (comment.isAnonymous()) {
prepStmt.setInt(5, 1);
} else {
prepStmt.setInt(5, 0);
}
prepStmt.setString(6, formatter.format(new java.util.Date()));
prepStmt.executeUpdate();
} finally {
DBUtil.close(prepStmt);
}
}
/**
* Method declaration
*
* @param rs
* @param questionContainerPK
* @return
* @throws SQLException
* @see
*/
public static QuestionContainerHeader getQuestionContainerHeaderFromResultSet(
ResultSet rs, QuestionContainerPK questionContainerPK) throws SQLException {
String id = Integer.toString(rs.getInt(1));
String title = rs.getString(2);
String description = rs.getString(3);
String comment = rs.getString(4);
String creatorId = rs.getString(5);
String creationDate = rs.getString(6);
String beginDate = rs.getString(7);
if (beginDate.equals(nullBeginDate)) {
beginDate = null;
}
String endDate = rs.getString(8);
if (endDate.equals(nullEndDate)) {
endDate = null;
}
int isClosed = rs.getInt(9);
boolean closed = false;
if (isClosed == 1) {
closed = true;
}
int nbVoters = rs.getInt(10);
int nbQuestionsPage = rs.getInt(11);
int nbMaxParticipations = rs.getInt(12);
int nbParticipationsBeforeSolution = rs.getInt(13);
int maxTime = rs.getInt(14);
boolean anonymous = (rs.getInt(15) == 1);
String instanceId = rs.getString(16);
questionContainerPK.setComponentName(instanceId);
QuestionContainerHeader result =
new QuestionContainerHeader(
new QuestionContainerPK(id, questionContainerPK),
title,
description,
comment,
creatorId,
creationDate,
beginDate,
endDate,
closed,
nbVoters,
nbQuestionsPage,
nbMaxParticipations,
nbParticipationsBeforeSolution,
maxTime,
anonymous);
return result;
}
/**
* Method declaration
*
* @param con
* @param questionContainerPK
* @throws SQLException
* @see
*/
public static void deleteQuestionContainerHeader(
Connection con, QuestionContainerPK questionContainerPK) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.deleteQuestionContainerHeader()",
"root.MSG_GEN_ENTER_METHOD",
"questionContainerPK = " + questionContainerPK);
String deleteStatement =
"delete from " + questionContainerPK.getTableName() + " where qcId = ? ";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(deleteStatement);
prepStmt.setInt(1, Integer.parseInt(questionContainerPK.getId()));
prepStmt.executeUpdate();
} finally {
DBUtil.close(prepStmt);
}
}
/**
* Method declaration
*
* @param con
* @param questionContainerPK
* @return
* @throws SQLException
* @see
*/
public static Collection<QuestionContainerHeader> getNotClosedQuestionContainers(
Connection con, QuestionContainerPK questionContainerPK) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.getNotClosedQuestionContainers()",
"root.MSG_GEN_ENTER_METHOD",
"questionContainerPK = " + questionContainerPK);
ResultSet rs = null;
QuestionContainerHeader questionContainerHeader = null;
String selectStatement =
"select "
+ QUESTIONCONTAINERCOLUMNNAMES
+ " from "
+ questionContainerPK.getTableName()
+ " where instanceId = '"
+ questionContainerPK.getComponentName()
+ "' "
+ " and qcIsClosed = 0 order by qcBeginDate DESC, qcEndDate DESC";
Statement stmt = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(selectStatement);
List<QuestionContainerHeader> list = new ArrayList<QuestionContainerHeader>();
while (rs.next()) {
questionContainerHeader = getQuestionContainerHeaderFromResultSet(rs, questionContainerPK);
list.add(questionContainerHeader);
}
return list;
} finally {
DBUtil.close(rs, stmt);
}
}
/**
* Method declaration
*
* @param con
* @param qcPK
* @return
* @throws SQLException
* @see
*/
public static Collection<Comment> getComments(Connection con, QuestionContainerPK qcPK)
throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.getComments()",
"root.MSG_GEN_ENTER_METHOD",
"qcPK = " + qcPK);
ResultSet rs = null;
Comment comment = null;
CommentPK commentPK = new CommentPK("unknown", qcPK);
String selectStatement =
"select "
+ COMMENTCOLUMNNAMES
+ " from "
+ commentPK.getTableName()
+ " where commentFatherId = ? "
+ " order by commentDate DESC";
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setInt(1, Integer.parseInt(qcPK.getId()));
rs = prepStmt.executeQuery();
List<Comment> list = new ArrayList<Comment>();
while (rs.next()) {
comment = getCommentFromResultSet(rs, qcPK);
list.add(comment);
}
return list;
} finally {
DBUtil.close(rs, prepStmt);
}
}
/**
* Method declaration
*
* @param con
* @param questionContainerHeader
* @return
* @throws SQLException
* @see
*/
public static QuestionContainerPK createQuestionContainerHeader(
Connection con, QuestionContainerHeader questionContainerHeader) throws SQLException {
SilverTrace.info(
"questionContainer",
"QuestionContainerDAO.createQuestionContainerHeader()",
"root.MSG_GEN_ENTER_METHOD",
"questionContainerHeader = " + questionContainerHeader);
int newId = 0;
String insertStatement =
"insert into "
+ questionContainerHeader.getPK().getTableName()
+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
try {
/* Retrieve next sequence identifier */
newId = DBUtil.getNextId(questionContainerHeader.getPK().getTableName(), "qcId");
} catch (Exception e) {
throw new QuestionContainerRuntimeException(
"QuestionContainerDAO.createQuestionContainerHeader()",
SilverpeasRuntimeException.ERROR,
"root.EX_GET_NEXTID_FAILED",
e);
}
QuestionContainerPK questionContainerPK = questionContainerHeader.getPK();
questionContainerPK.setId(Integer.toString(newId));
PreparedStatement prepStmt = null;
try {
prepStmt = con.prepareStatement(insertStatement);
prepStmt.setInt(1, newId);
prepStmt.setString(2, questionContainerHeader.getTitle());
prepStmt.setString(3, questionContainerHeader.getDescription());
prepStmt.setString(4, questionContainerHeader.getComment());
prepStmt.setString(5, questionContainerHeader.getCreatorId());
prepStmt.setString(6, formatter.format(new java.util.Date()));
if (questionContainerHeader.getBeginDate() == null) {
prepStmt.setString(7, nullBeginDate);
} else {
prepStmt.setString(7, questionContainerHeader.getBeginDate());
}
if ((questionContainerHeader.getEndDate() == null)
|| (questionContainerHeader.getEndDate().length() < 10)) {
prepStmt.setString(8, nullEndDate);
} else {
prepStmt.setString(8, questionContainerHeader.getEndDate());
}
prepStmt.setInt(9, 0);
prepStmt.setInt(10, 0);
prepStmt.setInt(11, questionContainerHeader.getNbQuestionsPerPage());
prepStmt.setInt(12, questionContainerHeader.getNbMaxParticipations());
prepStmt.setInt(13, questionContainerHeader.getNbParticipationsBeforeSolution());
prepStmt.setInt(14, questionContainerHeader.getMaxTime());
prepStmt.setString(15, questionContainerHeader.getPK().getComponentName());
if (questionContainerHeader.isAnonymous()) {
prepStmt.setInt(16, 1);
} else {
prepStmt.setInt(16, 0);
}
prepStmt.executeUpdate();
} finally {
DBUtil.close(prepStmt);
}
return questionContainerPK;
}