package org.mapache.data.mysql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.mapache.business.MapacheException;
import org.mapache.business.reply.RSSReply;
import org.mapache.business.reply.Reply;
import org.mapache.business.topic.Topic;
import org.mapache.data.DAOException;
import org.mapache.data.ReplyDAO;
import org.mapache.ui.common.FormatterUtil;
public class MySQLReplyDAO implements ReplyDAO {
private Connection _oConn;
private PreparedStatement _loadRepliesOfTopicStmt;
private PreparedStatement _loadAllRepliesStmt;
private PreparedStatement _createReplyStmt;
private PreparedStatement _loadRSSRepliesStmt;
public MySQLReplyDAO(){
}
public List<Reply> loadRepliesOfTopic(Topic topic) throws MapacheException {
List<Reply> replies = new ArrayList<Reply>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadRepliesOfTopicStmt =
_oConn.prepareStatement("SELECT CommentID,Comment,CreationDate,UserName,UserEmail,UserHomePage FROM Comment WHERE TopicID = ? ORDER BY CreationDate ASC");
_loadRepliesOfTopicStmt.setInt(1, topic.getTopicID());
ResultSet resultSet = _loadRepliesOfTopicStmt.executeQuery();
loadResultInList(replies,resultSet);
_loadRepliesOfTopicStmt.close();
} catch (SQLException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} catch (DAOException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return replies;
}
private void loadResultInList(List<Reply> replies, ResultSet resultSet) throws SQLException {
while(resultSet.next()){
replies.add(fetchRecordInReplyObject(resultSet));
}
}
private Reply fetchRecordInReplyObject(ResultSet record) throws SQLException {
Reply reply = new Reply();
reply.setReplyID(record.getInt("CommentID"));
reply.setContent(record.getString("Comment"));
reply.setCreationDate(FormatterUtil.stringToDate(record.getString("CreationDate")));
reply.setUserName(record.getString("UserName"));
reply.setUserEmail(record.getString("UserEmail"));
reply.setUserHomePage(record.getString("UserHomePage"));
return reply;
}
public List<Reply> loadAllReplies() throws MapacheException {
List<Reply> replies = new ArrayList<Reply>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadAllRepliesStmt =
_oConn.prepareStatement("SELECT CommentID,Comment,CreationDate,UserName,UserEmail,UserHomePage FROM Comment ORDER BY CreationDate ASC");
ResultSet resultSet = _loadAllRepliesStmt.executeQuery();
loadResultInList(replies,resultSet);
_loadAllRepliesStmt.close();
} catch (SQLException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} catch (DAOException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return replies;
}
public void createReply(Reply reply, int id) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_createReplyStmt =
_oConn.prepareStatement("INSERT INTO Comment(TopicID,Comment,UserName,UserEmail,UserHomePage) VALUES (?,?,?,?,?)");
_createReplyStmt.setInt(1, id);
_createReplyStmt.setString(2, reply.getContent());
_createReplyStmt.setString(3, reply.getUserName());
_createReplyStmt.setString(4, reply.getUserEmail());
if(reply.getUserHomePage()==null || reply.getUserHomePage().length()==0)
_createReplyStmt.setString(5, "");
else
_createReplyStmt.setString(5, reply.getUserHomePage());
_createReplyStmt.execute();
_createReplyStmt.close();
} catch (SQLException e) {
throw new MapacheException("Error occurred when creating reply", e);
} catch (DAOException e) {
throw new MapacheException("Error occurred when creating reply", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
}
public List<RSSReply> loadAllReplies_feed() throws MapacheException {
List<RSSReply> replies = new ArrayList<RSSReply>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadAllRepliesStmt =
_oConn.prepareStatement("SELECT Title,CommentID,Comment,C.CreationDate as CreationDate,UserName,UserEmail,UserHomePage FROM Comment C JOIN Topic T on T.TopicID=C.TopicID ORDER BY C.CreationDate ASC");
ResultSet resultSet = _loadAllRepliesStmt.executeQuery();
loadResultInRSSList(replies,resultSet);
_loadAllRepliesStmt.close();
} catch (SQLException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} catch (DAOException e) {
replies = null;
throw new MapacheException("Error occurred when loading replies from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return replies;
}
private void loadResultInRSSList(List<RSSReply> replies,
ResultSet resultSet) throws SQLException {
while(resultSet.next()){
replies.add(fetchRecordInRSSReplyObject(resultSet));
}
}
private RSSReply fetchRecordInRSSReplyObject(ResultSet record) throws SQLException {
RSSReply reply = new RSSReply();
reply.setReplyID(record.getInt("CommentID"));
reply.setContent(record.getString("Comment"));
reply.setCreationDate(FormatterUtil.stringToDate(record.getString("CreationDate")));
reply.setUserName(record.getString("UserName"));
reply.setUserEmail(record.getString("UserEmail"));
reply.setUserHomePage(record.getString("UserHomePage"));
reply.setTitle(record.getString("Title"));
return reply;
}
}