| |

Hibernate Tips: How to call a stored procedure


Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.


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!

2 Comments

  1. Avatar photo Laura Ingram says:

    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. Avatar photo Thorben Janssen says:

      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

Comments are closed.