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.Calendar;
import java.util.Collection;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;
import org.mapache.business.BusinessUtils;
import org.mapache.business.MapacheException;
import org.mapache.business.blog.Blog;
import org.mapache.business.category.Category;
import org.mapache.business.topic.Topic;
import org.mapache.data.DAOException;
import org.mapache.data.TopicDAO;
import org.mapache.ui.common.FormatterUtil;
import org.mapache.business.wiki.Parser;
public class MySQLTopicDAO implements TopicDAO {
private PreparedStatement _loadAllTopicsStmt;
private PreparedStatement _loadTopicsForBlogStmt;
private PreparedStatement _saveTopicStmt;
private PreparedStatement _deleteTopicStmt;
private PreparedStatement _createTopicStmt;
private PreparedStatement _loadTopicByTopicIDStmt;
private Connection _oConn;
public MySQLTopicDAO() {
}
public synchronized List<Topic> loadAllTopics() throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadAllTopicsStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
ResultSet resultSet = _loadAllTopicsStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadAllTopicsStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
private void loadResultInList(List<Topic>topics,ResultSet resultSet) throws SQLException,
MapacheException {
while(resultSet.next()){
topics.add(fetchRecordInTopicObject(resultSet));
}
}
private Topic fetchRecordInTopicObject(ResultSet record) throws SQLException,
MapacheException {
Topic topic = new Topic();
topic.setTopicID(record.getInt("TopicID"));
topic.setTitle(record.getString("Title"));
topic.setContent(record.getString("Content"));
topic.setCreationDate(MySQLDataUtils.stringToDate(record.getString("CreationDate")));
topic.setVisible(record.getBoolean("Visible"));
topic.setPublishDate(MySQLDataUtils.stringToDate(record.getString("PublishDate")));
topic.setReplyCount(record.getInt("ReplyCount"));
topic.setCategories(MySQLDAOFactory.getInstance().getCategoryDAO().loadCategoriesForTopic(topic.getTopicID()));
return topic;
}
public synchronized List<Topic> loadTopicsForBlog(int blogID) throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadTopicsForBlogStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE T.BlogID = ? GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
_loadTopicsForBlogStmt.setInt(1,blogID);
ResultSet resultSet = _loadTopicsForBlogStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadTopicsForBlogStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
public synchronized void saveTopic(Topic topic) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
new MySQLCategoryDAO().saveCategoriesForTopic(topic.getCategories(),topic.getTopicID());
_saveTopicStmt = _oConn.prepareStatement("UPDATE Topic SET Title = ?,Content = ?, Visible = ?, PublishDate = ? WHERE TopicID = ?");
_saveTopicStmt.setString(1, topic.getTitle());
_saveTopicStmt.setString(2,topic.getContent());
_saveTopicStmt.setBoolean(3, topic.isVisible());
_saveTopicStmt.setString(4,MySQLDataUtils.convertCalendarToSQLDateString(topic.getPublishDate()));
_saveTopicStmt.setInt(5, topic.getTopicID());
_saveTopicStmt.execute();
_saveTopicStmt.close();
} catch (SQLException e){
throw new MapacheException("Error occurred when saving a topic", e);
} catch (DAOException e) {
throw new MapacheException("Error occurred when saving a topic", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
}
public synchronized void deleteTopic(Topic topic) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_oConn.setAutoCommit(false);
_deleteTopicStmt = _oConn.prepareStatement("DELETE FROM TopicCategory WHERE TopicID = ?");
_deleteTopicStmt.setInt(1, topic.getTopicID());
_deleteTopicStmt.execute();
_deleteTopicStmt = _oConn.prepareStatement("DELETE FROM Topic WHERE TopicID = ?");
_deleteTopicStmt.setInt(1, topic.getTopicID());
_deleteTopicStmt.execute();
_oConn.commit();
_oConn.setAutoCommit(true);
_deleteTopicStmt.close();
} catch (SQLException e) {
throw new MapacheException("Error occurred when deleting a topic", e);
} catch (DAOException e) {
throw new MapacheException("Error occurred when deleting a topic", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
}
public synchronized void createTopic(Topic topic,
int blogID) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_oConn.setAutoCommit(false);
_createTopicStmt = _oConn.prepareStatement("INSERT INTO Topic(Title,Content,Visible,PublishDate,BlogID) Values(?,?,?,?,?)");
_createTopicStmt.setString(1, topic.getTitle());
_createTopicStmt.setString(2,topic.getContent());
_createTopicStmt.setBoolean(3, topic.isVisible());
_createTopicStmt.setString(4,MySQLDataUtils.convertCalendarToSQLDateString(topic.getPublishDate()));
_createTopicStmt.setInt(5, blogID);
_createTopicStmt.execute();
ResultSet s = _createTopicStmt.getGeneratedKeys();
s.next();
int id = s.getInt(1);
MySQLDAOFactory.getInstance().getCategoryDAO().saveCategoriesForTopic(topic.getCategories(),id);
_oConn.commit();
_oConn.setAutoCommit(true);
_createTopicStmt.close();
} catch (SQLException e) {
throw new MapacheException("Error occurred when creating topic", e);
} catch (DAOException e) {
throw new MapacheException("Error occurred when creating topic", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
}
public Topic loadTopicByTopicID(int topicID) throws MapacheException {
Topic topic = null;
try {
_oConn = MySQLDAOFactory.createConnection();
_loadTopicByTopicIDStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE T.TopicID = ? GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
_loadTopicByTopicIDStmt.setInt(1,topicID);
ResultSet resultSet = _loadTopicByTopicIDStmt.executeQuery();
if(resultSet.next())
topic = fetchRecordInTopicObject(resultSet);
_loadTopicByTopicIDStmt.close();
} catch (SQLException e) {
topic=null;
throw new MapacheException("Error occurred when loading topic", e);
} catch (DAOException e) {
topic=null;
throw new MapacheException("Error occurred when loading topic", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topic;
}
public List<Topic> loadAllTopics_visitor() throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadAllTopicsStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE T.Visible = 1 AND T.PublishDate <= CURRENT_TIMESTAMP() GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
ResultSet resultSet = _loadAllTopicsStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadAllTopicsStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
public List<Topic> loadTopicsOfBlog_visitor(int blogID) throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadTopicsForBlogStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE T.BlogID = ? AND T.Visible = 1 AND T.PublishDate <= CURRENT_TIMESTAMP() GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
_loadTopicsForBlogStmt.setInt(1,blogID);
ResultSet resultSet = _loadTopicsForBlogStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadTopicsForBlogStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
public List<Topic> loadTopicsOfCategory_visitor(int categoryID) throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadTopicsForBlogStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID LEFT JOIN TopicCategory TC ON T.TopicID = TC.TopicID WHERE TC.CategoryID=? AND T.Visible = 1 AND T.PublishDate <= CURRENT_TIMESTAMP() GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
_loadTopicsForBlogStmt.setInt(1,categoryID);
ResultSet resultSet = _loadTopicsForBlogStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadTopicsForBlogStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
public Topic loadTopicByTitle_visitor(String topicTitle) throws MapacheException {
Topic topic = null;
try {
_oConn = MySQLDAOFactory.createConnection();
_loadTopicByTopicIDStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE T.Title = ? GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
_loadTopicByTopicIDStmt.setString(1,topicTitle);
ResultSet resultSet = _loadTopicByTopicIDStmt.executeQuery();
if(resultSet.next())
topic = fetchRecordInTopicObject(resultSet);
_loadTopicByTopicIDStmt.close();
} catch (SQLException e) {
topic=null;
throw new MapacheException("Error occurred when loading topic", e);
} catch (DAOException e) {
topic=null;
throw new MapacheException("Error occurred when loading topic", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topic;
}
public String escapeSql(String text,String repl,String with,int max) {
if (text == null || repl == null || with == null || repl.length() == 0 || max == 0) {
return text;
}
StringBuffer buf = new StringBuffer(text.length());
int start = 0, end = 0;
while ((end = text.indexOf(repl, start)) != -1) {
buf.append(text.substring(start, end)).append(with);
start = end + repl.length();
if (--max == 0) {
break;
}
}
buf.append(text.substring(start));
return buf.toString();
}
public List<Topic> loadTopicsBySearch(String[] params) throws MapacheException {
List<Topic> topics = new ArrayList<Topic>();
String like_clause=" ";
for(int i = 0;i<params.length;i++){
if(i!=params.length)
like_clause+="T.Title LIKE '%"+escapeSql(params[i],"'","''",-1)+"%' AND T.Content LIKE '%"+escapeSql(params[i],"'","''",-1)+"%' AND ";
else{
like_clause+="T.Title LIKE '%"+escapeSql(params[i],"'","''",-1)+"%' AND T.Content LIKE '%"+escapeSql(params[i],"'","''",-1)+"%' ";
}
}
try {
_oConn = MySQLDAOFactory.createConnection();
_loadAllTopicsStmt = _oConn.prepareStatement("SELECT T.TopicID as TopicID ,Title,T.Content as Content,T.CreationDate as CreationDate,Visible,PublishDate, count(C.TopicID) as ReplyCount FROM Topic T LEFT JOIN Comment C ON C.TopicID=T.TopicID WHERE"+like_clause+" T.Visible = 1 AND T.PublishDate <= CURRENT_TIMESTAMP() GROUP BY T.TopicID ORDER BY CreationDate DESC, PublishDate DESC");
ResultSet resultSet = _loadAllTopicsStmt.executeQuery();
loadResultInList(topics,resultSet);
_loadAllTopicsStmt.close();
} catch (SQLException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} catch (DAOException e) {
topics = null;
throw new MapacheException("Error occurred when loading topics from database", e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return topics;
}
}