newsdbaccessor.java~4~

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

package ws.woa.news;

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 Naoki Takezoe
 */
public class NewsDBAccessor {

    /**
     * 巜掕偟偨僯儏乕僗ID偺忣曬傪庢摼偟傑偡丅
     *
     * @param newsID 僯儏乕僗ID
     * @param conn   僐僱僋僔儑儞
     * @return NewsBean
     */
    public static NewsBean getNews(long newsID,Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT NewsID     ,");
        sb.append("       Subject    ,");
        sb.append("       Content    ,");
        sb.append("       AcceptFlag ,");
        sb.append("       AddDate    ,");
        sb.append("       RepDate     ");
        sb.append("FROM News ");
        sb.append("WHERE NewsID=" + newsID);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        NewsBean bean = new NewsBean();
        if(rs.next()){
            bean.setNewsID(rs.getLong("NewsID"));
            bean.setSubject(rs.getString("Subject"));
            bean.setContent(rs.getString("Content"));
            bean.setAcceptFlag(rs.getInt("AcceptFlag"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));
            bean.setAcceptID(getAcceptID(newsID,conn));
        }
        rs.close();
        stmt.close();

        return bean;
    }

    /**
     * 巜掕偟偨僯儏乕僗ID偺嫋壜ID傪攝楍偱庢摼偟傑偡丅
     *
     * @param newsID 僯儏乕僗ID
     * @param conn   僐僱僋僔儑儞
     * @return 嫋壜ID
     */
    public static long[] getAcceptID(long newsID,Connection conn) throws SQLException {

        String sql = "SELECT NewsID,AcceptID FROM NewsAccept WHERE NewsID=" + newsID;

        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();

        // Vector偐傜long宆攝楍偵媗傔懼偊
        long[] acceptID = new long[vec.size()];
        for(int i=0;i<vec.size();i++){
            acceptID[i] = ((Long)vec.get(i)).longValue();
        }
        return acceptID;
    }

    /**
     * 巜掕偟偨儐乕僓偑墈棗壜擻側僯儏乕僗傪攝楍偱庢摼偟傑偡丅
     *
     * @param groupID 僌儖乕僾ID
     * @param userID  儐乕僓ID
     * @param from    庢摼奐巒峴
     * @param limit   庢摼峴悢
     * @param conn    僐僱僋僔儑儞
     * @return NewsBean[]
     */
    public static NewsBean[] getNewsList(long groupID,long userID,int from,int limit,
                                         Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("SELECT NewsID     ,");
        sb.append("       Subject    ,");
        sb.append("       Content    ,");
        sb.append("       AcceptFlag ,");
        sb.append("       AddDate    ,");
        sb.append("       RepDate     ");
        sb.append("FROM News ");
        sb.append("WHERE AcceptFlag!=3 ");
        sb.append("ORDER BY AddDate DESC ");
        if(limit!=0){
            sb.append("LIMIT " + from + "," + limit);
        }

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        Vector vec = new Vector();
        while(rs.next()){
            long   newsID     = rs.getLong("NewsID");
            int    acceptFlag = rs.getInt("AcceptFlag");
            long[] acceptID   = new long[0];
            boolean viewFlag = false;
            if(acceptFlag==0){ // 僌儖乕僾扨埵偱嫋壜
                acceptID = getAcceptID(newsID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==groupID){
                        viewFlag = true;
                        break;
                    }
                }
            } else if(acceptFlag==1){ // 儐乕僓扨埵偱嫋壜
                acceptID = getAcceptID(newsID,conn);
                for(int i=0;i<acceptID.length;i++){
                    if(acceptID[i]==userID){
                        viewFlag = true;
                        break;
                    }
                }
            } else if(acceptFlag==2){ // 慡堳偵嫋壜
                acceptID = getAcceptID(newsID,conn);
                viewFlag = true;
            }

            if(viewFlag){
                NewsBean bean = new NewsBean();
                bean.setNewsID(newsID);
                bean.setAcceptFlag(acceptFlag);
                bean.setAcceptID(acceptID);
                bean.setSubject(rs.getString("Subject"));
                bean.setContent(rs.getString("Content"));
                bean.setAddDate(rs.getTimestamp("AddDate"));
                bean.setRepDate(rs.getTimestamp("RepDate"));
                vec.add(bean);
            }
        }
        rs.close();
        stmt.close();

        // Vector偐傜NewsBean宆攝楍偵媗傔懼偊
        NewsBean[] newsList = new NewsBean[vec.size()];
        for(int i=0;i<vec.size();i++){
            newsList[i] = (NewsBean)vec.get(i);
        }

        return newsList;

    }

    /**
     * 慡偰偺僯儏乕僗傪攝楍偱庢摼偟傑偡丅
     *
     * @param from  庢摼奐巒峴
     * @param limit 庢摼峴悢
     * @param conn  僐僱僋僔儑儞
     * @return NewsBean[]
     */
    public static NewsBean[] getNewsList(int from,int limit,Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();

        sb.append("SELECT NewsID     ,");
        sb.append("       Subject    ,");
        sb.append("       Content    ,");
        sb.append("       AcceptFlag ,");
        sb.append("       AddDate    ,");
        sb.append("       RepDate     ");
        sb.append("FROM News ");
        sb.append("ORDER BY AddDate DESC ");
//        if(limit!=0){
//            sb.append("LIMIT " + from + "," + limit);
//        }

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        Vector vec = new Vector();
        while(rs.next()){
            NewsBean bean = new NewsBean();
            bean.setNewsID(rs.getLong("NewsID"));
            bean.setSubject(rs.getString("Subject"));
            bean.setContent(rs.getString("Content"));
            bean.setAcceptFlag(rs.getInt("AcceptFlag"));
            bean.setAddDate(rs.getTimestamp("AddDate"));
            bean.setRepDate(rs.getTimestamp("RepDate"));
            bean.setAcceptID(getAcceptID(rs.getLong("NewsID"),conn));
            vec.add(bean);
        }
        rs.close();
        stmt.close();

        // Vector偐傜NewsBean宆攝楍偵媗傔懼偊
        NewsBean[] newsList = new NewsBean[vec.size()];
        for(int i=0;i<vec.size();i++){
            newsList[i] = (NewsBean)vec.get(i);
        }

        return newsList;

    }

    /**
     * 僯儏乕僗傪搊榐偟傑偡丅
     *
     * @param subject    審柤
     * @param content    杮暥