JDBC操作数据库之分页查询

最新推荐文章于 2021-11-04 21:04:35 发布

微微尼亚

最新推荐文章于 2021-11-04 21:04:35 发布

阅读量438

点赞数

1、封装商品信息的JavaBean（Product.java）

package com.Bean;

public class Product {
	public static final int PAGE_SIZE = 2;//定义每页记录数
	private int id;
	private String product_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 getProduct_name() {
		return product_name;
	}
	public void setProduct_name(String product_name) {
		this.product_name = product_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;
	}
	
}

2、封装商品数据库的相关操作

package com.Connection.Test;

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

import com.Bean.Product;


public class ProductDao {
        //返回指定页面的数据信息
	public List<Product> find(int page){
		List<Product> list = new ArrayList<Product>();
		Connection conn = JdbcUtil.getConnection();
		String sql = "select * from tb_product order by id asc limit ?,?";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, (page-1)*Product.PAGE_SIZE);
			pstmt.setInt(2, Product.PAGE_SIZE);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setProduct_name(rs.getString("product_name"));
				p.setNum(rs.getInt("num"));
				p.setPrice(rs.getDouble("price"));
				p.setUnit(rs.getString("unit"));
				list.add(p);
			}
			rs.close();
			pstmt.close();
			conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
				
		return list;
		
	}
        //返回所有数据信息的总记录数
	public int findCount() {
		int count = 0;
		Connection conn = JdbcUtil.getConnection();
		String sql = "select count(*) from tb_product";
		Statement stmt;
		try {
			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、分页查询商品信息的Servlet对象，在该类中重写doGet方法处理分页请求

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int currPage = 1;  //当前页码
		if(request.getParameter("page")!=null) {
			currPage = Integer.valueOf(request.getParameter("page"));//对当前页码赋值
		}
		ProductDao dao = new ProductDao();
		List<Product> list = dao.find(currPage); //查询当前页码商品的数据记录
		request.setAttribute("list", list);//将此页面的数据记录放到request中
		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='FindByPage?page="+i+"'>"+i+"</a>"); //构建其他页面的分页条
			}
			sb.append(" ");//构建分页条
		}
		request.setAttribute("bar", sb.toString());//将分页条的字符串放置到request对象中
		request.getRequestDispatcher("product_list.jsp").forward(request, response);  //转发到product_list.jsp页面
	}

4、商品信息显示页面

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

微微尼亚

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JDBC操作数据库之分页查询

1、封装商品信息的JavaBean（Product.java）package com.Bean;public class Product { public static final int PAGE_SIZE = 2;//定义每页记录数 private int id; private String product_name; private double price; priva
复制链接

扫一扫