topicdao.java

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

		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, postid);
		ps.setInt(2, topicid);
		added = ps.executeUpdate();

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

	/**
	 * 按给定条件搜索所有论坛下的主题
	 * 
	 * @param forumid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> switchSearch(String cond, int forumid)
			throws NamingException, SQLException {
		String sqlPatch = null;

		if (cond.equals("best")) {
			sqlPatch = "topic.isbest='1'";
		} else if (cond.equals("top")) {
			sqlPatch = "topic.istop='1'";
		} else if (cond.equals("today")) {
			sqlPatch = "year(topicdate)=year(now())  and  month(topicdate)=month(now())  and day(topicdate)=day(now())";
		} else if (cond.equals("thisweek")) {
			sqlPatch = "year(topicdate)=year(now())  and  week(topic.topicdate)=week(now())";
		} else if (cond.equals("localbest")) {
			sqlPatch = "topic.forumid=" + forumid + "  and  topic.isbest='1' ";
		} else if (cond.equals("localhot")) {
			sqlPatch = "topic.forumid=" + forumid + "  and  topic.postnum>=10 ";
		}

		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  " + sqlPatch
				+ "    order by topic.topicdate desc,topicid  desc ";

		ArrayList<TopicPo> topicPos = queryTopics(sql);
		return topicPos;
	}

	/**
	 * 查询指定论坛的主题数 参数为0时查询所有论坛主题总数
	 * 
	 * @param forumid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getTopicNum(int forumid) throws NamingException,
			SQLException {
		int topicNum = 0;

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

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

		String sql = "select count(*) from topic   " + sqlPatch;

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

	/**
	 * 更新主题表
	 * 
	 * @param po
	 * @param operType
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int updateTopic(TopicPo 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 == TopicDao.OPER_OF_UPDATE) {
			String sqlUpdate = "update topic set  forumid=?,topictitle=?,hits=?,postnum=?,"
					+ "ishighlight=?,isbest=?,istop=?  where topicid=?";
			PreparedStatement ps = con.prepareStatement(sqlUpdate);

			ps.setInt(1, po.getForumid());
			ps.setString(2, po.getTopicTitle());
			ps.setInt(3, po.getHits());
			ps.setInt(4, po.getPostNum());

			ps.setString(5, po.isHighlight() ? "1" : "0");
			ps.setString(6, po.isBest() ? "1" : "0");
			ps.setString(7, po.isTop() ? "1" : "0");

			ps.setInt(8, po.getTopicid());

			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		} else if (operType == TopicDao.OPER_OF_DELETE) {
			String sqlDelete = "delete from topic where topicid=?";
			PreparedStatement ps = con.prepareStatement(sqlDelete);
			ps.setInt(1, po.getTopicid());

			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		}
		con.commit();
		mysqldb.closeConnection(con);
		return updated;
	}

	/**
	 * 主题回复数减一
	 * 
	 * @param topicid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int delonePost(int topicid) throws NamingException,
			SQLException {
		int updated = 0;

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

		String sql = "update topic set postnum=postnum-1 where topicid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, topicid);
		updated = ps.executeUpdate();

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

	/**
	 * 计算主题最后回复
	 * 
	 * @param topicid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int setLastPost(int topicid) throws NamingException,
			SQLException {
		int setted = 0;

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

		String sql = "update  topic set lastpostid=("
				+ "select postid from post  where topicid=? order by  postdate desc limit 1"
				+ ") where topicid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, topicid);
		ps.setInt(2, topicid);

		setted = ps.executeUpdate();

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

	/**
	 * 删除该用户的主题
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int delTopicByUserID(int userid) throws NamingException,
			SQLException {
		int del = 0;

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

		String sql = "delete from topic where authorid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, userid);
		del = ps.executeUpdate();

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

	/**
	 * 计算主题的回复数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int calcPostNum(int topicid) throws NamingException,
			SQLException {
		int calced = 0;

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

		String sql = "update topic set postnum="
				+ "(select count(*)  from post where topicid=" + topicid
				+ ")    where  topicid=" + topicid;

		calced = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);
		return calced;
	}

	/**
	 * 返回该主题的论坛ID
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getForumID(int topicid) throws NamingException,
			SQLException {
		int forumid = 0;

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

		String sql = "select forumid from topic where topicid=" + topicid;
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		if (rs.next()) {
			forumid = rs.getInt(1);
		}

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

	/**
	 * 删除该用户的主题,返回主题所属的论坛
	 */
	public  HashSet<Integer> delTopics(int userid)
			throws NamingException, SQLException {
		HashSet<Integer> set = new HashSet<Integer>();

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

		// 查询该用户的主题所属的论坛
		String sqlGetForumID = "select distinct(forumid)  from topic    where  topic.authorid="
				+ userid;
		ResultSet rs = con.prepareStatement(sqlGetForumID).executeQuery();
		while (rs.next()) {
			set.add(rs.getInt(1));
		}

		// 删除该用户的主题
		String sqlDel = "delete from topic where  authorid=" + userid;
		con.prepareStatement(sqlDel).executeUpdate();

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