forumdbaccessor.java~4~

一个自己做的公司网站和办公职员管理系统。

package ws.woa.forum;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import ws.woa.util.StrUtil;

/**
 * 僼僅乕儔儉儌僕儏乕儖偺DB傾僋僙僒丅
 *
 * @author Wang
 */
public class ForumDBAccessor {

    /**
     * 巜掕偟偨婰帠偺忣曬傪庢摼偟傑偡丅
     */
    public static ContentBean getContent(Connection conn,long forumID,
                                         long contentID) throws SQLException {
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT ForumContent.ContentID AS ContentID,");
        sb.append("       ForumContent.ForumID   AS ForumID  ,");
        sb.append("       ForumContent.ParentID  AS ParentID ,");
        sb.append("       ForumContent.UserID    AS UserID   ,");
        sb.append("       UserInfo.Name          AS UserName ,");
        sb.append("       ForumContent.Subject   AS Subject  ,");
        sb.append("       ForumContent.Content   AS Content  ,");
        sb.append("       ForumContent.AddDate   AS AddDate  ,");
        sb.append("       ForumContent.RepDate   AS RepDate   ");
        sb.append("FROM ForumContent,UserInfo ");
        sb.append("WHERE ForumContent.UserID    = UserInfo.UserID AND ");
        sb.append("      ForumContent.ForumID   = " + forumID  +" AND ");
        sb.append("      ForumContent.ContentID = " + contentID);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        ContentBean bean = new ContentBean();
        if(rs.next()){
            bean.setContentID(rs.getLong("ContentID"));
            bean.setUserID(rs.getLong("UserID"));
            bean.setUserName(rs.getString("UserName"));
            bean.setSubject(rs.getString("Subject"));
            bean.setContent(rs.getString("Content"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));

        }
        rs.close();
        stmt.close();

        return bean;
    }

    /**
     * 僼僅乕儔儉偺婰帠傪搊榐偟傑偡丅
     */
    public static void registContent(Connection conn,long forumID,long parentID,long userID,
                                     String subject,String content) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("INSERT INTO ForumContent ");
        sb.append("(ForumID,ParentID,UserID,Subject,Content,AddDate,RepDate)");
        sb.append(" VALUES (");
        sb.append(forumID  + ",");
        sb.append(parentID + ",");
        sb.append(userID   + ",");
        sb.append("'" + StrUtil.dbFilter(subject) + "',");
        sb.append("'" + StrUtil.dbFilter(content) + "',");
        //sb.append("SYSDATE(),SYSDATE())"); // Modified by Wang for Postgresql
        sb.append("now(),now())");

        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sb.toString());
        stmt.close();
    }

    /**
     * 巜掕偟偨僼僅乕儔儉偺婰帠傪庢摼偟傑偡丅
     */
    public static ContentBean[] getContentList(Connection conn,long forumID,int from) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("SELECT ForumContent.ContentID AS ContentID,");
        sb.append("       ForumContent.ForumID   AS ForumID  ,");
        sb.append("       ForumContent.ParentID  AS ParentID ,");
        sb.append("       ForumContent.UserID    AS UserID   ,");
        sb.append("       UserInfo.Name          AS UserName ,");
        sb.append("       ForumContent.Subject   AS Subject  ,");
        sb.append("       ForumContent.Content   AS Content  ,");
        sb.append("       ForumContent.AddDate   AS AddDate  ,");
        sb.append("       ForumContent.RepDate   AS RepDate   ");
        sb.append("FROM ForumContent,UserInfo ");
        sb.append("WHERE ForumContent.UserID   = UserInfo.UserID AND ");
        sb.append("      ForumContent.ForumID  = " + forumID  +" AND ");
        sb.append("      ForumContent.ParentID = 0 ");
        sb.append("ORDER BY AddDate DESC ");
        //sb.append("LIMIT "+from+","+ForumConstants.ONE_PAGE_TREE); // Modified by Wang for Postgresql
        sb.append("LIMIT "+ForumConstants.ONE_PAGE_TREE+" OFFSET "+from);

        Vector vec = new Vector();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        while(rs.next()){
            ContentBean bean = new ContentBean();
            bean.setContentID(rs.getLong("ContentID"));
            bean.setSubject(rs.getString("Subject"));
            bean.setContent(rs.getString("Content"));
            bean.setUserID(rs.getLong("UserID"));
            bean.setUserName(rs.getString("UserName"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));
            bean.setChildren(getChildContent(conn,forumID,rs.getLong("ContentID")));
            vec.add(bean);
        }
        rs.close();
        stmt.close();

        return toContentArray(vec);
    }

    /**
     * 巜掕偟偨婰帠偺巕婰帠傪庢摼偟傑偡丅
     */
    public static ContentBean[] getChildContent(Connection conn,long forumID,
                                                long parentID) throws SQLException {

        //System.out.println("ForumID="+forumID+":ParentID="+parentID);

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ForumContent.ContentID AS ContentID,");
        sb.append("       ForumContent.ForumID   AS ForumID  ,");
        sb.append("       ForumContent.ParentID  AS ParentID ,");
        sb.append("       ForumContent.UserID    AS UserID   ,");
        sb.append("       UserInfo.Name          AS UserName ,");
        sb.append("       ForumContent.Subject   AS Subject  ,");
        sb.append("       ForumContent.Content   AS Content  ,");
        sb.append("       ForumContent.AddDate   AS AddDate  ,");
        sb.append("       ForumContent.RepDate   AS RepDate   ");
        sb.append("FROM ForumContent,UserInfo ");
        sb.append("WHERE ForumContent.UserID   = UserInfo.UserID  AND ");
        sb.append("      ForumContent.ForumID  = " + forumID  + " AND ");
        sb.append("      ForumContent.ParentID = " + parentID + " ");
        sb.append("ORDER BY AddDate");

        Vector vec = new Vector();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        while(rs.next()){
            ContentBean bean = new ContentBean();
            bean.setContentID(rs.getLong("ContentID"));
            bean.setSubject(rs.getString("Subject"));
            bean.setContent(rs.getString("Content"));
            bean.setUserID(rs.getLong("UserID"));
            bean.setUserName(rs.getString("UserName"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));
            bean.setChildren(getChildContent(conn,forumID,rs.getLong("ContentID")));
            vec.add(bean);
            //System.out.println("捛壛偟傑偟偨丅");
        }
        rs.close();
        stmt.close();

        return toContentArray(vec);
    }

    /**
     * 巜掕偺儐乕僓偑墈棗壜擻側僼僅乕儔儉偺堦棗傪庢摼偟傑偡丅
     */
    public static ForumBean[] getForumList(Connection conn,long groupID,long userID) throws SQLException {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ForumID    ,");
        sb.append("       ForumName  ,");
        sb.append("       ForumDetail,");
        sb.append("       AcceptFlag ,");
        sb.append("       AddDate    ,");
        sb.append("       RepDate     ");
        sb.append("FROM Forum ORDER BY AddDate DESC");

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        Vector vec = new Vector();
        while(rs.next()){
            boolean flag = false;
            long[] acceptID = new long[0];
            int  acceptFlag = rs.getInt("AcceptFlag");
            long forumID    = rs.getLong("ForumID");

            if(acceptFlag==0){ // 僌儖乕僾扨埵
                acceptID = getAcceptID(forumID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==groupID){
                        flag = true;
                        break;
                    }
                }
            } else if(acceptFlag==1){ // 儐乕僓扨埵
                acceptID = getAcceptID(forumID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==userID){
                        flag = true;
                        break;
                    }
                }
            } else if(acceptFlag==2){ // 慡偰偵岞奐
                flag = true;
            }
            if(flag){
                ForumBean bean = new ForumBean();
                bean.setForumID(forumID);
                bean.setForumName(rs.getString("ForumName"));
                bean.setForumDetail(rs.getString("ForumDetail"));
                bean.setAcceptFlag(acceptFlag);
                bean.setAddDate(rs.getTimestamp("AddDate"));
                bean.setRepDate(rs.getTimestamp("RepDate"));
                bean.setAcceptID(acceptID);
                vec.add(bean);
            }
        }
        rs.close();
        stmt.close();

        return toForumArray(vec);
    }


    /**
     * 慡偰偺僼僅乕儔儉忣曬傪庢摼偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     */
    public static ForumBean[] getAllForum(Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ForumID    ,");
        sb.append("       ForumName  ,");
        sb.append("       ForumDetail,");
        sb.append("       AcceptFlag ,");
        sb.append("       AddDate    ,");
        sb.append("       RepDate     ");
        sb.append("FROM Forum ORDER BY AddDate DESC");

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        Vector vec = new Vector();
        while(rs.next()){
            ForumBean bean = new ForumBean();
            bean.setForumID(rs.getLong("ForumID"));
            bean.setForumName(rs.getString("ForumName"));
            bean.setForumDetail(rs.getString("ForumDetail"));
            bean.setAcceptFlag(rs.getInt("AcceptFlag"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));
            bean.setAcceptID(getAcceptID(rs.getLong("ForumID"),conn));
            vec.add(bean);
        }
        rs.close();
        stmt.close();

        return toForumArray(vec);
    }

    /**
     * 僼僅乕儔儉傪嶌惉偟傑偡丅
     *