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<Product> find(int page){ List<Product> 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" %> <html> <head> <title>商品信息条目</title> <style type="text/css"> td{font-size: 12px;} h2{margin: 0; } body{ background: #d7c7e9; align-items: center; text-align: center; font-size: large; } </style> </head> <body> <table align="center" width="800" border="1" height="400" bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1"> <tr bgcolor="white"> <td align="center" colspan="5"> <h2>所有商品信息</h2> </td> </tr> <tr align="center" bgcolor="#e1ffc1" > <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" bgcolor="white"> <td style="font-size: 16px;"><%=p.getId()%></td> <td style="font-size: 16px;"><%=p.getName()%></td> <td style="font-size: 16px;"><%=p.getPrice()%></td> <td style="font-size: 16px;"><%=p.getNum()%></td> <td style="font-size: 16px;"><%=p.getUnit()%></td> </tr> <% } %> <tr> <td style="font-size: 16px;" align="center" colspan="5" bgcolor="white"> <%=request.getAttribute("bar")%> </td> </tr> </table> </body> </html>
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<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("『").append(i).append("』"); }else{ sb.append("<a href=FindProductServlet?page=").append(i).append(">").append(i).append("</a>"); } sb.append(" "); } request.setAttribute("bar", sb.toString()); request.getRequestDispatcher("/JDBCOption/Product_list.jsp").forward(request, response); } }
5、调试运行
- 在项目的 index 增加一句:
<input type="button" value="分页查询" onclick="window.location.href='FindProductServlet'">
- 部署到 Tomcat 运行,效果如下:
- 与预计效果一样