分页实现

今天来说一下分页实现，我用的是mysql、jdbc、jsp去实现分页。
mysql使用limit而oracle使用的的是top去实现分页。

首先是定义一个商品类，下面是代码：

/**
 * 商品类
 * @author chenjingbin
 *
 */
public class Product {
    /**
     * 页面大小
     */
    public static  final int PAGE_SIZE = 3;
    /**
     * 商品id号
     */
    private int id ; 
    /**
     * 商品价格
     */
    private double price;
    /**
     * 商品数量
     */
    private int num ; 
    /**
     * 商品名称
     */
    private String  name ;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public double getPrice() {
        return price;
    }
    public void setPrice(double price) {
        this.price = price;
    }
    public int getNum() {
        return num;
    }
    public void setNum(int num) {
        this.num = num;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", price=" + price + ", num=" + num + ", name=" + name + "]";
    }



}

然后在写DAO类：里面的一些配置参数可以放到一个配置文件里面去写的，不只是我这种写法的。

/**
 * ProductDao 主要用于封装数据库一些操作
 * @author chenjingbin
 *
 */
public class ProductDao {

    private static final String  URL ="jdbc:mysql://localhost:3306/java";
    private static  final String  DRIVER="com.mysql.jdbc.Driver";
    private static final String NAME="root";
    private static final String PWD="111111";
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 得到Connection对象函数
     * @return
     */
    public static  Connection getConnection(){
        Connection connection  = null;
        try {
            connection = DriverManager.getConnection(URL, NAME, PWD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return connection;
    }
    /**
     * 关闭输入流
     * @param connection 
     * @param statement
     * @param resultSet
     */
    public static  void close(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet){
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(preparedStatement!= null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    /**
     * 分页查询所有商品信息
     * @param page 页数
     * @return List<Product>
     */
    public List<Product> find (int page){
        String sql = "select id,price ,num,name from product order by id desc limit ?,?";
        List<Product > list  = new ArrayList<Product>();
        Connection connection = getConnection();
        PreparedStatement prepareStatement = null ;
        ResultSet resultSet = null; 
        try {
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement .setInt(1, (page-1)*Product.PAGE_SIZE);
            prepareStatement.setInt(2, Product.PAGE_SIZE);
            resultSet  = prepareStatement.executeQuery();
            while(resultSet.next()){
                Product product = new Product();
                product.setId(resultSet.getInt("id"));
                product.setName(resultSet.getString("name"));
                product.setPrice(resultSet.getDouble("price"));
                product.setNum(resultSet.getInt("num"));
                list.add(product);
            }

            } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            close(connection, prepareStatement, resultSet);
        }
        return list;
    }
    /**
     * 查询总记录数
     * @return count 
     */
    public int findCount(){
        int count = 0; 
        Connection connection = getConnection();
        String sql = "select count(*) from product ";
        Statement statement = null; 
        ResultSet resultSet = null; 
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                count = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(connection!= null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            if(statement!= null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(resultSet!= null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return count;
    }
}

下面是写servlet类：

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;



public class FindServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            int countpage = 1; //当前码数
            if(request.getParameter("page") != null){
                countpage = Integer .parseInt(request.getParameter("page"));
            }
            ProductDao productDao = new ProductDao();
            List<Product> list = productDao.find(countpage);
            request.setAttribute("list", list);
            int page ; //总页数
            int count  = productDao.findCount();//查询总记录数
            if(count%Product.PAGE_SIZE == 0){
                page = count /Product.PAGE_SIZE;
            }else{
                page = count /Product.PAGE_SIZE +1;
            }
            StringBuffer  stringBuffer = new StringBuffer();
            for (int i = 1; i <= page; i++) {
                if(i == countpage){
                    stringBuffer.append("["+i+"]");
                }
                else{
                    stringBuffer.append("<a href='/webDay01/Find?page="+i+"'>"+i+"</a>");//构建分页条
                }
                stringBuffer.append("&nbsp;&nbsp;&nbsp;");
            }
        request.setAttribute("bar", stringBuffer.toString());
        request.getRequestDispatcher("product_list.jsp").forward(request, response);
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        this .doGet(request, response);
    }

}

接下来是product_list.jsp页面：

<%@page import="com.web.page.Product"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
    <table align="center" width=" 800" border="1">
        <tr>
            <td align="center" colspan="4">
                <h3>所有商品信息</h3>
            </td>
        </tr>
        <tr>
            <td >
                商品id
            </td>
            <td >
                商品名
            </td>
            <td >
                商品价格
            </td>
            <td >
                商品数量
            </td>
        </tr>
        <% 
        List<Product> list = (List<Product>)request.getAttribute("list");   
        for(Product p :list){
        %>  

            <tr >
                <td><%= p.getId() %></td>
                <td><%= p.getName() %></td>
                <td><%= p.getPrice() %></td>
                <td><%= p.getNum() %></td>
            </tr>
        <%} %>
        <tr>
            <td align="center"  colspan="4">
                <%=request.getAttribute("bar") %>
            </td>
        </tr>
    </table>

</body>
</html>

剩下index.jsp页面

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8" import="java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
 <body>
  <a href="/webDay01/Find">查询所有商品信息</a>
</body>
</html>

最终效果图如下：

总结一下：第一，在跳转servlet的时候忘记添加上项目名而出现了404错误，第二，粗心，通过request 返回我需要的对象的时候我打成request.getParameter(“”);这里应该是request.getAttribute(“”);才对的。