forumdao.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 javax.naming.NamingException;

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

/**
 * 论坛Dao
 * 
 * @author tiancen2001
 * 
 */
public class ForumDao {
	// 操作类型
	public static final int OPER_OF_INSERT = 1;
	public static final int OPER_OF_UPDATE = 2;
	public static final int OPER_OF_DELETE = 3;
	
    public ForumDao(){
    }
	/**
	 * 返回所有论坛Po列表
	 * 
	 * @author tiancen2001
	 * 
	 */
	public   ArrayList<ForumPo> getAllForum() throws NamingException,
			SQLException {
		ArrayList<ForumPo> forumPos = new ArrayList<ForumPo>();

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

		String sql = "select  "
				+ "forum.forumid,forum.boardid,"
				+ "forum.forumname,forum.aboutforum,"
				+ "forum.topicnum,forum.postnum,forum.todaytopicnum,"
				+ "forum.lasttopicid,"
				+ "topic.topictitle,topic.topicdate,"
				+ "user.username  "
				+ "from forum left join topic on forum.lasttopicid=topic.topicid  "
				+ "left join user on topic.authorid=user.userid ";
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		while (rs.next()) {
			ForumPo po = new ForumPo();
			po.setForumID(rs.getInt(1));
			po.setBoardID(rs.getInt(2));
			po.setForumName(rs.getString(3));
			po.setAboutForum(rs.getString(4));
			po.setTopicNum(rs.getInt(5));
			po.setPostNum(rs.getInt(6));
			po.setTodayTopicNum(rs.getInt(7));

			po.setLastTopicID(rs.getInt(8));

			po.setLastTopicTitle(StringUtil.NoNull(rs.getString(9)));
			po.setLastTopicDate(StringUtil.NoNull(rs.getString(10)));

			po.setLastTopicAuthor(StringUtil.NoNull(rs.getString(11)));

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

	/**
	 * 新增一个主题,设置论坛的最后主题,本论坛主题数加一,本论坛今日主题数加一
	 * 
	 * @param topicid
	 * @return
	 */
	public   int addOneTopic(int topicid, int forumid)
			throws NamingException, SQLException {
		int setted = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "update forum  "
				+ "set  topicnum=topicnum+1,"
				+ "todaytopicnum="
				+ "(select count(*)  from topic where forumid=?  "
				+ "and year(topicdate)=year(now())  and  month(topicdate)=month(now())  and day(topicdate)=day(now()) )+1,"
				+ "lasttopicid=?   " + " where forumid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, forumid);
		ps.setInt(2, topicid);
		ps.setInt(3, forumid);
		setted = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);

		return setted;
	}

	/**
	 * 回帖数加一
	 * 
	 * @param forumid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   int addOnePost(int forumid) throws NamingException,
			SQLException {
		int added = 0;

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

		String sql = "update  forum  set  postnum=postnum+1  where forumid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, forumid);
		added = ps.executeUpdate();

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

	/**
	 * 分别计算各个论坛的今日主题数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   void calcTodayTopicNum() throws NamingException, SQLException {

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

		// 按论坛分组统计主题表的今日主题数
		String sqlCount = "select forumid,count(*) from topic  "
				+ "where   year(topicdate)=year(now())   "
				+ "and  month(topicdate)=month(now())   "
				+ "and  day(topicdate)=day(now())  group by forumid";
		ResultSet rs = con.prepareStatement(sqlCount).executeQuery();

		// 清空原数据
		String sqlReset = "update forum set todaytopicnum=0";
		con.prepareStatement(sqlReset).executeUpdate();

		// 写入数据
		while (rs.next()) {
			String sqlUpdate = "update forum set todaytopicnum=? where forumid=?";
			PreparedStatement ps = con.prepareStatement(sqlUpdate);
			ps.setInt(1, rs.getInt(2));
			ps.setInt(2, rs.getInt(1));
			ps.executeUpdate();
			mysqldb.closePS(ps);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
	}

	/**
	 * 返回指定板块Id下的论坛
	 */
	public   ArrayList<ForumPo> getForumByBoardID(int boardid)
			throws SQLException, NamingException {
		ArrayList<ForumPo> forumPos = new ArrayList<ForumPo>();

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

		String sql = "select  "
				+ "forum.forumid,forum.boardid,"
				+ "forum.forumname,forum.aboutforum,"
				+ "forum.topicnum,forum.postnum,forum.todaytopicnum,"
				+ "forum.lasttopicid,"
				+ "topic.topictitle,topic.topicdate,"
				+ "user.username  "
				+ "from forum left join topic on forum.lasttopicid=topic.topicid  "
				+ "left join user on topic.authorid=user.userid  where forum.boardid="
				+ boardid;
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		while (rs.next()) {
			ForumPo po = new ForumPo();
			po.setForumID(rs.getInt(1));
			po.setBoardID(rs.getInt(2));
			po.setForumName(rs.getString(3));
			po.setAboutForum(rs.getString(4));
			po.setTopicNum(rs.getInt(5));
			po.setPostNum(rs.getInt(6));
			po.setTodayTopicNum(rs.getInt(7));

			po.setLastTopicID(rs.getInt(8));

			po.setLastTopicTitle(StringUtil.NoNull(rs.getString(9)));
			po.setLastTopicDate(StringUtil.NoNull(rs.getString(10)));

			po.setLastTopicAuthor(StringUtil.NoNull(rs.getString(11)));

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

	/**
	 * 返回指定论坛ID的论坛
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   ForumPo getForumByForumID(int forumid)
			throws NamingException, SQLException {
		ForumPo po = new ForumPo();

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

		String sql = "select  "
				+ "forum.forumid,forum.boardid,"
				+ "forum.forumname,forum.aboutforum,"
				+ "forum.topicnum,forum.postnum,forum.todaytopicnum,"
				+ "forum.lasttopicid,"
				+ "topic.topictitle,topic.topicdate,"
				+ "user.username  "
				+ "from forum left join topic on forum.lasttopicid=topic.topicid  "
				+ "left join user on topic.authorid=user.userid  where forum.forumid="
				+ forumid;
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		rs.next();

		po.setForumID(rs.getInt(1));
		po.setBoardID(rs.getInt(2));
		po.setForumName(rs.getString(3));
		po.setAboutForum(rs.getString(4));
		po.setTopicNum(rs.getInt(5));
		po.setPostNum(rs.getInt(6));
		po.setTodayTopicNum(rs.getInt(7));

		po.setLastTopicID(rs.getInt(8));

		po.setLastTopicTitle(StringUtil.NoNull(rs.getString(9)));
		po.setLastTopicDate(StringUtil.NoNull(rs.getString(10)));

		po.setLastTopicAuthor(StringUtil.NoNull(rs.getString(11)));

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

	/**
	 * 论坛更新
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   int updateForum(ForumPo 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 == ForumDao.OPER_OF_INSERT) {// 插入论坛
			String sqlInsert = "insert into forum(boardid, forumname, aboutforum) values(?,?,?)";
			PreparedStatement ps = con.prepareStatement(sqlInsert);
			ps.setInt(1, po.getBoardID());
			ps.setString(2, po.getForumName());
			ps.setString(3, po.getAboutForum());
			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		} else if (operType == ForumDao.OPER_OF_UPDATE) {// 更新论坛
			String sqlUpdate = "update forum set boardid=?,forumname=?,aboutforum=? where forumid=?";
			PreparedStatement ps = con.prepareStatement(sqlUpdate);
			ps.setInt(1, po.getBoardID());
			ps.setString(2, po.getForumName());
			ps.setString(3, po.getAboutForum());
			ps.setInt(4, po.getForumID());
			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		} else if (operType == ForumDao.OPER_OF_DELETE) {// 删除论坛
			String sqlDelete = "delete from forum where forumid=?";
			PreparedStatement ps = con.prepareStatement(sqlDelete);
			ps.setInt(1, po.getForumID());
			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		}
		con.commit();
		mysqldb.closeConnection(con);
		return updated;
	}

	/**
	 * 计算指定论坛的主题数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   void calcTopicNum(int forumid) throws NamingException,
			SQLException {
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update forum set topicnum="
				+ "(select count(*) from topic where forumid=?)  where forumid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, forumid);
		ps.setInt(2, forumid);
		ps.executeUpdate();

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

	/**
	 * 计算指定论坛的回复数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   void calcPostNum(int forumid) throws NamingException,
			SQLException {
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);

		String sqlQueryNum = "select count(*)   "
				+ "  from post,topic,forum  where  post.topicid=topic.topicid  "
				+ "  and topic.forumid=forum.forumid and forum.forumid=?";
		PreparedStatement ps = con.prepareStatement(sqlQueryNum);
		ps.setInt(1, forumid);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int postNum = rs.getInt(1);
		
		mysqldb.closeRS(rs);
		mysqldb.closePS(ps);

		String sqlUpdate = "update forum set postnum=?  where forumid=?";
		PreparedStatement ps2 = con.prepareStatement(sqlUpdate);
		ps2.setInt(1, postNum);
		ps2.setInt(2, forumid);
		ps2.executeUpdate();

		con.commit();
		mysqldb.closePS(ps2);
		mysqldb.closeConnection(con);
	}

	/**
	 * 计算指定论坛的最后主题
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public   void calcLastTopic(int forumid) throws NamingException,
			SQLException {
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);

		String sql = "update forum set lasttopicid=(  "
				+ "  select topicid from topic where forumid=?  "
				+ "  order by topicdate desc limit 1 )  "
				+ "  where forumid=?   ";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, forumid);
		ps.setInt(2, forumid);
		ps.executeUpdate();

		con.commit();
		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
	}

	/**
	 * 删除一个回帖
	 * 
	 * @param forumid
	 * @throws SQLException 
	 * @throws NamingException 
	 */
	public   int delOnePost(int forumid) throws NamingException, SQLException {
		int deled = 0;
		
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		
		String sql = "update forum set postnum=postnum-1 where forumid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, forumid);
		deled=ps.executeUpdate();
		
		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return deled;
	}
}