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() { 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 @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 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()); }); }
given(emf.createEntityManager()).willReturn(targetEm); given(targetEm.createStoredProcedureQuery("x")).willReturn(query); willReturn("y").given(query).getOutputParameterValue("a"); willReturn("z").given(query).getOutputParameterValue("c"); given(targetEm.isOpen()).willReturn(true); 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")); try { spq.getOutputParameterValue("b"); fail("Should have thrown IllegalArgumentException"); assertEquals("z", spq.getOutputParameterValue("c")); 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(); verifyNoMoreInteractions(query);
@Test public void testFunction() { try { doInJPA(entityManager -> { StoredProcedureQuery query = entityManager.createStoredProcedureQuery("fn_count_comments"); query.registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN); query.setParameter("postId", 1L); Long commentCount = (Long) query.getSingleResult(); assertEquals(Long.valueOf(2), commentCount); }); } catch (Exception e) { assertTrue(Pattern.compile("PROCEDURE high_performance_java_persistence.fn_count_comments does not exist").matcher(e.getCause().getCause().getMessage()).matches()); } }
@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 ); }); }
Object key = entry.getKey(); if (key instanceof Integer) { entry.setValue(storedProc.getOutputParameterValue((Integer) key)); entry.setValue(storedProc.getOutputParameterValue(key.toString()));
StoredProcedureQuery query = entityManager .createStoredProcedureQuery("count_comments") .registerStoredProcedureParameter( "postId", Long.class, ParameterMode.IN) .registerStoredProcedureParameter( "commentCount", Long.class, ParameterMode.OUT) .setParameter("postId", 1L); query.execute(); Long commentCount = (Long) query .getOutputParameterValue("commentCount");
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();
StoredProcedureQuery proc = em.createNamedStoredProcedureQuery("plus1"); proc.setParameter("arg", 1); proc.execute(); Integer res1 = (Integer) proc.getOutputParameterValue("res1"); Integer res2 = (Integer) proc.getOutputParameterValue("res2"); ...
StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("SPGetChart"); query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN); query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN); query.setParameter(1, idValue); query.setParameter(2, nameChart); query.execute();
public List<Account> getAccountFromStoreProcedure(EntityManager em, String routeCode, String round) { StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("getAccountFunction"); // set parameters storedProcedure.registerStoredProcedureParameter("in_route", String.class, ParameterMode.IN); storedProcedure.registerStoredProcedureParameter("in_round", String.class, ParameterMode.IN); storedProcedure.setParameter( "in_route",routeCode); storedProcedure.setParameter( "in_round",round); storedProcedure.executeUpdate(); List<Object> objectList = storedProcedure.getResultList(); for (int i = 0; i< objectList.size(); i++) { Account currAccount = new Account ((Object[]) objectList.get(i)); tmpList.add(currAccount); System.out.println("currAccount : " + currAccount.toString()); } }
@Test public void testResultSetDynamicCallWithResultMapping() throws Exception { inTransaction( entityManager -> { StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery( RESULT_SET_PROC, "carMapping" ); storedProcedureQuery.registerStoredProcedureParameter( 0, Void.class, ParameterMode.REF_CURSOR ); storedProcedureQuery.registerStoredProcedureParameter( 1, Integer.class, ParameterMode.IN ); storedProcedureQuery.registerStoredProcedureParameter( 2, String.class, ParameterMode.IN ); storedProcedureQuery.setParameter( 1, 1 ); Parameter<String> p2 = storedProcedureQuery.getParameter( 2, String.class ); storedProcedureQuery.setParameter( p2, "title'1" ); @SuppressWarnings("unchecked") List<Car> listResult = storedProcedureQuery.getResultList(); assertThat( listResult ).containsOnly( new Car( 1, "title'1" ) ); } ); }
@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 ); } ); }
@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); } }
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);
@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 StoredProcedureQuery setParameter(int position, Calendar value, TemporalType temporalType) { storedProcedureQuery.setParameter(position, value, temporalType); return this; }
@Override public Object getSingleResult() { Object singleResult = storedProcedureQuery.getSingleResult(); em.clear(); return singleResult; }