topicdao.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 java.util.Iterator;

import javax.naming.NamingException;

import com.lovo.bbs.po.TopicPo;
import com.lovo.bbs.util.DbUtil;
import com.lovo.bbs.util.MysqlDb;
import com.lovo.bbs.util.StringUtil;

/**
 * 主题Dao
 * 
 * @author tiancen2001
 * 
 */
public class TopicDao {
	// 操作类型
	public static final int OPER_OF_UPDATE = 2;
	public static final int OPER_OF_DELETE = 3;
	
	public  TopicDao(){
		
	}

	/**
	 * 返回指定论坛下的主题Po列表
	 * 
	 * @param queryPage
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> getTopics(int forumid, int queryPage)
			throws NamingException, SQLException {
		String sqlPatch = null;
		if (forumid == 0) {// 当forumid==0时,等效于搜索所有的主题
			sqlPatch = "";
		} else {
			sqlPatch = " where topic.forumid=" + forumid;
		}
		// 数据库分页边界
		int startIndex = (queryPage - 1) * 20;
		int endIndex = (queryPage - 1) * 20 + 20;
		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name,topic.content,forum.forumname,   "
				+ "a.topicnum   "

				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ sqlPatch
				+ "    order by topic.topicdate desc,topicid  desc  limit  "
				+ startIndex + "  ,  " + endIndex;

		return queryTopics(sql);
	}

	/**
	 * 返回指定论坛下的置顶主题Po列表
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> getTopTopics(int forumid)
			throws NamingException, SQLException {

		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name, topic.content,forum.forumname,  "
				+ "a.topicnum   "
				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ "where  topic.forumid="
				+ forumid
				+ "      and  topic.istop='1'    order by topic.topicdate desc,topicid  desc";
		return queryTopics(sql);
	}

	/**
	 * 返回指定ID的Topic po
	 * 
	 * @param topicid
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  TopicPo getTopicInfo(int topicid) throws NamingException,
			SQLException {

		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name,topic.content,forum.forumname,  "
				+ "a.topicnum   "
				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ "where  topic.topicid=" + topicid;

		ArrayList<TopicPo> topicPos = queryTopics(sql);
		Iterator<TopicPo> it = topicPos.iterator();
		TopicPo po = it.next();

		return po;
	}

	/**
	 * 查询主题列表
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	private  ArrayList<TopicPo> queryTopics(String sql)
			throws NamingException, SQLException {
		ArrayList<TopicPo> topicPos = new ArrayList<TopicPo>();

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

		ResultSet rs = con.prepareStatement(sql).executeQuery();
		while (rs.next()) {
			TopicPo po = new TopicPo();

			po.setTopicid(rs.getInt(1));
			po.setForumid(rs.getInt(2));

			po.setTopicTitle(rs.getString(3));
			po.setTopicDate(StringUtil.NoNull(rs.getString(4)));

			po.setHits(rs.getInt(5));
			po.setPostNum(rs.getInt(6));

			po.setHighlight(rs.getInt(7) == 1 ? true : false);
			po.setBest(rs.getInt(8) == 1 ? true : false);
			po.setTop(rs.getInt(9) == 1 ? true : false);
			po.setAttach(rs.getInt(10) == 1 ? true : false);

			po.setAuthorid(rs.getInt(11));
			po.setAuthorname(rs.getString(12));
			po.setAuthorScore(rs.getInt(13));
			po.setAuthorSignature(rs.getString(14));
			po.setAuthorRankid(rs.getInt(15));
			po.setAuthorHeadimg(rs.getString(16));

			po.setLastPostDate(StringUtil.NoNull(rs.getString(17)));
			po.setLastPostAuthor(StringUtil.NoNull(rs.getString(18)));

			po.setContent(StringUtil.NoNull(rs.getString(19)));

			po.setForumName(StringUtil.NoNull(rs.getString(20)));

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

			topicPos.add(po);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);

		return topicPos;
	}

	/**
	 * 新增主题
	 * 
	 * @param po
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int addOneTopic(TopicPo po) throws NamingException,
			SQLException {
		int topicid = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String insertSql = "insert into topic"
				+ " (forumid, topictitle, authorid, topicdate,content) "
				+ "values(?,?,(select userid from user where username=?),now(),?)";
		PreparedStatement ps = con.prepareStatement(insertSql);
		ps.setInt(1, po.getForumid());
		ps.setString(2, po.getTopicTitle());
		ps.setString(3, po.getAuthorname());
		ps.setString(4, po.getContent());
		int added = ps.executeUpdate();
		if (added == 1) {
			topicid = getMaxTopicid();
		}
		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);

		return topicid;
	}

	/**
	 * 返回最大主题ID
	 * 
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int getMaxTopicid() throws NamingException, SQLException {
		int topicid = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		ResultSet rs = con.prepareStatement("select max(topicid) from topic")
				.executeQuery();
		if (rs.next()) {
			topicid = rs.getInt(1);
		} else {
			topicid = 1;
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return topicid;
	}

	/**
	 * 查看数加1
	 * 
	 * @param topicid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int topicHitted(int topicid) throws NamingException,
			SQLException {
		int added = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update topic set hits=hits+1 where  topicid=" + topicid;
		added = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);
		return added;
	}

	/**
	 * 指定主题新增一个回复
	 * 
	 * @param postid
	 * @param topicid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int addOnePost(int postid, int topicid)
			throws NamingException, SQLException {
		int added = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update topic set postnum=postnum+1,lastpostid=? "
				+ "   where  topicid=?";