来自SQL View的嵌套JSON响应 [英] Nested JSON response from SQL View

问题描述

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

I have a SQL view (IdView) and it is having below values. I have only read access to this view and dont have any access to underlying tables.

+-------+-------+------------+------------+---------+-----------+----------------+
| 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 的URL)时，我必须提供以下响应http://localhost:8080/items?id = 80121.将有2种关系: Kits 和 Spare .我想将所有套件保留在一个键内，类似地将备用"保留在另一个键内，如下所示.

Now i have to provide the below response when user hits the below URL i.e., for id 80121 http://localhost:8080/items?id=80121 . There will be 2 relation : Kits and Spare. I want to keep all the Kits inside one key and similarly Spare in the other key like below.

{
    "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

Similarly when user hits the http://localhost:8080/items?id=80112

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

每个请求只有一个ID.请帮助实现这一目标

There will be only one id per request. Please help to achieve this

我在下面尝试过.

存储库

@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还是Entity?我不知道如何创建此嵌套的JSON并继续进行.任何方向将不胜感激.

I'm beginner in Java and spring boot. Should I create a custom POJO or Entity with view columns? I have no idea how to create this nested JSON and proceed further. Any directions would be appreciated.

推荐答案

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

You need to process data after you load them from database. The easiest way to do that is to group by Relation column and map object to Map instance. You can add below method to your service layer and invoke in your controller:

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;
}

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

Of course, you need to update type returned by controller method.

这篇关于来自SQL View的嵌套JSON响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！