Method to call SQL stored procedure: use CreateNativeQuery

Question

My SQL stored procedure contains two queries and I want to call the stored procedure from my Java code. I tried the code below but it only returns the result list of the first query in the stored procedure.

Query query = em.createNativeQuery("{call voucherRedemption(?,?,?)}");
    query.setParameter(1, loggedinWorkSpaceId);
    query.setParameter(2, startDate);
    query.setParameter(3, endDate);
    List<Object[]> results = query.getResultList();

The stored procedure is as follows:

CREATE PROCEDURE `cer`.`voucherRedemption`(IN workspaceId int(10), IN startDate VARCHAR(30),
    IN endDate VARCHAR(30))
BEGIN
select w.ws_name as wsName,str_to_date(startDate, '%Y-%m-%d') as startDate,str_to_date(endDate, '%Y-%m-%d') as endDate
from workspace w where w.ws_id = workspaceId;

select money.*,money.paidAmount money.prepaidAmount - money.clientCost,programs.programs
from program; END

Answer 1

I recommend using EntityManager#createStoredProcedureQuery like this:

StoredProcedureQuery query = em.createStoredProcedureQuery("voucherRedemption");
query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
query.setParameter(1, loggedinWorkSpaceId);
query.setParameter(2, startDate);
query.setParameter(3, endDate);
List<Object[]> results = query.getResultList();

I haven't used it to get actual results, just executed a process. If this works, please let me know.