@Override protected Object doExecute(AbstractJpaQuery jpaQuery, Object[] values) { Assert.isInstanceOf(StoredProcedureJpaQuery.class, jpaQuery); StoredProcedureJpaQuery storedProcedureJpaQuery = (StoredProcedureJpaQuery) jpaQuery; StoredProcedureQuery storedProcedure = storedProcedureJpaQuery.createQuery(values); storedProcedure.execute(); return storedProcedureJpaQuery.extractOutputValue(storedProcedure); } }
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 @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(); } }
spq.registerStoredProcedureParameter("b", Number.class, ParameterMode.IN); spq.registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT); spq.execute(); assertEquals("y", spq.getOutputParameterValue("a")); try { verify(query).registerStoredProcedureParameter("b", Number.class, ParameterMode.IN); verify(query).registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT); verify(query).execute(); verify(targetEm).close(); verifyNoMoreInteractions(query);
spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN); spq.registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT); spq.execute(); assertEquals("y", spq.getOutputParameterValue(0)); try { verify(query).registerStoredProcedureParameter(1, Number.class, ParameterMode.IN); verify(query).registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT); verify(query).execute(); verify(targetEm).close(); verifyNoMoreInteractions(query);
@Test @TestForIssue(jiraKey = "HHH-12138") public void testOutAndSysRefCursorAsOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery function = entityManager.createNamedStoredProcedureQuery( "outAndRefCursor" ); function.execute(); Integer value = (Integer) function.getSingleResult(); assertEquals( Integer.valueOf( 1 ), value ); assertEquals( Integer.valueOf( 1 ), function.getOutputParameterValue( 1 ) ); assertFalse( function.hasMoreResults() ); } ); }
@Test @TestForIssue(jiraKey = "HHH-12138") public void testSysRefCursorAsOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { StoredProcedureQuery function = entityManager.createNamedStoredProcedureQuery( "singleRefCursor" ); function.execute(); Integer value = (Integer) function.getSingleResult(); assertFalse( function.hasMoreResults() ); assertEquals( Integer.valueOf( 1 ), value ); } ); }
@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 -> { 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 -> { //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 ); }); }
@Override protected Object doExecute(AbstractJpaQuery jpaQuery, Object[] values) { Assert.isInstanceOf(StoredProcedureJpaQuery.class, jpaQuery); StoredProcedureJpaQuery storedProcedureJpaQuery = (StoredProcedureJpaQuery) jpaQuery; StoredProcedureQuery storedProcedure = storedProcedureJpaQuery.createQuery(values); storedProcedure.execute(); return storedProcedureJpaQuery.extractOutputValue(storedProcedure); } }
StoredProcedureQuery proc = em.createNamedStoredProcedureQuery("plus1"); proc.setParameter("arg", 1); proc.execute(); Integer res1 = (Integer) proc.getOutputParameterValue("res1"); Integer res2 = (Integer) proc.getOutputParameterValue("res2"); ...
@Override public String compactFeedProcessorStats() { StoredProcedureQuery query = em.createStoredProcedureQuery("compact_feed_processor_stats"); query.registerStoredProcedureParameter("res", String.class, ParameterMode.OUT); query.execute(); String result = (String) query.getOutputParameterValue("res"); return result; } }
StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("mypackage.myprocedure"); query.registerStoredProcedureParameter("i_input_1", String.class, ParameterMode.IN); query.registerStoredProcedureParameter("o_output_1", String.class, ParameterMode.OUT); query.setParameter("i_input_1", "valueOf_i_input_1"); boolean queryResult = query.execute(); String result = String.valueOf(query.getOutputParameterValue("o_output_1"));
@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); }); }