goodsdao.java

在线购物系统,ajax+jsp实现

package com.accp.gz.th.zm.server.dao;

import java.util.ArrayList;
import java.sql.*;
import com.accp.gz.th.zm.server.entity.Goods;
import com.accp.gz.th.zm.client.javabean.PageBean;

/**
 * Goods表的处理类
 */
public class GoodsDAO {
    /**
     * 查找所有记录
     */
    public ArrayList select() {

        ArrayList list = new ArrayList();

        Connection conn = ConnectionManager.getConn();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from Goods");
            while(rs.next()){
                Goods g=new Goods();
                g.setId(rs.getInt(1));
                g.setName(rs.getString(2));
                g.setPrice(rs.getInt(3));
                g.setNumber(rs.getInt(4));
                list.add(g);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }

        return list;
    }

    /**
     * 根据页码和每页行数查找记录
     */
    public ArrayList select(int pageNo,int linesPerPage) {

        int total = pageNo*linesPerPage;
        int start = (pageNo-1)*linesPerPage;
        ArrayList list = new ArrayList();

        Connection conn = ConnectionManager.getConn();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select top "+ total +" * from Goods");

            int count=0;
            while(rs.next()){

                if(count<start){
                    count++;
                    continue;
                }

                Goods g=new Goods();
                g.setId(rs.getInt(1));
                g.setName(rs.getString(2));
                g.setPrice(rs.getInt(3));
                g.setNumber(rs.getInt(4));
                list.add(g);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }

        return list;
    }

 /**
 * 根据页码和每页行数查找记录(接受数据时通过JavaBean获得，要记得计算其他值)
 */
public ArrayList select(PageBean bean) {

    int total = bean.getPageNo()*bean.getLinesPerPage();
    int start = (bean.getPageNo()-1)*bean.getLinesPerPage();
    ArrayList list = new ArrayList();

    String sql="select top "+ total +" * from Goods";
    int tid = bean.getTid();
    if(tid>0)
        sql+=" where tid="+tid;

    Connection conn = ConnectionManager.getConn();
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        int count=0;
        while(rs.next()){

            if(count<start){
                count++;
                continue;
            }

            Goods g=new Goods();
            g.setId(rs.getInt(1));
            g.setName(rs.getString(2));
            g.setPrice(rs.getInt(3));
            g.setNumber(rs.getInt(4));
            list.add(g);
        }
    } catch (SQLException ex) {
        System.out.println(ex.toString());
    }

    // 在返回之前，把其他几个值计算出来，也设置到JavaBean中去


    // 总页数
    int totalPages = getTotalPages(bean);
    bean.setTotalPages(totalPages);


    // 最后一页的行数

    // 总行数


    return list;
}





    /**
     * 计算总页数
     * @param linesPerPage int
     * @return int
     */
    public int getTotalPages(PageBean bean){

        int totalPages=bean.getTotalPages();

        String sql="select count(*) from Goods";
        int tid = bean.getTid();
        if(tid>0)
            sql+=" where tid="+tid;


        Connection conn = ConnectionManager.getConn();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            if(rs.next()){
                int temp = rs.getInt(1);
                totalPages = temp%bean.getLinesPerPage()==0?
                             temp/bean.getLinesPerPage():
                             temp/bean.getLinesPerPage()+1;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return totalPages;
    }

}