Hibernate Tips: How to call a stored procedure

By Thorben Janssen

Query

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.

Question:

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?

Solution:

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).

@NamedStoredProcedureQuery(
	name = "calculate", 
	procedureName = "calculate", 
	parameters = { 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), 
		@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
	}
)

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.

StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");


Learn More:

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







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

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!



Tags

Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Reply

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. I have done this, but after 20 calls, my connection pool is full. I’ve tried a number of workarounds found online to convince Hibernate to release the connection the rest of the way, but it doesn’t seem to work. Any tips?

    1. Hi Laura,

      the stored procedure call doesn’t affect Hibernate’s connection handling. Did you check if the stored procedure call returns in time and if you close and release everything at the end of your transaction?

      Regards,
      Thorben

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}