Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.
Get this Hibernate Tip as a printable PDF!I've created a printable PDF for this Hibernate Tip. As always, you can download it for free from the Thoughts on Java Library.
My DBA created a stored procedure which I need to call from my Java code. What’s the best way to do that with Hibernate?
Until JPA 2.0, you had to use a native SQL query to call a stored procedure. Since version 2.1, you can also do that it with a @NamedStoredProcedureQuery.
The following code snippet shows an example of a @NamedStoredProcedureQuery annotation. This query calls the stored procedure calculate with the input parameters x and y and the output parameter sum. As you can see, each parameter is defined by a @StoredProcedureParameter annotation which defines the parameter mode and its name. The parameter mode specifies if the parameter is used as an input (ParameterMode.IN), output (ParameterMode.OUT), input and output (ParameterMode.INOUT) or as a result set cursor (ParameterMode.REF_CURSOR).
That’s all you need to do to define the stored procedure call. You can now use it in your business code. You just have to provide its name to the createNamedStoredProcedureQuery method of the EntityManager to instantiate the query, set the input parameters, execute it and read the output parameter.
You can read more about stored procedure calls and the supported parameter modes in How to call a stored procedure in JPA