JPA Hibernate calling stored procedure

Question

I have a stored procedure in Postgres. That procedure has 4 OUT parameters. Using JPA normally I can't get the results. What I'm trying to do is use a SELECT query with that procedure.

For example If I try in pgAdmin the query:

SELECT * FROM get_results (arg0, arg1 etc);

I get one result row containing 4 columns with the results from the 4 OUT parameters.

But When I try to use it in JPA it fails. I'm trying something like:

Query q = em.createNativeQuery("SELECT * FROM get_results (arg0, arg1 etc)");
q.getSingleResult();

But it throws an java.lang.IllegalStateException [com.arjuna.ats.internal.jta.transaction.arjunacore.nosuchtx] [com.arjuna.ats.internal.jta.transaction.arjunacore.nosuchtx] No such transaction!

Any suggestions?

The error message points to a transaction problem. Do other native queries (like selecting from a table) work at that place? — mrab, Apr 23 '12 at 07:26
I tried the same query with count. em.createNativeQuery("SELECT count(*) FROM get_results (arg0, arg1 etc)"); q.getSingleResult(); and I get the number "1" as a result which is what I was expecting. So it works. But now when I try the normal query I get a : "[ConnectionManager] forcing batcher resource cleanup on transaction completion; forgot to close ScrollableResults/Iterato r?" Error. — Panos, Apr 23 '12 at 07:40
You can have a look [here](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256), I hope it helps : [How to call a stored procedure from Java and JPA](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256) — RED-ONE, Jun 05 '23 at 22:45

score 3 · Answer 1 · answered Sep 01 '13 at 06:12

JPA 2.1 now support Stored Procedure, read the Java doc here.

Example:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax");
// set parameters
storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
storedProcedure.setParameter("subtotal", 1f);
// execute SP
storedProcedure.execute();
// get result
Double tax = (Double)storedProcedure.getOutputParameterValue("tax");

See detailed example here.

score 0 · Answer 2 · answered Apr 23 '12 at 07:39

0

use below code co call procedure using hibernate.

Query query = session.getNamedQuery("ProcedureName")
    .setParameter(parameterName,value);
    .setParameter(parameterName,value);
    .setParameter(parameterName,value);
    .setParameter(parameterName,value);

answered Apr 23 '12 at 07:39

Vishal Ranapariya

1,026
8
8

I can't use this code because as I said before my procedure has 4 OUT parameters. JPA works with only 1 OUT parameter. So the only way is the "Select * FROM procedure_name(...)". – Panos Apr 23 '12 at 08:02

JPA Hibernate calling stored procedure

2 Answers2

Linked