ProductDao
1 /** 2 * 查询商品列表并分页,可输入的条件有:商品名(模糊),商品状态,店铺Id,商品类别 3 * 4 * @param productCondition 5 * @param rowIndex 从第几行开始取数据 6 * @param pageSize 返回多少条数据 7 * 比如rowIndex为1,pageSize为5 即从第一行开始取,取5条数据 8 * @return 9 */ 10 List<Product> queryProductList(@Param("productCondition")Product productCondition,@Param("rowIndex") int rowIndex,@Param("pageSize") int pageSize); 11 12 /** 13 * 按照条件查询对应的商品总数 14 * @param productCondition 15 * @return 16 */ 17 int queryProductCount(@Param("productCondition") Product productCondition);
ProductDao.xml
<select id="queryProductList" resultType="com.ryanxu.o2o.entity.Product"> SELECT product_id, product_name, product_desc, img_addr, normal_price, promotion_price, priority, create_time, last_edit_time, enable_status, product_category_id, shop_id FROM tb_product <where> <!-- 根据shopId 查询 --> <if test="productCondition.shop != null and productCondition.shop.shopId != null "> and shop_id = #{productCondition.shop.shopId} </if> <!-- 根据product_category_id 查询 --> <if test="productCondition.productCategory != null and productCondition.productCategory.productCategoryId != null"> and product_category_id = #{productCondition.productCategory.productCategoryId} </if> <!-- 根据product_name 模糊查询 --> <!-- 写like语句的时候一般都会写成like '% %'在mybatis里面写就是应该是like '%${name}%' 而不是 '%#{name}%' ${name}是不带单引号的,#{name} 是带引号的 --> <if test="productCondition.productName != null"> and product_name like '%${productCondition.productName}%' </if> <!-- 根据enable_status 查询 --> <if test="productCondition.enableStatus != null"> and enable_status = #{productCondition.enableStatus} </if> </where> ORDER BY priority DESC LIMIT #{rowIndex} ,#{pageSize}; </select> <select id="queryProductCount" resultType="int"> SELECT count(1) FROM tb_product <where> <!-- 根据shopId 查询 --> <if test="productCondition.shop != null and productCondition.shop.shopId != null "> and shop_id = #{productCondition.shop.shopId} </if> <!-- 根据product_category_id 查询 --> <if test="productCondition.productCategory != null and productCondition.productCategory.productCategoryId != null"> and product_category_id = #{productCondition.productCategory.productCategoryId} </if> <!-- 根据product_name 模糊查询 --> <!-- 写like语句的时候一般都会写成like '% %'在mybatis里面写就是应该是like '%${name}%' 而不是 '%#{name}%' ${name}是不带单引号的,#{name} 是带引号的 --> <if test="productCondition.productName != null"> and product_name like '%${productCondition.productName}%' </if> <!-- 根据enable_status 查询 --> <if test="productCondition.enableStatus != null"> and enable_status = #{productCondition.enableStatus} </if> </where> </select>
Service层
Service接口层只需要提供一个方法即可,内部调用Dao层的两个方法,将返回结果封装到DTO中。
1 /** 2 * 查询商品列表并分页,可输入的条件有:商品名(模糊),商品状态,店铺Id,商品类别 3 * 4 * @param productCondition 5 * @param pageIndex 6 * @param pageSize 7 * @return 8 */ 9 ProductExecution getProductList(Product productCondition,int pageIndex,int pageSize);
1 @Override 2 public ProductExecution getProductList(Product productCondition, int pageIndex, int pageSize) { 3 //页码转换成数据库的行码,并调用dao层取回指定页码的商品列表 4 int rowIndex = PageCalculator.calculateRowIndex(pageIndex, pageSize); 5 List<Product> productList = productDao.queryProductList(productCondition, rowIndex, pageSize); 6 //基于同样的查询条件返回该查询条件下的商品总数 7 int count = productDao.queryProductCount(productCondition); 8 ProductExecution pe = new ProductExecution(); 9 pe.setProductList(productList); 10 pe.setCount(count); 11 return pe; 12 }
Controller层
1 @RequestMapping(value="/getproductlistbyshop",method = RequestMethod.GET) 2 @ResponseBody 3 private Map<String, Object> getProductListByShop(HttpServletRequest request){ 4 Map<String, Object> modelMap = new HashMap<String,Object>(); 5 //获取前台传过来的页码 6 int pageIndex = HttpServletRequestUtil.getInt(request, "pageIndex"); 7 //获取前台传过来的每页要求返回的商品数上限 8 int pageSize = HttpServletRequestUtil.getInt(request, "pageSize"); 9 //从当前session中获取店铺信息,主要是获取shopId 10 Shop currentShop = (Shop) request.getSession().getAttribute("currentShop"); 11 //空值判断 12 if((pageIndex > -1) && (pageSize > -1)&&(currentShop !=null)&& (currentShop.getShopId() != null)) { 13 //获取传入的需要检索的条件,包括是否需要从某个商品类别以及模糊查找商品名去筛选某个店铺下的商品 14 //筛选的条件可以进行排列组合 15 long productCategoryId = HttpServletRequestUtil.getInt(request, "productCategoryId"); 16 String productName = HttpServletRequestUtil.getString(request, "productName"); 17 Product productCondition = compactProductCondition(currentShop.getShopId(),productCategoryId,productName); 18 //传入查询条件以及分页信息进行查询,返回相应商品列表以及总数 19 ProductExecution pe = productService.getProductList(productCondition, pageIndex, pageSize); 20 modelMap.put("productList", pe.getProductList()); 21 modelMap.put("count", pe.getCount()); 22 modelMap.put("success", true); 23 }else { 24 modelMap.put("success", false); 25 modelMap.put("errMsg", "empty pageSize or pageIndex or shopId"); 26 } 27 return modelMap; 28 } 29 30 private Product compactProductCondition(Long shopId, long productCategoryId,String productName) { 31 Product productCondition = new Product(); 32 Shop shop = new Shop(); 33 shop.setShopId(shopId); 34 productCondition.setShop(shop); 35 //若有指定类别的要求则添加进去 36 if(productCategoryId != -1L) { 37 ProductCategory productCategory = new ProductCategory(); 38 productCategory.setProductCategoryId(productCategoryId); 39 productCondition.setProductCategory(productCategory); 40 } 41 //若有商品名模糊查询的要求则添加进去 42 if(productName != null) { 43 productCondition.setProductName(productName); 44 } 45 return productCondition; 46 }