Calling Stored Procedures in JPA

Recently I had to call a stored procedure using JPA and to my surprise I soon found out that it’s not directly supported. So how do you call a stored procedure? My stack is

Db = Sybase
AppServer = JBoss 4.2.3
Java = 1.6
Persistance = EJB3 implemented by Hibernate
Hibernate = 3.4.0.GA

So the first thing I do is to create a named native query on my entity which looks something like this:

                  query="stored_proc_name(:param1, :param2, :param3)",
                      @QueryHint(name = "org.hibernate.callable",value = "true"), 
                      @QueryHint(name = "org.hibernate.readOnly",value = "true")
                  resultSetMapping="scalar", resultClass=Result.class)


Because I’m using hibernate as my EJB3 implementation, the default for JBoss, I can use it to call the stored proc for me using the hints attribute which takes in an array of queryhint objects.

Because native scalar results aren’t supported by EJB3 you also have to set the resultClass and the resultSetMapping.


All that is required now is to create the native query, set the required parameters and call it.

Query query = entityManager.createNamedQuery("StoredProc_Call");