概述
接着继续来完成商品列表的功能吧，需要支持分页的功能。
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中的记录，验证是否符合预期，这里单元测试通过。
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()); }
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 } ] }
