如何从数据库表中嵌套JSON响应

Question

我有两张表

DATA

+------+--------+---------------------+------------+
| ID   | Name   | Desc                | Type       |
+------+--------+---------------------+------------+
| 1001 | Motor  | Main motor 50 volt  | Mechanical |
| 1002 | Nut    | 25 mm dia           | Mechanical |
| 1003 | Bolt   | Hexa bolt           | Mechanical |
| 1004 | Engine | 750cc liquid cooled | Mechanical |
| 1005 | Oil    | 1 liter             | NA         |
| 1006 | Filter | Airfilter           | NA         |
| 1007 | AC     | 1000w               | Electrical |
+------+--------+---------------------+------------+

Relationship

+-----------+-----------+----------+
| Parent_id | Relation  | Child_id |
+-----------+-----------+----------+
| 1001      | Accessory | 1002     |
| 1001      | Accessory | 1003     |
| 1001      | Service   | 1005     |
| 1001      | Service   | 1006     |
| 1004      | Accessory | 1002     |
| 1004      | Accessory | 1003     |
| 1004      | Service   | 1005     |
+-----------+-----------+----------+

当用户搜索1001

时，我想提供以下响应

{
"id": "1001",
"name": "Motor",
"desc": "Main motor 50 volt",
"Accessory": [{
"id": "1002",
"name": "Nut"
},
{
"id": "1003",
"name": "Bolt"
}
],
"Service": [{
"id": "1005",
"name": "Oil"
},
{
"id": "1006",
"name": "Filter"
}
]
}

这里的Accessory和Service来自relationship表的relation列。我对春靴一无所知。在谷歌的帮助下，我做了下面的代码。这给了我一级json。但我无法找到如何创建这个嵌套JSON。请提供一些建议。

存储库:

@Repository
public interface MyDataRepo extends JpaRepository<Items, String> {
@Query(value="SELECT D.id,D.name,D.desc,R.relation,R.child_id as childid,DC.name as childname
FROM DATA D 
JOIN RELATIONSHIP R ON D.ID=R.PARENT_ID 
JOIN DATA DC ON DC.id=R.CHILD_ID
WHERE D.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();
}
}

服务:

public List<Data> getMyData(String id) {
return repo.findAllCategory(id);
}

控制器:

@GetMapping("/getData/{id}")
public ResponseEntity<List<Data>> retrieveData(@PathVariable String id) {
List<Data> stud = service.getMyData(id);
return ResponseEntity.ok().body(stud);
}

Answer 1

由于Relation字段是动态的，我们不能使用POJO来生成相关对象，所以我选择了一个Map对象来生成最终结果。

@GetMapping("/getData/{id}")
public ResponseEntity<Map<String, Object>> retrieveData(@PathVariable("id") Long id) {
List<MyDataRepo.Data> stud = myDataRepo.findAllCategory(id);
Map<String, Object> parent = new HashMap<>();
parent.put("id", stud.get(0).getid());
parent.put("name", stud.get(0).getname());
parent.put("desc", stud.get(0).getdesc());
stud.forEach(d -> {
String r = d.getrelation();
Map<String, String> child = new HashMap<>();
child.put("id", d.getchildid());
child.put("name", d.getchildname());
if (parent.containsKey(r)) {
List<Map<String, String>> children = (List<Map<String, String>>) parent.get(r);
children.add(child);
} else {
List<Map<String, String>> children = new ArrayList<>();
children.add(child);
parent.put(r, children);
}
});
return ResponseEntity.ok().body(parent);
}

最后，我们得到了结果

{
"id": "1001",
"name": "Motor",
"desc": "Main motor 50 volt",
"Accessory": [
{
"id": "1002",
"name": "Nut"
},
{
"id": "1003",
"name": "Bolt"
}
],
"Service": [
{
"id": "1005",
"name": "Oil"
},
{
"id": "1006",
"name": "Filter"
}
]
}

Answer 2

对于构建嵌套投影，您应该在实体之间建立关系，例如:

@Entity
@Table(name = "Data")
public class Data {
@Id
private long id;
@Column(name = "name")
private String name;
@Column(name = "desc")
private String desc;
@OneToMany(mappedBy = "parentId", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Set<Relationship> relationship;
...getters/setters...
}

@Entity
@Table(name = "Relationship")
@IdClass(RelationshipId.class)
public class Relationship {
@Id
@Column(name = "parent_id")
private long parentId;
@Id
@Column(name = "relation")
private String name;
@Id
@Column(name = "child_id")
private long childId;
@OneToOne
@JoinColumn(name = "child_id", unique = true, nullable = false, updatable = false, insertable = false)
private Data child;
...getters/setters...
}

复合键的id类:

public class RelationshipId implements Serializable {
private long parentId;
private String name;
private long childId;
// default constructor
public RelationshipId(long parentId, String name, long childId) {
this.parentId = parentId;
this.name = name;
this.childId = childId;
}
// equals() and hashCode()
}

早些时候，我检查了两个选项:@IdClass和@EmbeddedId。我认为使用@IdClass注释更合适。详细说明:https://www.baeldung.com/jpa-composite-primary-keys

以下预测:

public interface DataView {
String getId();
String getName();
String getDesc();
List<RelationshipView> getRelationship();
}

public interface RelationshipView {
String getId();
String getName();
String getChildId();
DataItemView getChild();
}

public interface DataItemView {
String getId();
String getName();
String getDesc();
}

在repo中，您可以通过单个查询接收所有所需的数据，并将数据重构为正确的DTO视图:

public interface DataRepo extends JpaRepository<Data, String> {
@Query(value = "SELECT d from Data d " +
"left join fetch d.relationship as r " +
"left join fetch r.child as c " +
"where d.id=:id")
DataView getDataViewById(long id);
}

@Service
public class DataService {
@Autowired
private DataRepo dataRepo;
public DataDto getMyData(long id) {
DataView data = dataRepo.getDataViewById(id);
DataDto dataDto = new DataDto();
dataDto.setId(data.getId());
dataDto.setName(data.getName());
dataDto.setDesc(data.getDesc());
for (RelationshipView rs : data.getRelationship()) {
DataItemView dataItemView = rs.getChild();
switch (rs.getName()) {
case "Accessory":
dataDto.getAccessory().add(new DataItemDto(dataItemView.getId(), dataItemView.getName()));
break;
case "Service":
dataDto.getService().add(new DataItemDto(dataItemView.getId(), dataItemView.getName()));
break;
}
}
return dataDto;
}
}

DTO类:

public class DataDto {
String id;
String name;
String desc;
List<DataItemDto> accessory = new LinkedList<>();
List<DataItemDto> service = new LinkedList<>();
...getters/setters...
}

public class DataItemDto {
String id;
String name;
public DataItemDto(String id, String name) {
this.id = id;
this.name = name;
}
...getters/setters...
}