分页查询

分页查询是Java Web开发中常用的技术，在数据库量非常大的情况下，不适合将所有的数据都显示到一个页面中，此时就要对数据进行分页查询。
MySQL数据库里面提供的分页机制limit关键字
limit arg1,arg2
参数说明：
arg1用于指定查询记录的起始位置
arg2用于指定查询数据所返回的记录数
例：通过MySQL数据库提供的分页机制，实现分页查询功能，将分页数据显示在JSP页面中
先创建一个Product得类，用于封装信息，JavaBean。

public class Product {
    public static final int PAGE_SIZE = 2; //每页的记录数
    private int id;                        //编号
    private String name;                   //名称
    private double price;                  //价钱
    private int num;                       //数量
    private String nuit;                   //单位
    public int getId(){
        return id;
    }
    public void setId(int id){
        this.id=id;
    }
    public String getName(){
        return name;
    }
    public void setName(String name){
        this.name=name;
    }
    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 getNuit(){
        return nuit;
    }
    public void setNuit(String nuit){
        this.nuit=nuit;
    }

}

然后创建ProductDao类连接数据库，和分页查询功能

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;

public class ProduceDao {
    public Connection getConnection(){   
        Connection conn=null; 
        try{
            Class.forName("com.mysql.jdbc.Driver");
            // 数据库连接字符串
         conn=(Connection) DriverManager.getConnection("jdbc:mysql:"+"//127.0.0.1:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8","root","123456");
            System.out.println("数据库连接成功");
        }catch(ClassNotFoundException e){
            e.printStackTrace();

        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
    public List<Product> find(int page){                //find()方法用于实现分页查询功能，该方法根据入口的参数page传递页码，查询指定页码中的记录主要通过limit实现
        List<Product> list = new ArrayList<Product>();
        Connection conn=getConnection();
        String sql = "select * from tb_product order by id desc limit ?,?";   //分页查询的SQL语句
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, (page-1)*Product.PAGE_SIZE);       //对SQL语句的第一个参数（？）赋值
            ps.setInt(2, Product.PAGE_SIZE);
            ResultSet rs = ps.executeQuery();     //执行查询操作
            while(rs.next()){
                Product p=new Product();
                p.setId(rs.getInt("id"));
                p.setName(rs.getString("name"));
                p.setNum(rs.getInt("num"));
                p.setPrice(rs.getDouble("price"));
                p.setNuit(rs.getString("unit"));
                list.add(p);        //对id，name等属性进行赋值，然后添加到list里面
            }
            rs.close();
            ps.close();
            conn.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }
    public int findCount(){           
    //计算数据库中商品的总记录数，然后后面除以 PAGE_SIZE得到页数
        int count = 0;
        Connection conn=getConnection();
        String sql = "select count(*) from tb_product";
        try{
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs=((java.sql.Statement) stmt).executeQuery(sql);
            if(rs.next()){
                count = rs.getInt(1);
                System.out.println(count);
            }
            rs.close();
            conn.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return count;

    }

}

然后写FindServlet类，重写doGet（）方法，对分页请求进行处理

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

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

/**
 * Servlet implementation class FindServlet
 */
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * Default constructor. 
     */
    public FindServlet() {
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int currPage=1;          //当前页码
        if(request.getParameter("page")!=null){
            currPage = Integer.parseInt(request.getParameter("page"));
        }
        ProduceDao dao = new ProduceDao();
        List<Product> list = dao.find(currPage);
        request.setAttribute("list", list);
        int pages;
        int count = dao.findCount();
        if(count%Product.PAGE_SIZE==0){        //计算总页数
            pages = count/Product.PAGE_SIZE;
        }else{
            pages = count/Product.PAGE_SIZE + 1;
        }
        StringBuffer sb = new StringBuffer();
        for(int i=1;i<=pages;i++){            //通过循环构建分页条
            if(i == currPage){
                sb.append("『" + i + "』");
            }else{
                sb.append("<a href='FindServlet?page=" + i + "'>" + i + "</a>");  //用超链接的方法显示分页条
            }
            sb.append(" ");
        }
        request.setAttribute("bar", sb.toString());
        request.getRequestDispatcher("product_list.jsp").forward(request, response); //转到product_list.jsp页面上来显示结果

        // TODO Auto-generated method stub
    }

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

}

创建product_list.jsp页面显示结果

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.List"%>
<%@page import="com.Product"%>
<!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>所有商品信息</title>
</head>
<body>
<table align="center" width="450" border="1">
<tr>
<td align="center" colspan="5">
<h2>所有商品信息</h2>
</td>
</tr>
<tr align="center">
<td><b>ID</b></td>
<td><b>名称</b></td>
<td><b>价格</b></td>
<td><b>数量</b></td>
<td><b>单位</b></td>
</tr>
<%
List<Product> list = (List<Product>)request.getAttribute("list");
for(Product p:list){

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

查询结果集list与分页条都是从request对象中获取，其中list通过for遍历并将每个商品信息输出到页面上，分页条输出到商品信息下方。
最后编写主程序页面index.jsp

<%@ 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>
<a href= "FindServlet">查看所有商品信息1</a>
<body>
</html>