文章詳情頁
jsp+mysql實現網頁的分頁查詢
瀏覽：270日期：2022-06-07 17:33:46
本文實例為大家分享了jsp+mysql實現網頁的分頁查詢的具體代碼，供大家參考，具體內容如下
一、實現分頁查詢的核心sql語句
（1）查詢數據庫的記錄總數的sql語句：
select count(*) from +（表名）;
（2）每次查詢的記錄數的sql語句：
其中：0是搜索的索引，2是每次查找的條數。
select * from 表名 limit 0,2;
二、代碼實現
*上篇寫過這兩個類 ， DBconnection類：用于獲取數據庫連接，Author對象類。這兩個類的代碼點擊連接查看。點擊鏈接查看 DBconnection類和Author對象類
（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>編號</td>? ? <td>名稱</td>? ? <td>價格</td>? ? <td>數量</td>? ? <td>日期</td>? ? <td>風格</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）功能實現：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();?? ??? ??? ?//獲取數據庫連接?? ??? ??? ?Connection conn = db.getConn();?? ??? ??? ??? ??? ??? ?String sql="select *from furnitures where name = ? and id = ?";?? ??? ??? ??? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);?? ??? ??? ?//設置用戶名和密碼作為參數放入sql語句?? ??? ??? ?ps.setString(1,username);?? ??? ??? ?ps.setInt(2,password);?? ??? ??? ?//執行查詢語句?? ??? ??? ?ResultSet rs = ps.executeQuery();?? ??? ??? ?//用戶名和密碼正確，查到數據 ?歐式風格 ?茶幾?? ??? ??? ?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();?? ??? ??? ?//獲取數據庫連接?? ??? ??? ?Connection conn = db.getConn();?? ??? ??? ??? ??? ??? ?String sql="select *from furnitures";?? ??? ??? ??? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);?? ??? ??? ??? ?//執行查詢語句?? ??? ??? ?ResultSet rs = ps.executeQuery();?? ??? ??? ?//用戶名和密碼正確，查到數據 ?歐式風格 ?茶幾?? ??? ??? ?//循環遍歷獲取用戶信息?? ??? ??? ?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里邊?? ??? ??? ??? ?list.add(obj);?? ??? ??? ?}?? ??? ?} catch (SQLException e) {?? ??? ??? ?// TODO Auto-generated catch block?? ??? ??? ?e.printStackTrace();?? ??? ?}?? ??? ? return list;?? ? }?? ???? ???? ? /**?? ? ?* 查詢用戶表總記錄數?? ? ?* @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;?? ? }?? ? /**?? ? ?* 查詢用戶分頁數據?? ? ?* @param pageIndex數據起始索引?? ? ?* @param pageSize每頁顯示條數?? ? ?* @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();?? ??? ??? ?//遍歷結果集獲取用戶列表數據?? ??? ??? ??? ??? ??? ?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();?? ??? ??? ?//獲取數據庫連接?? ??? ??? ?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());?? ??? ??? ??? ??? ??? ?//執行sql語句?? ??? ? ? ps.execute();?? ??? ? ???? ??? ??? ??? ??? ?} catch (SQLException e) {?? ??? ??? ?// TODO Auto-generated catch block?? ??? ??? ?e.printStackTrace();?? ??? ?}?? ??? ??? ??? ? }?? ? //刪除用戶?? ? public void del(int id) {?? ??? ? try {?? ??? ??? ??? ??? ??? ??? ??? ?DBConnection db = new DBConnection();?? ??? ??? ??? ?//獲取數據庫連接?? ??? ??? ??? ?Connection conn = db.getConn();?? ??? ??? ??? ??? ??? ??? ??? ?String sql="delete from furnitures where id = ?";?? ??? ??? ??? ??? ??? ??? ??? ?PreparedStatement ps=conn.prepareStatement(sql);?? ??? ??? ??? ??? ??? ??? ??? ?ps.setObject(1, id);?? ??? ??? ??? ??? ??? ??? ??? ?//執行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();?? ??? ?//總記錄數?? ??? ?int record = ad.queryUserListCount();?? ??? ?//接收頁面傳入的頁碼?? ??? ?String strPage = request.getParameter("currPage");?? ??? ?int currPage = 1;//默認第一頁?? ??? ?if(strPage != null) {?? ??? ??? ?currPage = Integer.parseInt(strPage);?? ??? ??? ?}?? ??? ??? ??? ?PageBean<Author> pb = new PageBean<Author>(currPage,pageSize,record);?? ??? ?//查詢某一頁的結果集?? ??? ?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。作用是：獲取結果集。
package com.util;import java.util.List;public class PageBean<T>{?? ?private int currentPage;//當前頁碼?? ?private int pageIndex;//數據起始索引?? ?private int pageSize;//每頁條數?? ??? ??? ?private int record;//總記錄數?? ?private int totalPage;//總頁數?? ??? ?private List<T>list;//每頁顯示的結果集?? ?/**?? ? * 構造方法初始化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;?? ??? ??? ??? ?//總頁數?? ??? ?if(record % pageSize == 0) {?? ??? ??? ?//整除，沒有多余的頁?? ??? ??? ?this.totalPage = record / pageSize;?? ??? ??? ??? ??? ?}?? ??? ?else {?? ??? ??? ?//有多余的數據，在增加一頁?? ??? ??? ?this.totalPage = record / pageSize + 1;?? ??? ?}?? ??? ??? ??? ?//計算數據起始索引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;?? ?}?? ?}三、運行結果
（1）首頁：
（2）中間頁：
（3）尾頁：
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持。
標簽：
JSP
排行榜
網公網安備