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.
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?
Don’t want to read? You can watch it here!
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.
Get this Hibernate Tip as a printable PDF!Join the free Thoughts on Java Library to get access to lots of member-only content, like a printable PDF for this post, lots of cheat sheets and 2 ebooks about Hibernate.
You can read more about stored procedure calls and the supported parameter modes in How to call a stored procedure in JPA
Hibernate Tips Book
It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching and statically and dynamically defined queries.
Get it now as a paperback, ebook or PDF.