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
