postdao.java

这是从网上下载下来的一个计算程序

package com.lovo.bbs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;

import javax.naming.NamingException;

import com.lovo.bbs.po.PostPo;
import com.lovo.bbs.util.DbUtil;
import com.lovo.bbs.util.MysqlDb;

/**
 * 回帖Dao
 * 
 * @author tiancen2001
 * 
 */
public class PostDao {
	 //操作类型
	 public static final int OPER_OF_UPDATE=2;
	 public static final int OPER_OF_DELETE=3;

	 public PostDao(){
		 
	 }
	/**
	 * 返回指定主题下的回帖列表
	 * 
	 * @param topicid
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  ArrayList<PostPo> getAllPost(int topicid)
			throws NamingException, SQLException {
		ArrayList<PostPo> pos = new ArrayList<PostPo>();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select  post.postid, post.topicid,"
				+ "post.posttitle, post.postdate,post.content ,"
				+ "post.userid, user.username,user.score,user.signature,"
				+ "userhead.headimg,user.rankid,user.topicnum  "
				+ "from post left join user on post.userid=user.userid  "
				+ "left join userhead on user.headimgid=userhead.headid   "
				+ "where  post.topicid=" + topicid
				+ "   order  by post.postdate";

		ResultSet rs = con.prepareStatement(sql).executeQuery();

		while (rs.next()) {
			PostPo po = new PostPo();
			po.setPostid(rs.getInt(1));
			po.setTopicid(rs.getInt(2));
			po.setPosttitle(rs.getString(3));
			po.setPostdate(rs.getString(4));
			po.setContent(rs.getString(5));

			po.setUserid(rs.getInt(6));
			po.setUsername(rs.getString(7));

			po.setScore(rs.getInt(8));
			po.setSignature(rs.getString(9));
			po.setHeadimg(rs.getString(10));
			po.setRankid(rs.getInt(11));

			po.setUserTopicNum(rs.getInt(12));

			pos.add(po);

		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);

		return pos;
	}

	/**
	 * 新增一个回帖
	 * 
	 * @param postvo
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int addOnePost(PostPo po) throws NamingException,
			SQLException {
		int postid = 0;
		int added = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "insert into post  "
				+ "(topicid, userid, posttitle, postdate, content) values"
				+ "(?, (select userid from user where username=?), ?, now(), ?) ";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, po.getTopicid());
		ps.setString(2, po.getUsername());
		ps.setString(3, po.getPosttitle());
		ps.setString(4, po.getContent());
		added = ps.executeUpdate();
		if (added == 1) {
			postid = getMaxPostid();
		}

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return postid;
	}

	/**
	 * 返回最大回帖ID
	 * 
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getMaxPostid() throws NamingException, SQLException {
		int postid = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select max(postid) from post";
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		if (rs.next()) {
			postid = rs.getInt(1);
		} else {
			postid = 1;
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return postid;
	}

	/**
	 * 取得指定ID的论坛的所有回帖
	 * 
	 * @param foumid
	 * @param queryPage
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<PostPo> getPostByForumID(int forumid, int queryPage)
			throws NamingException, SQLException {

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sqlPatch = null;
		if (forumid == 0) {// 当forumid==0时,等效于搜索所有的回帖
			sqlPatch = "  ";
		} else {
			sqlPatch = " where forum.forumid=" + forumid;
		}
		// 数据库分页边界
		int startIndex = (queryPage - 1) * 20;
		int endIndex = (queryPage - 1) * 20 + 20;
		String sql = "select  post.postid, post.topicid,"
				+ "post.posttitle, post.postdate,post.content ,"
				+ "post.userid, user.username,user.score,user.signature,"
				+ "userhead.headimg,user.rankid,user.topicnum,topic.topictitle      "
				+ "from   post  left  join user on post.userid=user.userid     "
				+ "                   left  join userhead on user.headimgid=userhead.headid    "
				+ "                   inner join topic on post.topicid=topic.topicid    "
				+ "                   inner join forum on topic.forumid = forum.forumid     "
				+ sqlPatch
				+ "                   order  by topic.topicdate desc,post.postdate desc,post.postid  limit  "
				+ startIndex + "  ,  " + endIndex;

		ArrayList<PostPo> pos = new ArrayList<PostPo>();

		ResultSet rs = con.prepareStatement(sql).executeQuery();

		while (rs.next()) {
			PostPo po = new PostPo();
			po.setPostid(rs.getInt(1));
			po.setTopicid(rs.getInt(2));
			po.setPosttitle(rs.getString(3));
			po.setPostdate(rs.getString(4));
			po.setContent(rs.getString(5));

			po.setUserid(rs.getInt(6));
			po.setUsername(rs.getString(7));

			po.setScore(rs.getInt(8));
			po.setSignature(rs.getString(9));
			po.setHeadimg(rs.getString(10));
			po.setRankid(rs.getInt(11));

			po.setUserTopicNum(rs.getInt(12));

			po.setTopicTitle(rs.getString(13));
			pos.add(po);

		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);

		return pos;
	}

	/**
	 * 取得指定ID的论坛的回帖数
	 * 
	 * @param foumid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getPostNumByForumID(int forumid) throws NamingException,
			SQLException {
		int postNum = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sqlPatch = null;
		if (forumid == 0) {
			sqlPatch = "  ";
		} else {
			sqlPatch = "  where forum.forumid=" + forumid;
		}

		String sql = "select count(*)     "
				+ "  from   post  "
				+ "                   left  join user on post.userid=user.userid     "
				+ "                   left  join userhead on user.headimgid=userhead.headid   "
				+ "                   inner join topic on post.topicid=topic.topicid   "
				+ "                   inner join forum on topic.forumid = forum.forumid    "
				+ sqlPatch;

		ResultSet rs = con.prepareStatement(sql).executeQuery();
		if (rs.next()) {
			postNum = rs.getInt(1);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);

		return postNum;
	}

	/**
	 * 取得指定ID回帖
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  PostPo getPostByID(int postid) throws NamingException,
			SQLException {
		PostPo po = new PostPo();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select  post.postid, post.topicid,"
				+ "post.posttitle, post.postdate,post.content ,"
				+ "post.userid, user.username,user.score,user.signature,"
				+ "userhead.headimg,user.rankid,user.topicnum  "
				+ "from post left join user on post.userid=user.userid  "
				+ "left join userhead on user.headimgid=userhead.headid   "
				+ "where  post.postid=" + postid;

		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			po.setPostid(rs.getInt(1));
			po.setTopicid(rs.getInt(2));
			po.setPosttitle(rs.getString(3));
			po.setPostdate(rs.getString(4));
			po.setContent(rs.getString(5));

			po.setUserid(rs.getInt(6));
			po.setUsername(rs.getString(7));

			po.setScore(rs.getInt(8));
			po.setSignature(rs.getString(9));
			po.setHeadimg(rs.getString(10));
			po.setRankid(rs.getInt(11));

			po.setUserTopicNum(rs.getInt(12));
		}
		mysqldb.closePS(ps);
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return po;
	}

	/**
	 * 找到该回复所在的论坛ID
	 * 
	 * @param postid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getForumIDByPostID(int postid) throws NamingException,
			SQLException {
		int forumid = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select forum.forumid from post,topic,forum    "
				+ "  where post.topicid=topic.topicid and topic.forumid=forum.forumid and post.postid=?";
		
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, postid);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			forumid = rs.getInt(1);
		}
		
		mysqldb.closePS(ps);
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		
		return forumid;
	}

	
	/**
	 * 回帖管理控制更新
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public  int updatePost(PostPo po, int operType) throws NamingException, SQLException {
		int updated = 0;
		
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);
		
		if(operType==PostDao.OPER_OF_UPDATE){//更新
			String sqlUpdate = "update post set posttitle=?  where postid=?";
			PreparedStatement ps1 = con.prepareStatement(sqlUpdate);
			ps1.setString(1, po.getPosttitle());
			ps1.setInt(2, po.getPostid());
			updated = ps1.executeUpdate();
			con.commit();
			mysqldb.closePS(ps1);
		}else if(operType==PostDao.OPER_OF_DELETE){//删除
			String  sqlDelete = "delete from post where postid=?";
			PreparedStatement ps2 = con.prepareStatement(sqlDelete);
			ps2.setInt(1, po.getPostid());
			updated = ps2.executeUpdate();
			con.commit();
			mysqldb.closePS(ps2);
		}
		
		
		mysqldb.closeConnection(con);
		
		return updated;
	}

	/**
	 * 删除该用户的回帖,返回回帖所属的主题
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public  HashSet<Integer> delPostByUserID(int userid) throws NamingException, SQLException {
		HashSet<Integer> set = new HashSet<Integer>();
		
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		
		//查询该用户回帖所属的主题
		String sqlGetTopicID = "select distinct(topicid) from post where userid="+userid;
		ResultSet rs = con.prepareStatement(sqlGetTopicID).executeQuery();
		while(rs.next()){
			set.add(rs.getInt(1));		
		}
		mysqldb.closeRS(rs);
		
		//删除该用户的回帖
		String sqlDel = "delete from post where userid="+userid;		
		
		con.prepareStatement(sqlDel).executeUpdate();
		mysqldb.closeConnection(con);
		return set;
	}
}