查询多级商品分类怎么依次显示到页面上？

Question

正在做一个商城项目，被商品分类的分级查询给难住了，sql语句我倒是写出来了，可是不知道怎么保存在Java对象里，我使用的是SpringBoot，mybatis，freemarker，mysql。

数据表结构：

CREATE TABLE goods_type
(
  typeId INT PRIMARY KEY AUTO_INCREMENT,
  typeName VARCHAR(255) NOT NULL ,
  typeDesc LONGTEXT NOT NULL,
  typeParent int REFERENCES goods_type(typeId) //上一级分类 ，最顶层为0
)CHARSET utf8

查询语句：

select l1.typeId as 一级菜单编号,l1.typeName as 一级菜单名称, l1.typeDesc as 1描述,
      l2.typeId as 二级菜单编号,l2.typeName as 二级菜单名称,l2.typeDesc as 2描述,
      l3.typeId as 三级菜单编号,l3.typeName as 三级菜单名称,l3.typeDesc as 3描述
      from goods_type l1
      inner JOIN goods_type l2 ON l1.typeId = l2.typeParent
      inner JOIN goods_type l3 on l3.typeParent = l2.typeId;

请问怎么保存在Java对象中，从而显示到页面。

Answer 1

mybatis 的话这个可以实现的, 我之前是写过一个类似的

表结构:

CREATE TABLE `admin_menu` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(64) NOT NULL COMMENT '菜单名',
  `parent_id` bigint(3) NOT NULL DEFAULT 0 COMMENT '父菜单的id, 如果是父菜单这个值为0',
  `url` varchar(500) NOT NULL DEFAULT '' COMMENT '菜单的链接',
  `icon` varchar(100) NOT NULL DEFAULT '' COMMENT '图标',
  `menu_index` bigint(3) NOT NULL DEFAULT 0 COMMENT '展示的顺序',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  KEY `uq_id` (`id`),
  KEY `uq_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='管理后台的菜单';

其中parentId 跟你的typeParent类似, 记录上一级的id

---

AdminMenu (Model):

public class AdminMenu implements Serializable {

    private static final long serialVersionUID = -6535315608269812875L;
    private int id;
    private String name;
    private int parentId;
    private String url;
    private String icon;
    private int menuIndex;
    private Date createTime;
    private Date updateTime;
    private List<AdminMenu> subMenus;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getParentId() {
        return parentId;
    }

    public void setParentId(int parentId) {
        this.parentId = parentId;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getIcon() {
        return icon;
    }

    public void setIcon(String icon) {
        this.icon = icon;
    }

    public int getMenuIndex() {
        return menuIndex;
    }

    public void setMenuIndex(int menuIndex) {
        this.menuIndex = menuIndex;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public List<AdminMenu> getSubMenus() {
        return subMenus;
    }

    public void setSubMenus(List<AdminMenu> subMenus) {
        this.subMenus = subMenus;
    }

    @Override
    public String toString() {
        return JsonUtil.toJson(this);
    }
}

Model的属性跟表结构一一对应, 最下面多了一个subMenu, 里面就是AdminMenu

---

下面是admin_menu.xml中的内容

查询SQL:

<select id="selectAllMenus" resultMap="adminMenuResult">
    SELECT
        id, name, parent_id, url, icon, menu_index, create_time, update_time
    FROM
      admin_menu
    WHERE parent_id=0
    ORDER BY menu_index
</select>

这里返回的就是adminMenuResult结果集:

<resultMap id="adminMenuResult" type="biz.menzil.admin.core.model.AdminMenu">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="parent_id" property="parentId"/>
    <result column="url" property="url"/>
    <result column="icon" property="icon"/>
    <result column="menu_index" property="menuIndex"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <association property="subMenus" column="id" select="selectSubMenus"/>
</resultMap>

其中这一行是最重要的

 <association property="subMenus" column="id" select="selectSubMenus"/>

这里用selectSubMenus来进行了另一个查询, 查询的参数为id, 把查询出来的结果放在Model中的subMenus属性中.

selectSubMenus查询SQL:

<select id="selectSubMenus" parameterType="long" resultMap="adminSubMenuResult">
    select
      id, name, parent_id, url, icon, menu_index, create_time, update_time
    from admin_menu
    where parent_id = #{id}
    order by menu_index
</select>

这里就是用第一层的id来查询有没有子菜单. 这里的#{id}就是上面那个结果集的column参数.
因为我只有两层菜单, 所以这里用了一个新的结果集,跟上面的区别就是没有subMenus字段.

adminSubMenuResult:

<resultMap id="adminSubMenuResult" type="biz.menzil.admin.core.model.AdminMenu">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="parent_id" property="parentId"/>
    <result column="url" property="url"/>
    <result column="icon" property="icon"/>
    <result column="menu_index" property="menuIndex"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
</resultMap>

如果你有三,四级的话你可以一个结果集. (第一层查询的时候用id去查询第二层, 第二层查询的时候用第二层的id去查询第三层...)

---

下面我贴一下整个的admin_menu.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="biz.menzil.admin.core.dao.AdminMenuDao">

    <resultMap id="adminMenuResult" type="biz.menzil.admin.core.model.AdminMenu">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="parent_id" property="parentId"/>
        <result column="url" property="url"/>
        <result column="icon" property="icon"/>
        <result column="menu_index" property="menuIndex"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <association property="subMenus" column="id" select="selectSubMenus"/>
    </resultMap>

    <resultMap id="adminSubMenuResult" type="biz.menzil.admin.core.model.AdminMenu">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="parent_id" property="parentId"/>
        <result column="url" property="url"/>
        <result column="icon" property="icon"/>
        <result column="menu_index" property="menuIndex"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <insert id="insertAdminMenu">
        INSERT INTO admin_menu(name, parent_id, url, icon, menu_index, create_time)
        VALUES (
        #{menu.name},
        #{menu.parentId},
        #{menu.url},
        #{menu.icon},
        #{menu.menuIndex},
        NOW()
        )
    </insert>

    <select id="selectById" resultMap="adminMenuResult">
        SELECT
            id, name, parent_id, url, icon, menu_index, create_time, update_time
        FROM
          admin_menu
        WHERE id = #{id}
    </select>

    <select id="selectAllMenus" resultMap="adminMenuResult">
        SELECT
            id, name, parent_id, url, icon, menu_index, create_time, update_time
        FROM
          admin_menu
        WHERE parent_id=0
        ORDER BY menu_index
    </select>

    <select id="selectSubMenus" parameterType="long" resultMap="adminSubMenuResult">
        select
          id, name, parent_id, url, icon, menu_index, create_time, update_time
        from admin_menu
        where parent_id = #{id}
        order by menu_index
    </select>

    <delete id="deleteAdminMenu">
        DELETE FROM
        admin_menu
        WHERE id=#{id}
    </delete>

    <update id="updateAdminMenu" >
        UPDATE admin_menu
        <set>
            <if test="menu.name != null and menu.name != ''">
                name=#{menu.name},
            </if>
            <if test="menu.parentId >= 0">
                parent_id=#{menu.parentId},
            </if>
            <if test="menu.url != null and menu.url != ''">
                url=#{menu.url},
            </if>
            <if test="menu.icon != null and menu.icon != ''">
                icon=#{menu.icon},
            </if>
            <if test="menu.menuIndex > 0">
                menu_index=#{menu.menuIndex},
            </if>
        </set>
        WHERE id=#{menu.id}
    </update>

</mapper>

Answer 2

mybatis的sql处理方式前面已经有答案了，不过个人不是很喜欢用复杂的sql来组装这种对象，sql就要尽量的简洁，只做数据的查询，像这种对象的处理封装还是交给程序控制的好。
JDK8以前，我们做这种树形结构对象的封装一般都是递归处理，之后有了流处理，代码就可以更简洁了，随便写了个例子，无限层级的树形菜单，希望能帮到题主：

@Test
public void test05() {
    //模拟创建数据
    List<GoodsType> list = Arrays.asList(
            new GoodsType(0, "typeName0", null),
            new GoodsType(1, "typeName1", 0),
            new GoodsType(2, "typeName2", 1),
            new GoodsType(3, "typeName3", 2),
            new GoodsType(4, "typeName4", 3),
            new GoodsType(5, "typeName5", 4)
    );

    //根据父节点id分组
    Map<Integer, List<GoodsType>> map = list.stream()
            .filter(o -> Objects.nonNull(o.getTypeParent()))
            .collect(Collectors.groupingBy(GoodsType::getTypeParent));
    //循环处理子节点 构建树状结构
    list.forEach(goodsType -> {
        if (map.containsKey(goodsType.getTypeId())) {
            goodsType.setSubGoods(map.get(goodsType.getTypeId()));
        }
    });

    //获取指定节点的对象
    GoodsType result = list.stream().filter(goodsType -> goodsType.getTypeId() == 0).findFirst().orElse(null);
    System.out.println(JSON.toJSONString(result, true));
}

树形对象 只是原对象的基础上加了子节点list

@Data
@NoArgsConstructor
@AllArgsConstructor
public class GoodsType {
    private Integer typeId;
    private String typeName;
    private String typeDesc;
    private Integer typeParent;
    private List<GoodsType> subGoods;

    public GoodsType(Integer typeId, String typeName, Integer typeParent) {
        this.typeId = typeId;
        this.typeName = typeName;
        this.typeParent = typeParent;
    }
}

控制台打印：

{
    "subGoods":[
        {
            "subGoods":[
                {
                    "subGoods":[
                        {
                            "subGoods":[
                                {
                                    "subGoods":[
                                        {
                                            "typeId":5,
                                            "typeName":"typeName5",
                                            "typeParent":4
                                        }
                                    ],
                                    "typeId":4,
                                    "typeName":"typeName4",
                                    "typeParent":3
                                }
                            ],
                            "typeId":3,
                            "typeName":"typeName3",
                            "typeParent":2
                        }
                    ],
                    "typeId":2,
                    "typeName":"typeName2",
                    "typeParent":1
                }
            ],
            "typeId":1,
            "typeName":"typeName1",
            "typeParent":0
        }
    ],
    "typeId":0,
    "typeName":"typeName0"
}

Answer 3

你说的应该是mybatis数据查询出来 如何持久化的问题
根据自己编写的sql 写出对应的resultMap
建议写一个DTO来保存对应你查询的数据