10.4 JDBC在Jave Web中的应用 分页查询
通过MySQL数据库提供的分页机制，实现商品信息的分页查询功能，将分页数据显示在JSP页面中。
- 1.
Product.java类 bean类
public class Product {
//id
private int id;
//名称
private String name;
//价格
private double price;
//数量
private int num;
//单位
private String unit;
//每页条数
public static final int PAGE_SIZE=2;
public Product() {
super();
}
public Product(int id, String name, double price, int num, String unit) {
super();
this.id = id;
this.name = name;
this.price = price;
this.num = num;
this.unit = unit;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
//省略部分 set、get方法
}
- 2.
ProductDao.java
/**
*
* Connection getConnection() 用来获取对象
* List<Product> find(int page) 根据分页获取Product集合
* int findCount() 获取数据库商品总数，用来判断分几页
* @author Admin
*
*/
public class ProductDao {
/**
* 获取 Connection对象
* @return
*/
public Connection getConnection(){
Connection connection=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String password="hwp123456";
String url="jdbc:mysql://localhost:3306/book";
String user="root";
connection=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public List<Product> find(int page){
List<Product> productList=new ArrayList<>();
Connection conn = getConnection();
String sql =" select * from product_tb order by id desc limit ?,?";
//limit arg1,arg2
//参数说明：
//arg1：用于指定查询记录的起始位置。
//arg2：用于指定查询数据所返回的记录数。
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, (page-1)*Product.PAGE_SIZE);
ps.setInt(2, Product.PAGE_SIZE);
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()){
productList.add(new Product(resultSet.getInt("id"),
resultSet.getString("name"),
resultSet.getDouble("price"),
resultSet.getInt("num"),
resultSet.getString("unit")));
}
conn.close();
ps.close();
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
return productList;
}
/**
* 获取总数
* @return
*/
public int findCount(){
int count=0;
Connection conn = getConnection();
//获取总数的sql语句
String sql =" select count(*) from product_tb";
try {
Statement statement= conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()){
count=resultSet.getInt(1);//对总数赋值
}
conn.close();
statement.close();
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
return count;//返回总数
}
}
- 3
.FindServlet.java类
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet{
private static final long serialVersionUID = 1L;
@Override
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("『"+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);
}
}
- 4.
product_list.jsp
该页面通过获取查询结果集List与分页条来分页显示商品信息数据
<%@page import="com.hwp.bean.Product"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="java.util.*"%>
<%@ page import="com.hwp.dao.*"%>
<!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=GB18030">
<title>Insert title here</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");
if(list!=null&&list.size()>0){
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.getUnit() %></td>
</tr>
<%
}
}
%>
<tr>
<td align="center" colspan="5"><%=request.getAttribute("bar") %>
</td>
</tr>
</table>
</body>
</html>
- 5.
index.jsp
编写程序中的主页面index.jsp，在该页面中编写分页查询商品信息的超链接，指向FindServlet
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>Insert title here</title>
</head>
<body>
<a href="FindServlet">查看所有商品信息</a>
</body>
</html>
表结构
微信截图_20190828173819.png
主页 index.jsp
微信截图_20190828173534.png
展示查询页面 product_list.jsp
微信截图_20190828173518.png
网友评论