溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

分頁(yè)查詢功能如何在JavaWeb項(xiàng)目中實(shí)現(xiàn)

發(fā)布時(shí)間：2020-11-23 15:28:13 來(lái)源：億速云 閱讀：134 作者：Leah 欄目：開(kāi)發(fā)技術(shù)

本篇文章給大家分享的是有關(guān)分頁(yè)查詢功能如何在JavaWeb項(xiàng)目中實(shí)現(xiàn)，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

一、MySql實(shí)現(xiàn)分頁(yè)查詢的SQL語(yǔ)句

1、分頁(yè)需求：

客戶端通過(guò)傳遞pageNo(頁(yè)碼)，counter(每頁(yè)顯示的條數(shù))兩個(gè)參數(shù)去分頁(yè)查詢數(shù)據(jù)庫(kù)表中的數(shù)據(jù)，那我們知道MySql數(shù)據(jù)庫(kù)提供了分頁(yè)的函數(shù)limit m,n，但是該函數(shù)的用法和我們的需求不一樣，所以就需要我們根據(jù)實(shí)際情況去改寫適合我們自己的分頁(yè)語(yǔ)句，具體的分析如下：

比如：

查詢第1條到第10條的數(shù)據(jù)的sql是：select * from table limit 0,10;   ->對(duì)應(yīng)我們的需求就是查詢第一頁(yè)的數(shù)據(jù)：select * from table limit (1-1)*10,10;

查詢第10條到第20條的數(shù)據(jù)的sql是：select * from table limit 10,20;  ->對(duì)應(yīng)我們的需求就是查詢第二頁(yè)的數(shù)據(jù)：select * from table limit (2-1)*10,10;

查詢第20條到第30條的數(shù)據(jù)的sql是：select * from table limit 20,30;  ->對(duì)應(yīng)我們的需求就是查詢第三頁(yè)的數(shù)據(jù)：select * from table limit (3-1)*10,10;

2、總結(jié)

通過(guò)上面的分析，可以得出符合我們自己需求的分頁(yè)sql格式是：select * from table limit (pageNo-1)*counter,counter; 其中pageNo是頁(yè)碼，counter是每頁(yè)顯示的條數(shù)。

二、JavaWeb程序

1、創(chuàng)建PageBeanUtils.java工具類

package com.ambow.utils;
import java.util.List;
public class PageBeanUtils<T> {
 private int prePage;//上一頁(yè)
 private int nextPage;//下一頁(yè)
 private int firstPage=1;//首頁(yè)
 private int lastPage;//尾頁(yè)
 private int currentPage = 1;//當(dāng)前
 private int totalPage;//總頁(yè)數(shù)
 private int pageSize;//每頁(yè)顯示條數(shù)，默認(rèn)顯示10條
 private int totalData;//數(shù)據(jù)總條數(shù)
 private List<T> pageData;//數(shù)據(jù)
 public PageBeanUtils(int currentPage,int pageSize, int totalData) {
 this.currentPage = currentPage;
 this.pageSize = pageSize;
 this.totalData = totalData;
 
 //計(jì)算獲得總頁(yè)數(shù)（尾頁(yè)）
// this.totalPage = this.lastPage = (totalData+pageSize-1)/pageSize;
 this.totalPage = this.lastPage = (int)Math.ceil((double)totalData/pageSize);
 //防止當(dāng)前頁(yè)小于1
 this.currentPage = Math.max(this.currentPage, 1);
 //防止當(dāng)前頁(yè)大于總的頁(yè)數(shù)
 this.currentPage = Math.min(this.totalPage, this.currentPage);
 //設(shè)置上一頁(yè)，上一頁(yè)不能小于1
 this.prePage = Math.max(this.currentPage-1, 1);
 //設(shè)置下一頁(yè)，下一頁(yè)不能大于總頁(yè)數(shù)
 this.nextPage = Math.min(this.currentPage+1, this.totalPage);
 
 /**
 * ceil
public static double ceil(double a)
返回最小的（最接近負(fù)無(wú)窮大） double 值，該值大于等于參數(shù)，并等于某個(gè)整數(shù)。特殊情況如下：
如果參數(shù)值已經(jīng)等于某個(gè)整數(shù)，那么結(jié)果與該參數(shù)相同。
如果參數(shù)為 NaN、無(wú)窮大、正 0 或負(fù) 0，那么結(jié)果與參數(shù)相同。
如果參數(shù)值小于 0，但是大于 -1.0，那么結(jié)果為負(fù) 0。
注意， Math.ceil(x) 的值與 -Math.floor(-x) 的值完全相同。
參數(shù)：
a - 一個(gè)值。
返回：
最小（最接近負(fù)無(wú)窮大）浮點(diǎn)值，該值大于等于該參數(shù)，并等于某個(gè)整數(shù)。
 */
 
 }
 
 public PageBeanUtils(int prePage, int nextPage, int firstPage, int lastPage, int currentPage, int totalPage,
 int pageSize, int totalData, List<T> pageData) {
 super();
 this.prePage = prePage;
 this.nextPage = nextPage;
 this.firstPage = firstPage;
 this.lastPage = lastPage;
 this.currentPage = currentPage;
 this.totalPage = totalPage;
 this.pageSize = pageSize;
 this.totalData = totalData;
 this.pageData = pageData;
 }
 public int getPrePage() {
 return prePage;
 }
 public void setPrePage(int prePage) {
 this.prePage = prePage;
 }
 public int getNextPage() {
 return nextPage;
 }
 public void setNextPage(int nextPage) {
 this.nextPage = nextPage;
 }
 public int getFirstPage() {
 return firstPage;
 }
 public void setFirstPage(int firstPage) {
 this.firstPage = firstPage;
 }
 public int getLastPage() {
 return lastPage;
 }
 public void setLastPage(int lastPage) {
 this.lastPage = lastPage;
 }
 public int getCurrentPage() {
 return currentPage;
 }
 public void setCurrentPage(int currentPage) {
 this.currentPage = currentPage;
 }
 public int getTotalPage() {
 return totalPage;
 }
 public void setTotalPage(int totalPage) {
 this.totalPage = totalPage;
 }
 public int getPageSize() {
 return pageSize;
 }
 public void setPageSize(int pageSize) {
 this.pageSize = pageSize;
 }
 public int getTotalData() {
 return totalData;
 }
 public void setTotalData(int totalData) {
 this.totalData = totalData;
 }
 public List<T> getPageData() {
 return pageData;
 }
 public void setPageData(List<T> pageData) {
 this.pageData = pageData;
 }
 
 
 /*
 * 
 * 
 * totalPage = (totalData+pageSize-1)/pageSize;
 * 
 * 
 * */
}

2、在接口里面定義分頁(yè)查詢的方法

package com.ambow.dao;
 
import java.util.List;
 
import com.ambow.pojo.Good;
 
public interface IGoodDao {
 //增刪改查
 public void add(Good good);
 public void delete(Good good);
 public void update(Good good);
 public void query(Good good);
 public Good queryOne(Good good);
 public List<Good> queryMore(Good good);
 public List<Good> queryByName(String name);//根據(jù)商家名稱進(jìn)行模糊查詢
 //條件分頁(yè)查詢
 public List<Good> queryByName(String name,int currentPage,int pageSize);
 //獲取滿足某個(gè)條件的總記錄數(shù)
 public int getTotalNum(String name);
}

3、在接口的實(shí)現(xiàn)類里面實(shí)現(xiàn)方法

package com.ambow.dao.impl;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import com.ambow.dao.IGoodDao;
import com.ambow.pojo.Good;
import com.ambow.utils.DBUtils;
 
public class GoodDaoImpl implements IGoodDao {
 DBUtils db = new DBUtils();
 @Override
 public void add(Good good) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 String sql = "insert into good (name,address,tel,dishes) values (&#63;,&#63;,&#63;,&#63;)";
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, good.getName());
 pstmt.setString(2, good.getAddress());
 pstmt.setString(3, good.getTel());
 pstmt.setString(4, good.getDishes());
 int isOk = pstmt.executeUpdate();
 //System.out.println("add-----"+isOk);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 
 @Override
 public void delete(Good good) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 String sql = "delete from good where id = &#63;";
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setInt(1, good.getId());
 int isOk = pstmt.executeUpdate();
 System.out.println("delete-----"+isOk);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 
 }
 
 @Override
 public void update(Good good) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 String sql = "update good set name=&#63;,address=&#63;,tel=&#63;,dishes=&#63; where id=&#63;";
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, good.getName());
 pstmt.setString(2, good.getAddress());
 pstmt.setString(3, good.getTel());
 pstmt.setString(4, good.getDishes());
 pstmt.setInt(5,good.getId());
 int isOk = pstmt.executeUpdate();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 
 }
 
 @Override
 public void query(Good good) {
 // TODO Auto-generated method stub
 
 }
 
 @Override
 public Good queryOne(Good good) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 String sql = "select * from good where id = &#63;";
 Good gd = null;
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setInt(1, good.getId());
 rs = pstmt.executeQuery();
 while(rs.next()){
  gd = new Good(rs.getInt(1),rs.getString(2),rs.getString(3),
  rs.getString(4),rs.getString(5));
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return gd;
 
 }
 
 @Override
 public List<Good> queryMore(Good good) {
 // TODO Auto-generated method stub
 return null;
 }
 
 @Override
 public List<Good> queryByName(String name) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 String sql = "select * from good where name like &#63;";
 List<Good> goodList = new ArrayList<Good>();
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, "%"+name+"%");
 rs = pstmt.executeQuery();
 while(rs.next()){
  goodList.add(new Good(rs.getInt(1),rs.getString(2),rs.getString(3),
  rs.getString(4),rs.getString(5)));
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return goodList;
 }
 
 @Override
 public List<Good> queryByName(String name, int currentPage, int pageSize) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 String sql = "select * from good where name like &#63; limit &#63;,&#63;";
 List<Good> goodList = new ArrayList<Good>();
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, "%"+name+"%");
 pstmt.setInt(2,(currentPage-1)*pageSize);
 pstmt.setInt(3,pageSize);
 rs = pstmt.executeQuery();
 while(rs.next()){
  goodList.add(new Good(rs.getInt(1),rs.getString(2),rs.getString(3),
  rs.getString(4),rs.getString(5)));
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return goodList;
 
 }
 
 @Override
 public int getTotalNum(String name) {
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 String sql = "select count(id) from good where name like &#63;";
 int total = 0;
 try {
 conn = DBUtils.getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, "%"+name+"%");
 rs = pstmt.executeQuery();
 while(rs.next()){
  total = rs.getInt(1);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return total;
 
 }
 
}

4.在Servlet里面調(diào)用實(shí)現(xiàn)類里面的分頁(yè)查詢方法

package com.ambow.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.ambow.dao.IGoodDao;
import com.ambow.dao.impl.GoodDaoImpl;
import com.ambow.pojo.Good;
import com.ambow.utils.PageBeanUtils;
 
@WebServlet("/QueryServlet")
public class QueryServlet extends HttpServlet {
 private static final long serialVersionUID = 1L;
 private IGoodDao goodDao = new GoodDaoImpl();
 public QueryServlet() {
  super();
 }
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 doPost(request,response);
 }
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 request.setCharacterEncoding("utf-8");
 response.setCharacterEncoding("utf-8");
 String keywords = request.getParameter("kw");
 String method = request.getParameter("method");
 if("add".equals(method)){
 String name = request.getParameter("name");
 String address = request.getParameter("address");
 String tel = request.getParameter("tel");
 String dishes = request.getParameter("dishes");
 Good good = new Good(0,name,address,tel,dishes);
 goodDao.add(good);
 //調(diào)用dao的查詢方法，返回一個(gè)List
 List<Good> goods = goodDao.queryByName(keywords);
 request.setAttribute("list", goods);
 request.getRequestDispatcher("goods.jsp").forward(request, response);
 }else if("search".equals(method)){
 if(null==keywords) {
 keywords="";
 }
 int currentPage = 1;
 try {
 currentPage=Integer.parseInt(request.getParameter("curPage"));
 if(currentPage<=0) {
  currentPage = 1;
 }
 }catch(Exception e) {
 currentPage = 1;
 }
 int pageSize=10;
 int totalData = goodDao.getTotalNum(keywords);
 int totalPage = (int)Math.ceil((double)totalData/pageSize);
 if(currentPage>totalPage){
 currentPage = totalPage;
 }
 List<Good> goods = goodDao.queryByName(keywords,currentPage,pageSize); 
 PageBeanUtils pg = new PageBeanUtils(currentPage,pageSize,totalData);
 pg.setPageData(goods);
 request.setAttribute("pg", pg);
 request.getRequestDispatcher("good2.jsp").forward(request, response);
 }else if("delete".equals(method)){
 System.out.println(keywords);
 //實(shí)現(xiàn)刪除
 String id = request.getParameter("id");
 Good good = new Good();
 good.setId(Integer.valueOf(id));
 goodDao.delete(good);
 //調(diào)用dao的查詢方法，返回一個(gè)List
 List<Good> goods = goodDao.queryByName(keywords);
 request.setAttribute("list", goods);
 request.getRequestDispatcher("goods.jsp").forward(request, response);
 }else if("queryById".equals(method)){
 //查詢一個(gè)
 String id = request.getParameter("id");
 Good good = new Good();
 good.setId(Integer.valueOf(id));
 good = goodDao.queryOne(good);
 //調(diào)用dao的查詢方法，返回一個(gè)good
 request.setAttribute("good", good);
 request.getRequestDispatcher("update.jsp").forward(request, response);
 }else if("update".equals(method)){
 String id = request.getParameter("id");
 String name = request.getParameter("name");
 String address = request.getParameter("address");
 String tel = request.getParameter("tel");
 String dishes = request.getParameter("dishes");
 Good good = new Good(Integer.valueOf(id),name,address,tel,dishes);
 goodDao.update(good);
 //調(diào)用dao的查詢方法，返回一個(gè)List
 List<Good> goods = goodDao.queryByName(keywords);
 request.setAttribute("list", goods);
 request.getRequestDispatcher("goods.jsp").forward(request, response);
 }else{
 //調(diào)用dao的查詢方法，返回一個(gè)List
 List<Good> goods = goodDao.queryByName(keywords);
 request.setAttribute("list", goods);
 request.getRequestDispatcher("goods.jsp").forward(request, response);
 }
 }
 
}

5.在JSP頁(yè)面獲取Servlet里面?zhèn)鬟^(guò)來(lái)的數(shù)據(jù)

<%@ page language="java" contentType="text/html; charset=utf-8"
 pageEncoding="utf-8" import="java.util.ArrayList,com.ambow.pojo.Good,
 com.ambow.pojo.User,com.ambow.utils.PageBeanUtils"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>外賣系統(tǒng)的搜索功能</title>
</head>
<body>
 <form action="QueryServlet&#63;method=search" method="POST">
 <input type="text" name="kw"/><input type="submit" value="搜索"/><a href=" ">添加商家</a >
 <table border="1">
 <tr><th>商家店名</th><th>商家地址</th><th>商家電話</th><th>經(jīng)營(yíng)菜品</th><th colspan="2">編輯</th></tr>
 <c:forEach items="${pg.pageData}" var="g">
  <tr><td>${g.name}</td><td>${g.address}</td><td>${g.tel}</td><td>${g.dishes}</td>
  <td><a href="QueryServlet&#63;method=queryById&id=${g.id}">修改</a ></td>
  <td><a href="QueryServlet&#63;method=delete&id=${g.id}" onClick="return confirm('確認(rèn)刪除本條數(shù)據(jù)嗎？');">刪除</a ></td></tr>
 
 </c:forEach>
 </table>
 <a href="QueryServlet&#63;method=search&curPage=${pg.firstPage}">首頁(yè)</a >
 <a href="QueryServlet&#63;method=search&curPage=${pg.currentPage - 1}">上一頁(yè)</a >
 <a href="QueryServlet&#63;method=search&curPage=${pg.currentPage + 1}">下一頁(yè)</a >
 <a href="QueryServlet&#63;method=search&curPage=${pg.lastPage}">尾頁(yè)</a >
 當(dāng)前第${pg.currentPage}頁(yè)/共${pg.totalPage}頁(yè)
 每頁(yè)顯示${pg.pageSize}條
 </form>
 
</body>
</html>

以上就是分頁(yè)查詢功能如何在JavaWeb項(xiàng)目中實(shí)現(xiàn)，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手