JPA Interface

StoredProcedureQuery

Super Interfaces:
Query
Interface used to control stored procedure query execution.

Stored procedure query execution may be controlled in accordance with the following:

  • The setParameter() methods are used to set the values of all required IN and INOUT parameters. It is not required to set the values of stored procedure parameters for which default values have been defined by the stored procedure.
  • When getResultList() and getSingleResult() are called on a StoredProcedureQuery object, the provider calls execute() on an unexecuted stored procedure query before processing getResultList or getSingleResult.
  • When executeUpdate() is called on a StoredProcedureQuery object, the provider will call execute() on an unexecuted stored procedure query, followed by getUpdateCount(). The results of executeUpdate will be those of getUpdateCount.
  • The execute() method supports both the simple case where scalar results are passed back only via INOUT and OUT parameters as well as the most general case (multiple result sets and/or update counts, possibly also in combination with output parameter values).
  • The execute method returns true if the first result is a result set, and false if it is an update count or there are no results other than through INOUT and OUT parameters, if any.
  • If the execute method returns true, the pending result set can be obtained by calling getResultList() or getSingleResult().
  • The hasMoreResults() method can then be used to test for further results.
  • If execute or hasMoreResults returns false, the getUpdateCount() method can be called to obtain the pending result if it is an update count. The getUpdateCount method will return either the update count (zero or greater) or -1 if there is no update count (i.e., either the next result is a result set or there is no next update count).
  • For portability, results that correspond to JDBC result sets and update counts need to be processed before the values of any INOUT or OUT parameters are extracted.
  • After results returned through getResultList() and getUpdateCount() have been exhausted, results returned through INOUT and OUT parameters can be retrieved.
  • The getOutputParameterValue() methods are used to retrieve the values passed back from the procedure through INOUT and OUT parameters.
  • When using REF_CURSOR parameters for result sets the update counts should be exhausted before calling getResultList() to retrieve the result set. Alternatively, the REF_CURSOR result set can be retrieved through getOutputParameterValue(). Result set mappings are applied to results corresponding to REF_CURSOR parameters in the order the REF_CURSOR parameters were registered with the query.
  • In the simplest case, where results are returned only via INOUT and OUT parameters, execute can be followed immediately by calls to getOutputParameterValue().
See Also:
Query
Parameter
Since:
JPA 2.1

Public Methods

boolean execute()
Return true if the first result corresponds to a result set, and false if it is an update count or if there are no results other than through INOUT and OUT parameters, if any.
Return:
true if first result corresponds to result set
Throws:
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
int executeUpdate()
Return the update count of -1 if there is no pending result or if the first result is not an update count.
The provider will call execute on the query if needed.
Return:
the update count or -1 if there is no pending result or if the next result is not an update count.
Throws:
TransactionRequiredException - if there is no transaction or the persistence context has not been joined to the transaction
QueryTimeoutException - if the statement execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
CacheRetrieveMode getCacheRetrieveMode()
The cache retrieval mode that will be in effect during query execution.
Inherited from:
Query
Since:
JPA 3.2
CacheStoreMode getCacheStoreMode()
The cache storage mode that will be in effect during query execution.
Inherited from:
Query
Since:
JPA 3.2
int getFirstResult()
The position of the first result the query object was set to retrieve.
Returns 0 if setFirstResult was not applied to the query object.
Return:
position of the first result
Inherited from:
Query
Since:
JPA 2.0
The Running JPA Queries article explains how to use getFirstResult.
FlushModeType getFlushMode()
Get the flush mode in effect for the query execution.
If a flush mode has not been set for the query object, returns the flush mode in effect for the entity manager.
Return:
flush mode
Inherited from:
Query
Since:
JPA 2.0
Map<String,Object> getHints()
Get the properties and hints and associated values that are in effect for the query instance.
Return:
query properties and hints
Inherited from:
Query
Since:
JPA 2.0
LockModeType getLockMode()
Get the current lock mode for the query.
Returns null if a lock mode has not been set on the query object.
Return:
lock mode
Throws:
IllegalStateException - if the query is found not to be a Jakarta Persistence query language SELECT query or a {@link jakarta.persistence.criteria.CriteriaQuery} query
Inherited from:
Query
Since:
JPA 2.0
int getMaxResults()
The maximum number of results the query object was set to retrieve.
Returns Integer if setMaxResults() was not applied to the query object.
Return:
maximum number of results
Inherited from:
Query
Since:
JPA 2.0
Object getOutputParameterValue(String parameterName)
Retrieve a value passed back from the procedure through an INOUT or OUT parameter.
For portability, all results corresponding to result sets and update counts must be retrieved before the values of output parameters.
Parameters:
parameterName - name of the parameter as registered or specified in metadata
Return:
the result that is passed back through the parameter
Throws:
IllegalArgumentException - if the parameter name does not correspond to a parameter of the query or is not an INOUT or OUT parameter
Since:
JPA 2.1
Object getOutputParameterValue(int position)
Retrieve a value passed back from the procedure through an INOUT or OUT parameter.
For portability, all results corresponding to result sets and update counts must be retrieved before the values of output parameters.
Parameters:
position - parameter position
Return:
the result that is passed back through the parameter
Throws:
IllegalArgumentException - if the position does not correspond to a parameter of the query or is not an INOUT or OUT parameter
Since:
JPA 2.1
Parameter<?> getParameter(String name)
Get the parameter object corresponding to the declared parameter of the given name.
This method is not required to be supported for native queries.
Parameters:
name - parameter name
Return:
parameter object
Throws:
IllegalArgumentException - if the parameter of the specified name does not exist
IllegalStateException - if invoked on a native query when the implementation does not support this use
Inherited from:
Query
Since:
JPA 2.0
Parameter<T> getParameter(String name, Class<T> type)
Get the parameter object corresponding to the declared parameter of the given name and type.
This method is required to be supported for criteria queries only.
Parameters:
name - parameter name
type - type
Return:
parameter object
Throws:
IllegalArgumentException - if the parameter of the specified name does not exist or is not assignable to the type
IllegalStateException - if invoked on a native query or Jakarta Persistence query language query when the implementation does not support this use
Inherited from:
Query
Since:
JPA 2.0
Parameter<?> getParameter(int position)
Get the parameter object corresponding to the declared positional parameter with the given position.
This method is not required to be supported for native queries.
Parameters:
position - position
Return:
parameter object
Throws:
IllegalArgumentException - if the parameter with the specified position does not exist
IllegalStateException - if invoked on a native query when the implementation does not support this use
Inherited from:
Query
Since:
JPA 2.0
Parameter<T> getParameter(int position, Class<T> type)
Get the parameter object corresponding to the declared positional parameter with the given position and type.
This method is not required to be supported by the provider.
Parameters:
position - position
type - type
Return:
parameter object
Throws:
IllegalArgumentException - if the parameter with the specified position does not exist or is not assignable to the type
IllegalStateException - if invoked on a native query or Jakarta Persistence query language query when the implementation does not support this use
Inherited from:
Query
Since:
JPA 2.0
T getParameterValue(Parameter<T> param)
Return the input value bound to the parameter.
(Note that OUT parameters are unbound.)
Parameters:
param - parameter object
Return:
parameter value
Throws:
IllegalArgumentException - if the parameter is not a parameter of the query
IllegalStateException - if the parameter has not been bound
Inherited from:
Query
Since:
JPA 2.0
Object getParameterValue(String name)
Return the input value bound to the named parameter.
(Note that OUT parameters are unbound.)
Parameters:
name - parameter name
Return:
parameter value
Throws:
IllegalStateException - if the parameter has not been bound
IllegalArgumentException - if the parameter of the specified name does not exist
Inherited from:
Query
Since:
JPA 2.0
Object getParameterValue(int position)
Return the input value bound to the positional parameter.
(Note that OUT parameters are unbound.)
Parameters:
position - position
Return:
parameter value
Throws:
IllegalStateException - if the parameter has not been bound
IllegalArgumentException - if the parameter with the specified position does not exist
Inherited from:
Query
Since:
JPA 2.0
Set<Parameter<?>> getParameters()
Get the parameter objects corresponding to the declared parameters of the query.
Returns empty set if the query has no parameters. This method is not required to be supported for native queries.
Return:
set of the parameter objects
Throws:
IllegalStateException - if invoked on a native query when the implementation does not support this use
Inherited from:
Query
Since:
JPA 2.0
List getResultList()
Retrieve the list of results from the next result set.
The provider will call execute on the query if needed. A REF_CURSOR result set, if any, is retrieved in the order the REF_CURSOR parameter was registered with the query.
Return:
a list of the results or null is the next item is not a result set
Throws:
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
Stream getResultStream()
Execute a SELECT query and return the query results as an untyped java.util.stream.Stream.

By default, this method delegates to getResultList().stream(), however persistence provider may choose to override this method to provide additional capabilities.

Return:
a stream of the results, or an empty stream if there are no results
Throws:
IllegalStateException - if called for a Jakarta Persistence query language UPDATE or DELETE statement
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
TransactionRequiredException - if a lock mode other than {@code NONE} has been set and there is no transaction or the persistence context has not been joined to the transaction
PessimisticLockException - if pessimistic locking fails and the transaction is rolled back
LockTimeoutException - if pessimistic locking fails and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Inherited from:
Query
See Also:
Stream
getResultList()
Since:
JPA 2.2
Object getSingleResult()
Retrieve a single result from the next result set.
The provider will call execute on the query if needed. A REF_CURSOR result set, if any, is retrieved in the order the REF_CURSOR parameter was registered with the query.
Return:
the result or null if the next item is not a result set
Throws:
NoResultException - if there is no result in the next result set
NonUniqueResultException - if more than one result
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
Object getSingleResultOrNull()
Retrieve a single result from the next result set.
The provider will call execute on the query if needed. A REF_CURSOR result set, if any, is retrieved in the order the REF_CURSOR parameter was registered with the query.
Return:
the result or null if the next item is not a result set or if there is no result in the next result set
Throws:
NonUniqueResultException - if more than one result
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
Integer getTimeout()
The query timeout.
Inherited from:
Query
Since:
JPA 3.2
int getUpdateCount()
Return the update count or -1 if there is no pending result or if the next result is not an update count.
Return:
update count or -1 if there is no pending result or if the next result is not an update count
Throws:
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
boolean hasMoreResults()
Return true if the next result corresponds to a result set, and false if it is an update count or if there are no results other than through INOUT and OUT parameters, if any.
Return:
true if next result corresponds to result set
Throws:
QueryTimeoutException - if the query execution exceeds the query timeout value set and only the statement is rolled back
PersistenceException - if the query execution exceeds the query timeout value set and the transaction is rolled back
Since:
JPA 2.1
boolean isBound(Parameter<?> param)
Return a boolean indicating whether a value has been bound to the parameter.
Parameters:
param - parameter object
Return:
boolean indicating whether parameter has been bound
Inherited from:
Query
Since:
JPA 2.0
StoredProcedureQuery registerStoredProcedureParameter(String parameterName, Class<?> type, ParameterMode mode)
Register a named parameter.
Parameters:
parameterName - name of the parameter as registered or specified in metadata
type - type of the parameter
mode - parameter mode
Return:
the same query instance
Since:
JPA 2.1
StoredProcedureQuery registerStoredProcedureParameter(int position, Class<?> type, ParameterMode mode)
Register a positional parameter.
All parameters must be registered.
Parameters:
position - parameter position
type - type of the parameter
mode - parameter mode
Return:
the same query instance
Since:
JPA 2.1
StoredProcedureQuery setCacheRetrieveMode(CacheRetrieveMode cacheRetrieveMode)
Set the cache retrieval mode that is in effect during query execution.
This cache retrieval mode overrides the cache retrieve mode in use by the entity manager.
Parameters:
cacheRetrieveMode - cache retrieval mode
Return:
the same query instance
Since:
JPA 3.2
StoredProcedureQuery setCacheStoreMode(CacheStoreMode cacheStoreMode)
Set the cache storage mode that is in effect during query execution.
This cache storage mode overrides the cache storage mode in use by the entity manager.
Parameters:
cacheStoreMode - cache storage mode
Return:
the same query instance
Since:
JPA 3.2
Query setFirstResult(int startPosition)
Set the position of the first result to retrieve.
Parameters:
startPosition - position of the first result, numbered from 0
Return:
the same query instance
Throws:
IllegalArgumentException - if the argument is negative
Inherited from:
Query
Since:
JPA 1.0
StoredProcedureQuery setFlushMode(FlushModeType flushMode)
Set the flush mode type to be used for the query execution.
The flush mode type applies to the query regardless of the flush mode type in use for the entity manager.
Parameters:
flushMode - flush mode
Return:
the same query instance
Since:
JPA 2.1
StoredProcedureQuery setHint(String hintName, Object value)
Set a query property or hint.
The hints elements may be used to specify query properties and hints. Properties defined by this specification must be observed by the provider. Vendor-specific hints that are not recognized by a provider must be silently ignored. Portable applications should not rely on the standard timeout hint. Depending on the database in use, this hint may or may not be observed.
Parameters:
hintName - name of the property or hint
value - value for the property or hint
Return:
the same query instance
Throws:
IllegalArgumentException - if the second argument is not valid for the implementation
Since:
JPA 2.1
Query setLockMode(LockModeType lockMode)
Set the lock mode type to be used for the query execution.
Parameters:
lockMode - lock mode
Return:
the same query instance
Throws:
IllegalStateException - if the query is found not to be a Jakarta Persistence query language SELECT query or a {@link jakarta.persistence.criteria.CriteriaQuery} query
Inherited from:
Query
Since:
JPA 2.0
Query setMaxResults(int maxResult)
Set the maximum number of results to retrieve.
Parameters:
maxResult - maximum number of results to retrieve
Return:
the same query instance
Throws:
IllegalArgumentException - if the argument is negative
Inherited from:
Query
Since:
JPA 1.0
StoredProcedureQuery setParameter(Parameter<T> param, T value)
Bind the value of a Parameter object.
Parameters:
param - parameter object
value - parameter value
Return:
the same query instance
Throws:
IllegalArgumentException - if the parameter does not correspond to a parameter of the query
Since:
JPA 2.1
StoredProcedureQuery setParameter(String name, Object value)
Bind an argument value to a named parameter.
Parameters:
name - parameter name
value - parameter value
Return:
the same query instance
Throws:
IllegalArgumentException - if the parameter name does not correspond to a parameter of the query or if the argument is of incorrect type
Since:
JPA 2.1
StoredProcedureQuery setParameter(int position, Object value)
Bind an argument value to a positional parameter.
Parameters:
position - position
value - parameter value
Return:
the same query instance
Throws:
IllegalArgumentException - if position does not correspond to a positional parameter of the query or if the argument is of incorrect type
Since:
JPA 2.1
StoredProcedureQuery setTimeout(Integer timeout)
Set the query timeout, in milliseconds.
This is a hint, and is an alternative to plain jakarta.persistence.query.timeout.
Parameters:
timeout - the timeout, in milliseconds, or null to indicate no timeout
Return:
the same query instance
Since:
JPA 3.2
T unwrap(Class<T> cls)
Return an object of the specified type to allow access to a provider-specific API.
If the provider implementation of Query does not support the given type, the PersistenceException is thrown.
Parameters:
cls - the type of the object to be returned. This is usually either the underlying class implementing {@code Query} or an interface it implements.
Return:
an instance of the specified class
Throws:
PersistenceException - if the provider does not support the given type
Inherited from:
Query
Since:
JPA 2.0