I'm getting the below data from SQL view.
+-------+-------+------------+------------+---------+-----------+----------------+
| 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 |
+-------+-------+------------+------------+---------+-----------+----------------+
Now i have to provide the below response when user hits the 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>
Similarly when user hits the http://localhost:8080/items?id=10112
{
"Id": "10112",
"Name": "Engine",
"Desc": "800cc",
"Accessory": [],
"Spare": [
{
"Id": "30127",
"Name": "Oil",
"Desc": "Standard oil"
}
]
}
There will be only one id per request. Please help to achieve this.
What I have tried:
I have tried below
Repository :
@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();
}
}
Service:
public List<Data> getMyData(String id) {
return repo.findAllCategory(id);
}
Controller :
@GetMapping("/items")
public ResponseEntity<List<Data>> retrieveData(@RequestParam("id") String id) {
List<Data> stud = service.getMyData(id);
return ResponseEntity.ok().body(stud);
}
Current ouput:
[{
"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"
}]