forumdbaccessor.java~3~

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

     * @param conn 僐僱僋僔儑儞
     * @param forumName 僼僅乕儔儉柤
     * @param forumDetail 愢柧
     * @param acceptFlag 嫋壜僼儔僌
     * @param acceptID 嫋壜ID
     */
    public static void registForum(Connection conn,String forumName,String forumDetail,
                                   int acceptFlag,long[] acceptID) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("INSERT INTO Forum (ForumName,ForumDetail,AcceptFlag,AddDate,RepDate) ");
        sb.append("VALUES (");
        sb.append("'"+StrUtil.dbFilter(forumName)+"',");
        sb.append("'"+StrUtil.dbFilter(forumDetail)+"',");
        //sb.append(acceptFlag+",SYSDATE(),SYSDATE()"); // Modified by Wang for Postgresql
        sb.append(acceptFlag+",now(),now()");
        sb.append(")");

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

        Statement idStmt = conn.createStatement();
        ResultSet idRs = idStmt.executeQuery("SELECT MAX(ForumID) AS ForumID FROM Forum");
        long forumID = 0;
        if(idRs.next()){ forumID = idRs.getLong("ForumID"); }
        idRs.close();
        idStmt.close();

        for(int i=0;i<acceptID.length;i++){
            Statement stmt2 = conn.createStatement();
            stmt2.executeUpdate("INSERT INTO ForumAccept (ForumID,AcceptID) "
                              + "VALUES ("+forumID+","+acceptID[i]+")");
            stmt2.close();
        }
    }

    /**
     * 僼僅乕儔儉傪峏怴偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @param forumID 僼僅乕儔儉ID
     * @param forumName 僼僅乕儔儉柤
     * @param forumDetail 愢柧
     * @param acceptFlag 嫋壜僼儔僌
     * @param acceptID 嫋壜ID
     */
    public static void updateForum(Connection conn,long forumID,String forumName,String forumDetail,
                                   int acceptFlag,long[] acceptID) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("UPDATE Forum ");
        sb.append("SET ForumName   ='"+StrUtil.dbFilter(forumName)+"',");
        sb.append("    ForumDetail ='"+StrUtil.dbFilter(forumDetail)+"',");
        sb.append("    AcceptFlag  = "+acceptFlag+",");
        //sb.append("    RepDate     = SYSDATE()"); // Modified by Wang for Postgresql
        sb.append("    RepDate     = now()");
        sb.append("WHERE ForumID="+forumID);

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

        Statement delStmt = conn.createStatement();
        delStmt.executeUpdate("DELETE FROM ForumAccept WHERE ForumID="+forumID);
        delStmt.close();

        for(int i=0;i<acceptID.length;i++){
            Statement stmt2 = conn.createStatement();
            stmt2.executeUpdate("INSERT INTO ForumAccept (ForumID,AcceptID) "
                              + "VALUES ("+forumID+","+acceptID[i]+")");
            stmt2.close();
        }
    }

    /**
     * 僼僅乕儔儉傪嶍彍偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @param forumID 僼僅乕儔儉ID
     */
    public static void deleteForum(Connection conn,long forumID) throws SQLException {

        Statement stmt1 = conn.createStatement();
        stmt1.executeUpdate("DELETE FROM ForumAccept WHERE ForumID="+forumID);
        stmt1.close();

        Statement stmt2 = conn.createStatement();
        stmt2.executeUpdate("DELETE FROM ForumContent WHERE ForumID="+forumID);
        stmt2.close();

        Statement stmt3 = conn.createStatement();
        stmt3.executeUpdate("DELETE FROM Forum WHERE ForumID="+forumID);
        stmt3.close();

    }

    /**
     * 巜掕偟偨僼僅乕儔儉偺忣曬傪庢摼偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @param forumID 僼僅乕儔儉ID
     */
    public static ForumBean getForum(Connection conn,long forumID) 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 WHERE ForumID="+forumID);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        ForumBean bean = new ForumBean();
        if(rs.next()){
            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));
        }
        rs.close();
        stmt.close();

        return bean;
    }

    /**
     * 巜掕偟偨僼僅乕儔儉偺嫋壜ID傪庢摼偟傑偡丅
     *
     * @param forumID 僼僅乕儔儉ID
     * @param conn 僐僱僋僔儑儞
     */
    private static long[] getAcceptID(long forumID,Connection conn) throws SQLException {

        String sql = "SELECT AcceptID FROM ForumAccept WHERE ForumID=" + forumID;

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        Vector vec = new Vector();
        while(rs.next()){
            vec.add(new Long(rs.getLong("AcceptID")));
        }
        rs.close();
        stmt.close();
        long[] acceptID = new long[vec.size()];
        for(int i=0;i<vec.size();i++){
            acceptID[i] = ((Long)vec.get(i)).longValue();
        }
        return acceptID;

    }

    /**
     * 僩僢僾儗儀儖偺婰帠偺審悢傪庢摼偟傑偡丅
     */
    public static int getArticleCount(Connection conn,long forumID) throws SQLException {
        String sql = "SELECT COUNT(*) AS Count FROM ForumContent WHERE ParentID=0 AND ForumID="+forumID;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        if(rs.next()){
            count = rs.getInt("Count");
        }
        rs.close();
        stmt.close();
        return count;
    }

    /**
     * 儐乕僓偑巜掕偟偨僼僅乕儔儉偺墈棗尃尷傪帩偭偰偄傞偐偳偆偐傪庢摼偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @param forumID 僯儏乕僗ID
     * @param userID  儐乕僓ID
     * @param groupID 僌儖乕僾ID
     * @return 墈棗壜擻側応崌true丄晄壜偺応崌false丅
     */
    public static boolean getCanShowForum(Connection conn,long forumID,
                                         long userID,long groupID) throws SQLException {
        String sql = "SELECT AcceptFlag FROM Forum WHERE ForumID="+forumID;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int acceptFlag = -1;
        if(rs.next()){
            acceptFlag = rs.getInt("AcceptFlag");
        }
        rs.close();
        stmt.close();

        // 慡堳偵岞奐
        if(acceptFlag==2){
            return true;
        // 旕岞奐
        } else if(acceptFlag==3){
            return false;
        // 僌儖乕僾暿
        } else if(acceptFlag==0){

            StringBuffer sb = new StringBuffer();
            sb.append("SELECT AcceptID FROM ForumAccept WHERE ForumID="+forumID);
            sb.append(" AND AcceptID="+groupID);

            Statement stmt2 = conn.createStatement();
            ResultSet rs2 = stmt2.executeQuery(sb.toString());
            boolean retValue = false;
            if(rs2.next()){
                retValue = true;
            }
            rs2.close();
            stmt2.close();
            return retValue;

        // 儐乕僓暿
        } else if(acceptFlag==1){

            StringBuffer sb = new StringBuffer();
            sb.append("SELECT AcceptID FROM ForumAccept WHERE ForumID="+forumID);
            sb.append(" AND AcceptID="+userID);

            Statement stmt2 = conn.createStatement();
            ResultSet rs2 = stmt2.executeQuery(sb.toString());
            boolean retValue = false;
            if(rs2.next()){
                retValue = true;
            }
            rs2.close();
            stmt2.close();
            return retValue;
        }

        return false;
    }

    /**
     * Vector傪ForumBean[]偵曄姺偟傑偡丅
     *
     * @param vec
     * @return ForumBean[]
     */
    private static ForumBean[] toForumArray(Vector vec){
        ForumBean[] array = new ForumBean[vec.size()];
        for(int i=0;i<vec.size();i++){
            array[i] = (ForumBean)vec.get(i);
        }
        return array;
    }

    /**
     * Vector傪ContentBean[]偵曄姺偟傑偡丅
     *
     * @param vec
     * @return ContentBean[]
     */
    private static ContentBean[] toContentArray(Vector vec){
        ContentBean[] array = new ContentBean[vec.size()];
        for(int i=0;i<vec.size();i++){
            array[i] = (ContentBean)vec.get(i);
        }
        return array;
    }
}