IGeekShop案例5-条件查询商品

今天药忘吃喽~ 2024-04-17 14:07 62阅读 0赞

# 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>
                                
                                <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>
            
                                        
                                        <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 直接用数组的话，长度又不确定，所以使用了集合。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZtazEwMjM_size_16_color_FFFFFF_t_70]

## 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>
                                
                                <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>
    
                                        
                                        <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调试中 ？ 占位符一直显示的都是? 不会改变。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZtazEwMjM_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/9f4313e045574c678fd39ed1fb674911.png