jsp+mysql實(shí)現(xiàn)網(wǎng)頁的分頁查詢
更新時(shí)間：2022年02月09日 14:30:44 作者：一只小小的螞蟻
這篇文章主要為大家詳細(xì)介紹了jsp+mysql實(shí)現(xiàn)網(wǎng)頁的分頁查詢，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
本文實(shí)例為大家分享了jsp+mysql實(shí)現(xiàn)網(wǎng)頁的分頁查詢的具體代碼，供大家參考，具體內(nèi)容如下
一、實(shí)現(xiàn)分頁查詢的核心sql語句
（1）查詢數(shù)據(jù)庫的記錄總數(shù)的sql語句：
select count(*) from +（表名）;
（2）每次查詢的記錄數(shù)的sql語句：
其中：0是搜索的索引，2是每次查找的條數(shù)。
select * from 表名 limit 0,2;
二、代碼實(shí)現(xiàn)
*上篇寫過這兩個(gè)類 ， DBconnection類：用于獲取數(shù)據(jù)庫連接，Author對(duì)象類。這兩個(gè)類的代碼點(diǎn)擊連接查看。點(diǎn)擊鏈接查看 DBconnection類和Author對(duì)象類
（1）登錄頁面：index.jsp。
<%@ page language="java" contentType="text/html; charset=utf-8" ? ? pageEncoding="utf-8"%> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Insert title here</title> </head> <body> ? ? <a href="AuthorListPageServlet">用戶列表分頁查詢</a> </body> </html>
（2）顯示頁面：userlistpage.jsp。
<%@ page language="java" contentType="text/html; charset=utf-8"
? ? pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>查詢頁面</title>
</head>
<body>
<table border="1">
? <tr>
? ? <td>編號(hào)</td>
? ? <td>名稱</td>
? ? <td>價(jià)格</td>
? ? <td>數(shù)量</td>
? ? <td>日期</td>
? ? <td>風(fēng)格</td>
? </tr>
? <c:forEach items="${pageBean.list}" var="author">
? <tr>
? ? <td>${author.id}</td>
? ? <td>${author.name }</td>
? ? <td>${author.price }</td>
? ? <td>${author.num }</td>
? ? <td>${author.dates}</td>
? ? <td>${author.style}</td>
? </tr>
? </c:forEach>
</table>
<c:if test="${ pageBean.record>0}">
<div>
? ? ??
? ? ? <c:if test="${pageBean.currentPage <= 1}">
? ? ? <span>首頁</span>
? ? ? <span>上一頁</span>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage + 1 }">下一頁</a>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.totalPage }">尾頁</a>
? ? ? </c:if>
? ? ??
? ? ? <c:if test="${pageBean.currentPage > 1 && pageBean.currentPage < pageBean.totalPage ?}">
? ? ? ?<a href ="AuthorListPageServlet?currPage=1">首頁</a>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage - 1 }">上一頁</a>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage + 1 }">下一頁</a>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.totalPage }">尾頁</a>
? ? ? </c:if>
? ? ?
? ? ?<c:if test="${ pageBean.currentPage >= pageBean.totalPage}">
? ? ? <a href ="AuthorListPageServlet?currPage=1">首頁</a>
? ? ? <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage - 1 }">上一頁</a>
? ? ?<span>下一頁</span>
? ? ?<span>尾頁</span>
? ? ?</c:if>
</div>
</c:if>
</body>
</html>（3）功能實(shí)現(xiàn)：AuthorDao.java。
package com.dao;
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.entity.Author;
public class AuthorDao {
?? ?
?? ? public ?Author check(String username ,int ?password ) {
?? ??? ??
?? ??? ? Author obj = null ;
?? ??? ? try {
?? ??? ??? ?DBConnection db = new DBConnection();
?? ??? ??? ?//獲取數(shù)據(jù)庫連接
?? ??? ??? ?Connection conn = db.getConn();
?? ??? ??? ?
?? ??? ??? ?String sql="select *from furnitures where name = ? and id = ?";
?? ??? ??? ?
?? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);
?? ??? ??? ?//設(shè)置用戶名和密碼作為參數(shù)放入sql語句
?? ??? ??? ?ps.setString(1,username);
?? ??? ??? ?ps.setInt(2,password);
?? ??? ??? ?//執(zhí)行查詢語句
?? ??? ??? ?ResultSet rs = ps.executeQuery();
?? ??? ??? ?//用戶名和密碼正確，查到數(shù)據(jù) ?歐式風(fēng)格 ?茶幾
?? ??? ??? ?if(rs.next()) {
?? ??? ??? ??? ?obj = new Author();
?? ??? ??? ??? ?obj.setId(rs.getInt(1));
?? ??? ??? ??? ?obj.setName(rs.getString(2));
?? ??? ??? ??? ?obj.setPrice(rs.getInt(3));
?? ??? ??? ??? ?obj.setNum(rs.getInt(4));
?? ??? ??? ??? ?obj.setDates(rs.getString(5));
?? ??? ??? ??? ?obj.setStyle(rs.getString(6));
?? ??? ??? ?}
?? ??? ?} catch (SQLException e) {
?? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ?e.printStackTrace();
?? ??? ?}
?? ??? ? return obj;
?? ? }
?? ? /**
?? ? ?* 用戶列表信息查詢
?? ? ?* @return
?? ? ?*/
?? ? public List<Author> queryAuthorList(){
?? ??? ? Author obj = null ;
?? ??? ? List<Author> list = new ArrayList<Author>();
?? ??? ? try {
?? ??? ??? ?DBConnection db = new DBConnection();
?? ??? ??? ?//獲取數(shù)據(jù)庫連接
?? ??? ??? ?Connection conn = db.getConn();
?? ??? ??? ?
?? ??? ??? ?String sql="select *from furnitures";
?? ??? ??? ?
?? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);
?? ?
?? ??? ??? ?//執(zhí)行查詢語句
?? ??? ??? ?ResultSet rs = ps.executeQuery();
?? ??? ??? ?//用戶名和密碼正確，查到數(shù)據(jù) ?歐式風(fēng)格 ?茶幾
?? ??? ??? ?//循環(huán)遍歷獲取用戶信息
?? ??? ??? ?while(rs.next()) {
?? ??? ??? ??? ?
?? ??? ??? ??? ?obj = new Author();
?? ??? ??? ??? ?obj.setId(rs.getInt(1));
?? ??? ??? ??? ?obj.setName(rs.getString(2));
?? ??? ??? ??? ?obj.setPrice(rs.getInt(3));
?? ??? ??? ??? ?obj.setNum(rs.getInt(4));
?? ??? ??? ??? ?obj.setDates(rs.getString(5));
?? ??? ??? ??? ?obj.setStyle(rs.getString(6));
?? ??? ??? ??? ?//將對(duì)象加入list里邊
?? ??? ??? ??? ?list.add(obj);
?? ??? ??? ?}
?? ??? ?} catch (SQLException e) {
?? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ?e.printStackTrace();
?? ??? ?}
?? ??? ? return list;
?? ? }
?? ??
?? ??
?? ? /**
?? ? ?* 查詢用戶表總記錄數(shù)
?? ? ?* @return
?? ? ?*/
?? ? public int queryUserListCount() {
?? ??? ? DBConnection db;
?? ??? ?try {
?? ??? ? ? ? db = new DBConnection();
?? ??? ??? ? Connection conn = db.getConn();
?? ??? ??? ? String sql = "select count(*) from furnitures";
?? ??? ??? ??
?? ??? ??? ? PreparedStatement ps = conn.prepareStatement(sql);
?? ??? ??? ? ResultSet rs = ps.executeQuery();
?? ??? ??? ??
?? ??? ??? ??
?? ??? ??? ? if(rs.next()) {
?? ??? ??? ??? ? return rs.getInt(1);
?? ??? ??? ? }
?? ??? ??? ??
?? ??? ??? ??
?? ??? ?} catch (SQLException e) {
?? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ?e.printStackTrace();
?? ??? ?}
?? ??? ?
?? ??? ? return 0;
?? ? }
?? ? /**
?? ? ?* 查詢用戶分頁數(shù)據(jù)
?? ? ?* @param pageIndex數(shù)據(jù)起始索引
?? ? ?* @param pageSize每頁顯示條數(shù)
?? ? ?* @return
?? ? ?*/
?? ? public List<Author>queryUserListPage(int pageIndex,int pageSize){
?? ??? ??
?? ??? ? Author obj = null;
?? ??? ? List<Author> list = new ArrayList<Author>();
?? ??? ??
?? ??? ? try {
?? ??? ??? ?Connection conn = new DBConnection().getConn();
?? ??? ??? ?String sql = "select * from furnitures limit ?,?;";
?? ??? ??? ?PreparedStatement ps = conn.prepareStatement(sql);
?? ??? ??? ?ps.setObject(1, pageIndex);
?? ??? ??? ?ps.setObject(2,pageSize);
?? ??? ??? ?
?? ??? ??? ?ResultSet rs = ps.executeQuery();
?? ??? ??? ?//遍歷結(jié)果集獲取用戶列表數(shù)據(jù)
?? ??? ??? ?
?? ??? ??? ?while(rs.next()) {
?? ??? ??? ??? ?obj = new Author();
?? ??? ??? ??? ?
?? ??? ??? ??? ?obj.setId(rs.getInt(1));
?? ??? ??? ??? ?obj.setName(rs.getString(2));
?? ??? ??? ??? ?obj.setPrice(rs.getInt(3));
?? ??? ??? ??? ?obj.setNum(rs.getInt(4));
?? ??? ??? ??? ?obj.setDates(rs.getString(5));
?? ??? ??? ??? ?obj.setStyle(rs.getString(6));
?? ??? ??? ??? ?
?? ??? ??? ??? ?list.add(obj);
?? ??? ??? ?}
?? ??? ?} catch (SQLException e) {
?? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ?e.printStackTrace();
?? ??? ?}
?? ??? ? return list;
?? ? }
?? ? /**
?? ? ?* 用戶新增
?? ? ?* @param obj
?? ? ?*/
?? ? public void add(Author obj) {
?? ??? ?
?? ??? ?try {
?? ??? ??? ?
?? ??? ??? ?DBConnection db = new DBConnection();
?? ??? ??? ?//獲取數(shù)據(jù)庫連接
?? ??? ??? ?Connection conn = db.getConn();
?? ??? ??? ?
?? ??? ??? ?String sql="insert into furnitures values(id,?,?,?,?,?)";
?? ??? ??? ?
?? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);
?? ??? ??? ?ps.setObject(1, obj.getName());
?? ??? ??? ?ps.setObject(2, obj.getPrice());
?? ??? ??? ?ps.setObject(3, obj.getNum());
?? ??? ??? ?ps.setObject(4,obj.getDates());
?? ??? ??? ?ps.setObject(5, obj.getStyle());
?? ??? ??? ?
?? ??? ??? ?//執(zhí)行sql語句
?? ??? ? ? ps.execute();
?? ??? ? ??
?? ??? ??? ?
?? ??? ?} catch (SQLException e) {
?? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ?e.printStackTrace();
?? ??? ?}
?? ??? ??? ?
?? ? }
?? ? //刪除用戶
?? ? public void del(int id) {
?? ??? ? try {
?? ??? ??? ??? ?
?? ??? ??? ??? ?DBConnection db = new DBConnection();
?? ??? ??? ??? ?//獲取數(shù)據(jù)庫連接
?? ??? ??? ??? ?Connection conn = db.getConn();
?? ??? ??? ??? ?
?? ??? ??? ??? ?String sql="delete from furnitures where id = ?";
?? ??? ??? ??? ?
?? ??? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);
?? ??? ??? ??? ?
?? ??? ??? ??? ?ps.setObject(1, id);
?? ??? ??? ??? ?
?? ??? ??? ??? ?//執(zhí)行sql語句
?? ??? ??? ? ? ps.execute();
?? ??? ??? ? ??
?? ??? ??? ??? ?
?? ??? ??? ?} catch (SQLException e) {
?? ??? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ??? ?e.printStackTrace();
?? ??? ??? ?}
?? ??? ??? ??? ?
?? ? }
?? ?
}（4）交互層：AuthorListPageServlet.java。
package com.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.dao.AuthorDao;
import com.entity.Author;
import com.util.PageBean;
/**
?* Servlet implementation class AuthorListPageServlet
?*/
@WebServlet("/AuthorListPageServlet")
public class AuthorListPageServlet extends HttpServlet {
?? ?private static final long serialVersionUID = 1L;
? ? ? ?
? ? /**
? ? ?* @see HttpServlet#HttpServlet()
? ? ?*/
? ? public AuthorListPageServlet() {
? ? ? ? super();
? ? ? ? // TODO Auto-generated constructor stub
? ? }
?? ?/**
?? ? * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
?? ? */
?? ?protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
?? ??? ?// TODO Auto-generated method stub
?? ??? ?int pageSize = 2;
?? ??? ?AuthorDao ad = new AuthorDao();
?? ??? ?//總記錄數(shù)
?? ??? ?int record = ad.queryUserListCount();
?? ??? ?//接收頁面?zhèn)魅氲捻摯a
?? ??? ?String strPage = request.getParameter("currPage");
?? ??? ?int currPage = 1;//默認(rèn)第一頁
?? ??? ?if(strPage != null) {
?? ??? ??? ?currPage = Integer.parseInt(strPage);
?? ?
?? ??? ?}
?? ??? ?
?? ??? ?PageBean<Author> pb = new PageBean<Author>(currPage,pageSize,record);
?? ??? ?//查詢某一頁的結(jié)果集
?? ??? ?List<Author> list = ad.queryUserListPage(pb.getPageIndex(), pageSize);
?? ??? ?pb.setList(list);
?? ??? ?request.setAttribute("pageBean", pb);
?? ??? ?request.getRequestDispatcher("userlistpage.jsp").forward(request, response);
?? ?}
?? ?/**
?? ? * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
?? ? */
?? ?protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
?? ??? ?// TODO Auto-generated method stub
?? ??? ?doGet(request, response);
?? ?}
}（5）工具類：PageBean.java。作用是：獲取結(jié)果集。
package com.util;
import java.util.List;
public class PageBean<T>{
?? ?private int currentPage;//當(dāng)前頁碼
?? ?private int pageIndex;//數(shù)據(jù)起始索引
?? ?private int pageSize;//每頁條數(shù)
?? ?
?? ?
?? ?private int record;//總記錄數(shù)
?? ?private int totalPage;//總頁數(shù)
?? ?
?? ?private List<T>list;//每頁顯示的結(jié)果集
?? ?/**
?? ? * 構(gòu)造方法初始化pageIndex和totalPage
?? ? * @param currentPage
?? ? * @param pageIndex
?? ? * @param pageSize
?? ? */
?? ?public PageBean(int currentPage,int pageSize,int record) {
?? ??? ?
?? ??? ?this.currentPage = currentPage;
?? ??? ?this.pageSize = pageSize;
?? ??? ?this.record = record;
?? ??? ?
?? ??? ?//總頁數(shù)
?? ??? ?if(record % pageSize == 0) {
?? ??? ??? ?//整除，沒有多余的頁
?? ??? ??? ?this.totalPage = record / pageSize;
?? ??? ??? ?
?? ??? ?}
?? ??? ?else {
?? ??? ??? ?//有多余的數(shù)據(jù)，在增加一頁
?? ??? ??? ?this.totalPage = record / pageSize + 1;
?? ??? ?}
?? ??? ?
?? ??? ?//計(jì)算數(shù)據(jù)起始索引pageIndex
?? ??? ?if(currentPage < 1) {
?? ??? ??? ?this.currentPage = 1;
?? ??? ?}
?? ??? ?else if(currentPage > this.totalPage) {
?? ??? ??? ?this.currentPage = this.totalPage;
?? ??? ?}
?? ??? ?this.pageIndex = (this.currentPage -1)*this.pageSize;
?? ?}
?? ?
?? ?public int getCurrentPage() {
?? ??? ?return currentPage;
?? ?}
?? ?public void setCurrentPage(int currentPage) {
?? ??? ?this.currentPage = currentPage;
?? ?}
?? ?public int getPageIndex() {
?? ??? ?return pageIndex;
?? ?}
?? ?public void setPageIndex(int pageIndex) {
?? ??? ?this.pageIndex = pageIndex;
?? ?}
?? ?public int getPageSize() {
?? ??? ?return pageSize;
?? ?}
?? ?public void setPageSize(int pageSize) {
?? ??? ?this.pageSize = pageSize;
?? ?}
?? ?public int getRecord() {
?? ??? ?return record;
?? ?}
?? ?public void setRecord(int record) {
?? ??? ?this.record = record;
?? ?}
?? ?public int getTotalPage() {
?? ??? ?return totalPage;
?? ?}
?? ?public void setTotalPage(int totalPage) {
?? ??? ?this.totalPage = totalPage;
?? ?}
?? ?public List<T> getList() {
?? ??? ?return list;
?? ?}
?? ?public void setList(List<T> list) {
?? ??? ?this.list = list;
?? ?}
?? ?
}三、運(yùn)行結(jié)果
（1）首頁：
（2）中間頁：
（3）尾頁：
以上就是本文的全部內(nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
Jsp中解決session過期跳轉(zhuǎn)到登陸頁面并跳出iframe框架的方法
這里我們是介紹一個(gè)網(wǎng)站管理后臺(tái)三個(gè)框架頁面當(dāng)我們的jsp定義的session變量超時(shí)時(shí)用戶點(diǎn)擊時(shí)自動(dòng)退出框架頁面并跳到登錄頁面去了，下面我來給大家演示一個(gè)實(shí)例2013-08-08
JSP實(shí)現(xiàn)計(jì)算器功能(網(wǎng)頁版)
這篇文章講述了JSP實(shí)現(xiàn)計(jì)算器功能的詳細(xì)代碼，網(wǎng)頁版的計(jì)算器，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2015-12-12
六、訪問CGI變量...2006-10-10
Jsp+Servlet實(shí)現(xiàn)文件上傳下載 文件列表展示（二）
這篇文章主要為大家詳細(xì)介紹了Jsp+Servlet實(shí)現(xiàn)文件上傳下載功能的第二部分，文件列表展示，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-01-01
這篇文章主要介紹了簡(jiǎn)單的手工hibernate程序示例,以實(shí)例形式分析了簡(jiǎn)單hibernate程序的實(shí)現(xiàn)技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-09-09
在jsp頁面中實(shí)現(xiàn)跳轉(zhuǎn)的方式分享
這篇文章介紹在jsp頁面中實(shí)現(xiàn)跳轉(zhuǎn)的方式，有需要的朋友可以參考一下2013-10-10
JSP（Java Server Pages)是由Sun Microsystems公司倡導(dǎo)、許多公司參與一起建立的一種動(dòng)態(tài)網(wǎng)頁技術(shù)標(biāo)準(zhǔn),與asp、php、asp.net類似的后臺(tái)語言2013-05-05
這篇文章主要為大家介紹了jsp中文亂碼問題的簡(jiǎn)單解決方法，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-04-04
JSP發(fā)送郵件實(shí)例...2006-10-10
JSP動(dòng)態(tài)實(shí)現(xiàn)web網(wǎng)頁登陸和注冊(cè)功能
這篇文章主要介紹是動(dòng)態(tài)實(shí)現(xiàn)web網(wǎng)頁登陸和注冊(cè)功能的示例代碼，文中代碼講解詳細(xì)，對(duì)我們學(xué)習(xí)JSP有一定的幫助，感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2021-12-12