Over a million developers have joined DZone.

Stored Procedure Support in JPA 2.1/Java EE 7

DZone's Guide to

Stored Procedure Support in JPA 2.1/Java EE 7

If you want to start a contentious debate at work, you should try initiating a discussion on the pros and cons of database stored procedures.

· Java Zone
Free Resource

Never build auth again! The Okta Developer Platform makes it simple to implement authentication, authorization, MFA and more in Java applications. Get started with the free API.

If you want to start a contentious debate at work, you should try initiating a discussion on the pros and cons of database stored procedures. If you want the discussion to be particularly colorful make sure to include a few DBAs or database developers instead of simply Java application developers :-). One of the least contentious benefits of stored procedures is that they can provide a performance boost for cases that involve multiple complex queries and heavy data manipulation by reducing the number of network round trips to the database. Some of the main weak points for stored procedures include lack of portability, weaker tooling for development/testing/debugging, and increased database load. The last one is a particularly salient point in my view - databases are the most difficult part of the data center to scale horizontally and they are already heavily IO bound. Frivolously adding stored procedures to the mix can work to make the database CPU bound to boot.

Where JPA and more generally ORM tools stand on this debate is not a great mystery - prior to JPA 2.1 the specification did not add direct support for stored procedures. That being said it should be noted that major JPA implementations have long supported stored procedures and adding support for them has been a frequent enough request for the JPA specification. The good news is that JPA 2.1 and Java EE 7 adds standardized support for accessing stored procedures. Pau Kiat Wee does a nice job of demonstrating the new feature in a very brief code centric blog post. Note that his example is Java SE based so it's actually even easier in a Java EE 7 environment. The newly added stored procedure support is very similar to the way JPA handles native queries. One of the best explanations of the feature comes from specification lead Linda DeMichiel. Check out minute 4-7 in the short video below (if you are having trouble accessing the embedded video it is available here).

Another very good source for more examples of the new feature is the EcliseLink 2.5 JPA 2.1 notes. Enjoy and don't forget to share if these are useful resources.

Please note that any views expressed here are my own only and certainly does not reflect the position of Oracle as a company

Build and launch faster with Okta’s user management API. Register today for the free forever developer edition!

java ,database ,jpa ,java ee 7

Published at DZone with permission of Reza Rahman, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}