jsp+mysql实现网页的分页查询
程序员文章站
2022-01-03 10:00:39
本文实例为大家分享了jsp+mysql实现网页的分页查询的具体代码,供大家参考,具体内容如下一、实现分页查询的核心sql语句(1)查询数据库的记录总数的sql语句:select count(*) fr...
本文实例为大家分享了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实现简单图片验证码功能
下一篇: js裁剪(分隔)字符串的三种常用方法