protected Output buildOutput() { if ( log.isDebugEnabled() ) { log.debugf( "Building Return [isResultSet=%s, updateCount=%s, extendedReturn=%s", isResultSet(), getUpdateCount(), hasExtendedReturns() ); } if ( isResultSet() ) { return buildResultSetOutput( extractCurrentResults() ); } else if ( getUpdateCount() >= 0 ) { return buildUpdateCountOutput( updateCount ); } else if ( hasExtendedReturns() ) { return buildExtendedReturn(); } throw new NoMoreReturnsException(); }
@Override public int getUpdateCount() { try { final Output rtn = outputs().getCurrent(); if ( rtn == null ) { return -1; } else if ( UpdateCountOutput.class.isInstance( rtn ) ) { return ( (UpdateCountOutput) rtn ).getUpdateCount(); } else { return -1; } } catch (NoMoreReturnsException e) { return -1; } catch (HibernateException he) { throw getExceptionConverter().convert( he ); } catch (RuntimeException e) { getProducer().markForRollbackOnly(); throw e; } }
@Override @SuppressWarnings("unchecked") public List<R> getResultList() { if ( getMaxResults() == 0 ) { return Collections.EMPTY_LIST; } try { final Output rtn = outputs().getCurrent(); if ( ! ResultSetOutput.class.isInstance( rtn ) ) { throw new IllegalStateException( "Current CallableStatement ou was not a ResultSet, but getResultList was called" ); } return ( (ResultSetOutput) rtn ).getResultList(); } catch (NoMoreReturnsException e) { // todo : the spec is completely silent on these type of edge-case scenarios. // Essentially here we'd have a case where there are no more results (ResultSets nor updateCount) but // getResultList was called. return null; } catch (HibernateException he) { throw getExceptionConverter().convert( he ); } catch (RuntimeException e) { getProducer().markForRollbackOnly(); throw e; } }
protected Output buildOutput() { if ( log.isDebugEnabled() ) { log.debugf( "Building Return [isResultSet=%s, updateCount=%s, extendedReturn=%s", isResultSet(), getUpdateCount(), hasExtendedReturns() ); } if ( isResultSet() ) { return buildResultSetOutput( extractCurrentResults( jdbcStatement ) ); } else if ( getUpdateCount() >= 0 ) { return buildUpdateCountOutput( updateCount ); } else if ( hasExtendedReturns() ) { return buildExtendedReturn(); } throw new NoMoreOutputsException(); }
@Test public void testHibernateProcedureCallRefCursor() { doInJPA(entityManager -> { Session session = entityManager.unwrap(Session.class); ProcedureCall call = session.createStoredProcedureCall("post_comments"); call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L); call.registerParameter(2, Class.class, ParameterMode.REF_CURSOR); Output output = call.getOutputs().getCurrent(); if (output.isResultSet()) { List<Object[]> postComments = ((ResultSetOutput) output).getResultList(); assertEquals(2, postComments.size()); } }); }
@Test public void baseTest() { Session session = openSession(); session.beginTransaction(); ProcedureCall procedureCall = session.createStoredProcedureCall( "user"); ProcedureOutputs procedureOutputs = procedureCall.getOutputs(); Output currentOutput = procedureOutputs.getCurrent(); assertNotNull( currentOutput ); ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); String name = (String) resultSetReturn.getSingleResult(); assertEquals( "SA", name ); session.getTransaction().commit(); session.close(); }
public boolean isResultSet() { return getCurrent().isResultSet(); } }
@Test @TestForIssue(jiraKey = "HHH-12138") public void testHibernateProcedureCallRefCursor() { EntityManager entityManager = createEntityManager(); entityManager.getTransaction().begin(); try { Session session = entityManager.unwrap( Session.class ); ProcedureCall call = session.createStoredProcedureCall( "sp_person_phones" ); call.registerParameter( 1, Long.class, ParameterMode.IN ).bindValue( 1L ); call.registerParameter( 2, Class.class, ParameterMode.REF_CURSOR ); Output output = call.getOutputs().getCurrent(); List<Object[]> postComments = ( (ResultSetOutput) output ).getResultList(); assertEquals( 2, postComments.size() ); } finally { entityManager.getTransaction().rollback(); entityManager.close(); } }
@Test public void testGetSingleResultTuple() { Session session = openSession(); session.beginTransaction(); ProcedureCall query = session.createStoredProcedureCall( "findOneUser" ); ProcedureOutputs procedureResult = query.getOutputs(); Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); Object result = resultSetReturn.getSingleResult(); assertTyping( Object[].class, result ); String name = (String) ( (Object[]) result )[1]; assertEquals( "Steve", name ); session.getTransaction().commit(); session.close(); }
@Test public void testPartialResults() { Configuration cfg = new Configuration() .addAnnotatedClass( Employee.class ) .setProperty( AvailableSettings.HBM2DDL_AUTO, "create-drop" ); cfg.addAuxiliaryDatabaseObject( new ProcedureDefinition() ); SessionFactory sf = cfg.buildSessionFactory(); try { Session session = sf.openSession(); session.beginTransaction(); ProcedureCall call = session.createStoredProcedureCall( "allEmployeeNames", "id-fname-lname" ); ProcedureOutputs outputs = call.getOutputs(); ResultSetOutput output = assertTyping( ResultSetOutput.class, outputs.getCurrent() ); assertEquals( 3, output.getResultList().size() ); assertTyping( Employee.class, output.getResultList().get( 0 ) ); session.getTransaction().commit(); session.close(); } finally { sf.close(); } } }
@Test public void testMappingAllFields() { inTransaction( session -> { final ProcedureCall call = session.createStoredProcedureCall( "findOneUser", "all-fields" ); final ProcedureOutputs procedureResult = call.getOutputs(); final Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); final ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); final Object result = resultSetReturn.getSingleResult(); assertTyping( H2ProcTesting.MyEntity.class, result ); assertEquals( "Steve", ( (H2ProcTesting.MyEntity) result ).name ); } ); }
@Test public void testHibernateProcedureCallReturnValueParameter() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-hibernate-call-sp-no-out-mysql-example[] Session session = entityManager.unwrap( Session.class ); ProcedureCall call = session.createStoredProcedureCall( "sp_phones" ); call.registerParameter( 1, Long.class, ParameterMode.IN ).bindValue( 1L ); Output output = call.getOutputs().getCurrent(); List<Object[]> personComments = ( (ResultSetOutput) output ).getResultList(); //end::sql-hibernate-call-sp-no-out-mysql-example[] assertEquals( 2, personComments.size() ); }); }
@Test public void testMappingSomeFields() { inTransaction( session -> { final ProcedureCall call = session.createStoredProcedureCall( "findOneUser", "some-fields" ); final ProcedureOutputs procedureResult = call.getOutputs(); final Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); final ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); final Object result = resultSetReturn.getSingleResult(); assertTyping( H2ProcTesting.MyEntity.class, result ); assertEquals( "Steve", ( (H2ProcTesting.MyEntity) result ).name ); } ); }
assertNotNull( currentOutput ); ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); List results = resultSetReturn.getResultList(); assertEquals( 3, results.size() );
@Test public void testMappingNoFields() { inTransaction( session -> { final ProcedureCall call = session.createStoredProcedureCall( "findOneUser", "no-fields" ); final ProcedureOutputs procedureResult = call.getOutputs(); final Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); final ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); final Object result = resultSetReturn.getSingleResult(); assertTyping( H2ProcTesting.MyEntity.class, result ); assertEquals( "Steve", ( (H2ProcTesting.MyEntity) result ).name ); } ); } }
@Test public void testInParametersByName() { Session session = openSession(); session.beginTransaction(); ProcedureCall query = session.createStoredProcedureCall( "findUserRange" ); query.registerParameter( "start", Integer.class, ParameterMode.IN ).bindValue( 1 ); query.registerParameter( "end", Integer.class, ParameterMode.IN ).bindValue( 2 ); ProcedureOutputs procedureResult = query.getOutputs(); Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); List results = resultSetReturn.getResultList(); assertEquals( 1, results.size() ); Object result = results.get( 0 ); assertTyping( Object[].class, result ); Integer id = (Integer) ( (Object[]) result )[0]; String name = (String) ( (Object[]) result )[1]; assertEquals( 1, (int) id ); assertEquals( "User 1", name ); session.getTransaction().commit(); session.close(); }
@Test public void testResultClass() { inTransaction( session -> { final ProcedureCall call = session.createStoredProcedureCall( "findOneUser", H2ProcTesting.MyEntity.class ); final ProcedureOutputs procedureResult = call.getOutputs(); final Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); final ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); final Object result = resultSetReturn.getSingleResult(); assertTyping( H2ProcTesting.MyEntity.class, result ); assertEquals( "Steve", ( (H2ProcTesting.MyEntity) result ).name ); } ); }
@Test public void testHibernateProcedureCallRefCursor() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-hibernate-call-sp-ref-cursor-oracle-example[] Session session = entityManager.unwrap(Session.class); ProcedureCall call = session.createStoredProcedureCall( "sp_person_phones"); call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L); call.registerParameter(2, Class.class, ParameterMode.REF_CURSOR); Output output = call.getOutputs().getCurrent(); List<Object[]> postComments = ( (ResultSetOutput) output ).getResultList(); assertEquals(2, postComments.size()); //end::sql-hibernate-call-sp-ref-cursor-oracle-example[] }); }
@Test public void testInParametersByPosition() { Session session = openSession(); session.beginTransaction(); ProcedureCall query = session.createStoredProcedureCall( "findUserRange" ); query.registerParameter( 1, Integer.class, ParameterMode.IN ) .bindValue( 1 ); query.registerParameter( 2, Integer.class, ParameterMode.IN ) .bindValue( 2 ); ProcedureOutputs procedureResult = query.getOutputs(); Output currentOutput = procedureResult.getCurrent(); assertNotNull( currentOutput ); ResultSetOutput resultSetReturn = assertTyping( ResultSetOutput.class, currentOutput ); List results = resultSetReturn.getResultList(); assertEquals( 1, results.size() ); Object result = results.get( 0 ); assertTyping( Object[].class, result ); Integer id = (Integer) ( (Object[]) result )[0]; String name = (String) ( (Object[]) result )[1]; assertEquals( 1, (int) id ); assertEquals( "User 1", name ); session.getTransaction().commit(); session.close(); }
// Call the stored procedure ProcedureCall procedureCall = getSession().createStoredProcedureCall("get_all_matching_email_profile"); // Register the output parameter, postgres support ref_cursor as first parameter only, so don't change it procedureCall.registerParameter(1, void.class, ParameterMode.REF_CURSOR); // Inpur parameters procedureCall.registerParameter(2, BigDecimal.class, ParameterMode.IN); procedureCall.registerParameter(3, String.class, ParameterMode.IN); procedureCall.getParameterRegistration(2).bindValue(companyId); procedureCall.getParameterRegistration(3).bindValue(tempArray[i]); // Execute the procedure and get the result ProcedureOutputs procedureOutputs = procedureCall.getOutputs(); ResultSetOutput resultSetOutput = (ResultSetOutput)procedureOutputs.getCurrent(); List results = resultSetOutput.getResultList(); log.info("The result is "+results.size()); for(Integer j=0;j<results.size();j++) { Object[] objects = (Object[]) results.get(j); log.info("The result is "+objects.length); }