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

4 thoughts on “Calling Stored Procedures in JPA

  1. 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

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

  3. Pingback: amazon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s