SpringBoot+Mybatis实现三级分类联动

SpringBoot+Mybatis实现三级分类联动⼀、后台管理系统表格分页形式的分类显⽰先来看实现的效果。这是所有商品的⼀级分类，选中⼀个⼀级分类后（如图中的“家电数码⼿机”）点击“下级分类管理”，即跳转⾄该分类的⼆级分类，如下图。再选择⼆级分类中的“家电”后，点击“下级分类管理”，即跳转⾄该⼆级分类下的三级分类，如下图。所有的分类都是记录在同⼀张表内，表内主要字段如下。属性名说明category_idcategory_level⾃增id，⽤于记录每个分类的编号表⽰该分类属于⼏级分类parent_id属性名表⽰该分类的上级分类的id，如果是⼀级分类，则为0说明category_namecategory_rank分类名称分类排序值，值越⾼越靠前显⽰另外在前后端传值的时候有三个关键的参数：categoryLevel，⽤来表格该分类的等级parentId，⽤来表格该分类的上级分类的id，⼀级分类则为0backParentId，⽤来表⽰三级分类的parentId所对应的⼆级分类的⼀级分类现在开始上代码。初试列表显⽰的是所有⼀级分类，因此这⾥三个参数分别为：categoryLevel=1，parentId=0，backParentId=0；url为：admin/categories?parentId=0&categoryLevel=1&backParentId=0⼆级列表和三级列表的参数由前端获取到后传给后端。NewBeeMallGoodsCategoryController.java@ResourceprivateNewBeeMallCategoryServicenewBeeMallCategoryService;/**列*表显⽰*/@RequestMapping(value="/categories/list",method=RequestMethod.GET)@ResponseBodypublicResultlist(@RequestParamMap<String,Object>params){if(StringUtils.isEmpty(params.get("page"))||StringUtil.isEmpty(params.get("limit"))){returnResultGenerator.genFailResult("参数异常");}PageQueryUtilpageQueryUtil=newPageQueryUtil(params);returnResultGenerator.genSuccessResult(newBeeMallCategoryService.getCategoriesPage(pageQueryUtil));}（categoryLevel=1,parentId=0封装在params中）NewBeeMallCategoryService.java/**管*理后台分页显⽰*@parampageQueryUtil*@return*/PageResultgetCategoriesPage(PageQueryUtilpageQueryUtil);NewBeeMallCategoryServiceImpl.java@AutowiredprivateGoodsCategoryMappergoodsCategoryMapper;@OverridepublicPageResultgetCategoriesPage(PageQueryUtilpageQueryUtil){<ListGoodsCategory>goodsCategories=goodsCategoryMapper.findGoodsCategoryList(pageQueryUtil);inttotal=goodsCategoryMapper.getTotalGoodsCategories(pageQueryUtil);PageResultpageResult=newPageResult(goodsCategories,total,pageQueryUtil.getLimit(),pageQueryUtil.getPage());returnpageResult;}GoodsCategoryMapper.java/**后*台获取分类列表*@parampageQueryUtil*@return*/List<GoodsCategory>findGoodsCategoryList(PageQueryUtilpageQueryUtil);/**后*台获取分类总数*@parampageQueryUtil*@return*/intgetTotalGoodsCategories(PageQueryUtilpageQueryUtil);GoodsCategoryMapper.xml<sqlid="Base_Column_List">category_id,category_level,parent_id,category_name,category_rank,is_deleted,create_time,create_user,update_time,update_user</sql><selectid="findGoodsCategoryList"parameterType="map"resultMap="BaseResultMap">select<includerefid="Base_Column_List"></include>fromtb_newbee_mall_goods_category<where><iftest="categoryLevel!=nullandcategoryLevel!=''">andcategory_level=#{categoryLevel}</if><iftest="parentId!=nullandparentId!=''">andparent_id=#{parentId}</if>andis_deleted=0</where>orderbycategory_rankdesc<iftest="start!=nullandlimit!=null">limit#{start},#{limit}</if></select><selectid="getTotalGoodsCategories"parameterType="Map"resultType="int">selectcount(*)fromtb_newbee_mall_goods_category<where><iftest="categoryLevel!=nullandcategoryLevel!=''">andcategory_level=#{categoryLevel}</if><iftest="parentId!=nullandparentId!=''">andparent_id=#{parentId}</if>andis_deleted=0</where></select>⼆、添加商品页⾯的下拉菜单式的三级联动实际效果如图所⽰。第⼀个下拉菜单默认显⽰排序值最⾼的⼀级分类。第⼆个下拉菜单显⽰的是该⼀级分类下的第⼀个⼆级分类。第三个下拉菜单显⽰的是该⼆级分类下的第⼀个三级分类。若没有内容则显⽰空⽩。这⾥有三种情况，分别是：（1）打开页⾯后的默认显⽰⼀打开这个页⾯默认显⽰的是第⼀个⼀级分类，和其所属的第⼀个⼆级分类以及该⼆级分类下的第⼀个三级分类，即：“家电数码⼿机”-->“家电”-->“⽣活电器”。NewBeeMallGoodsController.java@ResourceprivateNewBeeMallCategoryServicenewBeeMallCategoryService;@GetMapping("/goods/edit")publicStringedit(HttpServletRequestrequest){request.setAttribute("path","edit");//查询所有的⼀级分类<ListGoodsCategory>firstLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(0L),NewBeeMallCategoryLevelEnum.LEVEL_ONE.getLevel());if(!CollectionUtil.isEmpty(firstLevelCategories)){//查询⼀级分类列表中第⼀个实体的所有⼆级分类<GoListodsCategory>secondLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(firstLevelCategories.get(0).getCategoryId()),NewBeeMallCategoryLevelEnum.LEVEL_TWOif(!CollectionUtil.isEmpty(secondLevelCategories)){.getLevel());//查询⼆级分类列表中第⼀个实体的所有三级分类<GooListdsCategory>thirdLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(secondLevelCategories.get(0).getCategoryId()),NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel());.rsequesttAttribute("firstLevelCategories",firstLevelCategories);.rsequesttAttribute("secondLevelCategories",secondLevelCategories);.rsequesttAttribute("thirdLevelCategories",thirdLevelCategories);.rsequesttAttribute("path","goods-edit");return"admin/newbee_mall_goods_edit";}}return"error/error_5xx";}NewBeeMallCategoryService.java/**根*据parentId和level获取分类列表*@paramparentIds*@paramcategoryLevel*@return*/List<GoodsCategory>selectByLevelAndParentIdsAndNumber(List<Long>parentIds,intcategoryLevel);NewBeeMallCategoryServiceImpl.java@AutowiredprivateGoodsCategoryMappergoodsCategoryMapper;@OverridepublicList<GoodsCategory>selectByLevelAndParentIdsAndNumber(List<Long>parentIds,intcategoryLevel){returngoodsCategoryMapper.selectByLevelAndParentIdsAndNumber(parentIds,categoryLevel,0);//0代表查询所有}GoodsCategoryMapper.javaList<GoodsCategory>selectByLevelAndParentIdsAndNumber(@Param("parentIds")List<Long>parentIds,@Param("categoryLevel")intcategoryLevel,@Param("number")intnumber);GoodsCategoryMapper.xml<selectid="selectByLevelAndParentIdsAndNumber"resultMap="BaseResultMap">select<includerefid="Base_Column_List"/>fromtb_newbee_mall_goods_categorywhereparent_idin<foreachitem="parentId"collection="parentIds"open="("separator=","close=")">#{parentId,jdbcType=BIGINT}</foreach>andcategory_level=#{categoryLevel,jdbcType=TINYINT}andis_deleted=0orderbycategory_rankdesc<iftest="number>0">limit#{number}</if></select>（2）修改商品信息页，已知商品id这种情况下是⽤户在商品列表选择了相应的商品后，点击编辑按钮，希望修改这个商品的基本信息（如，分类、库存、售价等）,这个时候页⾯跳转到与前⾯的截图相同的页⾯，区别就是这个时候的三级菜单会⾃动显⽰当前该商品所在的三级分类，即数据回显。如下图所⽰，我选中了其中⼀个商品（如⼩⽶某型号⼿机），返回的页⾯是这样的。NewBeeMallGoodsController.java@ResourceprivateNewBeeMallGoodsServicenewBeeMallGoodsService;@GetMapping("/goods/edit/{goodsId}")publicStringedit(HttpServletRequestrequest,@PathVariable("goodsId")LonggoodsId){request.setAttribute("path","edit");NewBeeMallGoodsnewBeeMallGoods=newBeeMallGoodsService.getNewBeeMallGoodsById(goodsId);if(newBeeMallGoods==null){return"error/error_400";}if(newBeeMallGoods.getGoodsCategoryId()>0){if(newBeeMallGoods.getGoodsCategoryId()!=null||newBeeMallGoods.getGoodsCategoryId()>0){//有分类字段则查询相关分类数据返回给前端以供分类的三级联动显⽰GoodsCategorycurrentGoodsCategory=newBeeMallCategoryService.getGoodsCategoryById(newBeeMallGoods.getGoodsCategoryId());//商品表中存储的分类id字段为三级分类的id,不为三级分类则是错误数据if(currentGoodsCategory!=null&¤tGoodsCategory.getCategoryLevel()==NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel()){//查询所有的⼀级分类<GoodsListCategory>firstLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(0L),NewBeeMallCategoryLevelEnum.LEVEL_ONE.getLevel());//根据parentId查询当前parentId下所有的三级分类<GoodsListCategory>thirdLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(currentGoodsCategory.getParentId()),NewBeeMallCategoryLevelEnum//查询当前三级分类的⽗级⼆级分类.LEVEL_THREE.getLevel());GoodsCategorysecondCategory=newBeeMallCategoryService.getGoodsCategoryById(currentGoodsCategory.getParentId());if(secondCategory!=null){//根据parentId查询当前parentId下所有的⼆级分类<GoodsCaListtegory>secondLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(secondCategory.getParentId()),NewBeeMallCategoryLevelEnum.LEVEL_TWO.getLevel());//查询当前⼆级分类的⽗级⼀级分类GoodsCategoryfirstCat=newBeegoryMallCategoryService.getGoodsCategoryById(secondCategory.getParentId());if(firstCategory!=null){if(firstCategory!=null){//所有分类数据都得到之后放到request对象中供前端读取.setAttrequestribute("firstLevelCategories",firstLevelCategories);.setAttrequestribute("secondLevelCategories",secondLevelCategories);.setAttrequestribute("thirdLevelCategories",thirdLevelCategories);.setAttrequestribute("firstLevelCategoryId",firstCategory.getCategoryId());.setAttrequestribute("secondLevelCategoryId",secondCategory.getCategoryId());.setAttrequestribute("thirdLevelCategoryId",currentGoodsCategory.getCategoryId());}}}}}if(newBeeMallGoods.getGoodsCategoryId()==0){//查询所有的⼀级分类<GoListodsCategory>firstLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(0L),NewBeeMallCategoryLevelEnum.LEVEL_ONE.getLevel());if(!CollectionUtil.isEmpty(firstLevelCategories)){//查询⼀级分类列表中第⼀个实体的所有⼆级分类<GooListdsCategory>secondLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(firstLevelCategories.get(0).getCategoryId()),NewBeeMallCategoryLevelEnum.LEVEL_TWO.getLevel());if(!CollectionUtil.isEmpty(secondLevelCategories)){//查询⼆级分类列表中第⼀个实体的所有三级分类<GoodsListCategory>thirdLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(secondLevelCategories.get(0).getCategoryId()),NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel());.serequesttAttribute("firstLevelCategories",firstLevelCategories);.serequesttAttribute("secondLevelCategories",secondLevelCategories);.serequesttAttribute("thirdLevelCategories",thirdLevelCategories);}}}request.setAttribute("goods",newBeeMallGoods);request.setAttribute("path","goods-edit");return"admin/newbee_mall_goods_edit";}NewBeeMallGoodsService.java/**根*据id获取商品详情*@paramid*@return*/NewBeeMallGoodsgetNewBeeMallGoodsById(Longid);NewBeeMallGoodsServiceImpl.java@AutowiredprivateNewBeeMallGoodsMappergoodsMapper;@OverridepublicNewBeeMallGoodsgetNewBeeMallGoodsById(Longid){returngoodsMapper.selectByPrimaryKey(id);}NewBeeMallGoodsMapper.java/**根*据id获取商品*@paramgoodsId*@return*/NewBeeMallGoodsselectByPrimaryKey(LonggoodsId);NewBeeMallGoodsMapper.xml<sqlid="Base_Column_List">goods_id,goods_name,goods_intro,goods_category_id,goods_cover_img,goods_carousel,original_price,selling_price,stock_num,tag,goods_sell_status,create_user,create_time,update_user,update_time</sql><sqlid="Blob_Column_List">goods_detail_content</sql><selectid="selectByPrimaryKey"parameterType="java.lang.Long"resultMap="ResultMapWithBLOBs">select<includerefid="Base_Column_List"/>,<includerefid="Blob_Column_List"/>fromtb_newbee_mall_goods_infowheregoods_id=#{goodsId,jdbcType=BIGINT}</select>（3）⼿动选择更改分类当我们⼿动选择了⼀个⼀级分类后，⼆级分类的下拉菜单会⾃动修改为当前选择的那个⼀级分类下的第⼀个⼆级分类，三级分类的下拉菜单会⾃动修改为当前第⼀个⼆级分类下的第⼀个三级分类，如果没有相应的下属分类则不显⽰。当⾃⼰⼿动选择了⼀个⼆级分类后，三级分类的下拉菜单会⾃动修改为当前选择的⼆级分类下的第⼀个三级分类。NewBeeMallGoodsCategoryController.java@ResourceprivateNewBeeMallCategoryServicenewBeeMallCategoryService;/**添*加/修改商品信息页的列表显⽰*/@RequestMapping(value="/categories/listForSelect",method=RequestMethod.GET)@ResponseBodypublicResultlistForSelect(@RequestParam("categoryId")LongcategoryId){if(categoryId==null||categoryId<1){returnResultGenerator.genFailResult("缺少参数");}GoodsCategorycategory=newBeeMallCategoryService.getGoodsCategoryById(categoryId);//既不是⼀级分类也不是⼆级分类则不返回数据if(category==null||category.getCategoryLevel()==NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel()){returnResultGenerator.genFailResult("参数异常");}MapcategoryResult=newHashMap(2);if(category.getCategoryLevel()==NewBeeMallCategoryLevelEnum.LEVEL_ONE.getLevel()){//如果是⼀级分类则返回⼀级分类下的所有⼆级分类，以及⼆级分类列表中第⼀条数据下的所有三级分类//查询⼀级分类列表中第⼀个实体的所有⼆级分类<GoListodsCategory>secondLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(categoryId),NewBeeMallCategoryLevelEnum.LEVEL_TWO.getLevel());if(!CollectionUtil.isEmpty(secondLevelCategories)){//查询⼆级分类列表中第⼀个实体的所有三级分类<GooListdsCategory>thirdLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(secondLevelCategories.get(0).getCategoryId()),NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel());categoryResult.put("secondLevelCategories",secondLevelCategories);categoryResult.put("thirdLevelCategories",thirdLevelCategories);}}if(category.getCategoryLevel()==NewBeeMallCategoryLevelEnum.LEVEL_TWO//如果是⼆级分类则返回当前分类下的所有三级分类列表.getLevel()){<GoListodsCategory>thirdLevelCategories=newBeeMallCategoryService.selectByLevelAndParentIdsAndNumber(Collections.singletonList(categoryId),NewBeeMallCategoryLevelEnum.LEVEL_THREE.put("thirdLevelCategories",thirdLevelCategories);.getLevel());categoryResul}returnResultGenerator.genSuccessResult(categoryResult);}NewBeeMallCategoryService.java/**根*据id获取分类*@paramid*@return*/GoodsCategorygetGoodsCategoryById(Longid);/**根*据parentId和level获取分类列表*@paramparentIds*@paramcategoryLevel*@return*/List<GoodsCategory>selectByLevelAndParentIdsAndNumber(List<Long>parentIds,intcategoryLevel);NewBeeMallCategoryServiceImpl.java@AutowiredprivateGoodsCategoryMappergoodsCategoryMapper;@OverridepublicGoodsCategorygetGoodsCategoryById(Longid){returngoodsCategoryMapper.selectByPrimaryKey(id);}@OverridepublicList<GoodsCategory>selectByLevelAndParentIdsAndNumber(List<Long>parentIds,intcategoryLevel){returngoodsCategoryMapper.selectByLevelAndParentIdsAndNumber(parentIds,categoryLevel,0);//0代表查询所有}GoodsCategoryMapper.java/**根*据id查找分类*@paramcategoryId*@return*/GoodsCategoryselectByPrimaryKey(LongcategoryId);List<GoodsCategory>selectByLevelAndParentIdsAndNumber(@Param("parentIds")List<Long>parentIds,@Param("categoryLevel")intcategoryLevel,@Param("number")intnumber);GoodsCategoryMapper.xml<sqlid="Base_Column_List">category_id,category_level,parent_id,category_name,category_rank,is_deleted,create_time,create_user,update_time,update_user</sql><selectid="selectByPrimaryKey"parameterType="java.lang.Long"resultMap="BaseResultMap">select<includerefid="Base_Column_List"/>fromtb_newbee_mall_goods_categorywherecategory_id=#{categoryId,jdbcType=BIGINT}andis_deleted=0</select><selectid="selectByLevelAndParentIdsAndNumber"resultMap="BaseResultMap">select<includerefid="Base_Column_List"/>fromtb_newbee_mall_goods_categorywhereparent_idin<foreachitem="parentId"collection="parentIds"open="("separator=","close=")">#{parentId,jdbcType=BIGINT}</foreach>andcategory_level=#{categoryLevel,jdbcType=TINYINT}andis_deleted=0orderbycategory_rankdesc<iftest="number>0">limit#{number}</if></select>三、商城⾸页三级分类显⽰后台管理系统配置好了分类之后，就是⽤来在前台商城⾸页显⽰。⾸页如图所⽰。IndexController.java@ResourceprivateNewBeeMallCategoryServicenewBeeMallCategoryService;@GetMapping({"/index","/","/index.html"})publicStringindexPage(HttpServletRequestrequest){<ListNewBeeMallIndexCategoryVO>categories=newBeeMallCategoryService.getCategoriesForIndex();if(CollectionUtil.isEmpty(categories)){return"error/error_5xx";}request.setAttribute("categories",categories);//分类数据return"mall/index";}NewBeeMallCategoryService.java/**商*城⾸页返回分类数据*@return*/List<NewBeeMallIndexCategoryVO>getCategoriesForIndex();NewBeeMallCategoryServiceImpl.java@AutowiredprivateGoodsCategoryMappergoodsCategoryMapper;@OverridepublicList<NewBeeMallIndexCategoryVO>getCategoriesForIndex(){<ListNewBeeMallIndexCategoryVO>newBeeMallIndexCategoryVOS=newArrayList<>();//获取⼀级分类的固定数量的数据<ListGoodsCategory>firstLevelCategories=goodsCategoryMapper.selectByLevelAndParentIdsAndNumber(Collections.singletonList(0L),NewBeeMallCategoryLevelEnum.LEVEL_ONE.getLevel(),Constants.INDEX_CATEGORY_NUMBERif(!CollectionUtil.isEmpty(firstLevelCategories)){);<LoListng>firstLevelCategoryIds=firstLevelCategories.stream().map(GoodsCategory::getCategoryId).collect(Collectors.toList());//获取⼆级分类的数据<GoListodsCategory>secondLevelCategories=goodsCategoryMapper.selectByLevelAndParentIdsAndNumber(firstLevelCategoryIds,NewBeeMallCategoryLevelEnum.LEVEL_TWO.getLevel(),0);if(!CollectionUtil.isEmpty(secondLevelCategories)){<LonListg>secondLevelCategoryIds=secondLevelCategories.stream().map(GoodsCategory::getCategoryId).collect(Collectors.toList());//获取三级分类的数据<GooListdsCategory>thirdLevelCategories=goodsCategoryMapper.selectByLevelAndParentIdsAndNumber(secondLevelCategoryIds,NewBeeMallCategoryLevelEnum.LEVEL_THREE.getLevel(),0);if(!CollectionUtil.isEmpty(thirdLevelCategories)){//根据parentId将thirdLevelCategories分组<LongMap,List<GoodsCategory>>thirdLevelCategoryMap=thirdLevelCategories.stream().collect(groupingBy(GoodsCategory::getParentId));<SeconListdLevelCategoryVO>secondLevelCategoryVOS=newArrayList<>();//处理⼆级分类for(GoodsCategorysecondLevelCategory:secondLevelCategories){SecondLevelCategoryVOsecondLevelCategoryVO=newSecondLevelCategoryVO();.copBeanUtilyProperties(secondLevelCategory,secondLevelCategoryVO);//如果该⼆级分类下有数据则放⼊secondLevelCategoryVOS对象中if(thirdLevelCategoryMap.containsKey(secondLevelCategory.getCategoryId())){//根据⼆级分类的id取出thirdLevelCategoryMap分组中的三级分类list<GoodsCatListegory>tempGoodsCategories=thirdLevelCategoryMap.get(secondLevelCategory.getCategoryId());secondLevelCategoryVO.setThirdLevelCategoryVOS((BeanUtil.copyList(tempGoodsCategories,ThirdLevelCategoryVO.class)));secondLevelCategoryVOS.add(s