@TransactionAttribute(TransactionAttributeType.REQUIRED)
public boolean crearRegistrosEstados(int periodo) {
try {
if (em.isOpen()) {
// em.getTransaction().begin();
StoredProcedureQuery storedProcedure =
em.createStoredProcedureQuery("fn_calcular_registro_estado_default");
// set parameters
storedProcedure.registerStoredProcedureParameter(
"id_periodo", Integer.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(
"result", Boolean.class, ParameterMode.OUT);
storedProcedure.setParameter("id_periodo", periodo);
// execute SP
storedProcedure.execute();
// get result
Boolean result = (Boolean) storedProcedure.getOutputParameterValue("result");
System.out.println("result is: " + result);
return result;
}
} catch (Exception e) {
e.printStackTrace();
;
}
return false;
}
@Test
public void testStoredProcedureOutParameter() {
doInJPA(
this::entityManagerFactory,
entityManager -> {
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("count_phones");
query.registerStoredProcedureParameter("personId", Long.class, ParameterMode.IN);
query.registerStoredProcedureParameter("phoneCount", Long.class, ParameterMode.OUT);
query.setParameter("person_id", 1L);
query.execute();
Long phoneCount = (Long) query.getOutputParameterValue("phoneCount");
assertEquals(Long.valueOf(2), phoneCount);
});
}
@Test
public void testStoredProcedureRefCursor() {
doInJPA(
this::entityManagerFactory,
entityManager -> {
// tag::sql-jpa-call-sp-ref-cursor-oracle-example[]
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("personPhones");
query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR);
query.setParameter(1, 1L);
query.execute();
List<Object[]> postComments = query.getResultList();
// end::sql-jpa-call-sp-ref-cursor-oracle-example[]
assertNotNull(postComments);
});
}