newsdbaccessor.java~4~

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

     * @param acceptFlag 嫋壜僼儔僌
     * @param acceptID   嫋壜ID
     * @param conn       僐僱僋僔儑儞
     */
    public static void registNews(String subject,String content,int acceptFlag,long[] acceptID,
                                  Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("INSERT INTO News (Subject,Content,AcceptFlag,AddDate,RepDate) VALUES (");
        sb.append("'" + StrUtil.dbFilter(subject) + "',");
        sb.append("'" + StrUtil.dbFilter(content) + "',");
        //sb.append(" " + acceptFlag + ",SYSDATE(),SYSDATE())"); // Modified by wangxu for postgresql
        sb.append(" " + acceptFlag + ",now(),now())");

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

        // ID傪庢摼
        long newsID = 0;
        Statement stmtID = conn.createStatement();
        ResultSet rs = stmtID.executeQuery("SELECT MAX(NewsID) AS NewsID FROM News");
        if(rs.next()){
            newsID = rs.getLong("NewsID");
        }
        rs.close();
        stmtID.close();

        // 嫋壜ID傪峏怴
        for(int i=0;i<acceptID.length;i++){
            String sql = "INSERT INTO NewsAccept (NewsID,AcceptID) VALUES (" + newsID + "," + acceptID[i] + ")";
            Statement stmt2 = conn.createStatement();
            stmt2.executeUpdate(sql);
            stmt2.close();
        }
    }

    /**
     * 僯儏乕僗傪峏怴偟傑偡丅
     *
     * @param newsID     僯儏乕僗ID
     * @param subject    審柤
     * @param content    杮暥
     * @param acceptFlag 嫋壜僼儔僌
     * @param acceptID   嫋壜ID
     * @param conn       僐僱僋僔儑儞
     */
    public static void updateNews(long newsID,String subject,String content,int acceptFlag,long[] acceptID,
                                  Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("UPDATE News SET ");
        sb.append("Subject    ='" + StrUtil.dbFilter(subject) + "',");
        sb.append("Content    ='" + StrUtil.dbFilter(content) + "',");
        sb.append("AcceptFlag = " + acceptFlag                + " ,");
        //sb.append("RepDate    = SYSDATE() "); // Modified by wangxu for postgresql
        sb.append("RepDate    = now() ");
        sb.append("WHERE NewsID=" + newsID);

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

        // 嫋壜ID傪峏怴
        Statement stmtDel = conn.createStatement();
        stmtDel.executeUpdate("DELETE FROM NewsAccept WHERE NewsID=" + newsID);
        stmtDel.close();

        for(int i=0;i<acceptID.length;i++){
            String sql = "INSERT INTO NewsAccept (NewsID,AcceptID) VALUES (" + newsID + "," + acceptID[i] + ")";
            Statement stmt2 = conn.createStatement();
            stmt2.executeUpdate(sql);
            stmt2.close();
        }
    }

    /**
     * 僯儏乕僗傪嶍彍偟傑偡丅
     *
     * @param newsID 僯儏乕僗ID
     * @param conn   僐僱僋僔儑儞
     */
    public static void deleteNews(long newsID,Connection conn) throws SQLException {
        Statement stmt1 = conn.createStatement();
        stmt1.executeQuery("DELETE FROM NewsAccept WHERE NewsID="+newsID);
        stmt1.close();

        Statement stmt2 = conn.createStatement();
        stmt2.executeQuery("DELETE FROM News WHERE NewsID="+newsID);
        stmt2.close();
    }

    /**
     * 僯儏乕僗偺審悢傪庢摼偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @return 審悢
     */
    public static int getNewsCount(Connection conn) throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS Count FROM News");
        int count = 0;
        if(rs.next()){
            count = rs.getInt("Count");
        }
        rs.close();
        stmt.close();
        return count;
    }

    /**
     * 巜掕偟偨儐乕僓偑墈棗壜擻側僯儏乕僗偺審悢傪庢摼偟傑偡丅
     *
     * @param groupID 僌儖乕僾ID
     * @param userID  儐乕僓ID
     * @param conn    僐僱僋僔儑儞
     * @return 審悢
     */
    public static int getNewsCount(long groupID,long userID,Connection conn) throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT NewsID,AcceptFlag FROM News WHERE AcceptFlag!=3");
        int count = 0;
        while(rs.next()){
            long newsID     = rs.getLong("NewsID");
            int  acceptFlag = rs.getInt("AcceptFlag");
            // 僌儖乕僾扨埵偱嫋壜
            if(acceptFlag==0){
                long[] acceptID = getAcceptID(newsID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==groupID){
                        count++;
                        break;
                    }
                }
            // 儐乕僓扨埵偱嫋壜
            } else if(acceptFlag==1){
                long[] acceptID = getAcceptID(newsID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==userID){
                        count++;
                        break;
                    }
                }
            // 慡偰偺儐乕僓偵嫋壜
            } else if(acceptFlag==2){
                count++;
            }
        }
        rs.close();
        stmt.close();
        return count;
    }

    /**
     * 儐乕僓偑巜掕偟偨僯儏乕僗偺墈棗尃尷傪帩偭偰偄傞偐偳偆偐傪庢摼偟傑偡丅
     *
     * @param conn 僐僱僋僔儑儞
     * @param newsID 僯儏乕僗ID
     * @param userID 儐乕僓ID
     * @param groupID 僌儖乕僾ID
     * @return 墈棗壜擻側応崌true丄晄壜偺応崌false丅
     */
    public static boolean getCanShowNews(Connection conn,long newsID,
                                         long userID,long groupID) throws SQLException {
        String sql = "SELECT AcceptFlag FROM News WHERE NewsID="+newsID;
        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 NewsAccept WHERE NewsID="+newsID);
            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 NewsAccept WHERE NewsID="+newsID);
            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;
    }
}