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.Collection;
import java.util.List;
import org.mapache.business.MapacheException;
import org.mapache.business.blog.Blog;
import org.mapache.business.category.Category;
import org.mapache.business.user.User;
import org.mapache.data.BlogDAO;
import org.mapache.data.DAOException;
public class MySQLBlogDAO implements BlogDAO {
private Connection _oConn;
private PreparedStatement _loadAllBlogsStmt, _saveBlogStmt,_deleteBlogStmt,_createBlogStmt,_loadBlogByBlogIDStmt;
public MySQLBlogDAO() {
}
public synchronized List<Blog> loadAllBlogs() throws MapacheException {
List<Blog> blogs = new ArrayList<Blog>();
try{
_oConn = MySQLDAOFactory.createConnection();
_loadAllBlogsStmt = _oConn.prepareStatement("SELECT B.BlogID as BlogID," +
"B.Title as Title,Description," +
"count(T.BlogID) as TopicCount FROM Blog B " +
"LEFT JOIN Topic T on B.BlogID = T.BlogID GROUP BY B.BlogID");
ResultSet resultSet = _loadAllBlogsStmt.executeQuery();
loadResultInList(blogs,resultSet);
_loadAllBlogsStmt.close();
}
catch(SQLException e){
blogs=null;
throw new MapacheException("Error occurred when loading blogs from database!",e);
}
catch (DAOException e) {
blogs=null;
throw new MapacheException("Error occurred when loading blogs from database!",e);
}
finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return blogs;
}
private void loadResultInList(List<Blog> blogs,ResultSet resultSet) throws SQLException {
while(resultSet.next()){
blogs.add(fetchRecordInBlogObject(resultSet));
}
}
private Blog fetchRecordInBlogObject(ResultSet record) throws SQLException {
Blog blog = new Blog();
blog.setBlogID(record.getInt("BlogID"));
blog.setTitle(record.getString("Title"));
blog.setDescription(record.getString("Description"));
blog.setTopicCount(record.getInt("TopicCount"));
return blog;
}
public synchronized void saveBlog(Blog blog) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_saveBlogStmt =
_oConn.prepareStatement("UPDATE Blog SET Title = ?,Description = ? WHERE BlogID = ?");
_saveBlogStmt.setString(1, blog.getTitle());
_saveBlogStmt.setString(2, blog.getDescription());
_saveBlogStmt.setInt(3, blog.getBlogID());
_saveBlogStmt.execute();
_saveBlogStmt.close();
} catch (SQLException e) {
throw new MapacheException("Error occurred when saving blog ", e);
} catch (DAOException e) {
throw new MapacheException("Error occurred when saving blog ", e);
} finally {
MySQLDAOFactory.freeConnection(_oConn);
}
}
public synchronized void deleteBlog(Blog blog) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_deleteBlogStmt =
_oConn.prepareStatement("DELETE FROM Blog WHERE BlogID = ?");
_deleteBlogStmt.setInt(1, blog.getBlogID());
_deleteBlogStmt.execute();
_deleteBlogStmt.close();
} catch (SQLException e) {
throw new MapacheException("An error occurred when deleting the blog",e);
} catch (DAOException e) {
throw new MapacheException("An error occurred when deleting the blog",e);
} finally {
MySQLDAOFactory.freeConnection(_oConn);
}
}
public synchronized void createBlog(Blog newBlog) throws MapacheException {
try {
_oConn = MySQLDAOFactory.createConnection();
_createBlogStmt =
_oConn.prepareStatement("INSERT INTO Blog(Title,Description) VALUES( ?, ?)");
_createBlogStmt.setString(1, newBlog.getTitle());
_createBlogStmt.setString(2, newBlog.getDescription());
_createBlogStmt.execute();
_createBlogStmt.close();
} catch (SQLException e) {
throw new MapacheException("An error occurred when creating a new blog", e);
} catch (DAOException e) {
throw new MapacheException("An error occurred when creating a new blog", e);
} finally {
MySQLDAOFactory.freeConnection(_oConn);
}
}
public synchronized Blog loadBlogByBlogID(int blogID) throws MapacheException {
Blog b = new Blog();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadBlogByBlogIDStmt =
_oConn.prepareStatement("SELECT B.BlogID as BlogID," +
"B.Title as Title,Description," +
"count(T.BlogID) as TopicCount FROM Blog B " +
"LEFT JOIN Topic T on B.BlogID = T.BlogID WHERE B.BlogID = ? GROUP BY B.BlogID");
_loadBlogByBlogIDStmt.setInt(1, blogID);
ResultSet result = _loadBlogByBlogIDStmt.executeQuery();
if (!result.next())
b = null;
b = fetchRecordInBlogObject(result);
_loadBlogByBlogIDStmt.close();
} catch (SQLException e) {
throw new MapacheException("An error occurred when loading blog",e);
} catch (DAOException e) {
throw new MapacheException("An error occurred when loading blog",e);
} finally {
MySQLDAOFactory.freeConnection(_oConn);
}
return b;
}
public synchronized List<Blog> loadAllBlogs_visitor() throws MapacheException {
List<Blog> blogs = new ArrayList<Blog>();
try{
_oConn = MySQLDAOFactory.createConnection();
_loadAllBlogsStmt = _oConn.prepareStatement("SELECT B.BlogID as BlogID," +
"B.Title as Title,Description," +
"count(T.BlogID) as TopicCount FROM Blog B " +
"JOIN Topic T on B.BlogID = T.BlogID WHERE T.Visible AND T.publishDate <= CURRENT_TIMESTAMP() GROUP BY B.BlogID");
ResultSet resultSet = _loadAllBlogsStmt.executeQuery();
loadResultInList(blogs,resultSet);
_loadAllBlogsStmt.close();
}
catch(SQLException e){
blogs=null;
throw new MapacheException("Error occurred when loading blogs from database!",e);
}
catch (DAOException e) {
blogs=null;
throw new MapacheException("Error occurred when loading blogs from database!",e);
}
finally{
MySQLDAOFactory.freeConnection(_oConn);
}
return blogs;
}
public Blog loadBlogByBlogName_visitor(String string) throws MapacheException {
Blog b = new Blog();
try {
_oConn = MySQLDAOFactory.createConnection();
_loadBlogByBlogIDStmt =
_oConn.prepareStatement("SELECT B.BlogID as BlogID," +
"B.Title as Title,Description," +
"count(T.BlogID) as TopicCount FROM Blog B " +
"LEFT JOIN Topic T on B.BlogID = T.BlogID WHERE B.Title = ? GROUP BY B.BlogID");
_loadBlogByBlogIDStmt.setString(1,string);
ResultSet result = _loadBlogByBlogIDStmt.executeQuery();
if (!result.next())
b = null;
b = fetchRecordInBlogObject(result);
_loadBlogByBlogIDStmt.close();
} catch (SQLException e) {
throw new MapacheException("An error occurred when loading blog",e);
} catch (DAOException e) {
throw new MapacheException("An error occurred when loading blog",e);
} finally {
MySQLDAOFactory.freeConnection(_oConn);
}
return b;
}
}