jsp+mysql實現網頁的分頁查詢
本文實例為大傢分享瞭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）尾頁：
以上就是本文的全部內容，希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。
推薦閱讀：
- jquery+Ajax實現簡單分頁條效果
- Java原生操作JDBC連接以及原理詳解
- Java EE實現用戶後臺管理系統
- 詳解Java快速上手用戶後臺管理系統
- java最新版本連接mysql失敗的解決過程