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:

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

@SqlResultSetMapping(name="scalar",columns=@ColumnResult(name="result")

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.

@SqlResultSetMapping(name="scalar",columns=@ColumnResult(name="result")

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

Query query = entityManager.createNamedQuery("StoredProc_Call");
About these ads

3 thoughts on “Calling Stored Procedures in JPA

  1. Pingback: Java Developers: Make the Database Work for You! « AMIS Technology blog

  2. Hi Chris,
    I got the same surpise myself, Were you able to suck up the data coming back from the stored procedure, and cache it as an EJB 3.0 Entity? even though the data in the proc may have been coming from multiple tables?

    I have a similiar problem with a lot of EJB 2.1 BMP Entity beans which need replacing, that call stored proc’s and native JDBC.

    • Hi Mike,

      I was lucky enough to use the stored proc for updates so I wasn’t to interested in the results however you can use the @SqlResultSetMapping which details can be found here and here.

      Good luck.

      Chris

Comments are closed.