CODEWITHSUNDEEP
jpastored-procedures
Cecilia Arenas
Cecilia Arenas

Reputation: 163

JPA: How to call a stored procedure

I have a stored procedure in my project under sql/my_prod.sql

there I have my function delete_entity

In my entity

@NamedNativeQuery(name = "delete_entity_prod", 
query = "{call /sql/delete_entity(:lineId)}",

and I call it 

Query query = entityManager.createNamedQuery("delete_entity_prod")
              setParameter("lineId",lineId);

I followed this example: http://objectopia.com/2009/06/26/calling-stored-procedures-in-jpa/

but it does not execute the delete and it does not send any error.

I haven't found clear information about this, am I missing something? Maybe I need to load the my_prod.sql first? But how?

Upvotes: 0

Views: 21500

Answers (3)

panagdu
panagdu

Reputation: 2133

This is actually they way you create a query.

Query query = entityManager.createNamedQuery("delete_entity_prod")
          setParameter("lineId",lineId);

To call it you must execute:

query.executeUpdate();

Of course, the DB must already contain the procedure. So if you have it defined in your SQL file, have a look at Executing SQL Statements from a Text File(this is for MySQL but other database systems use a similar approach to execute scripts)

Upvotes: 1

Chris
Chris

Reputation: 21145

JPA 2.1 standardized stored procedure support if you are able to use it, with examples here http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures

Upvotes: 4

Mikko Maunu
Mikko Maunu

Reputation: 42084

There is no error shown because query is not executed at any point - just instance of Query is created. Query can be executed by calling executeUpdate:

query.executeUpdate();

Then next problem will arise: Writing some stored procedures to file is not enough - procedures live in database, not in files. So next thing to do is to check that there is correct script to create stored procedure in hands (maybe that is currently content of sql/my_prod.sql) and then use that to create procedure via database client.

All JPA implementations do not support calling stored procedures, but I assume Hibernate is used under the hood, because that is also used in linked tutorial.

It can be the case that current

{call /sql/delete_entity(:lineId)}

is right syntax for calling stored procedure in your database. It looks rather suspicious because of /sql/. If it turns out that this is incorrect syntax, then:

  1. Consult manual for correct syntax
  2. Test via client
  3. Use that as a value of query attribute in NamedNativeQuery annotation.

All that with combination MySQL+Hibernate is explained for example here.

Upvotes: 1

Related Questions