我的SQL过程包含两个查询,我想从我的java代码中调用该过程我厌倦了下面的代码,但它返回了来自过程的第一个查询的结果列表。
Query query = em.createNativeQuery("{call voucherRedemption(?,?,?)}");
query.setParameter(1, loggedinWorkSpaceId);
query.setParameter(2, startDate);
query.setParameter(3, endDate);
List<Object[]> results = query.getResultList();
程序如下:
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
我建议使用EntityManager#createStoredProcedureQuery
,如下所示:
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();
我并没有用它来实际得到结果,只是为了执行一个程序。让我知道这是否有效。