dbtopicfactory.java

bbs struts

package com.ntsky.bbs.service.db;

import java.sql.*;
import java.util.*;
import java.util.Date;
import org.apache.log4j.Logger ;

import com.ntsky.util.Timer;
import com.ntsky.util.TimerExpress;
import com.ntsky.bbs.service.Topic;
import com.ntsky.bbs.service.db.DBTopic;
import com.ntsky.bbs.service.TopicUtil;
import com.ntsky.bbs.service.TopicFactory;
import com.ntsky.xml.bbs.TopicConfigXML;
import com.ntsky.xml.bbs.dto.TopicConfigDTO;
import com.ntsky.datasource.DBConnectionManager;
import com.ntsky.bbs.service.BoardUtil;
import com.ntsky.bbs.service.ReplyFactory;
import com.ntsky.util.CodeFilter;
import com.ntsky.bbs.service.Reply;
import com.ntsky.bbs.service.TopicUtil;
import com.ntsky.bbs.service.UserFactory;
import com.ntsky.bbs.service.UserUtil;
import com.ntsky.bbs.service.BoardFactory;

/**
 * <p>Title: Ntsky OpenSource BBS</p>
 * <p>Description: 主题模块</p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: WWW.FM880.COM</p>
 * @author 姚君林
 * @version 1.0
 */

public class DBTopicFactory extends TopicFactory{
    

    private static final String INS_TOPIC = "INSERT INTO t_topic(topicTitle,topicContent,topicAuthor,topicPubTime) VALUES(?,?,?,?);";
    //最新的主题
    private static final String NEWEST_TOPIC = "SELECT * FROM t_topic WHERE topicBoaId=? ORDER BY topicPubTime DESC limit 0,1;";
    private static final String SUM_TOPIC = "SELECT topicId,topicReNum FROM t_topic;";
    private static final String LIST_BOARD_TOPIC = "SELECT * FROM t_topic WHERE topicBoaId=? ORDER BY topicTop DESC,topicReTime DESC,topicPubTime DESC;";
    private static final String SUM_BOARD_TOPIC = "SELECT COUNT(topicId) FROM t_topic WHERE topicBoaId=?;";
    private static final String LIST_TOPIC = "SELECT * FROM t_topic WHERE topicId=?;";
    private static final String UP_TOPIC_HITS = "UPDATE t_topic SET topicHits = topicHits + 1 WHERE topicId=?;";
    private static final String UP_TOPIC = "UPDATE t_topic SET topicTitle=?,topicContent=? WHERE topicId=?;";
    private static final String DEL_TOPIC = "DELETE FROM t_topic WHERE topicId=?;";
    private static final String UP_TOPIC_LOCK = "UPDATE t_topic SET topicLock=1 WHERE topicId=?;";
    private static final String UP_TOPIC_GOOD = "UPDATE t_topic SET topicElite=1 WHERE topicId=?;";
    private static final String UP_TOPIC_TOP = "UPDATE t_topic SET topicTop=1 WHERE topicId=?;";

    /**
     * 统计一天的主题总数
     * @return int
     */
    private int countTodayTopic(int boaId){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Timer timer = new TimerExpress("yyyy-MM-dd HH:mm:ss");
        String tempTime = timer.getNowDate();
        tempTime = tempTime.substring(0,10);
        int count = 0;
        String TODAY_TOPIC = "SELECT COUNT(*) FROM t_topic WHERE topicBoaId='"+boaId+"' AND topicPubTime BETWEEN '"+tempTime+" 00:00:00' AND '"+tempTime+" 23:59:59';";
        logger.info("TODAY_TOPIC : " + TODAY_TOPIC);
        try {
            pstm = conn.prepareStatement(TODAY_TOPIC);
            rs = pstm.executeQuery();
            rs.last();
            count = rs.getInt(1);
        }
        catch (Exception ex) {
            logger.error("获取一天帖子总和失败 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return count;
    }

    /**
     * 更新主题状态
     * @param topicId int
     * @param type String
     */
    public void upTopicState(int topicId,String type,String usrName){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        try {
            if(type.equals("lock"))
                pstm = conn.prepareStatement(UP_TOPIC_LOCK);
            if(type.equals("good")){
                //推荐为优秀帖子，更新用户的一些参数
                logger.info("设置帖子后更新相关的用户信息.........");
                UserFactory.getInstance().upUserState("good",usrName);
                logger.info("成功更新了用户信息........");
                pstm = conn.prepareStatement(UP_TOPIC_GOOD);
            }
            if(type.equals("top"))
                pstm = conn.prepareStatement(UP_TOPIC_TOP);
            pstm.setInt(1,topicId);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("更新主题状态失败 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 删除主题(删除主题同时删除主题对应的回复，用户体力值，更新前台显示)
     * @param topicId int
     */
    public void delTopic(int topicId){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        try {
            //删除回复信息
            ReplyFactory.getInstance().delTopicReply(topicId);
            //更新用户体力值
            Topic topic = listTopic(topicId);
            UserFactory.getInstance().upUserWealthDre(topic.getTopicAuthor());

            pstm = conn.prepareStatement(DEL_TOPIC);
            pstm.setInt(1,topicId);
            pstm.executeUpdate();

            //刷新前台数组
            TopicUtil.reset();
        }
        catch (Exception ex) {
            logger.error("删除主题失败 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 指定主题的数据
     * @return Topic
     */
    public Topic listTopic(int topicId){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Topic topic = new DBTopic();
        try {
            pstm = conn.prepareStatement(LIST_TOPIC);
            pstm.setInt(1,topicId);
            rs = pstm.executeQuery();
            rs.next();
            //当指针有数据时
            topic.setTopicId(rs.getInt("topicId"));
            topic.setTopicTitle(rs.getString("topicTitle"));
            topic.setTopicContent(rs.getString("topicContent"));
            topic.setTopicAuthor(rs.getString("topicAuthor"));
            topic.setTopicBoaId(rs.getInt("topicBoaId"));
            topic.setTopicHits(rs.getInt("topicHits"));
            topic.setTopicElite(rs.getInt("topicElite"));
            topic.setTopicTop(rs.getInt("topicTop"));
            topic.setTopicLock(rs.getInt("topicLock"));
            topic.setTopicDel(rs.getInt("topicDel"));
            topic.setTopicPubTime(rs.getString("topicPubTime"));
            topic.setTopicIp(rs.getString("topicIp"));
            logger.info("取得值后返回");
        }
        catch (Exception ex) {
            logger.error("列出最新数据失败 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return topic;
    }

    /**
     * 主题总和
     * @param topic Topic
     * @return boolean
     */
    public String[] sumTopicRep(){
        String[] string = new String[2];
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(SUM_TOPIC);
            rs = pstm.executeQuery();
            if(!rs.next()){
                string[0] = "0";
                string[1] = "0";
            }
            else{
                rs.last();
                string[0] = String.valueOf(rs.getRow());
                rs.first();
                int sumRep = rs.getInt("topicReNum");
                while(rs.next()){
                    sumRep = rs.getInt("topicReNum")+sumRep;
                }
                string[1] = String.valueOf(sumRep);
            }
        }
        catch (Exception ex) {
            logger.info("获取主题失败 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
        return string;
    }

    /**
     * 插入主题(更新模板)
     */
    public Topic createTopic(Topic topic){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        Timer timer = new TimerExpress("yyyy-MM-dd HH:mm:ss");
        Topic newTopic = null;
        try {
            pstm = conn.prepareStatement(INS_TOPIC);
            pstm.setString(1,topic.getTopicMood()+topic.getTopicTitle());
            pstm.setString(2,CodeFilter.htmlEncode(topic.getTopicContent()));
            String topicAuthor = topic.getTopicAuthor();
            pstm.setString(3,topicAuthor);
            
            pstm.setString(4,timer.getNowDate());
        
            pstm.executeUpdate();
            /**
             * 更新BoardUtil中的主题数目
             */
            //最新主题
            BoardUtil.upTopicNum(topic.getTopicBoaId());
            //刷新主题数组
            TopicUtil.reset();
            //增加作者的人气值2和用户发帖数
            UserUtil.upUserInfo_AddTopic(topicAuthor);
            //更新帖子热度
            //upTopicHits(topicId);
            //取得刚才添加的纪录
            newTopic = listNewTopic(boaId);
            //更新board中的值
          
        }
        catch (Exception ex) {
            logger.info("插入帖子失败 : " + ex.getMessage());
        }
        finally {
            try {