Issue
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 |
+-------+-------+------------+------------+---------+-----------+----------------+
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"
},
]
}
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"
}
]
}
There will be only one id per request. Please help to achieve this
I have tried below.
Repository
@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();
}
}
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 Output for 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"
}]
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.
Solution
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.
Answered By - MichaĆ Ziober