一.底层基类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;
}/**
* 分页查询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)根据菜品和价格条件查询!
扫一扫
2139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
