<mapper namespace="com.qfedu.fmmall.dao.CategoryMapper">

namespace:对应此xml的接口全路径

多表联查

<resultMap id="categoryVOMap" type="com.qfedu.fmmall.entity.CategoryVO">
        <id column="category_id1" jdbcType="INTEGER" property="categoryId"/>
        <result column="category_name1" jdbcType="VARCHAR" property="categoryName"/>
         <collection property="categories" ofType="com.qfedu.fmmall.entity.CategoryVO">
            <id column="category_id2" jdbcType="INTEGER" property="categoryId"/>
            <result column="category_name2" jdbcType="VARCHAR" property="categoryName"/>
         </collection>
</resultMap>

id:定义此resultMap 名 　　 type:对应实体类bean的全路径
id:数据库表主键 　　  jdbcType:类型 　　  property :实体类bean的属性名
collection:多表联查的一对多集合 　  property:一对多集合的属性名　 ofType:实体类bean的属性名

多表子查询

 <resultMap id="categoryVOMap2" type="com.qfedu.fmmall.entity.CategoryVO">
        <id column="category_id" jdbcType="INTEGER" property="categoryId"/>
        <result column="category_name" jdbcType="VARCHAR" property="categoryName"/>
        <collection property="categories" column="category_id"
                    select="com.qfedu.fmmall.dao.CategoryMapper.selectAllCategories2"/>
                    (又调用子查询反复查 查干净关系 用父表中的子类id递归查子类集合)
 </resultMap>
  <!-- 根据父级分类的id查询子级分类  -->
    <select id="selectAllCategories2" 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>

collection:多表子查询的一对多集合 　  property:一对多集合的属性名　 
column:对应子查询的输入条件属性category_id=> #{parentId}
select:子查询的id

实例Mappper.Xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qfedu.fmmall.dao.CategoryMapper">
	<!--多表联查-->
    <resultMap id="categoryVOMap" type="com.qfedu.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.qfedu.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.qfedu.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="selectAllCategories" resultMap="categoryVOMap">
        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.qfedu.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.qfedu.fmmall.dao.CategoryMapper.selectAllCategories2"/>
    </resultMap>

    <!-- 根据父级分类的id查询子级分类  -->
    <select id="selectAllCategories2" 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>
</mapper>

实体类

package com.qfedu.fmmall.entity;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class CategoryVO {

    private Integer categoryId;
    private String categoryName;
    private Integer categoryLevel;
    private Integer parentId;
    private String categoryIcon;
    private String categorySlogan;
    private String categoryPic;
    private String categoryBgColor;
    //实现首页的类别显示
    private List<CategoryVO> categories;
    //实现首页分类商品推荐
    private List<ProductVO> products;

}