@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 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 ); }); }
List phones = query.getResultList(); assertEquals( 1, phones.size() ); assertEquals( "123-456-7890", phones.get( 0 ) ); List votes = query.getResultList(); assertEquals( 1, votes.size() ); assertEquals( 1, ( (Number) votes.get( 0 ) ).intValue() );
@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 ); }); }
@SqlResultSetMapping(name = "CalendarsMapping", classes = { @ConstructorResult(targetClass = Calendar.class, columns = { @ColumnResult(name = "OutDate"), @ColumnResult(name = "CategoryID", type = Long.class), @ColumnResult(name = "CategoryName"), @ColumnResult(name = "DepositDate"), @ColumnResult(name = "EventDate"), @ColumnResult(name = "EventType"), @ColumnResult(name = "Status"), @ColumnResult(name = "Effective", type = String.class), @ColumnResult(name = "Indicator", type = String.class), @ColumnResult(name = "TermDate"), @ColumnResult(name = "TID"), @ColumnResult(name = "TName"), @ColumnResult(name = "Series"), @ColumnResult(name = "Symbol"), @ColumnResult(name = "TType", type = String.class) }) }) StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery( "GetDepAndTerm", "CalendarsMapping"); procedureQuery.getResultList();
@Override public List getResultList() { List resultList = storedProcedureQuery.getResultList(); em.clear(); return resultList; }
@Override public List getResultList() { try { return underlyingStoredProcedureQuery.getResultList(); } finally { underlyingEntityManager.clear(); } }
@Override public List getResultList() { List resultList = storedProcedureQuery.getResultList(); em.clear(); return resultList; }
@Override public List getResultList() { try { return underlyingStoredProcedureQuery.getResultList(); } finally { underlyingEntityManager.clear(); } }
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("my_procedure") .registerStoredProcedureParameter(1, String.class, ParameterMode.IN) .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR) .setParameter(1, 1L); query.execute(); List<Object[]> result = query.getResultList();
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("post_comments"); query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN); query.setParameter(1, 1L); List<Object[]> postComments = query.getResultList();
@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 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 testStoredProcedureRefCursor() { doInJPA(entityManager -> { StoredProcedureQuery query = entityManager .createStoredProcedureQuery("post_comments") .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN) .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR) .setParameter(1, 1L); query.execute(); List<Object[]> postComments = query.getResultList(); assertEquals(2, postComments.size()); }); }
@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 testResultSetStaticCallWithResultClass() throws Exception { inTransaction( entityManager -> { StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery( "returnNamedParametersWithEntity" ); storedProcedureQuery.setParameter( RESULT_SET_PROC_ID_PARAM, 1 ); storedProcedureQuery.setParameter( RESULT_SET_PROC_TITLE_PARAM, "title" ); @SuppressWarnings("unchecked") List<Car> listResult = storedProcedureQuery.getResultList(); assertThat( listResult ).containsExactly( new Car( 1, "title" ) ); } ); }
@Test public void testResultSetStaticCallWithResultClass() throws Exception { inTransaction( entityManager -> { StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery( "returnPositionalParametersWithEntity" ); // First parameter is void storedProcedureQuery.setParameter( 2, 1 ); storedProcedureQuery.setParameter( 3, "title" ); @SuppressWarnings("unchecked") List<Car> listResult = storedProcedureQuery.getResultList(); assertThat( listResult ).containsOnly( new Car( 1, "title" ) ); } ); }