A query for a call stored procedure using spring boot
Controller
@PostMapping(value = "/callSPBySB")
public ResponseEntity<Object> callSPBySB() throws ResourceNotFoundException {
return ResponseEntity.ok(service.callSPBySB(""));
}
Service class
public Object callSPBySB(String name) {
return infoRepository.getSPInfo(name);
}
Repository
@PersistenceContext
private EntityManager entityManager;
@SuppressWarnings("unchecked")
public List<Map<String, Object>> getSPInfo(String name) {
StoredProcedureQuery proc = entityManager.createStoredProcedureQuery("Usp_App_GetAll_User");
proc.registerStoredProcedureParameter("Email", String.class, ParameterMode.IN);
proc.setParameter("Email", name);
proc.execute();
List<Map<String, Object>> result = (List<Map<String, Object>>) proc.getResultList();
return result;
}
Above code working fine but getting output not as per expectation
Current output: Consider value1, value2... is getting only value don't consider key-value pair
[[value1, value2, value3], [value1, value2, value3], [value1, value2, value3]]
Expected output:
[{Key1: value1, key2: value2}, {Key1: value1, key2: value2}, {Key1: value1, key2: value2}]
Note:
- I don't want to use entity class and @NamedStoredProcedureQuery because Stored Procedure, not fixed name and output (Currently I used static SP name in above example)
- Purpose of the code make a generic service for the call Stored Procedure of the native SQL