实战SSM_O2O商铺_36【商品】商品列表之Dao+Service+Controller层的实现

原创

小小工匠 2022-04-13 15:11:42 ©著作权

文章标签 ssm实战 sql apache java 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者小小工匠的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

概述
Dao层

ProductDao.java
ProductDao.xml
单元测试

Service层

ProductService.java
ProductServiceImpl.java
单元测试

Controller层

ProductController.java

单元测试
Github地址

概述

接着继续来完成商品列表的功能吧，需要支持分页的功能。

Dao层

ProductDao.java

增加如下两个接口

/**
   * 
   * 
   * @Title: selectProductList
   * 
   * @Description: 支持分页功能的查询product
   * 
   *               需要支持根据商品名称（支持模糊查询）、商品状态、shopId、商品类别的查询及组合查询
   * 
   * @param productCondition
   * @param rowIndex
   *            从第几行开始取
   * @param pageSize
   *            返回多少行数据（页面上的数据量）
   * 
   *            比如 rowIndex为1,pageSize为5 即为 从第一行开始取，取5行数据
   * 
   * @return: List<Product>
   */
  List<Product> selectProductList(@Param("productCondition") Product productCondition, @Param("rowIndex") int rowIndex, @Param("pageSize") int pageSize);

  /**
   * 
   * 
   * @Title: selectCountProduct
   * 
   * @Description: 按照条件查询 符合前台传入的条件的商品的总数
   * 
   * @param productCondition
   * @return
   * 
   * @return: int
   */
  int selectCountProduct(@Param("productCondition") Product productCondition);

ProductDao.xml

增加如下SQL映射

<sql id="selectProductByCondition">
    <!-- 根据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>
    <!-- 根据enable_status  查询-->
    <if test="productCondition.enableStatus != null">
      and enable_status = #{productCondition.enableStatus}
    </if>
    <!-- 根据product_name  模糊查询-->
    <if test="productCondition.productName != null">
      and product_name  like '%${productCondition.productName}%'
    </if>
</sql>


<select id="selectProductList" resultType="com.artisan.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>
      <include refid="selectProductByCondition"></include>
    </where>
    ORDER BY 
      priority desc 
    LIMIT  #{rowIndex} ,#{pageSize}
  </select>
  
  
  <select id="selectCountProduct" resultType="Integer">
    SELECT
      count(1)
    FROM
      tb_product
    <where>
      <include refid="selectProductByCondition"></include>
    </where>
  </select>

单元测试

@Test
  public void testC_SelectProductListAndCount() {
    int rowIndex = 1;
    int pageSize = 2;
    List<Product> productList = new ArrayList<Product>();
    int effectNum = 0;

    Shop shop = new Shop();
    shop.setShopId(5L);

    Product productCondition = new Product();
    productCondition.setShop(shop);

    productList = productDao.selectProductList(productCondition, rowIndex, pageSize);
    Assert.assertEquals(2, productList.size());

    effectNum = productDao.selectCountProduct(productCondition);
    Assert.assertEquals(7, effectNum);

    System.out.println("==========================================");

    Shop shop2 = new Shop();
    shop2.setShopId(5L);


    ProductCategory productCategory = new ProductCategory();
    productCategory.setProductCategoryId(36L);

    Product productCondition2 = new Product();
    productCondition2.setShop(shop2);
    productCondition2.setProductCategory(productCategory);
    productCondition2.setProductName("test");

    productList = productDao.selectProductList(productCondition2, rowIndex, pageSize);
    Assert.assertEquals(2, productList.size());

    effectNum = productDao.selectCountProduct(productCondition2);
    Assert.assertEquals(5, effectNum);
  }

结合tb_product中的记录，验证是否符合预期，这里单元测试通过。

实战SSM_O2O商铺_36【商品】商品列表之Dao+Service+Controller层的实现_sql

JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@29a0cdb] will not be managed by Spring
==>  Preparing: 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 shop_id = ? ORDER BY priority desc LIMIT ? ,? 
==> Parameters: 5(Long), 1(Integer), 2(Integer)
<==    Columns: 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
<==        Row: 2, modifyProduct, modifyProduct desc, /mmm/ddd, 350, 300, 66, 2018-06-29 17:46:46.0, 2018-06-30 16:02:01.0, 1, 36, 5
<==        Row: 3, test_product, product desc, /aaa/bbb, 10, 8, 66, 2018-06-24 18:45:36.0, 2018-06-24 18:45:36.0, 1, 36, 5
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@492691d7]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b94089b] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@47f9738] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_product WHERE shop_id = ? 
==> Parameters: 5(Long)
<==    Columns: count(1)
<==        Row: 7
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b94089b]
==========================================
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@388526fb] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@6436a7db] will not be managed by Spring
==>  Preparing: 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 shop_id = ? and product_category_id = ? and product_name like '%test%' ORDER BY priority desc LIMIT ? ,? 
==> Parameters: 5(Long), 36(Long), 1(Integer), 2(Integer)
<==    Columns: 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
<==        Row: 4, test_product, product desc, \upload\item\shopImage\5\2018062515593428322.jpg, 10, 8, 66, 2018-06-25 15:58:16.0, 2018-06-25 15:58:17.0, 1, 36, 5
<==        Row: 5, test_product, product desc, \upload\item\shopImage\5\2018062516124013361.jpg, 10, 8, 66, 2018-06-25 16:12:40.0, 2018-06-25 16:12:40.0, 1, 36, 5
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@388526fb]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40238dd0] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@79179359] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_product WHERE shop_id = ? and product_category_id = ? and product_name like '%test%' 
==> Parameters: 5(Long), 36(Long)
<==    Columns: count(1)
<==        Row: 5
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40238dd0]

Service层

Service接口层只需要提供一个方法即可，内部调用Dao层的两个方法，将返回结果封装到DTO中。

ProductService.java

/**
   * 
   * 
   * @Title: queryProductionList
   * 
   * @Description: 查询
   * 
   * @param productCondition
   * @param pageIndex
   *            前端页面 只有第几页 第几页 定义为pageIndex
   * @param pageSize
   *            一页中展示的行数
   * @throws ProductOperationException
   * 
   * @return: ProductExecution
   */
  ProductExecution queryProductionList(Product productCondition, int pageIndex, int pageSize) throws ProductOperationException;

ProductServiceImpl.java

@Override
  public ProductExecution queryProductionList(Product productCondition, int pageIndex, int pageSize) throws ProductOperationException {
    List<Product> productList = null;
    int count = 0;
    try {
      // 将pageIndex 转换为Dao层识别的rowIndex
      int rowIndex = PageCalculator.calculateRowIndex(pageIndex, pageSize);
      // 调用Dao层获取productList和总量
      productList = productDao.selectProductList(productCondition, rowIndex, pageSize);
      count = productDao.selectCountProduct(productCondition);
    } catch (Exception e) {
      e.printStackTrace();
      new ProductExecution(ProductStateEnum.INNER_ERROR);
    }
    return new ProductExecution(ProductStateEnum.SUCCESS, productList, count);
  }

单元测试

@Test
  public void testQueryProductListAndCount() {
    // 库表中符合如下筛选条件的记录为5条
    // select * from tb_product a where a.product_category_id = 36 and
    // a.shop_id = 5 and a.product_name like '%test%';

    // 从第1页开始取，每页取3条
    int pageIndex = 1;
    int pageSize = 3;

    Shop shop2 = new Shop();
    shop2.setShopId(5L);

    ProductCategory productCategory = new ProductCategory();
    productCategory.setProductCategoryId(36L);

    Product productCondition = new Product();
    productCondition.setShop(shop2);
    productCondition.setProductCategory(productCategory);
    productCondition.setProductName("test");


    ProductExecution productExecution = productService.queryProductionList(productCondition, pageIndex, pageSize);
    // 操作成功的状态为1
    Assert.assertEquals(1, productExecution.getState());
    Assert.assertEquals(3, productExecution.getProductList().size());
    Assert.assertEquals(5, productExecution.getCount());

    // 从第2页开始取，每页依然取3条
    pageIndex = 2;
    productExecution = productService.queryProductionList(productCondition, pageIndex, pageSize);
    Assert.assertEquals(1, productExecution.getState());
    Assert.assertEquals(2, productExecution.getProductList().size());
    Assert.assertEquals(5, productExecution.getCount());

  }

实战SSM_O2O商铺_36【商品】商品列表之Dao+Service+Controller层的实现_apache_02

JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@177bea38] will not be managed by Spring
==>  Preparing: 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 shop_id = ? and product_category_id = ? and product_name like '%test%' ORDER BY priority desc LIMIT ? ,? 
==> Parameters: 5(Long), 36(Long), 0(Integer), 3(Integer)
<==    Columns: 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
<==        Row: 3, test_product, product desc, /aaa/bbb, 10, 8, 66, 2018-06-24 18:45:36.0, 2018-06-24 18:45:36.0, 1, 36, 5
<==        Row: 4, test_product, product desc, \upload\item\shopImage\5\2018062515593428322.jpg, 10, 8, 66, 2018-06-25 15:58:16.0, 2018-06-25 15:58:17.0, 1, 36, 5
<==        Row: 5, test_product, product desc, \upload\item\shopImage\5\2018062516124013361.jpg, 10, 8, 66, 2018-06-25 16:12:40.0, 2018-06-25 16:12:40.0, 1, 36, 5
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4716be8b]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@460ebd80] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@74f5ce22] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_product WHERE shop_id = ? and product_category_id = ? and product_name like '%test%' 
==> Parameters: 5(Long), 36(Long)
<==    Columns: count(1)
<==        Row: 5
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@460ebd80]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@dbd8e44] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@6a55299e] will not be managed by Spring
==>  Preparing: 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 shop_id = ? and product_category_id = ? and product_name like '%test%' ORDER BY priority desc LIMIT ? ,? 
==> Parameters: 5(Long), 36(Long), 3(Integer), 3(Integer)
<==    Columns: 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
<==        Row: 6, test_product, product desc, \upload\item\shopImage\5\2018062516132272045.jpg, 10, 8, 66, 2018-06-25 16:13:22.0, 2018-06-25 16:13:22.0, 1, 36, 5
<==        Row: 8, test_product, product desc, /aaa/bbb, 10, 8, 66, 2018-06-30 16:01:59.0, 2018-06-30 16:01:59.0, 1, 36, 5
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@dbd8e44]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a18cd76] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@403f0a22] will not be managed by Spring
==>  Preparing: SELECT count(1) FROM tb_product WHERE shop_id = ? and product_category_id = ? and product_name like '%test%' 
==> Parameters: 5(Long), 36(Long)
<==    Columns: count(1)
<==        Row: 5
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5a18cd76]

Controller层

ProductController.java

增加如下路由方法

@RequestMapping(value = "/getproductlist", method = RequestMethod.GET)
  @ResponseBody
  private Map<String, Object> queryProductList(HttpServletRequest request) {
    Map<String, Object> modelMap = new HashMap<String, Object>();
    // 获取前端传递过来的页码
    int pageIndex = HttpServletRequestUtil.getInt(request, "pageIndex");
    // 获取前端传过来的每页要求返回的商品数量
    int pageSize = HttpServletRequestUtil.getInt(request, "pageSize");

    // 从session中获取shop信息，主要是获取shopId 不依赖前台的参数，尽可能保证安全
    Shop currentShop = (Shop) request.getSession().getAttribute("currentShop");
    // 空值判断
    if ((pageIndex > -1) && (pageSize > -1) && currentShop != null && currentShop.getShopId() != null) {
      // 获取前台可能传递过来的需要检索的条件，包括是否需要从某个商品类别以及根据商品名称模糊查询某个店铺下的商品
      long productCategoryId = HttpServletRequestUtil.getLong(request, "productCategoryId");
      String productName = HttpServletRequestUtil.getString(request, "productName");
      // 拼装查询条件，根据前端传入的条件进行组合
      Product productCondition = compactProductCondition4Search(currentShop.getShopId(), productCategoryId, productName);
      // 调用服务
      ProductExecution pe = productService.queryProductionList(productCondition, pageIndex, pageSize);
      // 将结果返回给前台
      modelMap.put("productList", pe.getProductList());
      modelMap.put("count", pe.getCount());
      modelMap.put("success", true);
    } else {
      modelMap.put("success", false);
      modelMap.put("errMsg", "empty pageSize or pageIndex or shopId");
    }
    return modelMap;
  }

  /**
   * 
   * 
   * @Title: compactProductCondition4Search
   * 
   * @Description: 组装查询条件
   * 
   * @param shopId
   * @param productCategoryId
   * @param productName
   * 
   * @return: Product
   */
  private Product compactProductCondition4Search(Long shopId, long productCategoryId, String productName) {
    Product productCondition = new Product();
    Shop shop = new Shop();
    shop.setShopId(shopId);
    productCondition.setShop(shop);
    if (productCategoryId != -1L) {
      ProductCategory productCategory = new ProductCategory();
      productCategory.setProductCategoryId(productCategoryId);
      productCondition.setProductCategory(productCategory);
    }
    if (productName != null) {
      productCondition.setProductName(productName);
    }
    return productCondition;
  }

单元测试

启动tomcat的服务，第一次可以加入断点，使用debug的方式启动逐步调测该方法。

先获取shoplist,然后进入shop管理页面，使后端将该shop的信息写入到session中。因为这个方法的shop信息是从session中获取的。

最后访问

http://localhost:8080/o2o/shopadmin/getproductlist?pageIndex=1&&pageSize=8

根据数据库中的记录，合理设置pageIndex和pageSize，多次验证获取的数据是否符合预期。

{
    "success": true,
    "count": 7,
    "productList": [
        {
            "productId": 7,
            "productName": "offical_product1",
            "productDesc": "product offical desc1",
            "imgAddr": "\\upload\\item\\shopImage\\5\\2018070123313434331.png",
            "normalPrice": "1001",
            "promotionPrice": "801",
            "priority": 661,
            "createTime": 1530286468000,
            "lastEditTime": 1530502295000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 2,
            "productName": "modifyProduct",
            "productDesc": "modifyProduct desc",
            "imgAddr": "/mmm/ddd",
            "normalPrice": "350",
            "promotionPrice": "300",
            "priority": 66,
            "createTime": 1530308806000,
            "lastEditTime": 1530388921000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 3,
            "productName": "test_product",
            "productDesc": "product desc",
            "imgAddr": "/aaa/bbb",
            "normalPrice": "10",
            "promotionPrice": "8",
            "priority": 66,
            "createTime": 1529880336000,
            "lastEditTime": 1529880336000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 4,
            "productName": "test_product",
            "productDesc": "product desc",
            "imgAddr": "\\upload\\item\\shopImage\\5\\2018062515593428322.jpg",
            "normalPrice": "10",
            "promotionPrice": "8",
            "priority": 66,
            "createTime": 1529956696000,
            "lastEditTime": 1529956697000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 5,
            "productName": "test_product",
            "productDesc": "product desc",
            "imgAddr": "\\upload\\item\\shopImage\\5\\2018062516124013361.jpg",
            "normalPrice": "10",
            "promotionPrice": "8",
            "priority": 66,
            "createTime": 1529957560000,
            "lastEditTime": 1529957560000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 6,
            "productName": "test_product",
            "productDesc": "product desc",
            "imgAddr": "\\upload\\item\\shopImage\\5\\2018062516132272045.jpg",
            "normalPrice": "10",
            "promotionPrice": "8",
            "priority": 66,
            "createTime": 1529957602000,
            "lastEditTime": 1529957602000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        },
        {
            "productId": 8,
            "productName": "test_product",
            "productDesc": "product desc",
            "imgAddr": "/aaa/bbb",
            "normalPrice": "10",
            "promotionPrice": "8",
            "priority": 66,
            "createTime": 1530388919000,
            "lastEditTime": 1530388919000,
            "enableStatus": 1,
            "productImgList": null,
            "productCategory": null,
            "shop": null
        }
    ]
}

Github地址

代码地址： https://github.com/yangshangwei/o2o

上一篇：实战SSM_O2O商铺_33【商品】商品编辑之Service层的实现

下一篇：实战SSM_O2O商铺_34【商品】商品编辑之Controller层的实现

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯