shopdb.java

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

package electric.dbs;

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

public class Shopdb
{
	public Shopdb()
	{
	}
	//根据每页显示的记录，进行划分总的页数
	public int selectsql(int st)
	{
		String countSql="SELECT COUNT(*) FROM [USER] 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=(int)size/st;
				if(size%st>0)
				{
					sizepa=sizepa+1;
				}
			}
			rs.close();
			pstmt.close();
			con.close();
		}
		catch(SQLException sqle)
		{
			System.err.println(sqle.getMessage());
		}
		return sizepa;
	}
	
	//根据传递的值进行分页处理，将结果保存到Collection中
	public Collection select(int step,int page)
	{
		Collection coll=new ArrayList();
		Connection con=null;
		PreparedStatement pstmt=null;
		int tip=step*(page-1);
		try
		{
			String sql="SELECT * FROM [USER] WHERE STATUS<>1";
			con=DbConnectionManager.getConnection();
			pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs=pstmt.executeQuery();
			if(tip<=0)
			{
				rs.beforeFirst();
			}
			else
			{
				if(!rs.absolute(tip))
				{
					rs.beforeFirst();
				}
			}
			for(int i=1;rs.next()&&i<=step;i++)
			{
				Shop shop=new Shop();
				shop.setId(rs.getInt(1));
				shop.setUsername(rs.getString(2));
				shop.setPassword(rs.getString(3));
				shop.setShopname(rs.getString(4));
				shop.setArea(rs.getString(5));
				shop.setPeopleman(rs.getString(6));
				shop.setMantel(rs.getString(7));
				shop.setShoptel(rs.getString(8));
				shop.setUsertype(rs.getInt(9));
				shop.setCreatedate(rs.getString(10));
				shop.setStatus(rs.getInt(11));
				coll.add(shop);
			}
			rs.close();
			pstmt.close();
			con.close();
			
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
		finally
		{
			return coll;
		}
	}
	
	public Collection select_chain(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 [USER] WHERE STATUS<>1 AND Usertype=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();
	    	}
	    }
	    for(int i=1;rs.next()&&i<=step;i++)
	    {
	    	Shop shop=new Shop();
			shop.setId(rs.getInt(1));
			shop.setUsername(rs.getString(2));
			shop.setPassword(rs.getString(3));
			shop.setShopname(rs.getString(4));
			shop.setArea(rs.getString(5));
			shop.setPeopleman(rs.getString(6));
			shop.setMantel(rs.getString(7));
			shop.setShoptel(rs.getString(8));
			shop.setUsertype(rs.getInt(9));
			shop.setCreatedate(rs.getString(10));
			shop.setStatus(rs.getInt(11));
			coll.add(shop);
	    }
	    rs.close();
	    pstmt.close();
	    con.close();
	  }	
	  catch(SQLException sqle)
	  {
	  	System.out.println(sqle.getMessage());
	  }
	  finally
	  {
	  	return coll;
	  }
	}
	
	public static void main(String args[])
	{
		Shopdb db=new Shopdb();
		Collection coll=db.select_chain(5,1);
		Iterator ii=coll.iterator();
		while(ii.hasNext())
		{
			Shop shop=(Shop)ii.next();
			System.out.println(shop.getId());
			System.out.println(shop.getUsername());
		}
	}
}