时间戳

首页 > 解决方案 > 来自 SQL 视图的嵌套 JSON 响应

java - 来自 SQL 视图的嵌套 JSON 响应

问题描述

我有一个 SQL 视图 ( IdView)，它具有以下值。我只有对该视图的读取访问权限，而对基础表没有任何访问权限。

+-------+-------+------------+------------+---------+-----------+----------------+
| ID    | Name  | Desc       | Relation   | ChildId | ChildName | ChildDesc      |
+-------+-------+------------+------------+---------+-----------+----------------+
| 80121 | Car   | Model A    | Kits       | 50123   | Bolt      | Hexagonal Bolt |
| 80121 | Car   | Model A    | Kits       | 50124   | Nut       | 25mm Dia       |
| 80121 | Car   | Model A    | Spare      | 50125   | screw     | Type A         |
| 80121 | Car   | Model A    | Spare      | 50126   | Shaft     | 10m long       |
| 80122 | Bike  | Model H    | Spare      | 50127   | Oil       | Standard oil   |
+-------+-------+------------+------------+---------+-----------+----------------+

现在，当用户点击以下 URL 时，我必须提供以下响应，即 id 80121 http://localhost:8080/items?id=80121 。将有2个关系：Kits和Spare。我想将所有套件保存在一个键中，并且类似地备用在另一个键中，如下所示。

{
    "Id": "80121",
    "Name": "Car",
    "Desc": "Model A",
    "Kits": [
        {
            "Id": "50123",
            "Name": "Bolt",
            "Desc": "Hexagonal Bolt"
        },
        {
            "Id": "50124",
            "Name": "Nut",
            "Desc": "25mm Dia"
        },
    ],
    "Spare": [
        {
            "Id": "50125",
            "Name": "screw",
            "Desc": "Type A"
        },
        {
            "Id": "50126",
            "Name": "Shaft",
            "Desc": "10m long"
        },
    ]
}

同样，当用户点击 http://localhost:8080/items?id=80112

{
    "Id": "80112",
    "Name": "Bike",
    "Desc": "Model H",
    "Kits": [],
    "Spare": [
        {
            "Id": "50127",
            "Name": "Oil",
            "Desc": "Standard oil"
        }
    ]
}

每个请求只有一个 id。请帮助实现这一目标

我在下面尝试过。

存储库

@Repository
public interface MyDataRepo extends JpaRepository<List, String> {

    @Query(value="select ID,Name,Desc,Relation,ChildId,ChildName,ChildDesc from myview
                  WHERE ID=?1",nativeQuery=true)
    List<Data> findAllCategory(String id);

    public static interface Data {
      String getid();
      String getname();
      String getdesc();
      String getrelation();
      String getchildid();
      String getchildname();
      String getchilddesc();
    }
}

服务：

public List<Data> getMyData(String id) {
    return repo.findAllCategory(id);
}

控制器：

@GetMapping("/items")
public ResponseEntity<List<Data>> retrieveData(@RequestParam("id") String id) {
    List<Data> stud = service.getMyData(id);
    return ResponseEntity.ok().body(stud);
}

电流输出80121：

[{
    "Id": "80121",
    "Name": "Car",
    "Desc": "Model A",
    "Relation":"Kits",
    "ChildId":"50123",
    "ChildName":"Bolt",
    "ChildDesc":"Hexagonal Bolt"
    
}, {
    "Id": "80121",
    "Name": "Car",
    "Desc": "Model A",
    "Relation":"Kits",
    "ChildId":"50124",
    "ChildName":"Nut",
    "ChildDesc":"25mm Dia"
}, {
    "Id": "80121",
    "Name": "Car",
    "Desc": "Model A",
    "Relation":"Spare",
    "ChildId":"50125",
    "ChildName":"screw",
    "ChildDesc":"10m long "
}, {
    "Id": "80121",
    "Name": "Car",
    "Desc": "Model A",
    "Relation":"Spare",
    "ChildId":"50126",
    "ChildName":"Shaft",
    "ChildDesc":"Pasted Seal"
}]

我是 Java 和 Spring Boot 的初学者。我应该使用视图列创建自定义 POJO 或实体吗？我不知道如何创建这个嵌套的 JSON 并继续进行。任何方向将不胜感激。

标签： javajsonspring-boothibernatejackson

解决方案

从数据库加载数据后，您需要对其进行处理。最简单的方法是按Relation列分组并将对象映射到Map实例。您可以将以下方法添加到您的服务层并在您的控制器中调用：

public Map<String, Object> getGroupedByRelationData(String id) {
    List<Data> data = repo.findAllCategory(id)
    if (data == null || data.isEmpty()) {
        return Collections.emptyMap();
    }

    // from first objet on the list copy common properties
    Data first = data.get(0);
    Map<String, Object> result = new LinkedHashMap<>();
    result.put("Id", first.getId());
    result.put("Name", first.getName());
    result.put("Desc", first.getDesc());
    
    // group data by relation field
    Map<String, List<Data>> grouped = data.stream().collect(Collectors.groupingBy(Data::getRelation));
    
    // each entity convert to map with child values
    grouped.forEach((k, v) -> {
        result.put(k, v.stream().map(inputData -> {
            Map<String, Object> childResult = new HashMap<>();
            childResult.put("Id", inputData.getChildId());
            childResult.put("Name", inputData.getChildName());
            childResult.put("Desc", inputData.getChildDesc());

            return childResult;
        }).collect(Collectors.toList()));
    });

    return result;
}

当然，您需要更新控制器方法返回的类型。

推荐阅读