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()); } }
@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 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 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 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()); }); }
@Test public void testStoredProcedureRefCursorUsingNamedQuery() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-call-sp-ref-cursor-oracle-named-query-example[] List<Object[]> postComments = entityManager .createNamedStoredProcedureQuery( "sp_person_phones" ) .setParameter( "personId", 1L ) .getResultList(); //end::sql-jpa-call-sp-ref-cursor-oracle-named-query-example[] assertNotNull( postComments ); }); }
@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 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 ); } ); } }
.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( 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 ); }); }
@Override public StoredProcedureQuery setParameter(String name, Date value, TemporalType temporalType) { storedProcedureQuery.setParameter(name, value, temporalType); return this; }
@Override public StoredProcedureQuery setParameter(int position, Object value) { storedProcedureQuery.setParameter(position, value); return this; }
StoredProcedureQuery proc = em.createNamedStoredProcedureQuery("plus1"); proc.setParameter("arg", 1); proc.execute(); Integer res1 = (Integer) proc.getOutputParameterValue("res1"); Integer res2 = (Integer) proc.getOutputParameterValue("res2"); ...
@Test public void testResultSetStaticCallWithResultMapping() throws Exception { inTransaction( entityManager -> { StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery( "returnNamedParametersWithMapping" ); storedProcedureQuery.setParameter( RESULT_SET_PROC_ID_PARAM, 2 ); storedProcedureQuery.setParameter( RESULT_SET_PROC_TITLE_PARAM, "title'2" ); @SuppressWarnings("unchecked") List<Car> listResult = storedProcedureQuery.getResultList(); assertThat( listResult ).containsExactly( new Car( 2, "title'2" ) ); } ); }
@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 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(); } ); }