StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax"); // set parameters storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN); storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT); storedProcedure.setParameter("subtotal", 1f); // execute SP storedProcedure.execute(); // get result Double tax = (Double)storedProcedure.getOutputParameterValue("tax");
@Test public void testStoredProcedureRefCursor() { try { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_phones"); query.registerStoredProcedureParameter( 1, void.class, ParameterMode.REF_CURSOR); query.registerStoredProcedureParameter( 2, Long.class, ParameterMode.IN); query.setParameter(2, 1L); List<Object[]> personComments = query.getResultList(); assertEquals(2, personComments.size()); }); } catch (Exception e) { assertTrue(Pattern.compile("Dialect .*? not known to support REF_CURSOR parameters").matcher(e.getCause().getMessage()).matches()); } }
procedureQuery.registerStoredProcedureParameter( param.getName().orElseThrow(() -> new IllegalArgumentException(ParameterBinder.PARAMETER_NEEDS_TO_BE_NAMED)), param.getType(), ParameterMode.IN); } else { procedureQuery.registerStoredProcedureParameter(param.getIndex() + 1, param.getType(), ParameterMode.IN); procedureQuery.registerStoredProcedureParameter(outputParameterName, outputParameterType, mode); procedureQuery.registerStoredProcedureParameter(params.getNumberOfParameters() + 1, outputParameterType, mode);
spq.registerStoredProcedureParameter("a", String.class, ParameterMode.OUT); spq.registerStoredProcedureParameter("b", Number.class, ParameterMode.IN); spq.registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT); spq.execute(); assertEquals("y", spq.getOutputParameterValue("a")); verify(query).registerStoredProcedureParameter("a", String.class, ParameterMode.OUT); verify(query).registerStoredProcedureParameter("b", Number.class, ParameterMode.IN); verify(query).registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT); verify(query).execute(); verify(targetEm).close();
spq.registerStoredProcedureParameter(0, String.class, ParameterMode.OUT); spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN); spq.registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT); spq.execute(); assertEquals("y", spq.getOutputParameterValue(0)); verify(query).registerStoredProcedureParameter(0, String.class, ParameterMode.OUT); verify(query).registerStoredProcedureParameter(1, Number.class, ParameterMode.IN); verify(query).registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT); verify(query).execute(); verify(targetEm).close();
@Test public void testStoredProcedureRefCursor() { doInJPA( this::entityManagerFactory, entityManager -> { try { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_phones"); 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(); assertNotNull(postComments); } catch (Exception e) { assertTrue( Pattern.compile( "Dialect .*? not known to support REF_CURSOR parameters").matcher( e.getCause().getMessage()).matches()); } } ); }
@Test @TestForIssue(jiraKey = "HHH-12138") public void testStoredProcedureOutParameter() { EntityManager entityManager = createEntityManager(); entityManager.getTransaction().begin(); try { StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_count_phones" ); query.registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN ); query.registerStoredProcedureParameter( 2, Long.class, ParameterMode.OUT ); query.setParameter( 1, 1L ); query.execute(); Long phoneCount = (Long) query.getOutputParameterValue( 2 ); assertEquals( Long.valueOf( 2 ), phoneCount ); } finally { entityManager.getTransaction().rollback(); entityManager.close(); } }
@Test public void testStoredProcedureOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_count_phones"); query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN); query.registerStoredProcedureParameter(2, Long.class, ParameterMode.OUT); query.setParameter(1, 1L); query.execute(); Long phoneCount = (Long) query.getOutputParameterValue(2); assertEquals(Long.valueOf(2), phoneCount); }); }
@Test public void testStoredProcedureOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_count_phones"); query.registerStoredProcedureParameter("personId", Long.class, ParameterMode.IN); query.registerStoredProcedureParameter("phoneCount", Long.class, ParameterMode.OUT); query.setParameter("personId", 1L); query.execute(); Long phoneCount = (Long) query.getOutputParameterValue("phoneCount"); assertEquals(Long.valueOf(2), phoneCount); } ); }
@Test @TestForIssue(jiraKey = "HHH-12138") public void testStoredProcedureRefCursor() { EntityManager entityManager = createEntityManager(); entityManager.getTransaction().begin(); try { StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_person_phones" ); 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(); assertNotNull( postComments ); } finally { entityManager.getTransaction().rollback(); entityManager.close(); } }
@Test public void testStoredProcedureOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-call-sp-out-mysql-example[] StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_count_phones"); query.registerStoredProcedureParameter( "personId", Long.class, ParameterMode.IN); query.registerStoredProcedureParameter( "phoneCount", Long.class, ParameterMode.OUT); query.setParameter("personId", 1L); query.execute(); Long phoneCount = (Long) query.getOutputParameterValue("phoneCount"); //end::sql-jpa-call-sp-out-mysql-example[] assertEquals(Long.valueOf(2), phoneCount); }); }
@Test public void testStoredProcedureViaJPA() { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_test.sp_square_number"); query.registerStoredProcedureParameter("inputNumber", Integer.class, ParameterMode.IN); query.registerStoredProcedureParameter("outputNumber", Integer.class, ParameterMode.OUT); query.setParameter("inputNumber", 7); query.execute(); int result = (int) query.getOutputParameterValue("outputNumber"); assertEquals( 49, result ); } ); } }
@Test public void testStoredProcedureRefCursor() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-call-sp-ref-cursor-oracle-example[] StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_person_phones" ); 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 ); }); }
@Test public void testStoredProcedureReturnValue() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-call-sp-no-out-mysql-example[] StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_phones"); query.registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN); query.setParameter(1, 1L); List<Object[]> personComments = query.getResultList(); //end::sql-jpa-call-sp-no-out-mysql-example[] assertEquals(2, personComments.size()); }); }
.registerStoredProcedureParameter( 1, NumericBooleanType.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR ) .setParameter( 1, true ); .registerStoredProcedureParameter( 1, YesNoType.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR ) .setParameter( 1, true );
@Test public void testStoredProcedureRefCursor() { doInJPA(entityManager -> { StoredProcedureQuery query = entityManager .createStoredProcedureQuery("post_comments") .registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR) .registerStoredProcedureParameter(2, Long.class, ParameterMode.IN) .setParameter(2, 1L); List<Object[]> postComments = query.getResultList(); assertEquals(2, postComments.size()); }); }
@Test public void testStoredProcedureOutParameter() { doInJPA(entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("count_comments"); query.registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN); query.registerStoredProcedureParameter("commentCount", Long.class, ParameterMode.OUT); query.setParameter("postId", 1L); query.execute(); Long commentCount = (Long) query.getOutputParameterValue("commentCount"); assertEquals(Long.valueOf(2), commentCount); }); }
@Test public void testStoredProcedureReturnValue() { doInJPA(entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("post_comments"); query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN); query.setParameter(1, 1L); List<Object[]> postComments = query.getResultList(); assertEquals(2, postComments.size()); }); }
@Test public void testSingleResultDynamicCall() throws Exception { inTransaction( entityManager -> { StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery( SIMPLE_VALUE_PROC ); storedProcedureQuery.registerStoredProcedureParameter( UNIQUE_VALUE_PROC_PARAM, Integer.class, ParameterMode.IN ); storedProcedureQuery.setParameter( UNIQUE_VALUE_PROC_PARAM, 1 ); Number singleResult = (Number) storedProcedureQuery.getSingleResult(); assertThat( singleResult.intValue() ).isEqualTo( 1 ); } ); }
@Test public void testExceptionWhenProcedureFails() throws Exception { inTransaction( entityManager -> { thrown.expect( PersistenceException.class ); thrown.expectMessage( "org.hibernate.HibernateException: OGM000092" ); StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery( EXCEPTIONAL_PROCEDURE_NAME, Integer.class ); storedProcedureQuery.registerStoredProcedureParameter( "param", Integer.class, ParameterMode.IN ); storedProcedureQuery.setParameter( "param", 1 ); storedProcedureQuery.getSingleResult(); } ); }