@Test public void testExplicitClassReturn() { Session session = sf.openSession(); session.beginTransaction(); ProcedureCall call = session.createStoredProcedureCall( "all_items", Item.class ); call.registerParameter( 1, void.class, ParameterMode.REF_CURSOR ); ProcedureOutputs outputs = call.getOutputs(); ResultSetOutput results = assertTyping( ResultSetOutput.class, outputs.getCurrent() ); session.getTransaction().commit(); session.close(); } }
@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 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(); }
@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(); }
ProcedureCall query = session.createStoredProcedureCall( "findUserRange" ); query.registerParameter( 1, Integer.class, ParameterMode.IN ).enablePassingNulls( true ); query.registerParameter( 2, Integer.class, ParameterMode.IN ).bindValue( 2 );
ProcedureCall query = session.createStoredProcedureCall( "findUserRange" ); query.registerParameter( 1, Integer.class, ParameterMode.IN ); query.registerParameter( 2, Integer.class, ParameterMode.IN ).bindValue( 2 );
session.beginTransaction(); ProcedureCall query = session.createStoredProcedureCall( "findUsers" ); ProcedureOutputs procedureResult = query.getOutputs(); Output currentOutput = procedureResult.getCurrent();
@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 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 testHibernateProcedureCallOutParameter() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-hibernate-call-sp-out-mysql-example[] Session session = entityManager.unwrap( Session.class ); ProcedureCall call = session.createStoredProcedureCall( "sp_count_phones" ); call.registerParameter( "personId", Long.class, ParameterMode.IN ).bindValue( 1L ); call.registerParameter( "phoneCount", Long.class, ParameterMode.OUT ); Long phoneCount = (Long) call.getOutputs().getOutputParameterValue( "phoneCount" ); assertEquals( Long.valueOf( 2 ), phoneCount ); //end::sql-hibernate-call-sp-out-mysql-example[] }); }
@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 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(); }
@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[] }); }
/** * Creates a call to a stored procedure with specific result set entity mappings. Each class * named is considered a "root return". * * @param procedureName The name of the procedure. * @param resultClasses The entity(s) to map the result on to. * @return The representation of the procedure call. */ public ProcedureCall createStoredProcedureCall(String procedureName, Class... resultClasses) { return getSession().createStoredProcedureCall(procedureName, resultClasses); }
/** * Creates a call to a stored procedure. * * @param procedureName The name of the procedure. * @return The representation of the procedure call. */ public ProcedureCall createStoredProcedureCall(String procedureName) { return getSession().createStoredProcedureCall(procedureName); }
/** * Creates a call to a stored procedure with specific result set entity mappings. * * @param procedureName The name of the procedure. * @param resultSetMappings The explicit result set mapping(s) to use for mapping the results * @return The representation of the procedure call. */ public ProcedureCall createStoredProcedureCall(String procedureName, String... resultSetMappings) { return getSession().createStoredProcedureCall(procedureName, resultSetMappings); }
@Override public ProcedureCall createStoredProcedureCall(final String procedureName, final String... resultSetMappings) { return getWrappedSession().createStoredProcedureCall(procedureName, resultSetMappings); }
public ProcedureCall createStoredProcedureCall(String procedureName, Class... resultClasses) { initSession(); return session.createStoredProcedureCall(procedureName, resultClasses); }
@Test public void testHibernateProcedureCallOutParameter() { doInJPA(entityManager -> { Session session = entityManager.unwrap(Session.class); ProcedureCall call = session.createStoredProcedureCall("count_comments"); call.registerParameter("postId", Long.class, ParameterMode.IN).bindValue(1L); call.registerParameter("commentCount", Long.class, ParameterMode.OUT); Long commentCount = (Long) call.getOutputs().getOutputParameterValue("commentCount"); assertEquals(Long.valueOf(2), commentCount); }); }
@Test public void testHibernateProcedureCallReturnValueParameter() { doInJPA(entityManager -> { Session session = entityManager.unwrap(Session.class); ProcedureCall call = session.createStoredProcedureCall("post_comments"); call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L); Output output = call.getOutputs().getCurrent(); if (output.isResultSet()) { List<Object[]> postComments = ((ResultSetOutput) output).getResultList(); assertEquals(2, postComments.size()); } }); }