topicaction.java

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

        return total;
    }
    
    /**
     * 获取特定用户所发表的主题数量
     * @param userid 用户的id
     * @return total 该用户所发表的主题数
     */ 
    public static int getTotalTopicsByUserid(String userid) {
        int total = 0;
        String sql = "select * from topic " +
                "where t_beRevoke!=1 " +
                "and t_byUser=? ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, userid);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (SQLException sqle) {
            Elog.log("Exception:TopicAction.getTotalTopicsByUserid:" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取特定用户所发表的主题(一定数量），该方法主要用于分页)
     * @param startRow 获取数据的起始行
     * @param pageSize 获取的数量
     * @param userid 用户的id
     * @return topics
     */ 
    public static List<TopicModel> getTopicsTyUserid(int startRow, 
            int pageSize, String userid) {
        List<TopicModel> topics = null;
        String sql = "select * from topic " +
                "where t_beRevoke!=1 " +
                "and t_byUser=? " +
                "order by t_num desc " +
                "limit ?, ?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, userid);
            ps.setInt(2, startRow);
            ps.setInt(3, pageSize);
            ResultSet rs = ps.executeQuery();
            topics = getTopics(rs);
        } catch (SQLException sqle) {
            Elog.log("Exception:TopicAction.getTopicsTyUserid:" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topics;
    }
    
    /**
     * 关于被删除及被回收的主题,以下部分是关于获取及彻底删除已被回收的主题
     * 该方法获取已被回收的主题的总数量
     * @return total 
     */
    public static int getTotalRevokeTopics() {
        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("Exception:TopicAction:getTotalRevokeTopics:" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取一定数量的“已被回收的主题”,该方法主要用于分页获取
     * 该方法只获取，而不判断是哪一个讨论区的主题等
     * @param startRow 获取的开始行
     * @param pageSize 从开始行获取一定的数量
     * @return revokeTopics 被回收的主题
     */ 
    public static List<TopicModel> getRevokeTopics(int startRow, int pageSize) {
        List<TopicModel> revokeTopics = null;
        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, startRow);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            revokeTopics = getTopics(rs);
        } catch (Exception e) {
            Elog.log("Exception:TopicAction:getRevokeTopics:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return revokeTopics;
    }
    
    // ------------------------------------------------------------ Update topic
    
    /**
     * 更主题的回复数量，将主题的回复数加1
     * @param topicId 将要操作的主题的id
     * @return true 如果更新成功，否则返回false
     */
    public static boolean growTotalReplyById(int topicId) {
        boolean isOk = true;
        String sql = "update topic " +
                "set t_totalReply=t_totalReply+1 " +
                "where t_num=? ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, topicId);
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("TopicAction.growTotalReplyById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 更新主题信息,通过TopicModel
     * @param topic 需要被更新的主题信息
     * @return true 如果更新成功，否则返回false
     */
    public static boolean updateTopic(TopicModel topic) {
        boolean isOk = true;
        String sql = "update topic set " +
                "t_title=?,    t_content=?,    t_from=?,        t_date=?,  " +
                "t_ip=?,       t_type=?,       t_order=?,       t_totalView=?, " +
                "t_totalReply=?, t_byForum=?,  t_byUser=?,      t_beLock=?, " +
                "t_beRevoke=?, t_beUpload=?,   t_editByUser=?,  t_editByDate=?, " +
                "t_authType=?, t_authGroups=?, t_music=? " +
                "where t_num=?";
        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.setTimestamp(4, topic.getDate());
            ps.setString(5, topic.getIp());
            ps.setInt(6, topic.getType());
            ps.setInt(7, topic.getOrder());
            ps.setInt(8, topic.getTotalView());
            ps.setInt(9, topic.getTotalReply());
            ps.setInt(10, topic.getByForum());
            ps.setString(11, topic.getByUser());
            ps.setBoolean(12, topic.getBeLock());
            ps.setBoolean(13, topic.getBeRevoke());
            ps.setBoolean(14, topic.getBeUpload());
            ps.setString(15, topic.getEditByUser());
            ps.setTimestamp(16, topic.getEditByDate());
            ps.setInt(17, topic.getAuthType());
            ps.setString(18, topic.getAuthGroups());
            ps.setInt(19, topic.getMusic());
            ps.setInt(20, topic.getNum());
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("TopicAction.updateTopic()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    
    /**
     * 将Topic移入回收站,并不真正删除该Topic
     * @see #revokeTopicById(int)
     * @param topic 需要更新的topic
     * @return true 如果更新成功，否则返回false;
     */ 
    public static boolean revokeTopic(TopicModel topic) {
        return revokeTopicById(topic.getNum());
    }
    
    /**
     * 将Topic移入回收站,并不真正删除该Topic
     * @param topicId 需要更新的topic
     * @return true 如果更新成功，否则返回false;
     */ 
    public static boolean revokeTopicById(int topicId) {
        boolean isOk = false;
        String sql = "update topic set t_beRevoke=? where t_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setBoolean(1, true);
            ps.setInt(2, topicId);
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("TopicAction.revokeTopicById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 判断用户userId是否为该主题topicId的作者
     * @param topicId  主题的id
     * @param userId  用户的id
     * @return true 如果用户为该主题的作者,否则返回false;
     */
    public static boolean isAuthorByUser(int topicId, String userId) {
        boolean is = false;
        String sql = "select * from topic where t_num=? and t_byUser=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, topicId);
            ps.setString(2, userId);
            is = ps.executeQuery().next();
        } catch (SQLException sqle) {
            Elog.log("TopicAction.isAuthorByUser()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return is;
    }
    
    // ------------------------------------------------------------ Delete topics
    
    /**
     * 彻底删除所有已经被回收的主题信息,同时清理所有相关联的回复文章<br>
     * 删除主题，相关回复信息
     * @return true 如果正常删除,否则false
     */ 
    public static boolean clearRevokeTopics() {
        boolean isOk = true;
        String sql = "delete topic, reply " +
                "from topic " +
                "left join reply on t_num=r_byTopic " +
                "where t_beRevoke=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            isOk = false;
            Elog.log("Exception:TopicAction:clearRevokeTopics:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    // ------------------------------------------------------------ Search topics
    
    /**
     * 通过SearchModel的条件获得所要查询的主题信息的总数量
     * @param sModel 查询条件
     * @return total 符合条件的总数量
     */
    public static int searchTopicsTotal(SearchModel sModel) {
        int total = 0;
        boolean start = true;
        String keyword = sModel.getKeyword().trim();
        StringBuilder sql = new StringBuilder("select * from topic where t_beRevoke!=1");
        sql.append(" and (");
        if (sModel.getByTitle()) {
            sql.append(" t_title like '%" + keyword + "%'");
            start = false;
        }
        if (sModel.getByContent()) {
            if (!start) sql.append(" or ");
            sql.append(" t_content like '%" + keyword + "%'");
            start = false;
        }
        if (sModel.getByUserid()) {
            if (!start) sql.append(" or ");
            sql.append(" t_byUser like '%" + keyword + "%'");
            start = false;
        }
        sql.append(")");
        //System.out.println("sql1=" + sql.toString());
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (Exception e) {
            Elog.log("Exception:TopicAction:searchTopicsTotal:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 通过searchModel的条件查询一定数量的文章信息,主要用于分页查询
     * @param sModel 查询条件
     * @param startRow  起始行
     * @param pageSize  查询数量
     * @return topics 符合条件的数据
     */
    public static List<TopicModel> searchTopics(SearchModel sModel, 
            int startRow, int pageSize) {
        boolean start = true;
        List<TopicModel> topics = new ArrayList<TopicModel>();
        String keyword = sModel.getKeyword().trim();
        StringBuilder sql = new StringBuilder("select * from topic where t_beRevoke!=1");
        sql.append(" and (");
        if (sModel.getByTitle()) {
            sql.append(" t_title like '%" + keyword + "%'");
            start = false;
        }
        if (sModel.getByContent()) {
            if (!start) sql.append(" or ");
            sql.append(" t_content like '%" + keyword + "%'");
            start = false;
        }
        if (sModel.getByUserid()) {
            if (!start) sql.append(" or ");
            sql.append(" t_byUser like '%" + keyword + "%'");
            start = false;
        }
        sql.append(")");
        if (sModel.getOrderKey() != null)
            sql.append(" order by " + sModel.getOrderKey() + " " + sModel.getOrderType());
        sql.append(" limit " + startRow + "," + pageSize);
        //System.out.println("sql2=" + sql.toString());
        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("Exception:TopicAction:searchTopics:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return topics;
    }
    
    // -------------------------------------------------- Private of getTopcics
    /**
     * 通过ResultSet获取TopicModel
     * @param rs 包含来自数据库表topic信息的ResultSet
     * @return list 返回包含TopicModel对象的ArrayList集合
     */
    private static List<TopicModel> getTopics(ResultSet rs) {
        List<TopicModel> list = new ArrayList<TopicModel>();
        try {
            while (rs.next()) {
                list.add(getTopicByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopics()/" + sqle.getMessage());
        }
        return list;
    }
    
    /**
     * 通过该ResultSet的当前行获取相应的数据,并转为单个TopicModel返回
     * @param rs 一个ResultSet
     * @return topic
     */ 
    public static TopicModel getTopicByRS(ResultSet rs) {
        TopicModel topic = new TopicModel();
        try {
            topic.setNum(rs.getInt("t_num"));
            topic.setTitle(rs.getString("t_title"));
            topic.setContent(rs.getString("t_content"));
            topic.setFrom(rs.getString("t_from"));
            topic.setDate(rs.getTimestamp("t_date"));
            topic.setIp(rs.getString("t_ip"));
            topic.setType(rs.getInt("t_type"));
            topic.setOrder(rs.getInt("t_order"));
            topic.setAuthType(rs.getInt("t_authType"));
            topic.setAuthGroups(rs.getString("t_authGroups"));
            topic.setMusic(rs.getInt("t_music"));
            topic.setTotalView(rs.getInt("t_totalView"));
            topic.setTotalReply(rs.getInt("t_totalReply"));
            topic.setByForum(rs.getInt("t_byForum"));
            topic.setByUser(rs.getString("t_byUser"));
            topic.setBeLock(rs.getBoolean("t_beLock"));
            topic.setBeRevoke(rs.getBoolean("t_beRevoke"));
            topic.setBeUpload(rs.getBoolean("t_beUpload"));
            topic.setEditByUser(rs.getString("t_editByUser"));
            topic.setEditByDate(rs.getTimestamp("t_editByDate"));
        } catch (SQLException sqle) {
            Elog.log("TopicAction.getTopicByRS()/" + sqle.getMessage());
        }
        return topic;
    }
}