JavaWeb入门教程之分页查询功能的简单实现

2023-07-11 00:09:04 78

一、MySql实现分页查询的SQL语句

1、分页需求：

客户端通过传递pageNo(页码)，counter(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limitm,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：

比如：

查询第1条到第10条的数据的sql是：select*fromtablelimit0,10; ->对应我们的需求就是查询第一页的数据：select*fromtablelimit(1-1)*10,10;

查询第10条到第20条的数据的sql是：select*fromtablelimit10,20; ->对应我们的需求就是查询第二页的数据：select*fromtablelimit(2-1)*10,10;

查询第20条到第30条的数据的sql是：select*fromtablelimit20,30; ->对应我们的需求就是查询第三页的数据：select*fromtablelimit(3-1)*10,10;

2、总结

通过上面的分析，可以得出符合我们自己需求的分页sql格式是：select*fromtablelimit(pageNo-1)*counter,counter;其中pageNo是页码，counter是每页显示的条数。

二、JavaWeb程序

1、创建PageBeanUtils.java工具类

packagecom.ambow.utils;
importjava.util.List;
publicclassPageBeanUtils{
privateintprePage;//上一页
privateintnextPage;//下一页
privateintfirstPage=1;//首页
privateintlastPage;//尾页
privateintcurrentPage=1;//当前
privateinttotalPage;//总页数
privateintpageSize;//每页显示条数，默认显示10条
privateinttotalData;//数据总条数
privateListpageData;//数据
publicPageBeanUtils(intcurrentPage,intpageSize,inttotalData){
this.currentPage=currentPage;
this.pageSize=pageSize;
this.totalData=totalData;

//计算获得总页数（尾页）
//this.totalPage=this.lastPage=(totalData+pageSize-1)/pageSize;
this.totalPage=this.lastPage=(int)Math.ceil((double)totalData/pageSize);
//防止当前页小于1
this.currentPage=Math.max(this.currentPage,1);
//防止当前页大于总的页数
this.currentPage=Math.min(this.totalPage,this.currentPage);
//设置上一页，上一页不能小于1
this.prePage=Math.max(this.currentPage-1,1);
//设置下一页，下一页不能大于总页数
this.nextPage=Math.min(this.currentPage+1,this.totalPage);

/**
*ceil
publicstaticdoubleceil(doublea)
返回最小的（最接近负无穷大）double值，该值大于等于参数，并等于某个整数。特殊情况如下：
如果参数值已经等于某个整数，那么结果与该参数相同。
如果参数为NaN、无穷大、正0或负0，那么结果与参数相同。
如果参数值小于0，但是大于-1.0，那么结果为负0。
注意，Math.ceil(x)的值与-Math.floor(-x)的值完全相同。
参数：
a-一个值。
返回：
最小（最接近负无穷大）浮点值，该值大于等于该参数，并等于某个整数。
*/

}

publicPageBeanUtils(intprePage,intnextPage,intfirstPage,intlastPage,intcurrentPage,inttotalPage,
intpageSize,inttotalData,ListpageData){
super();
this.prePage=prePage;
this.nextPage=nextPage;
this.firstPage=firstPage;
this.lastPage=lastPage;
this.currentPage=currentPage;
this.totalPage=totalPage;
this.pageSize=pageSize;
this.totalData=totalData;
this.pageData=pageData;
}
publicintgetPrePage(){
returnprePage;
}
publicvoidsetPrePage(intprePage){
this.prePage=prePage;
}
publicintgetNextPage(){
returnnextPage;
}
publicvoidsetNextPage(intnextPage){
this.nextPage=nextPage;
}
publicintgetFirstPage(){
returnfirstPage;
}
publicvoidsetFirstPage(intfirstPage){
this.firstPage=firstPage;
}
publicintgetLastPage(){
returnlastPage;
}
publicvoidsetLastPage(intlastPage){
this.lastPage=lastPage;
}
publicintgetCurrentPage(){
returncurrentPage;
}
publicvoidsetCurrentPage(intcurrentPage){
this.currentPage=currentPage;
}
publicintgetTotalPage(){
returntotalPage;
}
publicvoidsetTotalPage(inttotalPage){
this.totalPage=totalPage;
}
publicintgetPageSize(){
returnpageSize;
}
publicvoidsetPageSize(intpageSize){
this.pageSize=pageSize;
}
publicintgetTotalData(){
returntotalData;
}
publicvoidsetTotalData(inttotalData){
this.totalData=totalData;
}
publicListgetPageData(){
returnpageData;
}
publicvoidsetPageData(ListpageData){
this.pageData=pageData;
}


/*
*
*
*totalPage=(totalData+pageSize-1)/pageSize;
*
*
**/
}

2、在接口里面定义分页查询的方法

packagecom.ambow.dao;

importjava.util.List;

importcom.ambow.pojo.Good;

publicinterfaceIGoodDao{
//增删改查
publicvoidadd(Goodgood);
publicvoiddelete(Goodgood);
publicvoidupdate(Goodgood);
publicvoidquery(Goodgood);
publicGoodqueryOne(Goodgood);
publicListqueryMore(Goodgood);
publicListqueryByName(Stringname);//根据商家名称进行模糊查询
//条件分页查询
publicListqueryByName(Stringname,intcurrentPage,intpageSize);
//获取满足某个条件的总记录数
publicintgetTotalNum(Stringname);
}

3、在接口的实现类里面实现方法

packagecom.ambow.dao.impl;

importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;

importcom.ambow.dao.IGoodDao;
importcom.ambow.pojo.Good;
importcom.ambow.utils.DBUtils;

publicclassGoodDaoImplimplementsIGoodDao{
DBUtilsdb=newDBUtils();
@Override
publicvoidadd(Goodgood){
Connectionconn=null;
PreparedStatementpstmt=null;
Stringsql="insertintogood(name,address,tel,dishes)values(?,?,?,?)";
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,good.getName());
pstmt.setString(2,good.getAddress());
pstmt.setString(3,good.getTel());
pstmt.setString(4,good.getDishes());
intisOk=pstmt.executeUpdate();
//System.out.println("add-----"+isOk);
}catch(SQLExceptione){
e.printStackTrace();
}
}

@Override
publicvoiddelete(Goodgood){
Connectionconn=null;
PreparedStatementpstmt=null;
Stringsql="deletefromgoodwhereid=?";
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,good.getId());
intisOk=pstmt.executeUpdate();
System.out.println("delete-----"+isOk);
}catch(SQLExceptione){
e.printStackTrace();
}

}

@Override
publicvoidupdate(Goodgood){
Connectionconn=null;
PreparedStatementpstmt=null;
Stringsql="updategoodsetname=?,address=?,tel=?,dishes=?whereid=?";
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,good.getName());
pstmt.setString(2,good.getAddress());
pstmt.setString(3,good.getTel());
pstmt.setString(4,good.getDishes());
pstmt.setInt(5,good.getId());
intisOk=pstmt.executeUpdate();
}catch(SQLExceptione){
e.printStackTrace();
}

}

@Override
publicvoidquery(Goodgood){
//TODOAuto-generatedmethodstub

}

@Override
publicGoodqueryOne(Goodgood){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
Stringsql="select*fromgoodwhereid=?";
Goodgd=null;
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,good.getId());
rs=pstmt.executeQuery();
while(rs.next()){
gd=newGood(rs.getInt(1),rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5));
}
}catch(SQLExceptione){
e.printStackTrace();
}
returngd;

}

@Override
publicListqueryMore(Goodgood){
//TODOAuto-generatedmethodstub
returnnull;
}

@Override
publicListqueryByName(Stringname){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
Stringsql="select*fromgoodwherenamelike?";
ListgoodList=newArrayList();
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,"%"+name+"%");
rs=pstmt.executeQuery();
while(rs.next()){
goodList.add(newGood(rs.getInt(1),rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5)));
}
}catch(SQLExceptione){
e.printStackTrace();
}
returngoodList;
}

@Override
publicListqueryByName(Stringname,intcurrentPage,intpageSize){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
Stringsql="select*fromgoodwherenamelike?limit?,?";
ListgoodList=newArrayList();
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,"%"+name+"%");
pstmt.setInt(2,(currentPage-1)*pageSize);
pstmt.setInt(3,pageSize);
rs=pstmt.executeQuery();
while(rs.next()){
goodList.add(newGood(rs.getInt(1),rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5)));
}
}catch(SQLExceptione){
e.printStackTrace();
}
returngoodList;

}

@Override
publicintgetTotalNum(Stringname){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
Stringsql="selectcount(id)fromgoodwherenamelike?";
inttotal=0;
try{
conn=DBUtils.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,"%"+name+"%");
rs=pstmt.executeQuery();
while(rs.next()){
total=rs.getInt(1);
}
}catch(SQLExceptione){
e.printStackTrace();
}
returntotal;

}

}

4.在Servlet里面调用实现类里面的分页查询方法

packagecom.ambow.servlet;

importjava.io.IOException;
importjava.util.List;

importjavax.servlet.ServletException;
importjavax.servlet.annotation.WebServlet;
importjavax.servlet.http.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;

importcom.ambow.dao.IGoodDao;
importcom.ambow.dao.impl.GoodDaoImpl;
importcom.ambow.pojo.Good;
importcom.ambow.utils.PageBeanUtils;

@WebServlet("/QueryServlet")
publicclassQueryServletextendsHttpServlet{
privatestaticfinallongserialVersionUID=1L;
privateIGoodDaogoodDao=newGoodDaoImpl();
publicQueryServlet(){
super();
}

protectedvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{
doPost(request,response);
}
protectedvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
Stringkeywords=request.getParameter("kw");
Stringmethod=request.getParameter("method");
if("add".equals(method)){
Stringname=request.getParameter("name");
Stringaddress=request.getParameter("address");
Stringtel=request.getParameter("tel");
Stringdishes=request.getParameter("dishes");
Goodgood=newGood(0,name,address,tel,dishes);
goodDao.add(good);
//调用dao的查询方法，返回一个List
Listgoods=goodDao.queryByName(keywords);
request.setAttribute("list",goods);
request.getRequestDispatcher("goods.jsp").forward(request,response);
}elseif("search".equals(method)){
if(null==keywords){
keywords="";
}
intcurrentPage=1;
try{
currentPage=Integer.parseInt(request.getParameter("curPage"));
if(currentPage<=0){
currentPage=1;
}
}catch(Exceptione){
currentPage=1;
}
intpageSize=10;
inttotalData=goodDao.getTotalNum(keywords);
inttotalPage=(int)Math.ceil((double)totalData/pageSize);
if(currentPage>totalPage){
currentPage=totalPage;
}
Listgoods=goodDao.queryByName(keywords,currentPage,pageSize);
PageBeanUtilspg=newPageBeanUtils(currentPage,pageSize,totalData);
pg.setPageData(goods);
request.setAttribute("pg",pg);
request.getRequestDispatcher("good2.jsp").forward(request,response);
}elseif("delete".equals(method)){
System.out.println(keywords);
//实现删除
Stringid=request.getParameter("id");
Goodgood=newGood();
good.setId(Integer.valueOf(id));
goodDao.delete(good);
//调用dao的查询方法，返回一个List
Listgoods=goodDao.queryByName(keywords);
request.setAttribute("list",goods);
request.getRequestDispatcher("goods.jsp").forward(request,response);
}elseif("queryById".equals(method)){
//查询一个
Stringid=request.getParameter("id");
Goodgood=newGood();
good.setId(Integer.valueOf(id));
good=goodDao.queryOne(good);
//调用dao的查询方法，返回一个good
request.setAttribute("good",good);
request.getRequestDispatcher("update.jsp").forward(request,response);
}elseif("update".equals(method)){
Stringid=request.getParameter("id");
Stringname=request.getParameter("name");
Stringaddress=request.getParameter("address");
Stringtel=request.getParameter("tel");
Stringdishes=request.getParameter("dishes");
Goodgood=newGood(Integer.valueOf(id),name,address,tel,dishes);
goodDao.update(good);
//调用dao的查询方法，返回一个List
Listgoods=goodDao.queryByName(keywords);
request.setAttribute("list",goods);
request.getRequestDispatcher("goods.jsp").forward(request,response);
}else{
//调用dao的查询方法，返回一个List
Listgoods=goodDao.queryByName(keywords);
request.setAttribute("list",goods);
request.getRequestDispatcher("goods.jsp").forward(request,response);
}
}

}

5.在JSP页面获取Servlet里面传过来的数据

<%@pagelanguage="java"contentType="text/html;charset=utf-8"
pageEncoding="utf-8"import="java.util.ArrayList,com.ambow.pojo.Good,
com.ambow.pojo.User,com.ambow.utils.PageBeanUtils"%>
<%@taglibprefix="c"uri="http://java.sun.com/jsp/jstl/core"%>




外卖系统的搜索功能



添加商家

商家店名商家地址商家电话经营菜品编辑

${g.name}${g.address}${g.tel}${g.dishes}
修改
删除



首页
上一页
下一页
尾页
当前第${pg.currentPage}页/共${pg.totalPage}页
每页显示${pg.pageSize}条

总结

到此这篇关于JavaWeb入门教程之分页查询功能的文章就介绍到这了,更多相关JavaWeb分页查询功能内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

JavaWeb入门教程之分页查询功能的简单实现

热门推荐

随机推荐