How to call a stored procedure without entity class in Spring Boot?

Viewed 1032

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
0 Answers
Related