web开发遇到的问题与解决思路

最近开发一个船舶项目，遇到的问题

[问题][6]：一个船舶上物料有很多分类,分类下面还存在分类,分类下面还存在分类，有一些分类下面只存在2级关系分类，有的是3级，有的是4级,如果是最终的类别还要带具体物料的名字以及基本信息：

---

[解决思路][6]：

1.创建表结构

以下为关键字段

字段名字	类型	备注
storesId	bigint(20)	物料ID，主键
parentId	bigint(20)	父类id
name	varchar(64)	物料名称
storesType	varchar(64)	物料or类别

---

2.由于主键唯一性,所以parentId放上一类别的主键id，依次循环放置parentId，然后循环查询出来就行里,这样依赖，看似解决了，问题出在查询这里，因为存在2级3级4级等不同的依赖关系，查询要查询多少次呢？查询2次不行，4次也不行。

3.然后只能用递归查询来解决问题了，[代码如下][6]

4.查询以json格式传回前端页面

{
  "code": "200",
  "message": "OK",
  "data": [
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463977621000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463977786000,
      "storesId": 10067,
      "storesType": "CATEGORY",
      "code": "WL0002",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": [
        {
          "displayOrder": 4,
          "status": "NORMAL",
          "createBy": 1,
          "createTime": 1463987635000,
          "updateBy": 0,
          "updateTime": null,
          "version": 0,
          "lastUpdate": 1463987727000,
          "storesId": 10070,
          "storesType": "CATEGORY",
          "code": "SS0001",
          "name": "物料2号",
          "specification": "12×1",
          "description": "没有描述",
          "unit": "T",
          "parentId": 10067,
          "children": [
            {
              "displayOrder": 4,
              "status": "NORMAL",
              "createBy": 1,
              "createTime": 1463991922000,
              "updateBy": 0,
              "updateTime": null,
              "version": 0,
              "lastUpdate": 1463991922000,
              "storesId": 10078,
              "storesType": "CATEGORY",
              "code": "SS0011",
              "name": "物料2号",
              "specification": "12×1",
              "description": "没有描述",
              "unit": "T",
              "parentId": 10070,
              "children": [
                {
                  "displayOrder": 4,
                  "status": "NORMAL",
                  "createBy": 1,
                  "createTime": 1463991946000,
                  "updateBy": 0,
                  "updateTime": null,
                  "version": 0,
                  "lastUpdate": 1463991946000,
                  "storesId": 10079,
                  "storesType": "CATEGORY",
                  "code": "SS0012",
                  "name": "物料2号",
                  "specification": "12×1",
                  "description": "没有描述",
                  "unit": "T",
                  "parentId": 10078,
                  "children": null }
              ]
            }
          ]
        },
        {
          "displayOrder": 4,
          "status": "NORMAL",
          "createBy": 1,
          "createTime": 1463987643000,
          "updateBy": 0,
          "updateTime": null,
          "version": 0,
          "lastUpdate": 1463987732000,
          "storesId": 10071,
          "storesType": "CATEGORY",
          "code": "SS0002",
          "name": "物料2号",
          "specification": "12×1",
          "description": "没有描述",
          "unit": "T",
          "parentId": 10067,
          "children": null
        }
      ]
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463977646000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463977787000,
      "storesId": 10068,
      "storesType": "CATEGORY",
      "code": "WL0003",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": [
        {
          "displayOrder": 4,
          "status": "NORMAL",
          "createBy": 1,
          "createTime": 1463991817000,
          "updateBy": 0,
          "updateTime": null,
          "version": 0,
          "lastUpdate": 1463991816000,
          "storesId": 10076,
          "storesType": "CATEGORY",
          "code": "SS0009",
          "name": "物料2号",
          "specification": "12×1",
          "description": "没有描述",
          "unit": "T",
          "parentId": 10068,
          "children": null
        },
        {
          "displayOrder": 4,
          "status": "NORMAL",
          "createBy": 1,
          "createTime": 1463991865000,
          "updateBy": 0,
          "updateTime": null,
          "version": 0,
          "lastUpdate": 1463991864000,
          "storesId": 10077,
          "storesType": "CATEGORY",
          "code": "SS0010",
          "name": "物料2号",
          "specification": "12×1",
          "description": "没有描述",
          "unit": "T",
          "parentId": 10068,
          "children": null
        }
      ]
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463977660000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463977790000,
      "storesId": 10069,
      "storesType": "CATEGORY",
      "code": "WL0004",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": null
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463991048000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463991047000,
      "storesId": 10072,
      "storesType": "CATEGORY",
      "code": "SS0003",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": null
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463991240000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463991240000,
      "storesId": 10073,
      "storesType": "CATEGORY",
      "code": "SS0004",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": null
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463991611000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463991611000,
      "storesId": 10074,
      "storesType": "CATEGORY",
      "code": "SS0005",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": null
    },
    {
      "displayOrder": 4,
      "status": "NORMAL",
      "createBy": 1,
      "createTime": 1463991811000,
      "updateBy": 0,
      "updateTime": null,
      "version": 0,
      "lastUpdate": 1463991815000,
      "storesId": 10075,
      "storesType": "CATEGORY",
      "code": "SS0008",
      "name": "物料2号",
      "specification": "12×1",
      "description": "没有描述",
      "unit": "T",
      "parentId": 0,
      "children": null
    }
  ]
}

5.如果有更好的解决思路,可以留言交流。