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实战