productdb.java

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

package electric.dbs;
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.*;
import electric.dbs.*;

public class Productdb
{
	public Productdb()
	{
	}
	
	//根据传递的参数，给出获取的总页数
	public int selectsql(int st)
	{
		String countSql="SELECT COUNT(*) FROM PRODUCT WHERE STATUS<>1";
		Connection co=null;
		ResultSet rs=null;
		PreparedStatement pstm=null;
		int size=0;
		int sizepa=0;
		try
		{
			co=DbConnectionManager.getConnection();
			pstm=co.prepareStatement(countSql);
			rs=pstm.executeQuery();
			while(rs.next())
			{
				size=rs.getInt(1);
				sizepa=(int)size/st;
				if(size%st>0)
				{
					sizepa=sizepa+1;
				}
			}
			rs.close();
			pstm.close();
			co.close();			
		}
		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 [PRODUCT] WHERE STATUS<>1";
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		   rs=pstmt.executeQuery();
		   //System.out.println(tip);
		   if(tip<=0)
		   {
		   	rs.beforeFirst();
		   }	
		   else
		   {
		   	 if(!rs.absolute(tip))
		   	 {
		   	 	rs.beforeFirst();
		   	 }
		   }
		   //将检索的的结果放到Collection中
		   for(int i=1;rs.next()&&i<=step;i++)
		   {
		   	Product product=new Product();
		    product.setId(rs.getInt(1));
		    product.setProductname(rs.getString(2));
		    product.setProductprice(rs.getString(3));
		    product.setProductform(rs.getString(4));
		    product.setProductnote(rs.getString(5));
		    product.setPic(rs.getString(6));
		    product.setStatus(rs.getInt(7));
		    coll.add(product);	    
		   }
		   rs.close();
		   pstmt.close();
		   con.close();
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		finally
		{
			return coll;
		}
	}
}