waredb.java

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

package electric.dbs;

import electric.dbs.Ware;
import java.util.Collection;
import java.util.ArrayList;
import java.sql.*;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Iterator;
import electric.electricUtils.*;

public class Waredb
{
	public Waredb()
	{
	}
	
	//对分页后的数据进行判断所产生的页数
	public int selectsql(int st)
	{
		String countsql="SELECT COUNT(*) FROM [WARE] WHERE STATUS<>1";
		Connection con=null;
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		int size=0;
		int sizepa=0;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(countsql);
		   rs=pstmt.executeQuery();
		   while(rs.next())
		   {
		   	size=rs.getInt(1);
		   	sizepa=size/st;
		   	if(size%st>0)
		   	{
		   		sizepa=sizepa+1;
		   	}
		   }	
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
        return sizepa;
	}
	
	//使用迭代的方法，将查询的信息保存到Collection对象中
	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 [WARE] WHERE STATUS<>1";
			con=DbConnectionManager.getConnection();
			pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs=pstmt.executeQuery();
			if(tip<=0)
			{
				rs.beforeFirst();
			}
			else
			{
				if(!rs.absolute(tip))
				{
					rs.beforeFirst();
				}
			}
			for(int i=1;rs.next()&&i<=step;i++)
			{
				Ware ware=new Ware();
				ware.setId(rs.getInt(1));
				ware.setPname(rs.getString(2));
				ware.setPmodel(rs.getString(3));
				ware.setPcost(rs.getString(4));
				ware.setPheft(rs.getString(5));
				ware.setPfacturer(rs.getString(6));
				ware.setPnote(rs.getString(7));
				ware.setCreatedate(rs.getString(8));
				ware.setStatus(rs.getInt(9));
				coll.add(ware);				
			}
			rs.close();
			pstmt.close();
			con.close();
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
		finally
		{
			return coll;
		}
	}
}