jsp+mysql实现网页的分页查询

更新时间:2023-04-04 23:33:02 阅读：评论：0

本文实例为大家分享了jsp+mysql实现网页的分页查询的具体代码，供大家参考，具体内容如下

一、实现分页查询的核心sql语句

（1）查询数据库的记录总数的sql语句：

lect count(*) from +（表名）;

（2）每次查询的记录数的sql语句：

其中：0是搜索的索引，2是每次查找的条数。

lect * from 表名 limit 0,2;

二、代码实现

*上篇写过这两个类， dbconnection类：用于获取数据库连接，author对象类。这两个类的代码点击连接查看。点击链接查看 dbconnection类和author对象类

（1）登录页面：index.jsp。

<%@ page language="java" contenttype="text/html; chart=utf-8"  pageencoding="utf-8"%><!doctype html><html><head><meta chart="utf-8"><title>inrt title here</title></head><body>  <a href="authorlistpagervlet">用户列表分页查询</a></body></html>

（2）显示页面：urlistpage.jsp。

<%@ page language="java" contenttype="text/html; chart=utf-8"  pageencoding="utf-8"%><%@ taglib prefix="c" uri="/d/file/titlepic/" %><!doctype html><html><head><meta chart="utf-8"><title>查询页面</title></head><body><table bor如何安装双操作系统der="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 ="authorlistpagervlet?currpage=${pagebean.currentpage + 1 }">下一页</a>   <a href ="authorlistpagervlet?currpage=${pagebean.totalpage }">尾页</a>   </c:if>     <c:if test="${pagebean.currentpage > 1 && pagebean.currentpage < pagebean.totalpage }">   <a href ="authorlistpagervlet?currpage=1">首页</a>   <a href ="authorlistpagervlet?currpage=${pagebean.currentpage - 1 }">上一页</a>   <a href ="authorlistpagervlet?currpage=${pagebean.currentpage + 1 }">下一页</a>   <a href ="authorlistpagervlet?currpage=${pagebean.totalpage }">尾页</a>   </c:if>    <c:if test="${ pagebean.currentpage >= pagebean.totalpage}">   <a href ="authorlistpagervlet?currpage=1">首页</a>   <a href ="authorlistpagervlet?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.resultt;import java.sql.sqlexception;import java.util.arraylist;import java.util.list;import com.entity.author;public class authordao {   public author check(string urname ,int password ) {     author obj = null ;   try {   dbconnection db = new dbconnection();   //获取数据库连接   connection conn = db.getconn();     中山房屋出租 string sql="lect *from furnitures where name = ? and id = ?";      preparedstatement ps=conn.preparestatement(sql);   //设置用户名和密码作为参数放入sql语句   ps.tstring(1,urname);   ps.tint(2,password);   //执行查询语句   resultt rs = ps.executequery();   //用户名和密码正确，查到数据 欧式风格 茶几   if(rs.next()) {    obj = new author();    obj.tid(rs.getint(1));    obj.tname(rs.getstring(2));    obj.tprice(rs.getint(3));    obj.tnum(rs.getint(4));    obj.tdates(rs.getstring(5));    obj.tstyle(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="lect *from furnitures";      preparedstatement ps=conn.preparestatement(sql);    //执行查询语句   resultt rs = ps.executequery();   //用户名和密码正确，查到数据 欧式风格 茶几   //循环遍历获取用户信息   while(rs.next()) {        obj = new author();    obj.tid(rs.getint(1));    obj.tname(rs.getstring(2));    obj.tprice(rs.getint(3));    obj.tnum(rs.getint(4));    obj.tdates(rs.getstring(5));    obj.tstyle(rs.getstring(6));    //将对象加入list里边    list.add(obj);   }  } catch (sqlexception e) {   // todo auto-generated catch block   e.printstacktrace();  }   return list;  }    /**  * 查询用户表总记录数  * @return  */  public int queryurlistcount() {   dbconnection db;  try {     db = new dbconnection();    connection conn = db.getconn();    string sql = "lect count(*) from furnitures";       preparedstatement ps = conn.preparestatement(sql);    resultt 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>queryurlistpage(int pageindex,int pagesize){     author obj = null;   list<author> list = new arraylist<author>();     try {   connection conn = new dbconnection().getconn();   string sql = "lect * from furnitures limit ?,?;";   preparedstatement ps = conn.preparestatement(sql);   ps.tobject(1, pageindex);   ps.tobject(2,pagesize);      resultt rs = ps.executequery();   //遍历结果集获取用户列表数据      while(rs.next()) {    obj = new author();        obj.tid(rs.getint(1));    obj.tname(rs.getstring(2));    obj.tprice(rs.getint(3));    obj.tnum(rs.getint(4));    obj.tdates(rs.getstring(5));    obj.tstyle(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="inrt into furnitures values(id,?,?,?,?,?)";      preparedstatement ps=conn.preparestatement(sql);   ps.tobject(1, obj.getname());   ps.tobject(2, obj.getprice());   ps.tobject(3, obj.getnum());   ps.tobject(4,obj.getdates());   ps.tobject(5, obj.getstyle());      //执行sql语句    ps.execute();        } catch (sqlexception e) {   // todo auto-generate消防安全歌d 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.tobject(1, id);        //执行sql语句     ps.execute();           } catch (sqlexception e) {    // todo auto-generated catch block    e.printstacktrace();   }      } }

（4）交互层：authorlistpagervlet.java。

package com.rvlet;import java.io.ioexception;import java.util金波的作品.list;import javax.rvlet.rvletexception;import javax.rvlet.annotation.webrvlet;import javax.rvlet.http.httprvlet;import javax.rvlet.http.httprvletrequest;import javax.rvlet.http.httprvletrespon;import com.dao.authordao;import com.entity.author;import com.util.pagebean;/*** rvlet implementation class authorlistpagervlet*/@webrvlet("/authorlistpagervlet")public class authorlistpagervlet extends httprvlet { private static final long rialversionuid = 1l;     /**  * @e httprvlet#httprvlet()  */  public authorlistpagervlet() {    super();    // todo auto-generated constructor stub  } /**  * @e httprvlet#doget(httprvletrequest request, httprvletrespon respon)  */ protected void doget(httprvletrequest request, httprvletrespon respon) throws rvletexception, ioexception {  // todo auto-generated method stub  int pagesize = 2;  authordao ad = new authordao();  //总记录数  int record = ad.queryurlistcount();  //接收页面传入的页码  string strpage = request.getparameter("currpage");  int currpage = 1;//默认第一页  if(strpage != null) {   currpage = integer.parint(strpage);   }    pagebean<author> pb = new pagebean<author>(currpage,pagesize,record);  //查询某一页的结果集  list<author> list = ad.queryurlistpage(pb.getpageindex(), pagesize);  pb.tlist(list);  request.tattribute("pagebean", pb);  request.getrequestdispatcher("urlistpage.jsp").forward(request, respon); } /**  * @e httprvlet#dopost(httprvletrequest request, httprvletrespon respon)  */ protected void dopost(httprvletrequest request, httprvletrespon respon) throws rvletexception, ioexception {  // todo auto-generated method stub  doget(request, respon); }}

（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;     }  el { 凤仙花开  //有多余的数据，在增加一页   this.totalpage = record / pagesize + 1;  }    //计算数据起始索引pageindex  if(currentpage < 1) {   this.currentpage = 1;  }  el if(currentpage > this.totalpage) {   this.currentpage = this.totalpage;  }  this.pageindex = (this.currentpage -1)*this.pagesize; }  public int getcurrentpage() {  return currentpage; } public void tcurrentpage(int currentpage) {  this.currentpage = currentpage; } public int getpageindex() {  return pageindex; } public void tpageindex(int pageindex) {  this.pageindex = pageindex; } public int getpagesize() {  return pagesize; } public void tpagesize(int pagesize) {  this.pagesize = pagesize; } public int getrecord() {  return record; } public void trecord(int record) {  this.record = record; } public int gettotalpage() {  return totalpage; } public void ttotalpage(int totalpage) {  this.totalpage = totalpage; } public list<t> getlist() {  return list; } public void tlist(list<t> list) {  this.list = list; } }