public ProcedureParameterImpl(
		ProcedureCallImpl procedureCall,
		String name,
		ParameterMode mode,
		Class<T> javaType,
		Type hibernateType,
		boolean initialPassNullsSetting) {
	super( hibernateType );
	this.procedureCall = procedureCall;
	this.name = name;
	this.position = null;
	this.mode = mode;
	this.javaType = javaType;
	this.passNullsEnabled = initialPassNullsSetting;

	setHibernateType( hibernateType );
}
 

public ProcedureParameterImpl(
		ProcedureCallImpl procedureCall,
		Integer position,
		ParameterMode mode,
		Class<T> javaType,
		Type hibernateType,
		boolean initialPassNullsSetting) {
	super( hibernateType );
	this.procedureCall = procedureCall;
	this.name = null;
	this.position = position;
	this.mode = mode;
	this.javaType = javaType;
	this.passNullsEnabled = initialPassNullsSetting;

	setHibernateType( hibernateType );
}
 

@Override
public void setHibernateType(Type expectedType) {
	super.setHibernateType( expectedType );

	if ( mode == ParameterMode.REF_CURSOR ) {
		sqlTypes = new int[] { Types.REF_CURSOR };
	}
	else {
		if ( expectedType == null ) {
			throw new IllegalArgumentException( "Type cannot be null" );
		}
		else {
			sqlTypes = expectedType.sqlTypes( procedureCall.getSession().getFactory() );
		}
	}

}
 

@Override
@SuppressWarnings("unchecked")
public <T> ParameterRegistration<T> registerParameter(String name, Class<T> type, ParameterMode mode) {
	final ProcedureParameterImpl parameter = new ProcedureParameterImpl(
			this,
			name,
			mode,
			type,
			getSession().getFactory().getTypeResolver().heuristicType( type.getName() ),
			globalParameterPassNullsSetting
	);

	registerParameter( parameter );

	return parameter;
}
 

@Override
@SuppressWarnings("unchecked")
public ProcedureCallImplementor<R> registerStoredProcedureParameter(int position, Class type, ParameterMode mode) {
	getProducer().checkOpen( true );

	try {
		registerParameter( position, type, mode );
	}
	catch (HibernateException he) {
		throw getExceptionConverter().convert( he );
	}
	catch (RuntimeException e) {
		getProducer().markForRollbackOnly();
		throw e;
	}

	return this;
}
 

@Override
@SuppressWarnings("unchecked")
public ProcedureCallImplementor<R> registerStoredProcedureParameter(String parameterName, Class type, ParameterMode mode) {
	getProducer().checkOpen( true );
	try {
		registerParameter( parameterName, type, mode );
	}
	catch (HibernateException he) {
		throw getExceptionConverter().convert( he );
	}
	catch (RuntimeException e) {
		getProducer().markForRollbackOnly();
		throw e;
	}

	return this;
}
 

private void internalSetValue(T value) {
	if ( procedureParameter.getMode() != ParameterMode.IN && procedureParameter.getMode() != ParameterMode.INOUT ) {
		throw new IllegalStateException( "Can only bind values for IN/INOUT parameters : " + procedureParameter );
	}

	if ( procedureParameter.getParameterType() != null ) {
		if ( value == null ) {
			if ( !procedureParameter.isPassNullsEnabled() ) {
				throw new IllegalArgumentException( "The parameter " +
						( procedureParameter.getName() != null
								? "named [" + procedureParameter.getName() + "]"
								: "at position [" + procedureParameter.getPosition() + "]" )
						+ " was null. You need to call ParameterRegistration#enablePassingNulls(true) in order to pass null parameters." );
			}
		}
		else if ( !procedureParameter.getParameterType().isInstance( value ) &&
				!procedureParameter.getHibernateType().getReturnedClass().isInstance( value ) ) {
			throw new IllegalArgumentException( "Bind value [" + value + "] was not of specified type [" + procedureParameter
					.getParameterType() );
		}
	}

	this.value = value;
	this.isBound = true;
}
 

@Test
public void calculate() {
	log.info("... calculate ...");
	EntityManager em = emf.createEntityManager();
       em.getTransaction().begin();
       
	// define the stored procedure
	StoredProcedureQuery query = em.createStoredProcedureQuery("calculate");
	query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);
	query.registerStoredProcedureParameter("y", Double.class, ParameterMode.IN);
	query.registerStoredProcedureParameter("sum", Double.class, ParameterMode.OUT);
	
	// set input parameter
	query.setParameter("x", 1.23d);
	query.setParameter("y", 4d);
	
	// call the stored procedure and get the result
	query.execute();
	Double sum = (Double) query.getOutputParameterValue("sum");
	log.info("Calculation result: 1.23 + 4 = " + sum);

       em.getTransaction().commit();
       em.close();
}
 

@Test
public void testProcedureCallMultipleOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("getStatistics")
            .registerStoredProcedureParameter(
                    "A", Long.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(
                    "B", Long.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(
                    "C", Long.class, ParameterMode.OUT);

        query.execute();

        Long a = (Long) query
                .getOutputParameterValue("A");
        Long b = (Long) query
                .getOutputParameterValue("B");
        Long c = (Long) query
                .getOutputParameterValue("C");
    });
}
 

@Test
public void testStoredProcedureRefCursor() {
    try {
        doInJPA(entityManager -> {
            StoredProcedureQuery query = entityManager.createStoredProcedureQuery("post_comments");
            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
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 testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        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");
        assertEquals(Long.valueOf(2), commentCount);
    });
}
 

@Test
public void testStoredProcedureRefCursor() {
    try {
        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();
            assertNotNull(postComments);
        });
    } catch (Exception e) {
        assertTrue(Pattern.compile("Dialect .*? not known to support REF_CURSOR parameters").matcher(e.getCause().getMessage()).matches());
    }
}
 

@Test
public void testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        try {
            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");
            assertEquals(Long.valueOf(2), commentCount);

            ProcedureOutputs procedureOutputs = query.unwrap(ProcedureOutputs.class);
            CallableStatement callableStatement = ReflectionUtils.getFieldValue(procedureOutputs, "callableStatement");
            assertFalse(callableStatement.isClosed());

            procedureOutputs.release();

            assertTrue(callableStatement.isClosed());
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    });
}
 

@Test
public void testStoredProcedureOutParameterCloseStatement() {
    doInJPA(entityManager -> {
        try {
            StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("count_comments")
            .registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter("commentCount", Long.class, ParameterMode.OUT)
            .setParameter("postId", 1L);

            try {
                query.execute();
                Long commentCount = (Long) query.getOutputParameterValue("commentCount");

                assertEquals(Long.valueOf(2), commentCount);
            } finally {
                query.unwrap(ProcedureOutputs.class).release();
            }

            CallableStatement callableStatement = ReflectionUtils.getFieldValue(query.unwrap(ProcedureOutputs.class), "callableStatement");
            assertTrue(callableStatement.isClosed());
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    });
}
 

@Test
public void testHibernateProcedureCallRefCursor() {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session
            .createStoredProcedureCall("post_comments");
        call.registerParameter(1, void.class, ParameterMode.REF_CURSOR);
        call.registerParameter(2, Long.class, ParameterMode.IN).bindValue(1L);

        Output output = call.getOutputs().getCurrent();
        if (output.isResultSet()) {
            List<Object[]> postComments = ((ResultSetOutput) output).getResultList();
            assertEquals(2, postComments.size());
        }
    });
}
 

@Test
public void deferredStoredProcedureQueryWithIndexedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	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);
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	spq.registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	spq.registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	spq.execute();
	assertEquals("y", spq.getOutputParameterValue(0));
	try {
		spq.getOutputParameterValue(1);
		fail("Should have thrown IllegalArgumentException");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	assertEquals("z", spq.getOutputParameterValue(2));

	verify(query).registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	verify(query).registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	verify(query).registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	verify(query).execute();
	verify(targetEm).close();
	verifyNoMoreInteractions(query);
	verifyNoMoreInteractions(targetEm);
}
 

@Test
public void deferredStoredProcedureQueryWithNamedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	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");
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	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");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	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);
	verifyNoMoreInteractions(targetEm);
}
 

@Test
public void deferredStoredProcedureQueryWithIndexedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	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);
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	spq.registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	spq.registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	spq.execute();
	assertEquals("y", spq.getOutputParameterValue(0));
	try {
		spq.getOutputParameterValue(1);
		fail("Should have thrown IllegalArgumentException");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	assertEquals("z", spq.getOutputParameterValue(2));

	verify(query).registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	verify(query).registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	verify(query).registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	verify(query).execute();
	verify(targetEm).close();
	verifyNoMoreInteractions(query);
	verifyNoMoreInteractions(targetEm);
}
 

@Test
public void deferredStoredProcedureQueryWithNamedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	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");
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	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");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	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);
	verifyNoMoreInteractions(targetEm);
}
 

@Override
public int[] getSqlTypes() {
	if ( mode == ParameterMode.REF_CURSOR ) {
		// we could use the Types#REF_CURSOR added in Java 8, but that would require requiring Java 8...
		throw new IllegalStateException( "REF_CURSOR parameters do not have a SQL/JDBC type" );
	}

	return determineHibernateType().sqlTypes( procedureCall.getSession().getFactory() );
}
 

@Override
public void verifyParametersBound(boolean callable) {
	parameterMetadata.visitRegistrations(
			queryParameter -> {
				final ProcedureParameterImplementor procParam = (ProcedureParameterImplementor) queryParameter;
				if ( procParam.getMode() == ParameterMode.IN
						|| procParam.getMode() == ParameterMode.INOUT ) {
					if ( !getBinding( procParam ).isBound() ) {
						// depending on "pass nulls" this might be ok...
						//  for now, just log a warning
					}
				}
			}
	);
}
 

@Override
public void registerParameters(
		String procedureName,
		CallableStatement statement,
		ParameterStrategy parameterStrategy,
		List<ParameterRegistrationImplementor<?>> parameterRegistrations,
		SharedSessionContractImplementor session) {
	// prepare parameters
	int i = 1;

	try {
		for ( ParameterRegistrationImplementor parameter : parameterRegistrations ) {
			if ( parameter.getMode() == ParameterMode.REF_CURSOR ) {
				statement.registerOutParameter( i, Types.OTHER );
				i++;

			}
			else {
				parameter.prepare( statement, i );
				i += parameter.getSqlTypes().length;
			}
		}
	}
	catch (SQLException e) {
		throw session.getJdbcServices().getSqlExceptionHelper().convert(
				e,
				"Error registering CallableStatement parameters",
				procedureName
		);
	}
}
 

@Override
public String renderCallableStatement(
		String procedureName,
		ParameterStrategy parameterStrategy,
		List<ParameterRegistrationImplementor<?>> parameterRegistrations,
		SharedSessionContractImplementor session) {
	final StringBuilder buffer = new StringBuilder().append( "{call " )
			.append( procedureName )
			.append( "(" );
	String sep = "";
	for ( ParameterRegistrationImplementor parameter : parameterRegistrations ) {
		if ( parameter == null ) {
			throw new QueryException( "Parameter registrations had gaps" );
		}

		if ( parameter.getMode() == ParameterMode.REF_CURSOR ) {
			verifyRefCursorSupport( session.getJdbcServices().getJdbcEnvironment().getDialect() );
			buffer.append( sep ).append( "?" );
			sep = ",";
		}
		else {
			for ( int i = 0; i < parameter.getSqlTypes().length; i++ ) {
				buffer.append( sep ).append( "?" );
				sep = ",";
			}
		}
	}

	return buffer.append( ")}" ).toString();
}
 

@Override
public void registerParameters(
		String procedureName,
		CallableStatement statement,
		ParameterStrategy parameterStrategy,
		List<ParameterRegistrationImplementor<?>> parameterRegistrations,
		SharedSessionContractImplementor session) {
	// prepare parameters
	int i = 1;

	try {
		for ( ParameterRegistrationImplementor parameter : parameterRegistrations ) {
			parameter.prepare( statement, i );
			if ( parameter.getMode() == ParameterMode.REF_CURSOR ) {
				i++;
			}
			else {
				i += parameter.getSqlTypes().length;
			}
		}
	}
	catch (SQLException e) {
		throw session.getJdbcServices().getSqlExceptionHelper().convert(
				e,
				"Error registering CallableStatement parameters",
				procedureName
		);
	}
}
 

@Override
@SuppressWarnings("unchecked")
public <T> ParameterRegistration<T> registerParameter(int position, Class<T> type, ParameterMode mode) {
	final ProcedureParameterImpl procedureParameter = new ProcedureParameterImpl(
			this,
			position,
			mode,
			type,
			getSession().getFactory().getTypeResolver().heuristicType( type.getName() ),
			globalParameterPassNullsSetting
	);

	registerParameter( procedureParameter );
	return procedureParameter;
}
 

/**
 * Collects any parameter registrations which indicate a REF_CURSOR parameter type/mode.
 *
 * @return The collected REF_CURSOR type parameters.
 */
public ParameterRegistrationImplementor[] collectRefCursorParameters() {
	final List<ParameterRegistrationImplementor> refCursorParams = new ArrayList<>();

	getParameterMetadata().visitRegistrations(
			queryParameter -> {
				final ParameterRegistrationImplementor registration = (ParameterRegistrationImplementor) queryParameter;
				if ( registration.getMode() == ParameterMode.REF_CURSOR ) {
					refCursorParams.add( registration );
				}
			}
	);
	return refCursorParams.toArray( new ParameterRegistrationImplementor[refCursorParams.size()] );
}
 

/**
 * Create the memento
 *
 * @param position The parameter position
 * @param name The parameter name
 * @param mode The parameter mode
 * @param type The Java type of the parameter
 * @param hibernateType The Hibernate Type.
 * @param passNulls Should NULL values to passed to the database?
 */
public ParameterMemento(
		int position,
		String name,
		ParameterMode mode,
		Class type,
		Type hibernateType,
		boolean passNulls) {
	this.position = position;
	this.name = name;
	this.mode = mode;
	this.type = type;
	this.hibernateType = hibernateType;
	this.passNulls = passNulls;
}
 

@Test
public void testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("count_comments");
        query.registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("commentCount", Long.class, ParameterMode.OUT);

        query.setParameter("postId", 1L);

        query.execute();
        Long commentCount = (Long) query.getOutputParameterValue("commentCount");
        assertEquals(Long.valueOf(2), commentCount);
    });
}
 

@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);
    });
}