MVC 实现多个条件的分页查询

一.底层基类Dao

	/**
	 * 连接池
	 * @return
	 */
	public Connection getConnection() {
		// 1.上下文对象
		try {
			Context context = new InitialContext();
			// 2.获得数据源
			DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mysql");

			// 3.获得连接
			return ds.getConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;

	}
	
	
	/**
	 * 获得个数
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public int getCount(String sql, Object... parameters) {

		// 1)获得连接
		Connection con = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		// 2)获得ps对象
		try {
			ps = con.prepareStatement(sql);

			// 判断
			if (parameters.length != 0) {
				// 3)给参数赋值
				for (int i = 0; i < parameters.length; i++) {
					ps.setObject(i + 1, parameters[i]);
				}

			}
			// 4)获得结果集
			rs = ps.executeQuery();

			// 5)光标往下移
			rs.next();

			// 6)获得数据
			return rs.getInt(1);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 关闭连接
			closeAll(rs, ps, con);

		}

		return 0;

	}

 二. 底层数据FoodDao

/**
	 * 分页查询2
	 * 
	 * @param page
	 * @return
	 */
	public List<Foodinfo> pageAll(PageInfo page, Map<String, String> condition) {
		// 条件是否为空
		if (condition == null || condition.size() == 0) {
			sql = "select * from  foodinfo limit ?,?";
			return this.executSQLFood(sql,
					(page.getCurrentPage() - 1) * page.getPageSize(),
					page.getPageSize());
		} else { // 不为空
			// 2个条件都不为空!
			if (condition.get("foodName") != null
					&& condition.get("foodPrice") != null) {
				System.out.println(condition.get("foodName") + "==>"
						+ condition.get("foodPrice"));
				sql = "select * from  foodinfo where foodName like ? and foodPrice=? limit ?,?";
				return this.executSQLFood(sql, "%" + condition.get("foodName")
						+ "%", condition.get("foodPrice"),
						(page.getCurrentPage() - 1) * page.getPageSize(),
						page.getPageSize());
			} else if (condition.get("foodName") != null) { //1个条件都不为空!
				sql = "select * from  foodinfo where foodName like ? limit ?,?";
				return this.executSQLFood(sql, "%" + condition.get("foodName")
						+ "%",
						(page.getCurrentPage() - 1) * page.getPageSize(),
						page.getPageSize());
			} else if (condition.get("foodPrice") != null) { //1个条件都不为空!
				sql = "select * from  foodinfo where foodPrice=? limit ?,?";
				return this.executSQLFood(sql, condition.get("foodPrice"),
						(page.getCurrentPage() - 1) * page.getPageSize(),
						page.getPageSize());
			}

		}
		return null;
	}

	/**
	 * 查询总个数
	 * 
	 * @return
	 */
	public int getCounts(Map<String, String> condition) {
		//为空!
		if (condition == null || condition.size() == 0) {
			sql = "select count(1) from foodinfo";
			return this.getCount(sql);
		} else {
			// 2个条件都不为空!
			if (condition.get("foodName") != null
					&& condition.get("foodPrice") != null) { //2个条件都不为空!
				sql = "select count(1) from  foodinfo where foodName like ? and foodPrice= ?";
				return this.getCount(sql,
						"%" + condition.get("foodName") + "%",
						condition.get("foodPrice"));
			} else if (condition.get("foodName") != null) { //1个条件都不为空!
				sql = "select count(1) from  foodinfo where foodName like ?";
				return this
						.getCount(sql, "%" + condition.get("foodName") + "%");
			} else if (condition.get("foodPrice") != null) { //1个条件都不为空!
				sql = "select count(1) from  foodinfo where foodPrice= ?";
				return this.getCount(sql, condition.get("foodPrice"));
			}
		}
		return 0;

	}

	/**
	 * 所有信息
	 * 
	 * @param sql
	 * @param ob
	 * @return
	 */
	public List<Foodinfo> executSQLFood(String sql, Object... ob) {
		List<Foodinfo> list = new ArrayList<Foodinfo>();
		con = getConnection();
		try {
			ps = con.prepareStatement(sql);
			if (ob.length != 0) {
				for (int i = 0; i < ob.length; i++) {
					ps.setObject(i + 1, ob[i]);
				}
			}
			// 获得结果集
			rs = ps.executeQuery();
			// 遍历添加到集合中
			while (rs.next()) {
				Foodinfo foodinfo = new Foodinfo(rs.getInt(1), rs.getString(2),
						rs.getString(3), rs.getDouble(4), rs.getString(5),
						rs.getString(6));
				list.add(foodinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, con);
		}
		return list;
	}

三.控制层Servlet

public void pageListCondition(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		// //
		request.setCharacterEncoding("utf-8");

		// 获得参数值
		String name = request.getParameter("foodName");
		String price = request.getParameter("price");

		// map对象
		Map<String, String> condition = (Map<String, String>) request
				.getAttribute("condition");
		//判断
		if (condition == null) {
			condition = new HashMap<String, String>(); // 创建对象
			request.setAttribute("condition", condition); // 保存对象
		}

		if (name != null && !"".equals(name)) {
			condition.put("foodName", name);

		}
		if (price != null && !"".equals(price)) {
			condition.put("foodPrice", price);

		}

	
		// ///

		// 获得当前页
		String string = request.getParameter("pageIndex");
		// 判断当前页是否是空
		int pageIndex = (string == null) ? (1) : (Integer.parseInt(string));

		// 业务对象
		FoodDao dao = new FoodDao();

		// 获得总个数
		int count = dao.getCounts(condition);


		PageInfo pageinfo = new PageInfo(count, pageIndex, 4);
		

		

		// 获得分页
		List<Foodinfo> list = dao.pageAll(pageinfo, condition);

		// 保存
		request.setAttribute("list", list);
		request.setAttribute("pageInfos", pageinfo);

		request.getRequestDispatcher("food2.jsp").forward(request, response);

		// 关闭out
		out.flush();
		out.close();
	}

四.JSP页面

	<div id="main">
		<div class="find">
			<form action="pageListCondition.do" method="post">
				菜品:<input id="d1" type="text" name="foodName"
					value="${condition.foodName}" />    价格:<select
					name="price">
					<option value="">请选择</option>
					<option value="0"
						<c:if test="${condition.foodPrice==0}">selected</c:if>>0</option>
					<option value="8"
						<c:if test="${condition.foodPrice==8}">selected</c:if>>8</option>
					<option value="7"
						<c:if test="${condition.foodPrice==7}">selected</c:if>>7</option>
					<option value="6"
						<c:if test="${condition.foodPrice==6}">selected</c:if>>6</option>
					<option value="12"
						<c:if test="${condition.foodPrice==12}">selected</c:if>>12</option>
				</select>    <input type="submit" value="查询" />
			</form>
		</div>

		<table border="1" width="623">
			<caption>
				<h1>菜品信息</h1>
			</caption>
			<tbody>
				<tr>
					<td> id</td>
					<td> name</td>
					<td> price</td>
					<td>remark <br></td>
					<td>description<br></td>
					<td> image</td>
				</tr>
				<c:choose>
					<c:when test="${fn:length(list)==0 }">
						<tr>
							<td class="rd" colspan="6" align="center">没有您要查找的数据</td>
						</tr>
					</c:when>

					<c:otherwise>
						<c:forEach var="food" items="${list }">

							<tr>
								<td>${food.foodid }</td>
								<td>${food.foodname }</td>
								<td>${food.foodprice }</td>
								<td>${food.remark }</td>
								<td>${food.description }</td>
								<td>${food.foodimage }</td>
							</tr>

						</c:forEach>
			</tbody>
			<tfoot>
				<tr align="center">
					<td colspan="6"><jsp:include page="page_footer2.jsp"></jsp:include>
					</td>
				</tr>
			</tfoot>

			</c:otherwise>
			</c:choose>
		</table>
	</div>

分页的页面:

 

<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">
	function goPage(id) {
		//跳转到servlet处理
		document.forms[0].action = "pageListCondition.do?pageIndex=" + id;
		document.forms[0].submit(); // 表单提交
	}
</script>
</head>

<body>

	第${pageInfos.currentPage}/${pageInfos.pageToal}页      
	<c:choose>
		<c:when test="${pageInfos.currentPage>1}">
			<a href="javascript:goPage(1)">首页</a>
			<a href="javascript:goPage(${pageInfos.currentPage-1})">上一页</a>
		</c:when>
		<c:otherwise>
		   首页    上一页
		</c:otherwise>
	</c:choose>
	<c:choose>
		<c:when test="${pageInfos.currentPage<pageInfos.pageToal}">
			<a href="javascript:goPage(${pageInfos.currentPage+1})">下一页</a>
			<a href="javascript:goPage(${pageInfos.pageToal})">尾页</a>
		</c:when>
		<c:otherwise>
		   下一页    尾页
		</c:otherwise>
	</c:choose>
	<input type="hidden" name="pageIndex" value="${pageInfos.currentPage}" />
</html>

 五.效果图

  1)无条件查询!

 2)根据菜品条件查询!

3)根据价格条件查询!

4)根据菜品和价格条件查询!

5)根据菜品和价格条件查询!