java - 使用 java 8 流或 SQL 中最好的方法对类进行分组

Question

数据是这样的

    head child assigned total
[   Mas   Mas1     2    5
,   Mas   Mas2     0    5
,   Usr   usr1     4    4
,   Usr   usr2     1    3
,   Inv   Inv1     3    5
,   Inv   Inv2     2    3
,   Inv   Inv3     2    3
,   Inv   Inv4     1    3
]

我想要他们的 child 的特定 header 的总和。

我在sql中使用分区

select head,
SUM(childAssigned) over (partition by am.acl_group) as assignedHead,
sum(childTotal) over (partition by am.acl_group) as totalHead,
child,
childAssigned,
childTotal

由于 HQL astquerytranslatorfactory 不支持“partition by”，因此我将其用作 native 查询

我有一个类正在保存 SQL 响应

class AclList{
    String head;
    Integer assignedHead;
    Integer totalHead;
    String child;
    Integer assignedChild;
    Integer totalChild;
}

数据是这样的

[   Mas 2   10  Mas1    2   5
,   Mas 2   10  Mas2    0   5
,   Usr 5   7   usr1    4   4
,   Usr 5   7   usr2    1   3
,   Inv 8   14  Inv1    3   5
,   Inv 8   14  Inv2    2   3
,   Inv 8   14  Inv3    2   3
,   Inv 8   14  Inv4    1   3
]

我想要一个响应，它将对同一 header 的子级进行分组。

响应应该类似于 ResponseClass(type,signed,total,List)

[ 
  { 
    "type":"MAS",
    "assigned":"2",
    "total":"10",
    "subType":[ 
      { 
        "type":"MAS1",
        "assigned":"2",
        "total":"5"
      },
      { 
        "type":"MAS2",
        "assigned":"0",
        "total":"5"
      }
    ]
  },
  { 
    "type":"USR",
    "assigned":"5",
    "total":"7",
    "subType":[ 
      { 
        "type":"USR1",
        "assigned":"4",
        "total":"4"
      },
      { 
        "type":"USR2",
        "assigned":"1",
        "total":"3"
      }
    ]
  }
]

类(class)详细信息(类型、分配、总计)我的方法是将 header 保留在 hashmap 的键中，将 child 保留在 hashmap 的值中由于头的所有 3 列都是相同的，因此，equals 和 hashcode 需要小心。

类 ChildDetails(列表) HashMap

Iterate sql response
if hashmap.contains(head) //exist
    fetch value, add new one in list
    hashmap.put(head, updated)
else
    create header,
    create child, add it to a blank list
    hashmap.put(head, new list)

再次迭代 hashmap，将其排列在新的 json 响应中

但是，这是一个繁琐的过程并且效率低下。

有没有办法用JAVA Stream()来完成？

Answer 1

是的，可以使用JAVA Stream()来完成

package com.bbc.enums;

import lombok.*;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.*;
import java.util.stream.Collectors;


public class StackOverFlow {
    public static void main(String[] args) {
        List<AclList> aclLists = Arrays.asList(
                new AclList("Mas", 2, 10, "Mas1", 2, 5),
                new AclList("Mas", 2, 10, "Mas2", 0, 5),
                new AclList("USR", 5, 7, "USR1", 4, 4),
                new AclList("USR", 5, 7, "USR2", 1, 3),
                new AclList("Inv", 8, 14, "Inv1", 3, 5),
                new AclList("Inv", 8, 14, "Inv2", 2, 3),
                new AclList("Inv", 8, 14, "Inv3", 2, 3),
                new AclList("Inv", 8, 14, "Inv4", 1, 3)

        );

        Map<String, Map<Integer, Map<Integer, List<AclList>>>> collect = aclLists.stream()
                .collect(Collectors.groupingBy(AclList::getHead, Collectors.groupingBy(AclList::getAssignedHead,
                        Collectors.groupingBy(AclList::getTotalHead))));

        List<HashMap<String, Object>> resList = new ArrayList<>();
        collect.forEach((k1, v1) -> {
            HashMap<String, Object> res = new HashMap<>();
            res.put("type", k1);
            v1.forEach((k2, v2) -> {
                res.put("assigned", k2);
                v2.forEach((k3, v3) -> {
                    res.put("total", k3);
                    List<HashMap<String, Object>> list = new ArrayList<>();
                    v3.forEach(aclList -> {
                        HashMap<String, Object> map1 = new HashMap<>();
                        map1.put("type", aclList.getChild());
                        map1.put("assigned", aclList.getAssignedChild());
                        map1.put("total", aclList.getTotalChild());
                        list.add(map1);
                    });
                    res.put("subType", list);
                });
            });
            resList.add(res);
        });
        System.out.println(resList);
        // if you want json response then
        System.out.println(listmap_to_json_string(resList));
    }


    public static String listmap_to_json_string(List<HashMap<String, Object>> list) {
        JSONArray json_arr = new JSONArray();
        for (Map<String, Object> map : list) {
            JSONObject json_obj = new JSONObject();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                try {
                    json_obj.put(key, value);
                } catch (JSONException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            json_arr.put(json_obj);
        }
        return json_arr.toString();
    }
}


@AllArgsConstructor
@Getter
@Setter
@ToString
@NoArgsConstructor
class AclList {
    String head;
    Integer assignedHead;
    Integer totalHead;
    String child;
    Integer assignedChild;
    Integer totalChild;
}