一、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);
-
-
/**
-
* ceil
-
public 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>
转载：https://blog.csdn.net/kuailexiaohan/article/details/109845618
查看评论