使用JDBC实现分页查询

来源：互联网发布：c语言入门程序设计编辑：程序博客网时间：2024/05/17 10:29

在MySQL中使用关键字limit控制查询的起始位置和返回的记录数量来实现分页，limit arg1,arg2.

示例如下：

起始页index.jsp：

<%@page import="java.net.URLDecoder"%><%@ page language="java" import="java.util.*"import="com.home.web.dto.*" 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>起始页</title></head><body><a href="FindServlet">查看所有商品信息</a></body></html>

查询数据的FindServlet：

package com.home.web.servlet;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;import com.home.web.dao.book.BookDao;import com.home.web.dto.Book;/** * Servlet implementation class AddServlet */@WebServlet("/FindServlet")public class FindServlet extends HttpServlet {private static final long serialVersionUID = 1L;           /**     * @see HttpServlet#HttpServlet()     */    public FindServlet() {        super();    }/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        int curPage=1;    if(request.getParameter("page")!=null){        curPage=Integer.parseInt(request.getParameter("page"));    }        BookDao dao = new BookDao();        List<Book> list = dao.find(curPage);        request.setAttribute("list", list);        int pages;        int count = dao.findCount();        // 计算页数        if (count % Book.PAGE_SIZE == 0) {            pages = count / Book.PAGE_SIZE;        } else {            pages = count / Book.PAGE_SIZE + 1;        }        // 构建分页条        StringBuffer sb = new StringBuffer();        for (int i = 1; i <= pages; i++) {            if (i == curPage) {                sb.append("【" + i + "】");            } else {                sb.append("<a href='FindServlet?page=" + i + "'>" + i + "</a>");            }            sb.append("  ");        }        request.setAttribute("bar", sb.toString());        request.getRequestDispatcher("result.jsp").forward(request, response);}/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        doGet(request, response);}}

展示页面result.jsp：

<%@page import="java.sql.*"%><%@ page language="java" import="java.util.*" autoFlush="true"import="com.home.web.dto.*" 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><table align="center" width="450" border="1"><tr><td colspan="5" align="center"><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<Book> list = (List<Book>) request.getAttribute("list");    if (list == null || list.size() < 1) {        out.println("没有数据！");    } else {        for (Book book : list) {%><tr align="center"><td><%=book.getId()%></td><td><%=book.getName()%></td><td><%=book.getPrice()%></td><td><%=book.getBookCount()%></td><td><%=book.getAuthor()%></td></tr><%    }    }%><tr><td align="center" colspan="5"><%=request.getAttribute("bar")%></td></tr></table></body></html>

实体类Book：

package com.home.web.dto;public class Book {    public static final int PAGE_SIZE = 3;    private int id;    private String name;    private double price;    private int bookCount;    private String author;    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 getBookCount() {        return bookCount;    }    public void setBookCount(int bookCount) {        this.bookCount = bookCount;    }    public String getAuthor() {        return author;    }    public void setAuthor(String author) {        this.author = author;    }}

数据库操作类BookDao:

0 0