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

1、分页需求：

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

比如：

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

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

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

2、总结

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

二、JavaWeb程序

1、创建PageBeanUtils.java工具类

package com.ambow.utils;import java.util.List;public class PageBeanUtils<T> { private int prePage;//上一页 private int nextPage;//下一页 private int firstPage=1;//首页 private int lastPage;//尾页 private int currentPage = 1;//当前 private int totalPage;//总页数 private int pageSize;//每页显示条数，默认显示10条 private int totalData;//数据总条数 private List<T> pageData;//数据 public PageBeanUtils(int currentPage,int pageSize, int totalData) { 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); /** * ceilpublic static double ceil(double a)返回最小的（最接近负无穷大） double 值，该值大于等于参数，并等于某个整数。特殊情况如下：如果参数值已经等于某个整数，那么结果与该参数相同。如果参数为 NaN、无穷大、正 0 或负 0，那么结果与参数相同。如果参数值小于 0，但是大于 -1.0，那么结果为负 0。注意， Math.ceil(x) 的值与 -Math.floor(-x) 的值完全相同。参数：a - 一个值。返回：最小（最接近负无穷大）浮点值，该值大于等于该参数，并等于某个整数。 */ } public PageBeanUtils(int prePage, int nextPage, int firstPage, int lastPage, int currentPage, int totalPage, int pageSize, int totalData, List<T> pageData) { 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; } public int getPrePage() { return prePage; } public void setPrePage(int prePage) { this.prePage = prePage; } public int getNextPage() { return nextPage; } public void setNextPage(int nextPage) { this.nextPage = nextPage; } public int getFirstPage() { return firstPage; } public void setFirstPage(int firstPage) { this.firstPage = firstPage; } public int getLastPage() { return lastPage; } public void setLastPage(int lastPage) { this.lastPage = lastPage; } public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getTotalData() { return totalData; } public void setTotalData(int totalData) { this.totalData = totalData; } public List<T> getPageData() { return pageData; } public void setPageData(List<T> pageData) { this.pageData = pageData; } /* * * * totalPage = (totalData+pageSize-1)/pageSize; * * * */}

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

package com.ambow.dao; import java.util.List; import com.ambow.pojo.Good; public interface IGoodDao { //增删改查 public void add(Good good); public void delete(Good good); public void update(Good good); public void query(Good good); public Good queryOne(Good good); public List<Good> queryMore(Good good); public List<Good> queryByName(String name);//根据商家名称进行模糊查询 //条件分页查询 public List<Good> queryByName(String name,int currentPage,int pageSize); //获取满足某个条件的总记录数 public int getTotalNum(String name);}

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

package com.ambow.dao.impl; 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.ambow.dao.IGoodDao;import com.ambow.pojo.Good;import com.ambow.utils.DBUtils; public class GoodDaoImpl implements IGoodDao { DBUtils db = new DBUtils(); @Override public void add(Good good) { Connection conn = null; PreparedStatement pstmt = null; String sql = "insert into good (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()); int isOk = pstmt.executeUpdate(); //System.out.println("add-----"+isOk); } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete(Good good) { Connection conn = null; PreparedStatement pstmt = null; String sql = "delete from good where id = ?"; try { conn = DBUtils.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, good.getId()); int isOk = pstmt.executeUpdate(); System.out.println("delete-----"+isOk); } catch (SQLException e) { e.printStackTrace(); } } @Override public void update(Good good) { Connection conn = null; PreparedStatement pstmt = null; String sql = "update good set name=?,address=?,tel=?,dishes=? where id=?"; 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()); int isOk = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void query(Good good) { // TODO Auto-generated method stub } @Override public Good queryOne(Good good) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from good where id = ?"; Good gd = null; try { conn = DBUtils.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, good.getId()); rs = pstmt.executeQuery(); while(rs.next()){ gd = new Good(rs.getInt(1),rs.getString(2),rs.getString(3), rs.getString(4),rs.getString(5)); } } catch (SQLException e) { e.printStackTrace(); } return gd; } @Override public List<Good> queryMore(Good good) { // TODO Auto-generated method stub return null; } @Override public List<Good> queryByName(String name) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from good where name like ?"; List<Good> goodList = new ArrayList<Good>(); try { conn = DBUtils.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%"+name+"%"); rs = pstmt.executeQuery(); while(rs.next()){ goodList.add(new Good(rs.getInt(1),rs.getString(2),rs.getString(3), rs.getString(4),rs.getString(5))); } } catch (SQLException e) { e.printStackTrace(); } return goodList; } @Override public List<Good> queryByName(String name, int currentPage, int pageSize) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from good where name like ? limit ?,?"; List<Good> goodList = new ArrayList<Good>(); 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(new Good(rs.getInt(1),rs.getString(2),rs.getString(3), rs.getString(4),rs.getString(5))); } } catch (SQLException e) { e.printStackTrace(); } return goodList; } @Override public int getTotalNum(String name) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select count(id) from good where name like ?"; int total = 0; try { conn = DBUtils.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%"+name+"%"); rs = pstmt.executeQuery(); while(rs.next()){ total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } }

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

package com.ambow.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.ambow.dao.IGoodDao;import com.ambow.dao.impl.GoodDaoImpl;import com.ambow.pojo.Good;import com.ambow.utils.PageBeanUtils; @WebServlet("/QueryServlet")public class QueryServlet extends HttpServlet { private static final long serialVersionUID = 1L; private IGoodDao goodDao = new GoodDaoImpl(); public QueryServlet() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request,response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); String keywords = request.getParameter("kw"); String method = request.getParameter("method"); if("add".equals(method)){ String name = request.getParameter("name"); String address = request.getParameter("address"); String tel = request.getParameter("tel"); String dishes = request.getParameter("dishes"); Good good = new Good(0,name,address,tel,dishes); goodDao.add(good); //调用dao的查询方法，返回一个List List<Good> goods = goodDao.queryByName(keywords); request.setAttribute("list", goods); request.getRequestDispatcher("goods.jsp").forward(request, response); }else if("search".equals(method)){ if(null==keywords) { keywords=""; } int currentPage = 1; try { currentPage=Integer.parseInt(request.getParameter("curPage")); if(currentPage<=0) { currentPage = 1; } }catch(Exception e) { currentPage = 1; } int pageSize=10; int totalData = goodDao.getTotalNum(keywords); int totalPage = (int)Math.ceil((double)totalData/pageSize); if(currentPage>totalPage){ currentPage = totalPage; } List<Good> goods = goodDao.queryByName(keywords,currentPage,pageSize); PageBeanUtils pg = new PageBeanUtils(currentPage,pageSize,totalData); pg.setPageData(goods); request.setAttribute("pg", pg); request.getRequestDispatcher("good2.jsp").forward(request, response); }else if("delete".equals(method)){ System.out.println(keywords); //实现删除 String id = request.getParameter("id"); Good good = new Good(); good.setId(Integer.valueOf(id)); goodDao.delete(good); //调用dao的查询方法，返回一个List List<Good> goods = goodDao.queryByName(keywords); request.setAttribute("list", goods); request.getRequestDispatcher("goods.jsp").forward(request, response); }else if("queryById".equals(method)){ //查询一个 String id = request.getParameter("id"); Good good = new Good(); good.setId(Integer.valueOf(id)); good = goodDao.queryOne(good); //调用dao的查询方法，返回一个good request.setAttribute("good", good); request.getRequestDispatcher("update.jsp").forward(request, response); }else if("update".equals(method)){ String id = request.getParameter("id"); String name = request.getParameter("name"); String address = request.getParameter("address"); String tel = request.getParameter("tel"); String dishes = request.getParameter("dishes"); Good good = new Good(Integer.valueOf(id),name,address,tel,dishes); goodDao.update(good); //调用dao的查询方法，返回一个List List<Good> goods = goodDao.queryByName(keywords); request.setAttribute("list", goods); request.getRequestDispatcher("goods.jsp").forward(request, response); }else{ //调用dao的查询方法，返回一个List List<Good> goods = goodDao.queryByName(keywords); request.setAttribute("list", goods); request.getRequestDispatcher("goods.jsp").forward(request, response); } } }

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

<%@ page language="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"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>外卖系统的搜索功能</title></head><body> <form action="QueryServlet?method=search" method="POST"> <input type="text" name="kw"/><input type="submit" value="搜索"/><a href=" ">添加商家</a > <table border="1"> <tr><th>商家店名</th><th>商家地址</th><th>商家电话</th><th>经营菜品</th><th colspan="2">编辑</th></tr> <c:forEach items="${pg.pageData}" var="g"> <tr><td>${g.name}</td><td>${g.address}</td><td>${g.tel}</td><td>${g.dishes}</td> <td><a href="QueryServlet?method=queryById&id=${g.id}">修改</a ></td> <td><a href="QueryServlet?method=delete&id=${g.id}" onClick="return confirm('确认删除本条数据吗？');">删除</a ></td></tr> </c:forEach> </table> <a href="QueryServlet?method=search&curPage=${pg.firstPage}">首页</a > <a href="QueryServlet?method=search&curPage=${pg.currentPage - 1}">上一页</a > <a href="QueryServlet?method=search&curPage=${pg.currentPage + 1}">下一页</a > <a href="QueryServlet?method=search&curPage=${pg.lastPage}">尾页</a > 当前第${pg.currentPage}页/共${pg.totalPage}页 每页显示${pg.pageSize}条 </form> </body></html>

总结

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