CODEWITHSUNDEEP
javahibernatepostgresqlstored-proceduresjpa
Panos
Panos

Reputation: 7407

JPA Hibernate calling stored procedure

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?

Upvotes: 0

Views: 6054

Answers (2)

Pau Kiat Wee
Pau Kiat Wee

Reputation: 9505

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.

Upvotes: 3

Vishal Ranapariya
Vishal Ranapariya

Reputation: 1086

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

Upvotes: 0

Related Questions