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.

Michał Ziober · Accepted Answer · 2021-03-08 12:36:38Z

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.

edited Mar 8, 2021 at 12:36

answered Mar 5, 2021 at 17:34

Michał Ziober

38.9k18 gold badges110 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Omega Over a year ago

Thanks a loooooottt @Michal. You saved my day ! :) :)

Alysson Malagutte · Accepted Answer · 2021-03-01 14:45:15Z

1

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

answered Mar 1, 2021 at 14:45

Alysson Malagutte

313 bronze badges

3 Comments

Omega Over a year ago

Thanks. It would be great if you could provide some sample code. As a SQL guy, I'm not familiar with java syntax like creating DTO, creating Map etc.,

Alysson Malagutte Over a year ago

Send me your project url in github and i can help you there

Omega Over a year ago

Unfortunately I have not created any project in Github. Also this is project specific code and I'm not allowed to use the code anywhere else. What i have given here in the question is a sample(modified version)

Akash Jain · Accepted Answer · 2021-03-01 16:59:47Z

1

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.

edited Mar 1, 2021 at 16:59

answered Mar 1, 2021 at 15:16

Akash Jain

3362 silver badges11 bronze badges

3 Comments

Omega Over a year ago

Thanks a lot.Can you please clarify what is this name = "car_entity"? I don't have such column. Also how should i access them in controller? Should i change my SQL query? As mentioned I'm very new to Java, it would be really helpful if you could include them as well

Akash Jain Over a year ago

There will be 3 tables in your database which will be mapped to all above entities. this car_entity is a foreign key reference to the car table. you can manually create tables inside of your database or let hibernate do the work for you. it will create new tables based on entity and there annotations.(p.s. you can also call car_entity, car_id instead for clarity)

Omega Over a year ago

Thanks again. But I do not have any tables in the database and I'm not allowed to create one also. I just have the read access to one of the SQL views to fetch the data from database and provide the response accordingly.

Collectives™ on Stack Overflow

Nested JSON response from SQL View

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related