如何使用存储过程中的数组进行嵌套的 JSON 响应 [英] How to make nested JSON response with Array from a Stored procedure
问题描述
我有一个从存储过程生成的数据集
I have a data set that is generated from a Stored procedure
+------+--------+---------------------+------------+
| Line |Status | Consent | tid |
+------+--------+---------------------+------------+
| 1001 | 1 | Yes | 1054 |
| 1002 | 1 | yes | 1055 |
| 1003 | 1 | Yes | 3045 |
| 1004 | 1 | No | 3046 |
| 1005 | 0 | No | 1023 |
| 1006 | 0 | Yes | 1024 |
| 1007 | 1 | No | 1025 |
+------+--------+---------------------+------------+
我希望能够从我从存储过程中获得的数据中生成一个 JSON
I want to be able to generate a JSON like from the data I get from the stored procedure
{
"totalMembers": 9,
"questionaryinfo": [
{
"line": "1001",
"status": "1",
"consent": "Yes",
"tid": "1054",
"adultquestionary":"Yes"
},
{
"line": "1002",
"status": "1",
"consent": "Yes",
"tid": "1055",
"adultquestionary":"Yes"
},
{
"line": "1003",
"status": "1",
"consent": "Yes",
"tid": "3035",
"adultquestionary":"Yes"
},
}
我尝试过使用哈希映射
公共响应实体<映射<字符串，对象>>retrieveLineListing(String Case){
public ResponseEntity<Map<String, Object>> retrieveLineListing(String Case){
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("get_listing", "ListingModel");
query.registerStoredProcedureParameter("Case", String.class, ParameterMode.IN);
query.setParameter("Case", Case);
query.execute();
List list = query.getResultList();
Map<String, Object> parent = new HashMap<>();
parent.put("totalMembers", 9);
parent.put("questionaryinfo", new HashMap<String, Object>());
parent.put("questionaryinfo", new ArrayList<HashMap<String,Object>>());
ArrayList<HashMap<String,Object>> listings = (ArrayList<HashMap<String,Object>>) parent.get("questionaryinfo");
if (list != null) {
list.forEach(d -> {
HashMap<String,Object> entry = new HashMap<>();
entry.put("adultquestionary","Yes");
entry.put("consent", list.get(1));
listings.add(entry);
});
}
return ResponseEntity.ok().body(parent);
}
更新 1
在修改@Rahul 建议我做的事情后，
After amending to how @Rahul advised me to do,
我的 JSON 看起来像
my JSON looks like
{
"totalMembers": 9,
"questionaryinfo": [
"consent"{
{
"line": "1001",
"status": "1",
"consent": "Yes",
"tid": "1054",
},
"adultquestionary":"Yes"
}
"consent"{
{
"line": "1002",
"status": "1",
"consent": "Yes",
"tid": "1055",
},
"adultquestionary":"Yes"
},
"consent"{
{
"line": "1003",
"status": "1",
"consent": "Yes",
"tid": "3035",
},
"adultquestionary":"Yes"
},
]
}
如何设置我的方法来生成上面的嵌套 JSON 并获取 Line、Status、consent 和 tid 值?
How can I set up my method to generate the nested JSON above and get the Line, Status, consent and tid values ?
推荐答案
我建议你使用 DTO 模型来处理你的请求和响应 作为您的资源/控制器层中的更好实践.您仍然需要使用存储过程响应正确填充模型，但这应该不是我在您的帖子中看到的挑战.
I would suggest you to use DTO models for your requests and responses as a better practice in your Resource/Controller layer. You will still need to populate the model correctly with the stored procedure response, but that should not be a challenge from what I can see in your post.
将您的响应格式封装到单个模型中的真正好处是灵活性、可扩展性以及您的 API 可以提供的响应的多样性返回.
The real benefits of encapsulating the format of your response into a single model are flexibility, extensibility and the variety of responses you API may return.
要检索上面定义的有效负载，它必须像这样简单:
For you to retrieve the payload defined above, it must be as simple as this:
@RestController
@RequestMapping("/v1/stackoverflow")
public class DemoController {
@GetMapping
public ResponseEntity<ResponseDTO> read() {
List<Consent> result = mockStoreProcedureResult();
ResponseDTO response = ResponseDTO.builder()
.totalMembers(result.size())
.questionaryInfo(result)
.build();
return ResponseEntity.ok(response);
}
}
其中 ResponseDTO 模型封装了响应的所需形状.您会看到我还创建了一个 Consent 模型来反映您的初始提案.
In which ResponseDTO model encapsulates the desired shape of your response. You will see that I also created a Consent model that reflects your initial proposal.
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder(toBuilder = true)
public class ResponseDTO {
private Integer totalMembers;
private List<Consent> questionaryInfo;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder(toBuilder = true)
public class Consent {
private String line;
private String status;
private String consent;
private String tid;
private String adultQuestionary;
}
当我运行演示应用程序并提出请求时，我得到了与您要查找的内容类似的响应.如果我想更改 questionaryInfo 中对象的格式，就像编辑用于此目的的模型一样简单.
When I ran the demo app and make a request, I get a similar response to what you are looking for. If I wanted to alter the format of the objects inside questionaryInfo, is just as simple as editing the model being used for that.
curl -X GET --location "http://localhost:8080/v1/stackoverflow" \
-H "Content-Type: application/json"
HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked
Date: Tue, 29 Jun 2021 18:35:11 GMT
Keep-Alive: timeout=60
Connection: keep-alive
{
"totalMembers": 3,
"questionaryInfo": [
{
"line": "1001",
"status": "1",
"consent": "Yes",
"tid": "1054",
"adultQuestionary": "Yes"
},
{
"line": "1002",
"status": "1",
"consent": "Yes",
"tid": "1055",
"adultQuestionary": "Yes"
},
{
"line": "1003",
"status": "1",
"consent": "Yes",
"tid": "3055",
"adultQuestionary": "Yes"
}
]
}
希望你觉得这有用，干杯.
Hope you find this useful, cheers.
回答您的问题@arriff，您如何为响应模型注入值?
Answering to your question @arriff, how do you inject values to your response model?
您可以轻松地遍历从存储过程返回的列表并将每个条目映射到您的模型中.为此，必须在您的模型中定义一个可以接收预期参数的构造函数.
You could easily iterate over the list you get back from your stored procedure and map each entry into your model. For that a constructor must be defined in you model that can receive the expected parameters.
public List<Consent> getConsentAnswers() {
return dbStoreProcedureResult().stream()
.map(dbo -> new Consent(dbo, "Yes", "Yes"))
.collect(Collectors.toList());
}
这是为 Consent 模型定义的附加自定义构造函数.
Here is the additional custom constructor defined for Consent model.
public Consent(@NonNull DBConsent dbo,
@NonNull String consent,
@NonNull String adultQuestionary) {
this.line = dbo.getLine();
this.tid = dbo.getTid();
this.status = dbo.getStatus();
this.consent = consent;
this.adultQuestionary = adultQuestionary;
}
这篇关于如何使用存储过程中的数组进行嵌套的 JSON 响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
