查詢多級商品分類怎么依次顯示到頁面上？

正在做一個商城項目，被商品分類的分級查詢給難住了，sql語句我倒是寫出來了，可是不知道怎么保存在Java對象里，我使用的是SpringBoot，mybatis，freemarker，mysql。

數(shù)據(jù)表結(jié)構(gòu)：

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對象中，從而顯示到頁面。

回答

編輯回答

瞄小懶

mybatis 的話這個可以實現(xiàn)的, 我之前是寫過一個類似的

表結(jié)構(gòu):

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 '創(chuàng)建時間',
  `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的屬性跟表結(jié)構(gòu)一一對應(yīng), 最下面多了一個subMenu, 里面就是AdminMenu

下面是admin_menu.xml中的內(nèi)容

查詢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結(jié)果集:

<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來進行了另一個查詢, 查詢的參數(shù)為id, 把查詢出來的結(jié)果放在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}就是上面那個結(jié)果集的column參數(shù).
因為我只有兩層菜單, 所以這里用了一個新的結(jié)果集,跟上面的區(qū)別就是沒有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>

如果你有三,四級的話你可以一個結(jié)果集. (第一層查詢的時候用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>

2017年8月21日 12:52

編輯回答

近義詞

mybatis的sql處理方式前面已經(jīng)有答案了，不過個人不是很喜歡用復雜的sql來組裝這種對象，sql就要盡量的簡潔，只做數(shù)據(jù)的查詢，像這種對象的處理封裝還是交給程序控制的好。
JDK8以前，我們做這種樹形結(jié)構(gòu)對象的封裝一般都是遞歸處理，之后有了流處理，代碼就可以更簡潔了，隨便寫了個例子，無限層級的樹形菜單，希望能幫到題主：

@Test
public void test05() {
    //模擬創(chuàng)建數(shù)據(jù)
    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)
    );

    //根據(jù)父節(jié)點id分組
    Map<Integer, List<GoodsType>> map = list.stream()
            .filter(o -> Objects.nonNull(o.getTypeParent()))
            .collect(Collectors.groupingBy(GoodsType::getTypeParent));
    //循環(huán)處理子節(jié)點 構(gòu)建樹狀結(jié)構(gòu)
    list.forEach(goodsType -> {
        if (map.containsKey(goodsType.getTypeId())) {
            goodsType.setSubGoods(map.get(goodsType.getTypeId()));
        }
    });

    //獲取指定節(jié)點的對象
    GoodsType result = list.stream().filter(goodsType -> goodsType.getTypeId() == 0).findFirst().orElse(null);
    System.out.println(JSON.toJSONString(result, true));
}

樹形對象只是原對象的基礎(chǔ)上加了子節(jié)點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;
    }
}

控制臺打?。?/p>

{
    "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"
}

2018年1月12日 13:08

編輯回答