listmodel.java

新闻发布系统

/*
 * ListModel.java
 *
 * Created on 2007年3月17日, 上午10:09
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package News.ProcessData;

import java.sql.*;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.util.*;

import News.Common.DBManager;
/**
 *
 * @author Owner
 */
public class ListModel {
    private int recordCount=0;
    private int pageCount=0;
    private int pageSize=7;
    private int showPage=0;
    private int nextPage=0;
    private int frontPage=0;
    private String sql="";
    private Connection conn=null;
    private PreparedStatement ps=null;
    private ResultSet rs=null;
    /** Creates a new instance of ListModel */
    public ListModel() {
    }
    public ArrayList listLinks(String showpage)
    {
        ArrayList al=new ArrayList();
        sql="select count(id) from Links";
        try{
            showPage=Integer.parseInt(showpage);
        }catch(NumberFormatException e){showPage=1;}
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement(sql);
        rs=ps.executeQuery();
        if(rs.next())
        {
            recordCount=rs.getInt(1);
            pageCount=((recordCount%pageSize==0)?(recordCount/pageSize):(recordCount/pageSize+1));
            if(showPage<1)
            {
                showPage=1;
            }else if(showPage>pageCount)
            {
                showPage=pageCount;
            }
            if(showPage>1)
                sql="select Top "+pageSize+" * from Links where ID not in (select Top "+pageSize*(showPage-1)+" ID from Links)";
            else sql="select Top "+pageSize+" * from Links";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next())
            {
                String ID=rs.getString("ID");
                al.add("<TR bgcolor=\"#F5F5F5\"><TD><div align=\"center\">"+ID+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString("Name")+"</div></TD>"   
                      +"<TD><div align=\"center\">"+rs.getString("URL")+"</div></TD>"
                      +"<TD><div align=\"center\"><img src=\"../UploadPhoto/"+rs.getString("Logo")+"\"></div></TD>"
                      +"<TD><div align=\"center\"><a href=\"See.do?manner=Link&ID="+ID+"\">查看详细信息</a></div></TD>"
                      +"<TD><div align=\"center\"><a href=\"Delete.do?manner=DeleteLink&ID="+ID+"\">删除此连接</a></div></TD></TR>");  
                
            }
          }
        else {return null;}
         }catch(SQLException e){System.out.println(e.getMessage());return null;}
         finally{
             try{
            if(rs!=null)rs.close();
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
             }catch(SQLException e1){System.out.println(e1.getMessage());}
         }
         return al;
    }
    
    public ArrayList getAllNewsCatalogs()    //得到所有的新闻版块名和ID
    {
        ArrayList al=new ArrayList();
        conn=DBManager.getConnection();
        try{
        ps=conn.prepareStatement("select ID,Title from Catalogs");
        rs=ps.executeQuery();
        while(rs.next())
        {
            al.add("<option value=\""+rs.getString("ID")+"\">"+rs.getString("Title")+"</option>");
        }
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
        }
        return al;
    }
    
    public ArrayList listCatalogs(String showpage)
    {
        ArrayList al=new ArrayList();
        sql="select count(id) from catalogs";
        try{
            showPage=Integer.parseInt(showpage);
        }catch(NumberFormatException e){showPage=1;}
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement(sql);
        rs=ps.executeQuery();
        if(rs.next())
        {
            recordCount=rs.getInt(1);
            pageCount=((recordCount%pageSize==0)?(recordCount/pageSize):(recordCount/pageSize+1));
            if(showPage<1)
            {
                showPage=1;
            }else if(showPage>pageCount)
            {
                showPage=pageCount;
            }
            if(showPage>1)
                sql="select Top "+pageSize+" * from Catalogs where ID not in (select Top "+pageSize*(showPage-1)+" ID from Catalogs)";
            else sql="select Top "+pageSize+" * from Catalogs";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next())
            {
                String ID=rs.getString("ID");
                al.add("<TR bgcolor=\"#F5F5F5\"><TD><div align=\"center\">"+ID+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString("Title")+"</div></TD>"   
                      +"<TD><div align=\"center\">"+rs.getDate("ModifyTime")+"</div></TD>"
                      +"<TD><div align=\"center\"><a href=\"See.do?manner=Catalog&ID="+ID+"\">查看详细信息</a></div></TD>"
                      +"<TD><div align=\"center\"><a href=\"Delete.do?manner=DeleteCatalog&ID="+ID+"\">删除此版块</a></div></TD></TR>");  
                
            }
          }
        else {return null;}
         }catch(SQLException e){System.out.println(e.getMessage());return null;}
         finally{
             try{
            if(rs!=null)rs.close();
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
             }catch(SQLException e1){System.out.println(e1.getMessage());}
         }
         return al;
    }
    
    public ArrayList listNotices(String showpage)
    {
        ArrayList al=new ArrayList();
        sql="select count(id) from Notice";
        try{
            showPage=Integer.parseInt(showpage);
        }catch(NumberFormatException e){showPage=1;}
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement(sql);
        rs=ps.executeQuery();
        if(rs.next())
        {
            recordCount=rs.getInt(1);
            pageCount=((recordCount%pageSize==0)?(recordCount/pageSize):(recordCount/pageSize+1));
            if(showPage<1)
            {
                showPage=1;
            }else if(showPage>pageCount)
            {
                showPage=pageCount;
            }
            if(showPage>1)
                sql="select Top "+pageSize+" * from Notice where ID not in (select Top "+pageSize*(showPage-1)+" ID from Notice)";
            else sql="select Top "+pageSize+" * from Notice";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next())
            {
                String ID=rs.getString("ID");
                al.add("<TR bgcolor=\"#F5F5F5\"><TD><div align=\"center\">"+ID+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString("Message")+"</div></TD>"   
                      +"<TD><div align=\"center\">"+rs.getDate("Time")+"</div></TD>"
                      +"<TD><div align=\"center\"><a href=\"See.do?manner=Notice&ID="+ID+"\">查看详细信息</a></div></TD>"
                      +"<TD><div align=\"center\"><a href=\"Delete.do?manner=DeleteNotice&ID="+ID+"\">删除此条通知</a></div></TD></TR>");  
          
            }
          }
        else {return null;}
         }catch(SQLException e){System.out.println(e.getMessage());return null;}
         finally{
             try{
            if(rs!=null)rs.close();
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
             }catch(SQLException e1){System.out.println(e1.getMessage());}
         }
         return al;
    }
    
    public ArrayList listNews(String showpage,String key,String keywords)
    {
        ArrayList al=new ArrayList();
        sql="select Count(News.ID) from News,Catalogs where News.parentId=Catalogs.ID and "+key+" like ?";
        try{
            showPage=Integer.parseInt(showpage);
        }catch(NumberFormatException e){showPage=1;}
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement(sql);
        ps.setString(1,keywords);
        rs=ps.executeQuery();
        if(rs.next())
        {
            recordCount=rs.getInt(1);
            if(recordCount==0){return null;}
            pageCount=((recordCount%pageSize==0)?(recordCount/pageSize):(recordCount/pageSize+1));
            if(showPage<1)
            {
                showPage=1;
            }else if(showPage>pageCount)
            {
                showPage=pageCount;
            }
            if(showPage>1)
                sql="select Top "+pageSize+" * from News,Catalogs where "+key+" like ? and News.parentId=Catalogs.ID and News.ID not in (select Top "+pageSize*(showPage-1)+" News.ID from News,Catalogs where "+key+" like ? and News.parentId=Catalogs.ID)";
            else sql="select Top "+pageSize+" * from News,Catalogs where "+key+" like ? and News.parentId=Catalogs.ID";
       
            ps=conn.prepareStatement(sql);
            if(showPage==1)
            {
            ps.setString(1,keywords);
            }
            if(showPage>1)
            {
                ps.setString(1,keywords);
                ps.setString(2,keywords);
            }
            rs=ps.executeQuery();
            while(rs.next())
            {
                String ID=rs.getString(1);
                al.add("<TR bgcolor=\"#F5F5F5\"><TD><div align=\"center\">"+ID+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString(3)+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString(4)+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString(6)+"</div></TD>"
                      +"<TD><div align=\"center\">"+rs.getString(9)+"</div></TD>"
                      +"<TD><div align=\"center\"><a href=\"See.do?manner=News&ID="+ID+"\">查看详细信息</a></div></TD>"
                      +"<TD><div align=\"center\"><a href=\"Delete.do?manner=DeleteNew&ID="+ID+"\">删除此条新闻</a></div></TD></TR>");  
           
            }
          }
        else {return null;}
         }catch(SQLException e){System.out.println(e.getMessage());return null;}
         finally{
             try{
            if(rs!=null)rs.close();
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
             }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
         }
         return al;
    }
    
    public int getPageSize()
    {
        return pageSize;
    }
    
    public void setPageSize(int i)
    {
        pageSize=i;
    }
    
    public int getShowPage()
    {
        return showPage;
    }
    
    public void setShowPage(int i)
    {
        showPage=i;
    }
    
    public int getNextPage()
    {
        return (showPage+1);
    }
    
    public void setNextPage(int i)
    {
        nextPage=i;
    }
    
    public int getFrontPage()
    {
        return (showPage-1);
    }
    
    public void setFrontPage(int i)
    {
        frontPage=i;
    }
    
    public int getPageCount()
    {
        return pageCount;
    }
    
    public void setPageCount(int i)
    {
        pageCount=i;
    }
    
    public int getRecordCount()
    {
        return recordCount;
    }
    
    public void setRecordCount(int i)
    {
        recordCount=i;
    }
    
    public static void main(String arg[])
    {
     
    }
}