mysql根据一级查询所有商品_查询多级商品分类怎么依次显示到页面上？

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 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 getSubMenus() {

return subMenus;

}

public void setSubMenus(List subMenus) {

this.subMenus = subMenus;

}

@Override

public String toString() {

return JsonUtil.toJson(this);

}

}

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

下面是admin_menu.xml中的内容

查询SQL:

SELECT

id, name, parent_id, url, icon, menu_index, create_time, update_time

FROM

admin_menu

WHERE parent_id=0

ORDER BY menu_index

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

其中这一行是最重要的

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

selectSubMenus查询SQL:

select

id, name, parent_id, url, icon, menu_index, create_time, update_time

from admin_menu

where parent_id = #{id}

order by menu_index

这里就是用第一层的id来查询有没有子菜单. 这里的#{id}就是上面那个结果集的column参数.

因为我只有两层菜单, 所以这里用了一个新的结果集,跟上面的区别就是没有subMenus字段.

adminSubMenuResult:

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

下面我贴一下整个的admin_menu.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

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()

)

SELECT

id, name, parent_id, url, icon, menu_index, create_time, update_time

FROM

admin_menu

WHERE id = #{id}

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

id, name, parent_id, url, icon, menu_index, create_time, update_time

from admin_menu

where parent_id = #{id}

order by menu_index

DELETE FROM

admin_menu

WHERE id=#{id}

UPDATE admin_menu

name=#{menu.name},

parent_id=#{menu.parentId},

url=#{menu.url},

icon=#{menu.icon},

menu_index=#{menu.menuIndex},

WHERE id=#{menu.id}