java - 如何在Java中对SQL查询处理的数据进行处理和分组？

Question

我是核心 Java 的初学者。我正在使用 JDBC 模板和连接从 SQL Server 获取数据。现在我想对收到的数据进行分组。

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。谁能指导我一下吗？

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

Answer 1

有很多方法可以做到这一点，其中一种可能是以下。

首先，创建一个模型来构建结果:

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 序列化器，用于根据需要显示最终结果(平面列表而不是 map )。

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

扩展名:

当然可以，你可以省略 jackson 处理所必需的 setter/getter 。这会导致类似的结果(没有 setter/getter ):

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 类并编写另一个类似的类，但必须以某种方式对数据进行结构化。