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}">
? ? ? 首頁
? ? ? 上一頁
? ? ? <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>
? ? ?下一頁
? ? ?尾頁
? ? ?</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實戰