IGeekShop案例5-条件查询商品
1 修改list.jsp页面
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<HTML>
<HEAD>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link href="${pageContext.request.contextPath}/css/Style1.css"
rel="stylesheet" type="text/css" />
<script language="javascript"
src="${pageContext.request.contextPath}/js/public.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript">
function addProduct(){
window.location.href = "${pageContext.request.contextPath}/adminAddProductUI";
}
function delProduct(pid){
var flag = confirm("你确认要删除吗?");
/* alert(pid); */
/* alert(flag); */
if(flag){
location.href="${pageContext.request.contextPath}/delProduct?pid="+pid;
}
}
function editProduct(pid){
/* alert(pid); */
location.href="${pageContext.request.contextPath}/adminEditProduct?pid="+pid;
}
</script>
</HEAD>
<body>
<br>
<form id="Form1" name="Form1"action="${pageContext.request.contextPath}/adminSearchProductList"method="post">
商品名称:<input type="text" name="pname" value=""/>
是否热门:<select id="isHot" name="isHot">
<option value="">不限</option>
<option value="1">是</option>
<option value="2">否</option>
</select>
商品类别:<select id="cid" name="cid">
<option value="">不限</option>
<option value="1">手机数码</option>
<option value="2">电脑办公</option>
</select>
<input type="submit" value="搜索"/>
<table style="margin-top: 10px" cellSpacing="1" cellPadding="0" width="100%" align="center"bgColor="#f5fafe" border="0">
<TBODY>
<tr>
<td class="ta_01" align="center" bgColor="#afd1f3"><strong>商品列表</strong>
</TD>
</tr>
<tr>
<td class="ta_01" align="right">
<button type="button" id="add" name="add" value="添加"
class="button_add" onclick="addProduct()">
添加</button>
</td>
</tr>
<tr>
<td class="ta_01" align="center" bgColor="#f5fafe">
<table cellspacing="0" cellpadding="1" rules="all"
bordercolor="gray" border="1" id="DataGrid1"
style="BORDER-RIGHT: gray 1px solid; BORDER-TOP: gray 1px solid; BORDER-LEFT: gray 1px solid; WIDTH: 100%; WORD-BREAK: break-all; BORDER-BOTTOM: gray 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #f5fafe; WORD-WRAP: break-word">
<tr
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; HEIGHT: 25px; BACKGROUND-COLOR: #afd1f3">
<td align="center" width="18%">序号</td>
<td align="center" width="17%">商品图片</td>
<td align="center" width="17%">商品名称</td>
<td align="center" width="17%">商品价格</td>
<td align="center" width="17%">是否热门</td>
<td width="7%" align="center">编辑</td>
<td width="7%" align="center">删除</td>
</tr>
<!-- varStatus 记录第几次遍历 -->
<c:forEach items="${productList }" var="product" varStatus="vs">
<tr onmouseover="this.style.backgroundColor = 'white'"
onmouseout="this.style.backgroundColor = '#F5FAFE';">
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="18%">${vs.count }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%"><img width="40" height="45" src="${pageContext.request.contextPath }/${product.pimage }"></td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">${product.pname }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">${product.shop_price }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">
${product.is_hot==1?"是":"否" }</td>
<td align="center" style="HEIGHT: 22px"><a
href="javascript:void(0)" onclick="editProduct(${product.pid})">
<img
src="${pageContext.request.contextPath}/images/i_edit.gif"
border="0" style="CURSOR: hand">
</a></td>
<!-- href="javascript:void(0)"让它不跳转 -->
<td align="center" style="HEIGHT: 22px"><a href="javascript:void(0)" onclick="delProduct(${product.pid})"> <img
src="${pageContext.request.contextPath}/images/i_del.gif"
width="16" height="16" border="0" style="CURSOR: hand">
</a></td>
</tr>
</c:forEach>
</table>
</td>
</tr>
</TBODY>
</table>
</form>
</body>
</HTML>
2 web层
package www.test.web;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.BeanUtils;
import www.test.domain.Category;
import www.test.domain.Product;
import www.test.service.AdminProductService;
import www.test.vo.Condition;
public class AdminSearchProductListServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 1 获取用户输入的查询条件数据
Map<String, String[]> properties = request.getParameterMap();
// 2 封装获取到的数据到condition对象中
Condition condition = new Condition();
//利用BeauUtils进行子映射封装
//将散装的查询数据封装到一个 VO 实体中
try {
BeanUtils.populate(condition, properties);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
//到这里condition对象已经封装好了
// 3 将获取到的数据传递给service层
AdminProductService service = new AdminProductService();
List<Product> productList = null;
try {
productList = service.findProductListByCondition(condition);
} catch (SQLException e) {
e.printStackTrace();
}
//点击搜索之后,信息回显实现
//获得所有的商品的类别数据
List<Category> categoryList = null;
try {
categoryList = service.findAllCategory();
} catch (SQLException e) {
e.printStackTrace();
}
// 4 将数据存储到request域中
request.setAttribute("productList", productList);
request.setAttribute("categoryList", categoryList);
request.setAttribute("condition", condition);//用于数据的回显判断依据
//转发
request.getRequestDispatcher("admin/product/list.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
3 service层
package www.test.service;
import java.sql.SQLException;
import java.util.List;
import www.test.dao.AdminProductDao;
import www.test.domain.Category;
import www.test.domain.Product;
import www.test.vo.Condition;
public class AdminProductService {
public List<Product> findAllProduct() throws SQLException {
//因为没有复杂业务 直接传递请求到dao层
AdminProductDao dao = new AdminProductDao();
return dao.findAllProduct();
}
public List<Category> findAllCategory() throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.finAllCategory();
}
// 添加商品
public boolean addProduct(Product product) throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.addProduct(product);
}
//删除数据
public boolean delProduct(String pid) throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.delProduct(pid);
}
// 修改数据
public Product editProduct(String pid) throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.editProduct(pid);
}
//保存修改后的数据
public boolean editProductSave(Product product) throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.editProductSave(product);
}
//根据用户输入的添加查询product
public List<Product> findProductListByCondition(Condition condition) throws SQLException {
AdminProductDao dao = new AdminProductDao();
return dao.findProductListByCondition(condition);
}
}
4 dao层
package www.test.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import www.test.domain.Category;
import www.test.domain.Product;
import www.test.utils.C3P0Utils;
import www.test.vo.Condition;
public class AdminProductDao {
public List<Product> findAllProduct() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
List<Product> productList = qr.query(sql, new BeanListHandler<Product>(Product.class));
return productList;
}
public List<Category> finAllCategory() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from category";
List<Category> categoryList = qr.query(sql, new BeanListHandler<Category>(Category.class));
return categoryList;
}
//添加数据
public boolean addProduct(Product product) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";
Object[] params = {product.getPid(),product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getCid()};
int num = qr.update(sql, params);
if(num>0){
return true;
}else{
return false;
}
}
// 删除数据
public boolean delProduct(String pid) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "delete from product where pid=?";
int num = qr.update(sql, pid);
if(num>0){
return true;
}else{
return false;
}
}
//修改数据
public Product editProduct(String pid) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product where pid=?";
Product product = qr.query(sql, new BeanHandler<Product>(Product.class),pid);
return product;
}
//保存修改后的数据
public boolean editProductSave(Product product) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "update product set pname=?,market_price=?,shop_price=?,pimage=?,pdate=?,is_hot=?,pdesc=?,pflag=?,cid=? where pid=?";
int num = qr.update(sql, product.getPname(),product.getMarket_price(),
product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),
product.getPdesc(),product.getPflag(),product.getCid(),product.getPid());
System.out.println(num);
if(num>0){
return true;
}else{
return false;
}
}
//根据用户输入的条件查询商品
public List<Product> findProductListByCondition(Condition condition) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//定义一个存储实际参数的容器
List<String> list = new ArrayList<String>();
String sql = "select * from product where 1=1 ";
// 判断用户是否输入了商品名称,并且要去除两端的空格
if(condition.getPname()!=null&&!condition.getPname().trim().equals("")){
//防止连接到了一起,最好加上问号。
sql+=" and pname like ?";
//将传递的参数存储到list集合中
list.add("%"+condition.getPname().trim()+"%");
}
if(condition.getIsHot()!=null&&!condition.getIsHot().trim().equals("")){
sql+=" and is_hot=? ";
list.add(condition.getIsHot().trim());
}
if(condition.getCid()!=null&&!condition.getCid().trim().equals("")){
sql+=" and cid=? ";
list.add(condition.getCid().trim());
}
List<Product> productList = qr.query(sql, new BeanListHandler<Product>(Product.class) ,list.toArray());
return productList;
}
}
1 直接用数组的话,长度又不确定,所以使用了集合。
5 修改list.jsp
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<HTML>
<HEAD>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link href="${pageContext.request.contextPath}/css/Style1.css"
rel="stylesheet" type="text/css" />
<script language="javascript"
src="${pageContext.request.contextPath}/js/public.js"></script>
<script type="text/javascript"
src="${pageContext.request.contextPath }/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript">
function addProduct(){
window.location.href = "${pageContext.request.contextPath}/adminAddProductUI";
}
function delProduct(pid){
var flag = confirm("你确认要删除吗?");
/* alert(pid); */
/* alert(flag); */
if(flag){
location.href="${pageContext.request.contextPath}/delProduct?pid="+pid;
}
}
function editProduct(pid){
/* alert(pid); */
location.href="${pageContext.request.contextPath}/adminEditProduct?pid="+pid;
}
$(function(){
$("#cid option[value='${condition.cid}']").prop("selected","selected");
$("#isHot option[value='${condition.isHot}']").prop("selected","selected");
});
</script>
</HEAD>
<body>
<br>
<form id="Form1" name="Form1"
action="${pageContext.request.contextPath}/adminSearchProductList"
method="post">
商品名称:<input type="text" name="pname" value="${condition.pname }" />
是否热门:<select id="isHot" name="isHot">
<option value="">不限</option>
<option value="1">是</option>
<option value="0">否</option>
</select> 商品类别:<select id="cid" name="cid">
<option value="">不限</option>
<c:forEach items="${categoryList }" var="category">
<option value="${category.cid }">${category.cname }</option>
</c:forEach>
</select> <input type="submit" value="搜索" />
<table style="margin-top: 10px" cellSpacing="1" cellPadding="0"
width="100%" align="center" bgColor="#f5fafe" border="0">
<TBODY>
<tr>
<td class="ta_01" align="center" bgColor="#afd1f3"><strong>商品列表</strong>
</TD>
</tr>
<tr>
<td class="ta_01" align="right">
<button type="button" id="add" name="add" value="添加"
class="button_add" onclick="addProduct()">添加</button>
</td>
</tr>
<tr>
<td class="ta_01" align="center" bgColor="#f5fafe">
<table cellspacing="0" cellpadding="1" rules="all"
bordercolor="gray" border="1" id="DataGrid1"
style="BORDER-RIGHT: gray 1px solid; BORDER-TOP: gray 1px solid; BORDER-LEFT: gray 1px solid; WIDTH: 100%; WORD-BREAK: break-all; BORDER-BOTTOM: gray 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #f5fafe; WORD-WRAP: break-word">
<tr
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; HEIGHT: 25px; BACKGROUND-COLOR: #afd1f3">
<td align="center" width="18%">序号</td>
<td align="center" width="17%">商品图片</td>
<td align="center" width="17%">商品名称</td>
<td align="center" width="17%">商品价格</td>
<td align="center" width="17%">是否热门</td>
<td width="7%" align="center">编辑</td>
<td width="7%" align="center">删除</td>
</tr>
<!-- varStatus 记录第几次遍历 -->
<c:forEach items="${productList }" var="product" varStatus="vs">
<tr onmouseover="this.style.backgroundColor = 'white'"
onmouseout="this.style.backgroundColor = '#F5FAFE';">
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="18%">${vs.count }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%"><img width="40" height="45"
src="${pageContext.request.contextPath }/${product.pimage }"></td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">${product.pname }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">${product.shop_price }</td>
<td style="CURSOR: hand; HEIGHT: 22px" align="center"
width="17%">${product.is_hot==1?"是":"否" }</td>
<td align="center" style="HEIGHT: 22px"><a
href="javascript:void(0)"
onclick="editProduct(${product.pid})"> <img
src="${pageContext.request.contextPath}/images/i_edit.gif"
border="0" style="CURSOR: hand">
</a></td>
<!-- href="javascript:void(0)"让它不跳转 -->
<td align="center" style="HEIGHT: 22px"><a
href="javascript:void(0)" onclick="delProduct(${product.pid})">
<img
src="${pageContext.request.contextPath}/images/i_del.gif"
width="16" height="16" border="0" style="CURSOR: hand">
</a></td>
</tr>
</c:forEach>
</table>
</td>
</tr>
</TBODY>
</table>
</form>
</body>
</HTML>
6 注意事项
1 debug调试中 ? 占位符一直显示的都是? 不会改变。