Java中多层级一对多关系的处理

问题

有一个业务的数据库结构，包含很多一对多关系，大致类似下图的result属性的结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
{
	"errorcode": 0,
	"errormsg": "",
	"result": [{
		"indexes": [{
			"domain_id": [1],
			"overlay_id": [],
			"industry_code": [0],
			"orig_name": "12个月动量",
			"source": 1,
			"index_id": 60371,
			"explanation": "",
			"period_id": [1]
		}, {
			"domain_id": [1],
			"overlay_id": [],
			"industry_code": [0],
			"orig_name": "13个月继续率",
			"source": 1,
			"index_id": 63923,
			"explanation": "",
			"period_id": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
		}],
		"group_id": 78,
		"industry_code": [0, 881155],
		"display_name": "行业test",
		"explanation": null
	}, {
		"indexes": [{
			"domain_id": [1],
			"overlay_id": [62595, 67061, 66617, 64237, 63613],
			"industry_code": [0],
			"orig_name": "偿债能力评分",
			"source": 1,
			"index_id": 64187,
			"explanation": "",
			"period_id": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
		}],
		"group_id": 98,
		"industry_code": [0],
		"display_name": "成长能力评分",
		"explanation": null
	}]
}

这在业务处理的时候，特别是给前端输出json数据的时候，比较麻烦，会耗费很多时间精力；且每次联表查询这些数据，性能也比较低。

解决方案

1、将常用的、变更频率低的数据，提前查询出来，组装成一个数据结构，供其他业务调用(类似数据库视图的概念)

2、组装结果时，采用自下而上的方式组装，将每个层级的一对多关系单独用一个HashMap保存起来，然后再拼装不同层级的关联关系

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
@Service
@Scope(value= WebApplicationContext.SCOPE_REQUEST, proxyMode= ScopedProxyMode.TARGET_CLASS)
public class GroupCacheServiceImpl extends MySQLCacheServiceImpl{
    @Autowired
    GroupMapper groupMapper;
	
	 /**
     * 给指标组添加属性
     * @param ids
     * @return
     */
    @Override
    public ArrayList addAttributesForIds(List<Integer> ids)
    {
        if (null == data) {
            try {
                data = initCache();
            } catch (Exception exp) {
                exp.printStackTrace();
            }
        }
        ArrayList dataList = new ArrayList();
        for (int id : ids) {
            if (null != data.get(id)) {
                dataList.add(data.get(id));
            }
        }
        return dataList;
    }

    /**
     * 考虑到这里的数据，仅用于输出http接口，没必要封装为具体对象了，因此直接处理为HashMap和ArrayList即可
     *
     * 这里的处理逻辑应该优化下，不要直接从最顶级开始往下拼装；可以分关联关系进行拼装。
     * 比如overlay_id, domain_id, index_industry_code, period_id这些属性只和index关联，那么可以先组装一个index和这些属性的对应HashMap
     *
     *
     * @return
     */
    @Override
    protected HashMap<Integer, Object> initCache() {
        List<HashMap> groupHashMaps = groupMapper.selectAllInvalidGroups();

        HashMap<Integer, HashMap<String, HashSet<Integer>>> indexRelationship = makeIndexRelationship(groupHashMaps);

        HashMap<Integer, HashSet<Integer>> groupRelationship = makeGroup2IndustryRelationship(groupHashMaps);
        HashMap<Integer, Object> groups = new HashMap<>();
        for (HashMap groupData : groupHashMaps) {
            int groupId = (int)groupData.get("group_id");
            // 初始化group
            if (null == groups.get(groupId)) {
                HashMap<String, Object> group = new HashMap<>();
                group.put("group_id", groupId);
                group.put("display_name", groupData.get("display_name"));
                group.put("explanation", groupData.get("group_explanation"));
                group.put("group_id", groupId);
                // 一对多关系
                group.put("industry_code", groupRelationship.get(groupId));
                group.put("indexes", new ArrayList());
                groups.put(groupId, group);
            }

            HashMap<String, Object> group1 = (HashMap<String, Object>)groups.get(groupId);
            ArrayList indexes = (ArrayList) group1.get("indexes");
            int indexId = (int) groupData.get("index_id");
            HashMap<String, Object> index = getIndex(indexes, indexId);
            // 这个指标之前没处理过，列表中还没有该指标
            if (null == index) {
                index = new HashMap<String, Object>();
                index.put("index_id", indexId);
                index.put("orig_name", groupData.get("orig_name"));
                index.put("source", groupData.get("source"));
                index.put("industry_code", groupData.get("index_industry_code"));
                index.put("explanation", groupData.get("index_explanation"));
                // 一对多关系
                index.put("overlay_id", indexRelationship.get(indexId).get("overlay_id"));
                index.put("domain_id", indexRelationship.get(indexId).get("domain_id"));
                index.put("industry_code", indexRelationship.get(indexId).get("industry_code"));
                index.put("period_id", indexRelationship.get(indexId).get("period_id"));
                indexes.add(index);
            }
        }


        return groups;
    }

	/**
     * 生成指标与其一对多属性的关联关系
     * 数据结构：
     * {
     *     1 : {
     *         "overlay_id" : [1, 2, 3],
     *         "period_id" : [4, 5, 6]
     *     }
     * }
     * @param groups
     * @return
     */
    protected HashMap<Integer, HashMap<String, HashSet<Integer>>> makeIndexRelationship(List<HashMap> groups)
    {
        HashMap<Integer, HashMap<String, HashSet<Integer>>> indexes = new HashMap<>();
        for (HashMap group : groups) {
            int indexId = (int)group.get("index_id");
            if (null == indexes.get(indexId)) {
                HashMap<String, HashSet<Integer>> indexMap = new HashMap<>();
                indexMap.put("overlay_id", new HashSet<Integer>());
                indexMap.put("domain_id", new HashSet<Integer>());
                indexMap.put("industry_code", new HashSet<Integer>());
                indexMap.put("period_id", new HashSet<Integer>());
                indexes.put(indexId, indexMap);
            }

            HashMap<String, HashSet<Integer>> indexMap1 = indexes.get(indexId);

            if (group.get("overlay_id") != null) {
                indexMap1.get("overlay_id").add((int) group.get("overlay_id"));
            }
            if (group.get("domain_id") != null) {
                indexMap1.get("domain_id").add((int) group.get("domain_id"));
            }
            if (group.get("index_industry_code") != null) {
                indexMap1.get("industry_code").add(Integer.parseInt((String) group.get("index_industry_code")));
            }
            if (group.get("period_id") != null) {
                indexMap1.get("period_id").add((int) group.get("period_id"));
            }
        }

        return indexes;
    }
	
    /**
     * 判断list中是否已存在某个指标
     * @param indexes
     * @param indexId
     * @return
     */
    protected HashMap<String, Object> getIndex(ArrayList indexes, int indexId)
    {
        for (Object index : indexes) {
            int id = (int) ((HashMap<String, Object>) index).get("index_id");
            if (indexId == id) {
                return (HashMap<String, Object>)index;
            }
        }
        return null;
    }

    /**
     * 生成指标组和所属行业的关联关系
     * @param groups
     * @return
     */
    protected HashMap<Integer, HashSet<Integer>> makeGroup2IndustryRelationship(List<HashMap> groups)
    {
        HashMap<Integer, HashSet<Integer>> groupRelations = new HashMap<>();
        for (HashMap group : groups) {
            int groupId = (int)group.get("group_id");
            if (null == groupRelations.get(groupId)) {
                groupRelations.put(groupId, new HashSet<Integer>());
            }

            HashSet<Integer> industrySet = groupRelations.get(groupId);
            if (group.get("group_industry_code") != null) {
                industrySet.add(Integer.parseInt((String)group.get("group_industry_code")));
            }
        }

        return groupRelations;
    }
}