本文共 6036 字，大约阅读时间需要 20 分钟。

JavaWeb——JDBC操作数据库

八、分页查询

• 当数据库中数据量非常大时，将所有数据都存放在一个页面显然是不合适的，因此有必要进行分页查询，将数据按顺序分为几个页面来显示。
• 实现方法由两种，一种是通过 Resultset 的光标进行分页，优点是数据库通用，缺点是占用资源非常大；另外一种是通过数据库机制进行分页，本博文也是采用此种方法，针对 MySQL 数据库的语法，使用 limit 关键字，做到分页查询。

1、数据表准备

• 在目标数据库新建一个数据表，结果如下图：
  
  
  

3、编写 Bean

• 需要准备两个 Bean，代码分别如下：
• Bean 1

  public class Product {
           public static final int PAGE_SIZE = 2;    private int id;    private String name;    private double price;    private int num;    private String unit;    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 getUnit() {
               return unit;    }    public void setUnit(String unit) {
               this.unit = unit;    }}

• Bean 2

  import java.sql.*;import java.util.ArrayList;import java.util.List;public class ProductDao {
           public Connection getConnection(){
               Connection conn;        try{
                   Class.forName("com.mysql.jdbc.Driver");            String url = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";            String username = "root";            String password = "********";            try{
                       conn = DriverManager.getConnection(url, username, password);                if(conn != null)                {
                           return conn;                }            }catch (SQLException e){
                       e.printStackTrace();            }        }catch (ClassNotFoundException e){
                   e.printStackTrace();        }        return null;    }    public List
       
         find(int page){
               List
        
          list = new ArrayList<>();        Connection conn = getConnection();        String sql = "select * from tb_product order by id desc limit ?,?";        try{
                   PreparedStatement ps = conn.prepareStatement(sql);            ps.setInt(1, (page-1)*Product.PAGE_SIZE);            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.setPrice(rs.getDouble("price"));                p.setNum(rs.getInt("num"));                p.setUnit(rs.getString("unit"));                list.add(p);            }            rs.close();            ps.close();            conn.close();        } catch (SQLException e){
                   e.printStackTrace();        }        return list;    }    public int findCount(){
               int count = 0;        Connection conn = getConnection();        String sql = "select count(*) from tb_product";        try{
                   Statement stmt = conn.createStatement();            ResultSet rs = stmt.executeQuery(sql);            if(rs.next()){
                       count = rs.getInt(1);            }            rs.close();            stmt.close();            conn.close();        } catch (SQLException e){
                   e.printStackTrace();        }        return count;    }}
        
       

3、JSP 页面准备

• 准备一个可视化页面，JSP，编辑如下代码：

  <%@ page contentType="text/html;charset=UTF-8" language="java" %><%@ page import="com.lyq.bean.Product" %><%@ page import="java.util.List" %>        
       
         list = (List
        
         )request.getAttribute("list");        for(Product p : list){    %>    
        
       
       
      <%        List
      <%        }    %>    
            
         
                  
              
  所有商品信息
          
             
             
                  
  ID
                  
  商品名称
                  
  价格
                  
  数量
                  
  单位
             
         
                  
  <%=p.getId()%>
                  
  <%=p.getName()%>
                  
  <%=p.getPrice()%>
                  
  <%=p.getNum()%>
                  
  <%=p.getUnit()%>
             
         
                  
              <%=request.getAttribute("bar")%>        
             
        
       

4、Servlet 创建

• 创建一个 Servlet 用于处理请求，代码如下：

  import com.lyq.bean.Product;import com.lyq.bean.ProductDao;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.util.List;@WebServlet(name = "FindProductServlet", urlPatterns = "/FindProductServlet")public class FindProductServlet extends HttpServlet {
           protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           }    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
               int currPage = 1;        if(request.getParameter("page") != null){
                   currPage = Integer.parseInt(request.getParameter("page"));        }        ProductDao dao = new ProductDao();        List
       
         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("『").append(i).append("』");            }else{
                       sb.append("
        ").append(i).append("");            }            sb.append(" ");        }        request.setAttribute("bar", sb.toString());        request.getRequestDispatcher("/JDBCOption/Product_list.jsp").forward(request, response);    }}
       

5、调试运行

• 在项目的 index 增加一句：

• 部署到 Tomcat 运行，效果如下：
  
• 与预计效果一样

转载地址：http://laqgn.baihongyu.com/