catalog.java

一个用Bean实现的数据分页显示程序

package jnestore.javabeans;

import javax.sql.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

import jnestore.javabeans.*;

/** 
 *Catalog类负责从数据库中获得商品信息
 */

public class Catalog {	 
    
    int rowsPerPage = 3;
    DataAccess dBean = new DataAccess();
    // 依据商品库存编号（SKU）查询商品
    public ItemInfo getItem(String sku) {
    	
    	ItemInfo item = new ItemInfo();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pStatement = null;

        try {
            //获得数据库连接
            con = dBean.getConnection();
            String sql = "SELECT * FROM CATALOG_ITEMS WHERE SKU = ?";
            //生成PrepatedStatement对象 
            pStatement = con.prepareStatement(sql);
            pStatement.setString(1, sku);
            //通过pStatement执行查询，返回结果集对象rs
            rs = pStatement.executeQuery();
            //将结果集中数据信息封装到ItemInfo对象中
            while (rs.next()) {
                item.setSku(rs.getString("SKU"));
                item.setName(rs.getString("NAME"));
                item.setDescription(rs.getString("DESCRIPTION"));
                item.setPrice(rs.getFloat("PRICE"));
            }

        } catch (Exception e) {
            System.out.println("Error retrieving SKU: " + sku);
        } finally {
            try {
                rs.close();
                pStatement.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
            return item;           
       
         }
  }  

    //取得所有商品信息，以ArrayList形式返回
    public ArrayList getAllItems() {         
        ArrayList items = new ArrayList();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pStatement = null;

        try {
            //获得数据库连接
            con = dBean.getConnection();

            String sql = "SELECT * FROM CATALOG_ITEMS";
            //生成PrepatedStatement对象
            pStatement = con.prepareStatement(sql);
            //通过pStatement执行查询，返回结果集对象rs
            rs = pStatement.executeQuery();
            //遍历结果集，将每一行的数据封装到一个ItemInfo对象中，ItemInfo对象
            //被存储到集合items中
            while (rs.next()) {
                ItemInfo item = new ItemInfo();
                item.setSku(rs.getString("SKU"));
                item.setName(rs.getString("NAME"));
                item.setDescription(rs.getString("DESCRIPTION"));
                item.setPrice(rs.getFloat("PRICE"));

                items.add(item);
            }

        } catch (Exception e) {
            System.out.println("Error retrieving catalog items");
        } finally {
            try {
                rs.close();
                pStatement.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
            return items;
          }
    }
       //返回catalog_items表中记录数
       private  int getAvailableCount()throws Exception
	{
		 Connection con = null;
		 Statement stmt = null;
		  ResultSet rs = null;
		  int ret =0;
		try {
		 con = dBean.getConnection();		
		 stmt=con.createStatement();
		String strSql="select count(*) from catalog_items";
		rs=stmt.executeQuery(strSql);
		while(rs.next())
		{
			ret=rs.getInt(1);
		}
	    } catch (Exception e) {
            System.out.println("Error ");
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
		return ret;		
	 }
  }
	//根据总行数计算总页数
	private int countTotalPage(int totalRows) {     
          if (totalRows % this.rowsPerPage==0){
           return totalRows/this.rowsPerPage;
         }else{
          return totalRows/this.rowsPerPage + 1;        
        }
      }
	//返回存储了指定页数据的PageDataBean对象
        public PageDataBean getPageData(String page)throws Exception
	{
	       DataAccess dBean = new DataAccess();
	       Connection con = dBean.getConnection();
	       Statement stmt = null;
	       ResultSet rs = null;
		int totalRows = getAvailableCount();//获得表中总行数
		int totalPages =  countTotalPage(totalRows);//获得总页数
		ArrayList data = new ArrayList();
		
		PageDataBean pageBean=new PageDataBean();
			
		try
		{
			
			int pageNum=Integer.parseInt(page);
			 stmt=con.createStatement();
			
			String strSql="select *   from catalog_items order by sku limit " + (pageNum-1)*rowsPerPage
			 +"," + rowsPerPage;//查询page页所对应的数据
			 rs=stmt.executeQuery(strSql);
	
			while(rs.next())
			{				
					ItemInfo item = new ItemInfo();	
					 item.setSku(rs.getString("SKU"));
                                          item.setName(rs.getString("NAME"));
                                         item.setDescription(rs.getString("DESCRIPTION"));
                                          item.setPrice(rs.getFloat("PRICE")); 
					 data.add(item);				
			}			
			
			pageBean.setCurPage(pageNum);
			pageBean.setData(data);//将page页对应的数据封装到pageBean对象中
			pageBean.setTotalPage(totalPages);			
			
		}
		catch (Exception e) {
                 System.out.println("Error ");
           } finally {
               try {
                  rs.close();
		  stmt.close();
                  con.close();
                } catch (Exception ex) {
                   System.out.println("Error closing database");
                }
	     return pageBean;	
	   }
	}


}