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 ;";
    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
     */
   

    /**
     * 删除主题(删除主题同时删除主题对应的回复，用户体力值，更新前台显示)
     * @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.setTopicPubTime(rs.getString("topicPubTime"));
           
            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 boolean 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.getTopicTitle());
            pstm.setString(2,CodeFilter.htmlEncode(topic.getTopicContent()));
            String topicAuthor = topic.getTopicAuthor();
            pstm.setString(3,topicAuthor);
            
            pstm.setString(4,timer.getNowDate());
        
            pstm.executeUpdate();
            
        //    sumtopic();
            /**
             * 更新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 {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
        return true;
    }

    /**
     * 更新主题内容
     */
  

    /**
     * 模块主题总数
     */
    public int sumtopic(){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int topicNum = 0;
        try {
            pstm = conn.prepareStatement(SUM_BOARD_TOPIC);
            
            rs = pstm.executeQuery();
            rs.next();
            topicNum = rs.getInt(1);
        }
        catch (Exception ex) {
            logger.info("模块主题总和统计失败 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.error("关闭 rs 失败 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return topicNum;
    }

   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.setTopicPubTime(rs.getString("topicPubTime"));
           
            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;
    }
 /**
     * 主题列表
     */
    public Iterator topicList(int maxNum, int page) {
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List list = new ArrayList();
       
       
        int i = 0;
        try {
            try {
                pstm = conn.prepareStatement(LIST_BOARD_TOPIC);
              
                rs = pstm.executeQuery();
            }
            catch (Exception e) {
                logger.error("创建指针对象错误 : " + e.getMessage());
            }
            if (page == 1) { //第一页
                try {
                    rs.absolute(1);
                }
                catch (Exception e) {
                    logger.error("指针移到第一条数据错误 : " + e.getMessage());
                }
            }
            else {
                rs.absolute( (page - 1) * maxNum);
            }
            /**
             * 根据定位的指针来取得主题的值
             */
            for (i = 0; i < maxNum; i++) {
                if (page != 1) {
                    if (!rs.next()) {
                        break;
                    }
                }
                Topic topic = new DBTopic();
                topic.setTopicId(rs.getInt("topicId"));
                String topicTitle = rs.getString("topicTitle");
                topic.setTopicTitle(topicTitle);
                topic.setTopicContent(rs.getString("topicContent"));
                String topicAuthor = rs.getString("topicAuthor");
                topic.setTopicAuthor(topicAuthor);
               
                String topicPubTime = rs.getString("topicPubTime");
                topic.setTopicPubTime(topicPubTime);
              
                list.add(topic);
                /**
                 * 当页数为1的时候(填充数据后在返回)
                 */
                if (page == 1) {
                    if (!rs.next()) {
                        break;
                    }
                }
            }
        }
        catch (Exception e) {
            logger.error("获取主题对象错误 : " + e.getMessage());
        }
        finally {
            try {
               rs.close();
           }
           catch (Exception ex) {
               logger.error("关闭rs失败 : " + ex.getMessage());
           }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭pstm失败 : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭conn失败 : " + ex.getMessage());
            }
        }
        return list.iterator();
    }