java - 如何处理和分组从Java中的SQL查询处理的数据？

我是Java初学者。
我正在使用JDBC模板和联接从sql服务器中获取数据。
现在，我想对接收到的数据进行分组。

processId  processName  subId  subName  valueId value gId  gradeName
1           p1           1      s1       11      v1    1     g1
1           p1           1      s1       11      v1    2     g2
2           p2           2      s2       null    null  null null
3           p3           3      s3       13      v3    null null

我想要以下输出：

[{
    "processId": 1,
    "processname": "p1",
    "sub": [{
        "subId": 11,
        "subName": "s1",
        "value": [{
            "valueId": 11,
            "value": "v1",
            "grades": [{
                "gId": 1,
                "gradeName": "g1"
            }, {
                "gId": 2,
                "gradeName": "g2"
            }]
        }]
    }]
}, {
    "processId": 2,
    "processname": "p2",
    "sub": [{
        "subId": 12,
        "subName": "s2",
        "value": []
    }]
}, {
    "processId": 3,
    "processname": "p3",
    "sub": [{
        "subId": 13,
        "subName": "s3",
        "value": [{
            "valueId": 3,
            "value": "g3",
            "grade": []
        }]
    }]
}]

我在这里发现了类似的问题：link

如链接的问题中所述，我使用了while循环来遍历输出，但是我无法这样做。

用户之一在评论中使用了hashmap，但是我无法实现hashmap。有人可以指导我吗？

任何人都可以指导/帮助我解决这个问题。

最佳答案

有许多方法可以做到这一点，可能是以下几种。

首先，创建一个模型以构建结果：

package core.map;

import java.util.HashMap;
import java.util.Map;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;

public class Process {
    private String processId;
    private String processName;

    @JsonSerialize(using = MapAsListSerializer.class)
    private Map<String, Sub> sub = new HashMap<>();

    public Process(final String processId, final String processName) {
        this.processId = processId;
        this.processName = processName;
    }

    public String getProcessId() {
        return processId;
    }

    public String getProcessName() {
        return processName;
    }

    public Map<String, Sub> getSub() {
        return sub;
    }

    static class Sub {
        private String subId;
        private String subName;

        @JsonSerialize(using = MapAsListSerializer.class)
        private Map<String, Value> value = new HashMap<>();

        public Sub(final String subId, final String subName) {
            this.subId = subId;
            this.subName = subName;
        }

        public String getSubId() {
            return subId;
        }

        public String getSubName() {
            return subName;
        }

        public Map<String, Value> getValue() {
            return value;
        }

        static class Value {
            private String valueId;
            private String value;

            @JsonSerialize(using = MapAsListSerializer.class)
            private Map<String, Grade> grades = new HashMap<>();

            public Value(final String valueId, final String value) {
                this.valueId = valueId;
                this.value = value;
            }

            public String getValueId() {
                return valueId;
            }

            public String getValue() {
                return value;
            }

            public Map<String, Grade> getGrades() {
                return grades;
            }

            static class Grade {
                private String gId;
                private String gradeName;

                public Grade(final String gId, final String gradeName) {
                    this.gId = gId;
                    this.gradeName = gradeName;
                }

                public String getgId() {
                    return gId;
                }

                public String getGradeName() {
                    return gradeName;
                }

            }
        }
    }
}

除了以下行外，这只是一堆（或多或少）简单的POJO。

@JsonSerialize(using = MapAsListSerializer.class)

MapAsListSerializer只是一个很小的自定义Json序列化器，用于根据需要显示最终结果（平面列表而不是地图）。

package core.map;

import java.io.IOException;
import java.util.Map;

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;

public class MapAsListSerializer extends JsonSerializer<Map<?, ?>> {

    @Override
    public void serialize(Map<?, ?> incomingMap, JsonGenerator generator, SerializerProvider arg2)
            throws IOException, JsonProcessingException {
        generator.writeObject(incomingMap.values());

    }
}

至少我们需要代码来计算（伪数据库）结果，这可能类似于：

package core.map;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import core.map.Process.Sub;
import core.map.Process.Sub.Value;
import core.map.Process.Sub.Value.Grade;

public class ResultMapper {

    private final static ObjectMapper mapper = new ObjectMapper();

    private final static List<List<String>> resultSet = new ArrayList<>();

    private final static Map<String, Process> processes = new HashMap<>();

    static {
        resultSet.add(Arrays.asList("1", "p1", "1", "s1", "11", "v1", "1", "g1"));
        resultSet.add(Arrays.asList("1", "p1", "1", "s1", "11", "v1", "2", "g2"));
        resultSet.add(Arrays.asList("2", "p2", "2", "s2", null, null, null, null));
        resultSet.add(Arrays.asList("3", "p3", "3", "s3", "13", "v3", null, null));
    }

    public static void main(String[] args) throws JsonProcessingException {
        resultSet.forEach(row -> rowToProcess(row, processes));
        System.out.println(mapper.writeValueAsString(processes.values()));
    }

    private static void rowToProcess(final List<String> row, final Map<String, Process> processes) {

        final String processId = row.get(0);
        final String processName = row.get(1);
        final String subId = row.get(2);
        final String subName = row.get(3);
        final String valueId = row.get(4);
        final String value = row.get(5);
        final String gId = row.get(6);
        final String gradeName = row.get(7);

        Process currentProcess = processes.get(processId);
        if (currentProcess == null) {
            currentProcess = new Process(processId, processName);
            processes.put(processId, currentProcess);
        }

        Map<String, Sub> subs = currentProcess.getSub();
        Sub currentSub = subs.get(subId);
        if (currentSub == null) {
            currentSub = new Process.Sub(subId, subName);
            subs.put(subId, currentSub);
        }

        Map<String, Value> values = currentSub.getValue();
        if (valueId == null)
            return;
        Value currentValue = values.get(valueId);
        if (currentValue == null) {
            currentValue = new Sub.Value(valueId, value);
            values.put(valueId, currentValue);
        }

        if (gId == null)
            return;
        Map<String, Grade> grades = currentValue.getGrades();
        grades.put(gId, new Value.Grade(gId, gradeName));
    }
}

因此，除了Json依赖项之外，几乎所有内容都不在JDK（1.8）中。所有需要的其他东西都可用here

延期：

当然，有可能，您可以省略杰克逊处理所必需的吸气剂。这将导致类似（没有吸气剂）的情况：

package core.map;

import java.util.HashMap;
import java.util.Map;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;

public class Process {
    private String processId;
    private String processName;

    @JsonSerialize(using = MapAsListSerializer.class)
    private Map<String, Sub> sub = new HashMap<>();

    public Process(final String processId, final String processName) {
        this.processId = processId;
        this.processName = processName;
    }

    public Map<String, Sub> getSub() {
        return sub;
    }

    static class Sub {
        private String subId;
        private String subName;

        @JsonSerialize(using = MapAsListSerializer.class)
        private Map<String, Value> value = new HashMap<>();

        public Sub(final String subId, final String subName) {
            this.subId = subId;
            this.subName = subName;
        }

        public Map<String, Value> getValue() {
            return value;
        }

        static class Value {
            private String valueId;
            private String value;

            @JsonSerialize(using = MapAsListSerializer.class)
            private Map<String, Grade> grades = new HashMap<>();

            public Value(final String valueId, final String value) {
                this.valueId = valueId;
                this.value = value;
            }

            public Map<String, Grade> getGrades() {
                return grades;
            }

            static class Grade {
                private String gId;
                private String gradeName;

                public Grade(final String gId, final String gradeName) {
                    this.gId = gId;
                    this.gradeName = gradeName;
                }

            }
        }
    }
}

但是然后您必须告诉Jackson来直接使用这些字段而不是使用getter：

mapper.setVisibility(PropertyAccessor.GETTER, Visibility.NONE);
mapper.setVisibility(PropertyAccessor.FIELD, Visibility.ANY);

我将在此处保留这两种解决方案，因为这与个人喜好有关。当然，您通常可以省略Process类，并编写另一个与此类似的类，但是必须以某种方式构造数据。