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

「爱情、让人受尽委屈。」 2023-10-17 17:44 46阅读 0赞

**一.底层基类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>

分页的页面:

**五.效果图**

1)无条件查询!

**![Center][]**

2)根据菜品条件查询!

**![Center 1][]**

3)根据价格条件查询!

![Center 2][]

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

![Center 3][]

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

![Center 4][]

[Center]: https://img-blog.csdn.net/20180112164732732?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20180112164936722?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20180112165106317?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: https://img-blog.csdn.net/20180112165218249?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: https://img-blog.csdn.net/20180112165245507?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center