分页学习

最新推荐文章于 2020-10-12 11:54:45 发布

欢乐大象

最新推荐文章于 2020-10-12 11:54:45 发布

阅读量377

点赞数

分类专栏：补充

本文链接：https://blog.csdn.net/qq_39088066/article/details/100061186

版权

补充专栏收录该内容

26 篇文章 0 订阅

订阅专栏

在这里插入图片描述

前端页面

页面中的数据有：
结果集：通过 SQL 语句查询得来的——List

分页条中的数据有：
当前页：用户传递到后台——currentPage
总页数：计算的来——totalPage
上一页：计算的来——prePage
下一页：计算的来——nextPage
尾页：计算的来（总页数）——lastPage
页面大小（即每一页显示的条数）：
用户传递到后台——count
总条数：
通过 SQL 语句查询得来的——totalCount

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
	<!-- 包含公共的JSP代码片段 -->
<title>无线点餐平台</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="${pageContext.request.contextPath }/sys/style/js/jquery.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath }/sys/style/js/page_common.js"></script>
<link href="${pageContext.request.contextPath }/sys/style/css/common_style_blue.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/sys/style/css/index_1.css" />
</head>
<body>
<!-- 页面标题 -->
<div id="TitleArea">
	<div id="TitleArea_Head"></div>
	<div id="TitleArea_Title">
		<div id="TitleArea_Title_Content">
			<img border="0" width="13" height="13" src="${pageContext.request.contextPath }/sys/style/images/title_arrow.gif"/> 菜品列表
		</div>
    </div>
	<div id="TitleArea_End"></div>
</div>


	<!-- 过滤条件 -->
	<div id="QueryArea">
		<form action="${pageContext.request.contextPath }/food" method="post">
			<input type="hidden" name="method" value="search">
			<input type="text" name="keyword" title="请输入菜品名称">
			<input type="submit" value="搜索">
		</form>
	</div>
<!-- 主内容区域（数据列表或表单显示） -->
<div id="MainArea">
    <table class="MainArea_Content" align="center" cellspacing="0" cellpadding="0">
        <!-- 表头-->
        <thead>
            <tr align="center" valign="middle" id="TableTitle">
				<td>菜编号</td>
				<td>菜名</td>
				<td>所属菜系</td>
				<td>价格</td>
                <td>会员价格</td>
				<td>操作</td>
			</tr>
		</thead>	
		<!--显示数据列表 -->
        <tbody id="TableData">
		<c:forEach items="${requestScope.list}" var="food" varStatus="vs">
			<tr class="TableDetail1">
				<td>${vs.count }</td>
				<td>${food.foodName }</td>
				<!-- 将request中types取出，用type做为临时对象保存types中每个值。 -->
				<c:forEach items="${ requestScope.types}" var="type" >
				<!--测试food.foodType_id值是否与type中的id值比较，如果相同，则取出type中的typeName字段，并设置到typename中 -->
				<!-- 消除笛卡尔积 -->
					<c:if test="${food.foodType_id==type.id }">
						<c:set var="typename" value="${type.typeName}"/>
					</c:if>
				</c:forEach>
				<td><c:out value="${typename}"></c:out></td>
				<td>${food.id}</td>
				<td>${food.price}</td>
                <td>${food.mprice}</td>
				<td>
					<a href="${pageContext.request.contextPath}/food?method=show&id=${food.id}"  class="FunctionButton">更新</a>				
					<a href="${pageContext.request.contextPath}/food?method=delete&id=${food.id}" onClick="return delConfirm();"class="FunctionButton">删除</a>				
				</td>
			</tr>
        
		</c:forEach>
        </tbody>
    </table>
	
   <!-- 其他功能超链接 -->
	<div id="TableTail" align="center">
		<div class="FunctionButton"><a href="${pageContext.request.contextPath }/food?method=findFoodType">添加</a></div>
    	当前${requestScope.pageBean.currentPage }/${requestScope.pageBean.totalPage }页     &nbsp;&nbsp;
		<a href="${pageContext.request.contextPath }/food?method=list&currentPage=1">首页</a>
		<a href="${pageContext.request.contextPath }/food?method=list&currentPage=${requestScope.pageBean.currentPage-1}">上一页 </a>
		<a href="${pageContext.request.contextPath }/food?method=list&currentPage=${requestScope.pageBean.currentPage+1}">下一页 </a>
		<a href="${pageContext.request.contextPath }/food?method=list&currentPage=${requestScope.pageBean.totalPage}">末页</a>
    </div> 
</div>
</body>
</html>

currentPage=$ {requestScope.pageBean.currentPage-1}">上一页
currentPage=$ {requestScope.pageBean.totalPage} 末页
根据前端的代码我们可以接收到pageBean对象及其参数
所以我们要使用 PageBean类来接受参数

PageBean类

public class PageBean<T> {
	private int currentPage = 1; // 当前页, 默认显示第一页
	private int pageCount = 5;   // 每页显示的行数(查询返回的行数), 默认每页显示4行
	private int totalCount;      // 总记录数
	private int totalPage;       // 总页数 = 总记录数 / 每页显示的行数  (+ 1)
	private List<T> pageData;       // 分页查询到的数据
	private Condition condition; //查询条件 都在里面了 食品名字,菜系名字 ,如果在加条件 就方便修改
	
	public Condition getCondition() {
		return condition;
	}
	public void setCondition(Condition condition) {
		this.condition = condition;
	}
	// 返回总页数
	public int getTotalPage() {
		if (totalCount % pageCount == 0) {
			totalPage = totalCount / pageCount;
		} else {
			totalPage = totalCount / pageCount + 1;
		}
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getPageCount() {
		return pageCount;
	}
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}
	
	public List<T> getPageData() {
		return pageData;
	}
	public void setPageData(List<T> pageData) {
		this.pageData = pageData;
	}
	
	

}

Dao实现

public interface IFoodDao {
	public void add(Food food);
	public void delete(int id);
	public void update(Food food);
	public List<Food> query(); //查询所有
	public Food findById(int id); 
	
	public List<Food> query(String keyword);
	//通过菜系找菜品
	List<Food> findByType(int type);
	//分页查询（要判断查询条件)
	public void getAll(PageBean<Food> pb);
	public int getTotalCount(PageBean<Food> pb);
}

public class FoodDao implements IFoodDao {

	private QueryRunner qr = JdbcUtils.getQuerrRunner();

	@Override
	public void add(Food food) {
		String sql = " INSERT food(foodName,foodType_id,price,mprice,remark,img) VALUES(?,?,?,?,?,?)";
		try {
			qr.update(sql, food.getFoodName(), food.getFoodType_id(), food.getPrice(), food.getMprice(),
					food.getRemark(), food.getImg());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public void delete(int id) {
		try {
			String sql = "DELETE FROM food WHERE id=?";
			qr.update(sql, id);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public void update(Food food) {
		try {
			String sql = "UPDATE food SET foodName=?,foodType_id=?,price=?,mprice=?,remark=?,img=? WHERE id =?";
			qr.update(sql, food.getFoodName(), food.getFoodType_id(), food.getPrice(), food.getMprice(),
					food.getRemark(), food.getImg(), food.getId());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public List<Food> query() {
		try {
			String sql = "SELECT * FROM food";
			return qr.query(sql, new BeanListHandler<Food>(Food.class));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public Food findById(int id) {
		try {
			String sql = "SELECT * FROM food where id =?";
			return qr.query(sql, new BeanHandler<Food>(Food.class), id);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public List<Food> findByType(int type) {
		try {
			// 根据食物类型找到食物
			String sql = "SELECT * FROM food WHERE foodType_id =?";
			return qr.query(sql, new BeanListHandler<Food>(Food.class), type);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public List<Food> query(String keyword) {
		try {
			String sql = "SELECT * FROM food WHERE foodName LIKE ?";
			return qr.query(sql, new BeanListHandler<Food>(Food.class), "%" + keyword + "%");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public int getTotalCount(PageBean<Food> pb) {
		StringBuilder sb = new StringBuilder(); // null
		// 存储查询条件
		List<Object> list = new ArrayList<Object>();
		sb.append("select count(*) from food f,foodtype ft where f.foodType_id=ft.id");
		Condition condition = pb.getCondition();
		if (condition != null) {
			// 菜名 sql,sql语句里的参数
			String foodName = condition.getFoodName();
			if (foodName != null && !foodName.isEmpty()) {
				sb.append(" and f.foodName like ?");
				list.add("%" + foodName + "%");
			}
			// 菜系
			int type_id = condition.getFoodType_id();
			if (type_id > 0) {
				sb.append(" and f.foodType_id=?");
				list.add(type_id);
			}
		}
		// list->array->'红'->?
		try {

			Long count = qr.query(sb.toString(), new ScalarHandler<Long>(), list.toArray());
			return count.intValue();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public void getAll(PageBean<Food> pb) {
		// 1.查询出总记录数，并设置到pb对象
		int totalCount = this.getTotalCount(pb);
		pb.setTotalCount(totalCount);
		// 控制翻页
		if (pb.getCurrentPage() <= 0) {
			pb.setCurrentPage(1);
		} else if (pb.getCurrentPage() > pb.getTotalPage()) {
			pb.setCurrentPage(pb.getTotalPage());
		}

		// 2.获取当前页：计算limit index,count
		int currentPage = pb.getCurrentPage();
		int index = (currentPage - 1) * pb.getPageCount();
		int count = pb.getPageCount();

		// 3.分页查询数据，按foodName和foodType_id
		Condition condition = pb.getCondition();
		StringBuilder sb = new StringBuilder();
		sb.append("select f.id,f.foodName,f.foodType_id,f.price,f.mprice,f.remark,f.img,ft.typeName "
				+ "from food f,foodtype ft " + "where f.foodType_id=ft.id");
		// 存储查询条件的集合
		List<Object> list = new ArrayList<Object>();
		if (condition != null) {
			String foodName = condition.getFoodName();
			if (foodName != null && !foodName.isEmpty()) {
				sb.append(" and f.foodName like ?");
				list.add("%" + foodName + "%");
			}
			int type_id = condition.getFoodType_id();
			if (type_id > 0) {
				sb.append(" and f.foodType_id=?");
				list.add(type_id);
			}
		}
		sb.append(" limit ?,?");
		list.add(index);
		list.add(count);
		try {
			if (index >= 0) {
				List<Food> pageData = qr.query(sb.toString(), new BeanListHandler<Food>(Food.class), list.toArray());
				pb.setPageData(pageData);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

}

MySQL

数据库分页
Select * from 表名 limit startrow,pagesize
(Pagesize为每页显示的记录条数)

欢乐大象

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分页学习

前端页面<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 T...
复制链接

扫一扫