《锋迷商城》系列项目
链接: 《一》 项目搭建
链接: 《二》数据库的创建
链接: 《三》业务流程设计
链接: 《四》业务流程实现:用户管理
链接: 《五》逆向工程
链接: 《六》用户认证
九、首页-分类列表
9.1实现流程分析
- 方案一:一次性查询三级分类
- 优点 :只需要一次查询,根据一级分类显示二级分类时响应速度较快
- 缺点:数据查询效率较低,页面首次加载的速度也相对较慢
- 方案二:先只查询一级分类,用户点击一级分类时动态加载二级分类
- 优点: 数据库查询效率提高,页面首次加载速度提高
- 缺点:需要多次连接数据库
9.2 接口开发
9.2.1数据库和DAO层
-
数据模型
-
编写所需SQL
- 连接查询
SELECT c1.category_id 'category_id1', c1.category_name 'category_name1', c1.category_level 'category_level1', c1.parent_id 'parent_id1', c1.category_icon 'category_icon1', c1.category_slogan 'category_slogan1', c1.category_pic 'category_pic1', c1.category_bg_color 'category_bg_color1', c2.category_id 'category_id2', c2.category_name 'category_name2', c2.category_level 'category_level2', c2.parent_id 'parent_id2', c3.category_id 'category_id3', c3.category_name 'category_name3', c3.category_level 'category_level3', c3.parent_id 'parent_id3' FROM category c1 INNER JOIN category c2 on c2.parent_id=c1.category_id LEFT JOIN category c3 on c3.parent_id=c2.category_id WHERE c1.category_level =1
- 子查询
-- 根据父级分类的id 查询类别信息 select * from categor where parent_id=0
-
在
beans
子工程下的entity创建categoryVO类(用于封装查询的类别信息)//用于存放子分类 private List<CategoryVO> categories; public List<CategoryVO> getCategories() { return categories; } public void setCategories(List<CategoryVO> categories) { this.categories = categories; }
-
dao层接口
@Repository public interface CategoryMapper extends GeneralDAO<Category> { /** * 连接查询 查询全部的数据 * @return */ List<CategoryVO> selectAllCategory(); /** * 子查询 根据parentID进行查询 * @return */ List<CategoryVO> selectAllCategory2(int parentId); }
-
mapper.xml映射文件
<resultMap id="Category" type="com.sjtest.fmmall.entity.CategoryVO"> <id column="category_id1" jdbcType="INTEGER" property="categoryId"/> <result column="category_name1" jdbcType="VARCHAR" property="categoryName"/> <result column="category_level1" jdbcType="INTEGER" property="categoryLevel"/> <result column="parent_id1" jdbcType="INTEGER" property="parentId"/> <result column="category_icon1" jdbcType="VARCHAR" property="categoryIcon"/> <result column="category_slogan1" jdbcType="VARCHAR" property="categorySlogan"/> <result column="category_pic1" jdbcType="VARCHAR" property="categoryPic"/> <result column="category_bg_color1" jdbcType="VARCHAR" property="categoryBgColor"/> <collection property="categories" ofType="com.sjtest.fmmall.entity.CategoryVO"> <id column="category_id2" jdbcType="INTEGER" property="categoryId"/> <result column="category_name2" jdbcType="VARCHAR" property="categoryName"/> <result column="category_level2" jdbcType="INTEGER" property="categoryLevel"/> <result column="parent_id2" jdbcType="INTEGER" property="parentId"/> <collection property="categories" ofType="com.sjtest.fmmall.entity.CategoryVO"> <id column="category_id3" jdbcType="INTEGER" property="categoryId"/> <result column="category_name3" jdbcType="VARCHAR" property="categoryName"/> <result column="category_level3" jdbcType="INTEGER" property="categoryLevel"/> <result column="parent_id3" jdbcType="INTEGER" property="parentId"/> </collection> </collection> </resultMap> <select id="selectAllCategory" resultMap="Category"> SELECT c1.category_id 'category_id1', c1.category_name 'category_name1', c1.category_level 'category_level1', c1.parent_id 'parent_id1', c1.category_icon 'category_icon1', c1.category_slogan 'category_slogan1', c1.category_pic 'category_pic1', c1.category_bg_color 'category_bg_color1', c2.category_id 'category_id2', c2.category_name 'category_name2', c2.category_level 'category_level2', c2.parent_id 'parent_id2', c3.category_id 'category_id3', c3.category_name 'category_name3', c3.category_level 'category_level3', c3.parent_id 'parent_id3' FROM category c1 INNER JOIN category c2 on c2.parent_id=c1.category_id LEFT JOIN category c3 on c3.parent_id=c2.category_id WHERE c1.category_level =1 </select> <!---------------------------------------> <resultMap id="CategoryVOMap2" type="com.sjtest.fmmall.entity.CategoryVO"> <id column="category_id" jdbcType="INTEGER" property="categoryId"/> <result column="category_name" jdbcType="VARCHAR" property="categoryName"/> <result column="category_level" jdbcType="INTEGER" property="categoryLevel"/> <result column="parent_id" jdbcType="INTEGER" property="parentId"/> <result column="category_icon" jdbcType="VARCHAR" property="categoryIcon"/> <result column="category_slogan" jdbcType="VARCHAR" property="categorySlogan"/> <result column="category_pic" jdbcType="VARCHAR" property="categoryPic"/> <result column="category_bg_color" jdbcType="VARCHAR" property="categoryBgColor"/> <collection property="categories" column="category_id" select="com.sjtest.fmmall.dao.CategoryMapper.selectAllCategory2"/> </resultMap> <select id="selectAllCategory2" resultMap="CategoryVOMap2"> SELECT category_id, category_name, category_level, parent_id, category_icon, category_slogan, category_pic, category_bg_color FROM category where parent_id=#{parentId} </select>
9.2.2 业务层
CategoryService 接口
public interface CategoryService {
ResultVO listCategories();
}
------------------------
CategoryServiceImpl 实现类
@Service
public class CategoryServiceImpl implements CategoryService {
@Autowired
private CategoryMapper categoryMapper;
@Override
public ResultVO listCategories() {
List<CategoryVO> categoryVOS = categoryMapper.selectAllCategories();
ResultVO resultVO = new ResultVO(ResStatus.OK, "success", categoryVOS);
return resultVO;
}
}
9.2.3控制层
在IndexConller下添加方法
@Autowired
private CategoryService categoryService;
@GetMapping("/category-list")
@ApiOperation("商品分类查询接口")
public ResultVO listCategories(){
return categoryService.listCategories();
}