@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName) { return em.createStoredProcedureQuery(procedureName); }
@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class[] resultClasses) { return em.createStoredProcedureQuery(procedureName, resultClasses); }
@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName, String... resultSetMappings) { return em.createStoredProcedureQuery(procedureName, resultSetMappings); }
@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName, String... resultSetMappings) { return em().createStoredProcedureQuery(procedureName, resultSetMappings); }
@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class... resultClasses) { return em().createStoredProcedureQuery(procedureName, resultClasses); }
@Override public StoredProcedureQuery createStoredProcedureQuery(String procedureName) { return em().createStoredProcedureQuery(procedureName); }
@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()); }); }
StoredProcedureQuery query = mock(StoredProcedureQuery.class); given(emf.createEntityManager()).willReturn(targetEm); given(targetEm.createStoredProcedureQuery("x")).willReturn(query); willReturn("y").given(query).getOutputParameterValue("a"); willReturn("z").given(query).getOutputParameterValue("c"); StoredProcedureQuery spq = em.createStoredProcedureQuery("x"); spq.registerStoredProcedureParameter("a", String.class, ParameterMode.OUT); spq.registerStoredProcedureParameter("b", Number.class, ParameterMode.IN);
StoredProcedureQuery query = mock(StoredProcedureQuery.class); given(emf.createEntityManager()).willReturn(targetEm); given(targetEm.createStoredProcedureQuery("x")).willReturn(query); willReturn("y").given(query).getOutputParameterValue(0); willReturn("z").given(query).getOutputParameterValue(2); StoredProcedureQuery spq = em.createStoredProcedureQuery("x"); spq.registerStoredProcedureParameter(0, String.class, ParameterMode.OUT); spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
@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); }); }
String procedureName = procedureAttributes.getProcedureName(); StoredProcedureQuery procedureQuery = getEntityManager().createStoredProcedureQuery(procedureName);
@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 ); } ); } }
StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_phone_validity" ) .registerStoredProcedureParameter( 1, NumericBooleanType.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR ) StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_votes" ) .registerStoredProcedureParameter( 1, YesNoType.class, ParameterMode.IN ) .registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR )
@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 ); }); }