JPA: How to call a stored procedure

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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)