topicaction.java

tbuy1.1.5是在netbeans环境下用JSF技术编写的一个论坛tbuy1.1.5是在netbeans环境下用JSF技术编写的一个论坛

/*
 * 作者: 胡李青
 * qq: 31703299
 * Copyright (c) 2007 huliqing
 * 主页 http://www.tbuy.biz/
 * 你可以免费使用该软件，未经许可请勿作用于任何商业目的,如有技术问题请与本人联系！
 */

package biz.tbuy.bbs;

import biz.tbuy.bbs.bean.BaseBean;
import biz.tbuy.common.logs.Elog;
import biz.tbuy.common.pool.ProxyConn;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
 * @author huliqing
 * <p><b>qq：</b>31703299
 * <p><b>E-mail：</b>
 * <a href="mailto:[e-mail]">[e-mail]</a>
 * <p><b>Homepage：</b>
 * <a href="http://www.tbuy.biz/">http://www.tbuy.biz/</a>
 */
public class TopicAction extends BaseBean{
    
    public TopicAction() {}
    
    // ------------------------------------------------------------ Insert topic
    
    /**
     * 将主题插入于数据库中
     * @param topic 将要插入数据库的主题模型
     * @return true 如果完成数据操作，否则返回false;
     * @see #addTopicWithKey(TopicModel)
     */
    public static boolean addTopic(TopicModel topic) {
        if (addTopicWithKey(topic) != -1) {
            return true;
        }
        return false;
    }
    
    /**
     * 将主题信息插入数据库并返回该主题的主键t_num;
     * 该方法没有使用字段:t_authType, t_authGroups
     * @param topic 将要插入数据库的主题模型
     * @return key 如果操作成功则返回相关主键值，否则返回-1;
     */ 
    public static int addTopicWithKey(TopicModel topic) {
        int key = -1;
        String sql = "insert into topic (t_title, t_content, t_from, t_date, " +
            "t_ip, t_type, t_totalView, t_totalReply, t_byForum, t_byUser, " +
            "t_beLock, t_beRevoke, t_beUpload, t_order, t_music)" +
            " values (?, ?, ?, now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, topic.getTitle());
            ps.setString(2, topic.getContent());
            ps.setString(3, topic.getFrom());
            ps.setString(4, topic.getIp());
            ps.setInt(5, topic.getType());
            ps.setInt(6, topic.getTotalView());
            ps.setInt(7, topic.getTotalReply());
            ps.setInt(8, topic.getByForum());
            ps.setString(9, topic.getByUser());
            ps.setBoolean(10, topic.getBeLock());
            ps.setBoolean(11, topic.getBeRevoke());
            ps.setBoolean(12, topic.getBeUpload());
            ps.setInt(13, topic.getOrder());
            ps.setInt(14, topic.getMusic());
            if (ps.executeUpdate() > 0) {
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    key = rs.getInt(1);
                }
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.addTopicWithKey()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return key;
    }
    
    // ------------------------------------------------------------ Get topics
    
    /**
     * 从所有topic中获取一定数量的topic信息,按时间desc顺序排列，即获取从start行起的
     * size数量的topic信息(非revoke)
     * @param start 起始行
     * @param size 获取的数量
     * @return topics
     */
    public static List<TopicModel> getTopics(int start, int size) {
        List<TopicModel> topics = new ArrayList<TopicModel>(size);
        String sql = "select * from topic where t_beRevoke!=1 " +
                " order by t_num desc limit ?, ?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, size);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                topics.add(getTopicByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopics()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topics;
    }
    
    /*
     * 通过主题的ID获取该主题的信息
     * @param topicId 主题的id
     * @return topic 主题的信息
     */
    public static TopicModel getTopicsById(int topicId) {
        TopicModel topic = null;
        String sql = "select * from topic " +
                "where t_num=? " +
                "and t_beRevoke!=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, topicId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                topic = getTopicByRS(rs);
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topic;
    }
    
    /**
     * 通过主题的id及发表者的id获取主题信息
     * @param topicId 主题的id
     * @param userId 发表该主题的作者
     * @return topic 如果获取成功则返回TopicModel,否则返回null;
     */
    public static TopicModel getTopicByIdAndUser(int topicId, String userId) {
        TopicModel topic = null;
        String sql = "select * from topic " +
                "where t_num=? " +
                "and t_byUser=? " +
                "and t_beRevoke!=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, topicId);
            ps.setString(2, userId);
            ResultSet rs = ps.executeQuery();
            List<TopicModel> list = new ArrayList<TopicModel>();
            list = getTopics(rs);
            if (!list.isEmpty()) {
                topic = list.get(0);
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicByIdAndUser()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topic;
    }
    
    /**
     * 通过讨论区ID，获取该讨论区最后发表的主题信息（不获取版规信息)
     * @param forumId 最新发表主题的讨论区的ID
     * @return topic 该讨论区最后发表的主题TopicModel
     */
    public static TopicModel getLastTopicByForumId(int forumId) {
        TopicModel topic = null;
        String sql = "select * from topic " +
                "where t_byForum=? " +
                "and t_type!=? " +
                "and t_beRevoke!=1 " +
                "order by t_num desc " +
                "limit 0, 1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, forumId);
            ps.setInt(2, BBSGroups.TYPE_RULE);
            ResultSet rs = ps.executeQuery();
            List<TopicModel> list = getTopics(rs);
            if (!list.isEmpty()) {
                topic = list.get(0);
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getLastTopicByForumId()/" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topic;
    }
    
    /** 
     * @param forum
     * @return List of TopicModel
     */
    public static List<TopicModel> getTopicsByForum(ForumModel forum) {
        return getTopicsByForumId(forum.getNum());
    }
    
    /**
     * 通过讨论区的id,获取该讨论区的所有主题信息.
     * 该方法不会获取已经被移入回收区的主题信息(即t_beRevoke=true)
     * @param forumId 需要获取其信息的讨论区的id
     * @return list 包含TopicModel的ArrayList集合
     */
    public static List<TopicModel> getTopicsByForumId(int forumId) {
        String sql = "select * from topic " +
                "where t_type=? " +
                "or t_byForum=? " +
                "and t_beRevoke!=1 " +
                "order by t_num desc ";
        ProxyConn myConn = getConn();
        List<TopicModel> list = new ArrayList<TopicModel>();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, BBSGroups.TYPE_RULE);
            ps.setInt(2, forumId);
            ResultSet rs = ps.executeQuery();
            list = getTopics(rs);
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicsByForumId()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return list;
    }
    
    /**
     * @see #getTopicsForPageByForumId(int, int , int)
     * @param start 
     * @param pageSize
     * @param forum
     * @return List of TopicModel
     */
    public static List<TopicModel> getTopics(int start, int pageSize, 
            ForumModel forum) {
        return getTopicsForPageByForumId(start, pageSize, forum.getNum());
    }
    
    /**
     * 从讨论区获取其主题信息,并返回为ArrayList类型的集合
     * 该方法从数据库获取指定数量的主题信息，从位置start开始
     * 获取pageSize数量的主题,主要用于分页信息
     * 1.该方法会获取type值为50的版规信息
     * 2.不会获取已经被回收的主题信息
     * 3.先按t_order降序，再按t_num降序排列
     * @param start 获取信息的开始位置
     * @param pageSize 获取数量
     * @param forumId 主题信息所属的讨论区
     * @return list 一个包含TopicModel的ArrayList集合
     */
    public static List<TopicModel> getTopicsForPageByForumId(int start, 
            int pageSize, int forumId) {
        String sql = "select * from topic " +
                "where (t_type=? or t_byForum=?) and t_beRevoke!=1 " +
                "order by t_order desc, t_num desc " +
                "limit ?, ?";
        ProxyConn myConn = getConn();
        List<TopicModel> list = new ArrayList<TopicModel>();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, BBSGroups.TYPE_RULE);
            ps.setInt(2, forumId);
            ps.setInt(3, start);
            ps.setInt(4, pageSize);
            ResultSet rs = ps.executeQuery();
            list = getTopics(rs);
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicsForPageByForumId()/" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return list;
    }
    
    /**
     * 通过给定的forum的id数组获取这些forum下的topics信息，按t_num的desc顺序从起始
     * 行start获取size数量的topics
     * @param fid forum的id数组
     * @param start 获取的起始行
     * @param size 获取的数量
     * @return topics 
     */
    public static List<TopicModel> getTopicByForums(int[] fid, 
            int start, int size) {
        if (fid.length <= 0) return null;
        StringBuilder sql = new StringBuilder("select * from topic where t_beRevoke!=1 and (");
        for (int i = 0; i < fid.length; i++) {
            if (i == 0) {
                sql.append(" t_byForum=" + fid[i]);
            } else {
                sql.append(" or t_byForum=" + fid[i]);
            }
        }
        sql.append(") order by t_num desc ");
        sql.append(" limit " + start + ", " + size);
        List<TopicModel> topics = new ArrayList<TopicModel>();
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                topics.add(getTopicByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicByForums()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topics;
    }
    
    /**
     * 获取所有的主题数量,该方法不获取已经回收的主题信息
     * @return total
     */
    public static int getTotalTopics() {
        int total = 0;
        String sql = "select * from topic where t_beRevoke!=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (SQLException sqle) {
            Elog.log("TopicAction:getTotalTopics:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取特定日期的主题发表数量,该方法不会计算已经被回收的主题
     * @param date
     * @return total
     */
    public static int getTotalTopicsByDate(Date date) {
        int total = 0;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(date);
        String sql = "select * from topic where " +
                "t_beRevoke!=1 and t_date like '" + dateStr + "%' ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (SQLException sqle) {
            Elog.log("TopicAction:getTotalTopicsByDate:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 通过ForumModel获取该讨论区的所有主题数量
     * @see #getTotalTopicsByForumId(int)
     * @param forum 需要查找主题数的讨论区ForumModel
     * @return total 返回该讨论区的所有主题数量
     */
    public static int getTotalTopicsByForum(ForumModel forum) {
        return getTotalTopicsByForumId(forum.getNum());
    }
    
    /**
     * 获得讨论区的所有主题数
     * @param forumId 需要检索主题数的讨论区的ID
     * @return total 返回该讨论区的所有主题数，没有主题则返回0
     */
    public static int getTotalTopicsByForumId(int forumId) {
        int total = 0;
        String sql = "select * from topic " +
                "where (t_type=? or t_byForum=?) and t_beRevoke!=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, BBSGroups.TYPE_RULE);
            ps.setInt(2, forumId);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTotalByForumId()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }