blogop.java

博客管理系统。安装说明： 1、在SQLSERVER中创建blog数据库并创建登陆帐号blog：blog

package huc.blog.op;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import huc.blog.bean.Blog;
import huc.blog.bean.Category;
import huc.blog.bean.User;
import huc.blog.util.DateUtils;
import huc.blog.util.PageObject;

public class BlogOp extends BaseOp{
	
	/**
	 * 添加blog文章
	 * @param blog
	 * @return true:添加成功；false:添加失败
	 */
	public boolean add(Blog blog){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into H_BLOG(");
			sql.append("title,publishTime,content,userid,categoryid) ");
			sql.append(" values(?,?,?,?,?)");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, blog.getTitle());
			pst.setString(2, blog.getPublishTime());
			pst.setString(3, blog.getContent());
			pst.setString(4, blog.getUserid());
			pst.setString(5, blog.getCategoryid());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}
	
	/**
	 * 更新blog信息
	 * @param blog
	 * @return
	 */
	public boolean update(Blog blog){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("update H_BLOG ");
			sql.append(" set title=?,publishTime=?,content=?,userid=?,categoryid=?");
			sql.append(" where id = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, blog.getTitle());
			pst.setString(2, blog.getPublishTime());
			pst.setString(3, blog.getContent());
			pst.setString(4, blog.getUserid());
			pst.setString(5, blog.getCategoryid());
			pst.setString(6, blog.getId());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}
	
	/**
	 * 删除blog(根据id或userid)
	 * @param blog
	 * @return
	 */
	public boolean delete(Blog blog){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from H_BLOG where id = ? or userid = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, blog.getId());
			pst.setString(2, blog.getUserid());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}

	/**
	 * 列出符合查询条件的blogs
	 * @param otherSQL		指定查询的条件			like(" and param = value")
	 * @param orderBy		指定查询结果的排序方式	like(" time desc")
	 * @return
	 */
/*	public List getBlogs(String otherSQL, String orderBy){
		conn = db.getConnection();
		List result = new ArrayList();
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a.*,(");
			sql.append("select count(H_REMARK.id) from H_BLOG left join H_REMARK");
			sql.append(" on H_BLOG.id = H_REMARK.blogid");
			sql.append(" where H_BLOG.id=a.id group by H_BLOG.id");
			sql.append(") as remarkNum from H_BLOG a");
			sql.append(" where 1 = 1 ");
			if(otherSQL != null && !otherSQL.trim().equals(""))
				sql.append(otherSQL);
			if(orderBy != null && !orderBy.trim().equals("")){
				sql.append(" order by ");
				sql.append(orderBy);
			}
			pst = conn.prepareStatement(sql.toString());
			
			rs = pst.executeQuery();
			while(rs.next()){
				Blog blog = new Blog();
				
				blog.setId(rs.getString("id"));
				blog.setTitle(rs.getString("title"));
				blog.setPublishTime(rs.getString("publishTime"));
				blog.setContent(rs.getString("content"));
				blog.setUserid(rs.getString("userid"));
				blog.setCategoryid(rs.getString("categoryid"));
				blog.setRemarkNum(rs.getInt("remarkNum"));
				
				result.add(blog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList();
		}
		finally{
			close();
		}
		
		return result;
	}
*/	
	
	public List getMonthBlogs(String userid){
		return getMonthBlogs(userid, DateUtils.format(Calendar.getInstance().getTime()));
	}
	
	/**
	 * 获得指定id的user的当月的所有blog
	 * @param userid
	 * @return
	 */
	public List getMonthBlogs(String userid, String stringDate){
		conn = db.getConnection();
		List result = new ArrayList();
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a.*,(");
			sql.append("select count(H_REMARK.id) from H_BLOG left join H_REMARK");
			sql.append(" on H_BLOG.id = H_REMARK.blogid");
			sql.append(" where H_BLOG.id=a.id group by H_BLOG.id");
			sql.append(") as remarkNum,");
			sql.append("b.categoryName from H_BLOG a left join H_CATEGORY b on a.categoryid = b.id");
			sql.append(" where a.userid = ?");
			sql.append(" and a.publishTime > ?");
			sql.append(" and a.publishTime < ?");
			sql.append(" order by a.publishTime desc");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, userid);
			pst.setString(2, DateUtils.getFirstTime(stringDate).toLocaleString());
			pst.setString(3, DateUtils.getLastTime(stringDate).toLocaleString());
			
			rs = pst.executeQuery();
			while(rs.next()){
				Blog blog = new Blog();
				
				blog.setId(rs.getString("id"));
				blog.setTitle(rs.getString("title"));
				blog.setPublishTime(rs.getString("publishTime"));
				blog.setContent(rs.getString("content"));
				blog.setUserid(rs.getString("userid"));
				blog.setCategoryid(rs.getString("categoryid"));
				blog.setRemarkNum(rs.getInt("remarkNum"));
				Category category = new Category();
				category.setId(blog.getCategoryid());
				category.setCategoryName(rs.getString("categoryName"));
				blog.setCategory(category);
				
				result.add(blog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList();
		}
		finally{
			close();
		}
		
		return result;
	}
	
	/**
	 * 获得指定id的blog
	 * @param id	指定的blog的id
	 * @return
	 */
	public Blog getBlogById(String id){
		conn = db.getConnection();
		Blog blog = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a.*,(");
			sql.append("select count(H_REMARK.id) from H_BLOG left join H_REMARK");
			sql.append(" on H_BLOG.id = H_REMARK.blogid");
			sql.append(" where H_BLOG.id=a.id group by H_BLOG.id");
			sql.append(") as remarkNum,");
			sql.append("b.categoryName from H_BLOG a left join H_CATEGORY b on a.categoryid = b.id");
			sql.append(" where a.id = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, id);
			
			rs = pst.executeQuery();
			if(rs.next()){
				blog = new Blog();
				blog.setId(rs.getString("id"));
				blog.setTitle(rs.getString("title"));
				blog.setPublishTime(rs.getString("publishTime"));
				blog.setContent(rs.getString("content"));
				blog.setUserid(rs.getString("userid"));
				blog.setCategoryid(rs.getString("categoryid"));
				blog.setRemarkNum(rs.getInt("remarkNum"));
				Category category = new Category();
				category.setId(blog.getCategoryid());
				category.setCategoryName(rs.getString("categoryName"));
				blog.setCategory(category);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			close();
		}
		
		return blog;
	}
	
	/**
	 * 获得分页的数据
	 * @param page		封装的分页信息
	 * @param otherSQL	分页数据的查询条件	like " and name=value"
	 * @param orderBy	分页数据的排序方式 like " order by id asc"
	 * @return 封装的分页数据
	 */
	public PageObject getPage(PageObject page, String otherSQL, String orderBy){
		conn = db.getConnection();
		
		try {
			StringBuffer sql = new StringBuffer();
			//获得符合分页的对象的总记录数
			sql.append("select count(1) as total from H_BLOG a where 1=1 ");
			if(otherSQL != null && !otherSQL.trim().equals(""))
				sql.append(otherSQL);

			pst = conn.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			if(rs.next() && rs.getInt("total") != 0)
				page.setTotal(rs.getInt("total"));
			else{
				page.setList(new ArrayList());
				page.setTotal(0);
				return page;
			}
			rs.close();

			//得到分页sql语句
			StringBuffer keySQL = new StringBuffer();
			keySQL.append(" a.*,(");
			keySQL.append("select count(H_REMARK.id) from H_BLOG left join H_REMARK");
			keySQL.append(" on H_BLOG.id = H_REMARK.blogid");
			keySQL.append(" where H_BLOG.id=a.id group by H_BLOG.id");
			keySQL.append(") as remarkNum,");
			keySQL.append("b.categoryName from H_BLOG a left join H_CATEGORY b on a.categoryid = b.id");
			sql = this.getSQL(page, keySQL.toString(), otherSQL, orderBy);

			pst = conn.prepareStatement(sql.toString());
			rs = pst.executeQuery();

			List result = new ArrayList();
			while(rs.next()){
				Blog blog = new Blog();
				
				blog.setId(rs.getString("id"));
				blog.setTitle(rs.getString("title"));
				blog.setPublishTime(rs.getString("publishTime"));
				blog.setUserid(rs.getString("userid"));
				blog.setCategoryid(rs.getString("categoryid"));
				blog.setRemarkNum(rs.getInt("remarkNum"));
				Category category = new Category();
				category.setId(blog.getCategoryid());
				category.setCategoryName(rs.getString("categoryName"));
				blog.setCategory(category);
				
				result.add(blog);
			}
			page.setList(result);
		} catch (SQLException e) {
			e.printStackTrace();
			return page;
		}
		finally{
			close();
		}
		
		return page;
	}
}