正在做一個商城項(xiàng)目,被商品分類的分級查詢給難住了,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 的話這個可以實(shí)現(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 '圖標(biāo)',
`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來進(jìn)行了另一個查詢, 查詢的參數(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ù).
因?yàn)槲抑挥袃蓪硬藛? 所以這里用了一個新的結(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>
mybatis的sql處理方式前面已經(jīng)有答案了,不過個人不是很喜歡用復(fù)雜的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é)點(diǎn)id分組
Map<Integer, List<GoodsType>> map = list.stream()
.filter(o -> Objects.nonNull(o.getTypeParent()))
.collect(Collectors.groupingBy(GoodsType::getTypeParent));
//循環(huán)處理子節(jié)點(diǎn) 構(gòu)建樹狀結(jié)構(gòu)
list.forEach(goodsType -> {
if (map.containsKey(goodsType.getTypeId())) {
goodsType.setSubGoods(map.get(goodsType.getTypeId()));
}
});
//獲取指定節(jié)點(diǎn)的對象
GoodsType result = list.stream().filter(goodsType -> goodsType.getTypeId() == 0).findFirst().orElse(null);
System.out.println(JSON.toJSONString(result, true));
}
樹形對象 只是原對象的基礎(chǔ)上加了子節(jié)點(diǎn)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"
}
北大青鳥APTECH成立于1999年。依托北京大學(xué)優(yōu)質(zhì)雄厚的教育資源和背景,秉承“教育改變生活”的發(fā)展理念,致力于培養(yǎng)中國IT技能型緊缺人才,是大數(shù)據(jù)專業(yè)的國家
北大青鳥中博軟件學(xué)院創(chuàng)立于2003年,作為華東區(qū)著名互聯(lián)網(wǎng)學(xué)院和江蘇省首批服務(wù)外包人才培訓(xùn)基地,中博成功培育了近30000名軟件工程師走向高薪崗位,合作企業(yè)超4
中公教育集團(tuán)創(chuàng)建于1999年,經(jīng)過二十年潛心發(fā)展,已由一家北大畢業(yè)生自主創(chuàng)業(yè)的信息技術(shù)與教育服務(wù)機(jī)構(gòu),發(fā)展為教育服務(wù)業(yè)的綜合性企業(yè)集團(tuán),成為集合面授教學(xué)培訓(xùn)、網(wǎng)
達(dá)內(nèi)教育集團(tuán)成立于2002年,是一家由留學(xué)海歸創(chuàng)辦的高端職業(yè)教育培訓(xùn)機(jī)構(gòu),是中國一站式人才培養(yǎng)平臺、一站式人才輸送平臺。2014年4月3日在美國成功上市,融資1
曾工作于聯(lián)想擔(dān)任系統(tǒng)開發(fā)工程師,曾在博彥科技股份有限公司擔(dān)任項(xiàng)目經(jīng)理從事移動互聯(lián)網(wǎng)管理及研發(fā)工作,曾創(chuàng)辦藍(lán)懿科技有限責(zé)任公司從事總經(jīng)理職務(wù)負(fù)責(zé)iOS教學(xué)及管理工作。
浪潮集團(tuán)項(xiàng)目經(jīng)理。精通Java與.NET 技術(shù), 熟練的跨平臺面向?qū)ο箝_發(fā)經(jīng)驗(yàn),技術(shù)功底深厚。 授課風(fēng)格 授課風(fēng)格清新自然、條理清晰、主次分明、重點(diǎn)難點(diǎn)突出、引人入勝。
精通HTML5和CSS3;Javascript及主流js庫,具有快速界面開發(fā)的能力,對瀏覽器兼容性、前端性能優(yōu)化等有深入理解。精通網(wǎng)頁制作和網(wǎng)頁游戲開發(fā)。
具有10 年的Java 企業(yè)應(yīng)用開發(fā)經(jīng)驗(yàn)。曾經(jīng)歷任德國Software AG 技術(shù)顧問,美國Dachieve 系統(tǒng)架構(gòu)師,美國AngelEngineers Inc. 系統(tǒng)架構(gòu)師。