Nested JSON response from SQL View

Question

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.

Answer 1

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.

Answer 2

You should declare three separate Entity's like below. And access them in rest controller.
KitEntity

package com.example.demo;

import com.fasterxml.jackson.annotation.JsonIgnore;

import javax.persistence.*;

@Entity
public class KitEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // automatic ID creation by hibernate.
    private int id;
    private String name;
    private String desc;

    @ManyToOne
    @JoinColumn(name = "car_entity") // foreign key column which points to car table
    @JsonIgnore 
    private CarEntity carEntity;
    
    // getters and setters here
}

SpareEntity

package com.example.demo;

import com.fasterxml.jackson.annotation.JsonIgnore;

import javax.persistence.*;

@Entity
public class SpareEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
    private String desc;
    @ManyToOne
    @JoinColumn(name = "car_entity") // foreign key column which points to car table
    @JsonIgnore
    private CarEntity carEntity;
    
    // setters and getters here
}

CarEntity

@Entity
public class CarEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
    @OneToMany(mappedBy = "carEntity") // here carEntity is a veriable name in KitEntity who have ManyToOne relation.
    private List<KitEntity> kits;
    @OneToMany(mappedBy = "carEntity") // here carEntity is a veriable name in SpareEntity who have ManyToOne relation.
    private List<SpareEntity> spare;

    // getters and setters here.
}

You can use Spring Data JPA's JpaRepository for database access. or you can use hibernate queries to get the data from this entities.
Also You'll need two separate Entities to maintain two list of different types that is spare and kits. You can also use enums to define type but that will be complicated so I am not doing it that way.

p.s. also note that you have to use @JsonIgnore on kit and spare entities to make sure that recursive call on getters do not happen.

edit: car_entity is a column name in your Kit and Spare Entity. if you want to create tables by yourself, then you should use @Column annotation on each veriable to map it to appropriate column in database. and also @Table on class level and provide your table name in that annotation.

Answer 3

The way you are doing this is bringing information per line. One way to solve it would be to develop a serialization function before returning to control, like a DTO.

Another solution would be to map the entities correctly and try to perform the same select, so that in this way it returns as expected