【解決方法】SQL クエリからネストされた JSON API 応答を作成するにはどうすればよいですか?

[ad_1]

SQLビューから以下のデータを取得しています。

+-------+-------+------------+------------+---------+-----------+----------------+
| ID    | Name  | Desc       | Relation   | ChildId | ChildName | ChildDesc      |
+-------+-------+------------+------------+---------+-----------+----------------+
| 10111 | Motor | Main Motor | Accessory  | 30123   | Bolt      | Hexagonal Bolt |
| 10111 | Motor | Main Motor | Accessory  | 30124   | Nut       | 25mm Dia       |
| 10111 | Motor | Main Motor | Spare      | 30125   | screw     | Type A         |
| 10111 | Motor | Main Motor | Spare      | 30126   | Shaft     | 10m long       |
| 10112 | Engine| 800cc      | Spare      | 30127   | Oil       | Standard oil   |
+-------+-------+------------+------------+---------+-----------+----------------+

ユーザーが http://localhost:8080/items?id=10111 にアクセスしたときに、以下の応答を提供する必要があります。

{
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Accessory": [
		{
			"Id": "30123",
			"Name": "Bolt",
			"Desc": "Hexagonal Bolt"
		},
		{
			"Id": "30124",
			"Name": "Nut",
			"Desc": "25mm Dia"
		},
	],
	"Spare": [
		{
			"Id": "30125",
			"Name": "screw",
			"Desc": "Type A"
		},
		{
			"Id": "30126",
			"Name": "Shaft",
			"Desc": "10m long"
		},
	]
}<pre>

同様に、ユーザーが http://localhost:8080/items?id=10112 にアクセスしたとき

{
	"Id": "10112",
	"Name": "Engine",
	"Desc": "800cc",
	"Accessory": [],
	"Spare": [
		{
			"Id": "30127",
			"Name": "Oil",
			"Desc": "Standard oil"
		}
	]
}

リクエストごとに 1 つの ID しかありません。これを達成するのを手伝ってください。

私が試したこと：

私は以下を試しました

リポジトリ:

@Repository
public interface MyDataRepo extends JpaRepository<Items, 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);
}

現在の出力:

[{
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Accessory",
	"ChildId":"30123",
	"ChildName":"Bolt",
    "ChildDesc":"Hexagonal Bolt"
	
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Accessory",
	"ChildId":"30124",
	"ChildName":"Nut",
    "ChildDesc":"25mm Dia"
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Spare",
	"ChildId":"30125",
	"ChildName":"screw",
    "ChildDesc":"10m long "
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Spare",
	"ChildId":"30126",
	"ChildName":"Shaft",
    "ChildDesc":"Pasted Seal"
}]