Java中多层级一对多关系的处理
问题
有一个业务的数据库结构，包含很多一对多关系，大致类似下图的result属性的结构：
{
"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保存起来，然后再拼装不同层级的关联关系
代码
@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;
}
}