newsdb.java

基于JSP的家电连锁超市管理系统 数据库也在文件内

package electric.dbs;
import electric.dbs.Dbnews;
import electric.dbs.News;
import java.util.Collection;
import java.util.ArrayList;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Iterator;
import java.sql.*;
import electric.electricUtils.*;

public class Newsdb
{
	public Newsdb()
	{
	}
	//将要分页的大小作为参数传递到selectsql方法中
	//获取显示的页数
	public int selectsql(int st)
	{
		String countSql="SELECT COUNT(*) FROM NEWS WHERE STATUS<>1";
		Connection co=null;
		ResultSet r=null;
		PreparedStatement pstm=null;
		int size=0;
		int sizepa=0;
		try
		{
			co=DbConnectionManager.getConnection();
			pstm=co.prepareStatement(countSql);
			r=pstm.executeQuery();
			while(r.next())
			{
				size=r.getInt(1);
				sizepa=(int)size/st;
				if(size%st>0)
				{
					sizepa=sizepa+1;
				}
			}
			r.close();
			r=null;
			pstm.close();
			pstm=null;
			co.close();
			co=null;
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
		return sizepa;
	}
	//每页要显示的大小及要显示的页数
	public Collection select(int step,int page)
	{
		Collection coll=new ArrayList();
		Connection con=null;
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		int tip=step*(page-1);
		try
		{
			String sql="SELECT * FROM [NEWS] WHERE STATUS<>1";
			con=DbConnectionManager.getConnection();
			pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
			   ResultSet.CONCUR_READ_ONLY);
			rs=pstmt.executeQuery();
			//System.out.print(tip);
			if(tip<=0)
			{
				rs.beforeFirst();
			}
			else
			{
				if(!rs.absolute(tip))
				{
					rs.beforeFirst();
				}
			}
			//将符合要求的结果存储到Collection中，返回到前台进行读取
			for(int i=1;rs.next()&&i<=step;i++)
			{
				News news=new News();
				news.setId(rs.getInt(1));
				news.setMotif(rs.getString(2));
				news.setKithepeople(rs.getString(3));
				news.setContent(rs.getString(4));
				news.setCreatedate(rs.getString(5));
				news.setCreatepeople(rs.getInt(6));
				news.setStatus(rs.getInt(7));
				coll.add(news);
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			con.close();
			con=null;
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		finally
		{
			return coll;
		}
	}
	
	public static void main(String args[])
	{
		
		try
		{
			Newsdb db=new Newsdb();
		    int count=db.selectsql(10);
		    System.out.println("#### "+count+"$$");
		    ArrayList al=(ArrayList)db.select(5,1);
		    Iterator ii=al.iterator();
		    while(ii.hasNext())
		    {
		    	News n=(News)ii.next();
		    	System.out.print(n.getId()+" ");
				System.out.print(n.getMotif()+" ");
				System.out.print(n.getKithepeople()+" ");
				System.out.print(n.getContent()+" ");
				System.out.print(n.getCreatedate()+" ");
				System.out.print(n.getCreatePeople()+" ");
				System.out.print(n.getStatus()+" ");
				
				System.out.println("");
		    }		    		    		                
		}
		catch(Exception se)
		{
			se.printStackTrace();
		}
	}	
}