Why I chose O/R Mapping (JPA) over Stored Procedures for CRUD

I don’t choose easily when a large project is on the line. It took me months to choose JSF over Swing. And most recently, I’ve chosen JPA over Stored Procedures. I know it may be frustrating for my teammates to have to witness my arduous decision making process. With this decision in particular, I went back and forth many times before I was able to settle on something I was comfortable with. I have to look at things from many different angles, and come up with answers for many different questions.

Why did I feel I needed to choose between JPA and Stored Procedures? Are they really mutually exclusive?

Before we even decided to migrate our application from Powerbuilder to the web, we had already planned to move the data layer to Stored Procedures. There are really only 2 things a Powerbuilder programmer can do to separate presentation from business. 1.) Break out business logic into Non-Visual Objects (NVOs) 2.) Put all SQL into Stored Procedures. Otherwise, you get this classic pile of Client-Server spaghetti where all the business logic is hidden behind Button controls and such. So, it just made sense to go with Stored Procedures…initially. Once, we decided to migrate to Java, we just carried forward with the assumption that we would use Stored Procedures. That’s where the training dollars had gone, and most of us had already had experience with SPs. Well, it didn’t take long to realize that SPs were not exactly all the rage with n-tier, enterprise java applications. Instead, we found that POJO persistence was the standard for middle-tier. Oops!

At first, I thought maybe these two technologies might fit together. So I contacted Doug Clarke, Director of Product Management for Oracle’s Toplink. He wrote the following:

It is possible to use stored procedures with JPA as of our Oracle TopLink 11gR1 version (currently in preview) as well as in the new open source EclipseLink project. We have added @NamedStoredProcedureQuery annotations to facilitate this.

Here is the 11gR1 docs:
Here is the similar docs for EclipseLink

But he went on to write:

The challenge with combining stored procedures with JPA is that you may be sacrificing some of the inherent benefits of JPA. I am a big fan of using stored procedures where they offer performance optimizations of bottle-neck queries. Using them as a general data access mechanism will prevent the JPA provider from being able to leverage its cache for reading and some of the SQL generation optimizations when reading graphs of objects.

It became clear to me that it would be better to just choose one way or the other. So I began to search for arguments for and against. I soon discovered that this was not a simple choice. There are good arguments on both sides, and in the end, both paths are valid and can lead to a successful system.

Why even consider using Stored Procedures?

Some of the best arguments for using SPs come from Eric Bruno. In his article Use Stored Procedures for Java Persistence, he listed the following problems with using JPA that Stored Procedures solves:

Using stored procedures to encapsulate data manipulation helps to solve the following problems often found when this logic—the associated queries—exists within the Java layer:

  • Client/server data shuttling: When queries are made from Java code, potentially large amounts of data are shuttled back and forth between the application server and the database. This can greatly impact performance.
  • Transactions opened from outside the database: A transaction is often created for each query executed from the Java code within an application server. When a single logical operation requires multiple queries, many individual transactions may be created.
  • Tight coupling of database schema and Java code: The database structure is exposed to the Java layer. Hence, future changes to database structure may require changes to the Java code. As with Java class structure and member data, the database structure should be abstracted from other layers in the software.
  • Software release coordination: Because the Java code depends on the database structure, database releases that contain schema changes must be coordinated with releases of the dependent Java code. It’s often difficult to coordinate such releases. Furthermore, if one system must be rolled-back due to a problem, dependent systems must be rolled back also.

The article was written in September 2005 (kind of old), so I shot him an email to see if he still supported his assertions. He wrote:

I still support the use of stored procedures for data-related business logic. In short, any queries that involve joins, multi-table lookups, or any other reason that multiple calls to the database are required, are best done in the database. This helps to limit the IO usage from un-needed data shuttling, as well as extra 2PC transactions being created from the Java layer.

Also, my database administrator made the following strong argument:

Why not let the RDBMS do what it does best? What better to manage your Relational Database than a Relational Database Management System?

I also know that a few of our guys were around when our system was migrated from VS Wang to Powerbuilder. They might be thinking, “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Stored Procedures run fast. They can be used by just about any kind of application. They’re tried and proven.

The list is long.

Why I ultimately chose ORM

It’s not hard to find good reasons to use ORM technology such as JPA, Hibernate, and Toplink. The internet is buzzing with it. Here are some reasons I want to use it:

  • Boosts productivity – JPA frameworks handles the low-level work of writing lots of sql, mapping data to objects and objects to data, and managing database connections properly. It is also becoming increasingly well-supported by Java IDEs… a drag here, a drop here, a wizard there, a tweak there, and POOF!
  • All the cool kids are doing it – It’s no fun to be outside of the current trend. When you go with the flow, you get a lot of community support and involvement. Conferences, JUGS, forums are a lot more valuable when you’re doing what everyone else is doing. If 100,000 other guys are doing exactly what you’re doing, then someone’s bound to have solved the same problems you’re encountering.
  • It just makes sense -Think about it,
    • Objects are relational in nature
    • Relational Databases are relational in nature
    • JPA capitalizes on their shared relational nature

Ok, so here are my answers to some of the strongest arguments made for using Store Procedures instead of JPA:

Argument 1: Stored Procedures are faster and they minimize the number of trips to the database.

Answer: This would be true if there was no caching for JPA…but there is. With SPs, every interaction with the data will require a call to a stored procedure which resides in the database tier. Both Hibernate and Toplink employ very sophisticated 1st and 2nd level caching to prevent unnecessary trips to the database. Nothing is faster than no call at all!

Argument 2: Why not let the database do what it does best?

Answer: Persisting data is what Database Servers do best. Persisting business logic is what Application Servers do best. In addition, why would I want to use a procedural language to take something relational, flatten it out into unrelated result sets, and put them all back together again into something relational? And why would I want to relinguish Object-Oriented design for an entire tier of my application?

Argument 3: “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Answer: Next time we migrate, it may be the database technology that we’re migrating. Then we’ll be glad that we used a platform independent technology instead of tying everything to Oracle PL/SQL Stored Procedures. Now get off your lazy ass and learn something new for a change! (talking to myself here)


26 thoughts on “Why I chose O/R Mapping (JPA) over Stored Procedures for CRUD

  1. Thanks, Eric, for your kind words. And thanks again for your feedback on this topic. You gave me plenty to chew on.

  2. I’m struggling with exactlty this at the moment, and have been following your deliberations with interest. I’m sure I’m not the only one..

    We ported from PB to Web based apps about 8 years ago.. and went from NVO’s and Stored Procs and Datawindows to COM and stored procedures and HTML via custom web server and middle tier based objects. (and now thats looking decidely old hat – legacy)

    Now I’m looking at Netbeans’GlassFish and I’m still not convinced JPA is the answer.

    To me it almost as if you are looking for reasons to choose JPA over stored procedures. (Rather than making a reasoned comparison) Cool, and new, and trendy, and easier, arent actually arguments that should be used. (they have a way of coming back to bite you in the bum) Scalable, Maintainable, Seperation of Data from Business Logic from Presentation, Performance, they are arguments that will hold water.

    For what its worth I’m probably goint to choose JPA because it is so easy to integrate with the NetBeans IDE, and I’m loving the Netbeans/GlassFish platform.

    So its now down to the fact I’ve picked my platform, and this is what IT (the platform) does best. JPA and Entity binding is just so easy!

    And whats the downside? A complete rewrite when it turns out to be a bad choice, will keep some poor sod in job, even if its not me!

    I’d be very interested to hear how you go when you have some quantifiable experience with the JPA architecture..

  3. Great comments, Mick. Thanks for weighing in, and good luck on your decisions with this one. As I’m sure you’ve discovered, it’s not an easy one.

    I agree that “All the cool kids are doing it” is not an argument for going with JPA. But when I wrote this post, I wanted to be really honest about “Why I ultimately chose ORM”. Industry trend is an influential factor for me for reasons that I’ve stated. There are also other reasons. I want to choose standards that will be easily supported by the future work force in case my position needs to be backfilled.

    In reading the description of your current system, if I were you I would very reticent to abandon my stored procedures. Unlike our Powerbuilder system, yours already has a clear separation of business logic in a form that can be easily consumed by a Java EE app. JPA would not be buying you productivity or time savings. In fact, it would be adding hours to your project. JPA cannot compete with Stored Procedures if the Stored Procedures already exist! (my 2 cents)

  4. Hey, interesting discussion. Thanks for those pointers. I am also working on an application thats being migrated from PB to JEE(JSF,EJB3, JPA).We are trying to migrate all the stored procedures to logic in java but we hit a road block at security. The application is currently using Oracle security and there are SPs written to create user, default password e.t.c. And obviously the DBA is not interested to migrate that. So we r being used to forced to use SPs for login, user credentials stuff. So its a mix for us.

  5. Hi,Thank you all for posting such a wonderfull insight into your works.I was looking for a reason for using Hibernate in our new project. Though our techinical team suggested about Hibernate ,i was not convinced in moving from JDBC to Hibernate.But this post gives be different views about this aspect. I think i can now look forward to have a discussion with our team.
    jossy v jose

  6. Great reasons. Just started on a little project and it’s full of reams of java code, written on the “old style” some “class methods” are even over 1500 lines long!

    …and guess what’s delivering persistance?

    It turns out the folks who wrote the guff have never heard of JPA.

    ..It’s nice living in 2008.


  7. What is going to change first, your RDBMS or Java as a language? you can migrate from jboss to glassfish, and still your JPA will work.

  8. Good work everyone – David, very well done … I found everyone’s comments, useful

    My 2 cents worth would be:

    It’s easy enough to think that, well …, we know SP stuff already … we can easily write SPs … etc. – right?
    Well, fair enough but, you still have to bring the results into some kind of objects into java – right? I mean surely you’re not going to NOT use OO based development in 2008 right?
    Having said that, you’re investment in learning about how to write your SQL needs in JPA, will be very much leveraged through the fact that the results of the SQL queries generated from the JPA based objects will already be, some sound OO objects.
    NOTE: This point I think was already addressed in bullet number 3 in David’s response.

    Secondly, the concept of ORM has nothing to do with the particular programming language – the investment in understanding this concept and making sure that you’re using the right provider that realizes the great potential that ORM promises, this investment should be leverage if and when, another language replaces java – as a matter of fact – the same IDE (provider like Oracle), should in theory, reverse engineer your code and should enable you, to push a button and re-produce your same model, in another language.

    So the concept is very sound strategically speaking and Oracle takes it very seriously.

    Thank you all – great contribution.

    Oracle Corporation

  9. This point about OO objects can’t be overstated. There’s no avoiding the work of transfering data from the persistence tier into objects. You have a choice 1.) Write it yourself 2.) Use someone’s excellent mapping API to do it for you (Hibernate/Toplink).

    My team has had Hibernate training now and is beginning to use the product in our project. We’ve found that not only has it saved us time in our development process, it has greatly enhanced our ability to interact with the data.

    The IDE support alone makes it worth it!

    Key points for those concerned about performance/scalability:

    1.) Use Data Transfer Objects in your presentation tier. Don’t tie your presentation directly to Entity Beans. Only expose the data that your presentation layer absolutely NEEDS through your DTO. Also, you don’t want your presentation layer performing business functions via your entity beans. Leave that to your business tier

    2.) Don’t persist your entire database in your JPA/Hibernate/Toplink persistence tier. Focus your persistence tier around your application’s data needs.

    3.) Use your 2nd-level caching wisely. Save hits to the DB with relatively static data by persisting it in your 2nd-level cache.

    Thanks, Anwar, for your feedback!

  10. David,

    What IDE did you choose to go with?

    If I my also ask, from the ORM support point of view, do you mind sharing with us why you chose that particular IDE?

    Thanks again David,

    Anwar Khalil
    Sydney, Australia

  11. We went with MyEclipse. It has the best support for Icefaces. We decided that the combination of MyEclipse, Glassfish, Solaris, Liferay, Hibernate, and Icefaces with Facelets is a very happy family. Liferay and Sun have a new partnership, and so does Icefaces and Liferay.

    From the point of ORM, I believe MyEclipse supported Hibernate earlier than Netbeans. Netbeans went with Toplink at the beginning and then included a Hibernate plugin later.

    We’ve also decided to skip JPA and go straight to Hibernate so that we can get the full functionality.

    • UPDATE: We actually went with Netbeans, Eclipselink, Stripes, HTML, CSS, JQuery, Oracle, and Glassfish. We decided that JSF wasn’t as elegant an MVC framework as Stripes. They focus too much on visual components. We needed a separation of the view and the Model and Controller. JSF is so much like swing. I’ve built Swing apps. I would never choose a web framework that is designed to work like swing. Have you tried working with Swing? Much easier to use straight HTML, CSS, Javascript, and something like Stripes (http://www.stripesframework.org/) for the view.

  12. thank you very much for your detailed answer – sorry it took me a while to acknowledge your reply – I’ve been away on a course for a couple of days – Cheers

    Anwar Khalil

  13. (I know this post is rather old: sorry for intruding.)

    It’s really easy to cache stored procedure calls. Your chosen ORM tool may not support it, but I’d call that a tool defect. You might have to build a small handful of tools to do it yourself, but it’s totally possible, and not at all difficult.

    Also, using stored procedures allows you to treat your database more like a service layer or an API, with defined inputs and outputs. Applications tend to have a defined set of queries that they run, with a known set of parameters, which sounds a lot like stored procedures. Truly ad-hoc querying is the exception, not the rule.

  14. What is interesting in this article is the fact that you claim for one solution to be superior to the other. That might be true in the context that you had been in at the time. But now, 4 years later, it might not have been true anymore. Effectively, there is always a pro and a con side to every technology applied to a given problem. In this case:

    JPA still is the de-facto standard of accessing and persisting data from Java. Specifically, “persisting” should be used as a term, because you have your domain in Java and you want to persist it to the DB.

    An entirely other paradigm is that you design your (relational) data model first, and then start to think about client code. While this wasn’t the case in your setup, it may well be in large-scale OLAP applications. In this case, JPA with JPQL / CriteriaQuery lacks a lot of relational feature support, including decent support for stored procedures. Recent developments in larger RDBMS (namely Oracle, SQL Server, DB2, Sybase) show that data-centric models might celebrate their come-back.

    So I’d be curious what you think about all this now, 4 years later. Also, I’d be curious if you would have considered http://www.jooq.org for your evaluation – in favour of stored procedures

    • 4 years later. I’m glad you asked. We are using JPA (Eclipselink) with the Stripes framework for MVC and some EJBs for data access components. We also use stored procedures on occasion which we map to JPA Entities. We deal with performance issues with caching of entities on the app server (Glassfish) and database optimization (indexes and so on). I can’t really say how scalable this so far because we only have about 500 users, but it is performing very well so far.

      Netbeans offers excellent support of JPA and EJB. We’re having an easy time with them because of that.

      • Alright, yes I can see how this setup works with 500 users. I’m currently working on an application with 800k users and a daily 100k sessions (max 8000 concurrent sessions), so maybe that’s not comparable.

      • Oracle has hardly ever failed us with our queries. Large updates are usually done using stored procedures, inter-process and inter-server messaging is done with Oracle AQ. We have a lot of OLAP queries, too, which we fine-tune with indexes and hints, if required. The heaviest one in terms of I/O in the database is a query on a transactions table with several billions of transactions. That particular query calculates the transaction balance as a running total, the way I had recently blogged about here:

        Regular CRUD is done with a home-grown ORM similar to http://www.jooq.org. This ORM doesn’t do any caching, as Oracle actually has a very good cache of its own. We found that with the right connection-pooling, I/O between Java and the DB is not an issue at all.

        For the most-used service calls, we implemented a simple cache in the presentation layer, to prevent identical service-calls where this makes sense (e.g. “GetUserProperties”, “GetBankProperties”, “GetAccounts”, etc). Essentially, we think that Hibernate just kills off any performance that you could have achieved by using database-features, directly. That might not apply to cheaper databases, but Oracle is really a gem…

      • This is close to what our Oracle DBA wanted to do. The web developers out-voted him, though. Using JPA makes it a hell of a lot easier to build and java system. It’s likely that we’ll never have more than a few thousand users. so I feel pretty good about it. The key is to give Glassfish all of the resources it needs to manage the cache. Oracle caches the procedures.

        Thanks for all of your input. This article is getting pretty dusty, and it gets the most traffic of anything else I’ve written. I hope people read all of the comments as well.

      • You’re welcome!

        Yes, I’m sure you made the right decision for your setup. If the system is twice as fast, but no one understands it except the DBA, then that’s not a good situation… And in the end, if only the DBA can tune it, then maybe it isn’t even going to be twice as fast… 🙂

  15. Hi devid,
    You have done a great job man, thanks for it.
    I am a java developer working on a financial application in which concurrency of banking transaction is more than 150 transactions per second.

    After reading Lukaseder’s comments I am little bit confused whether I should use Hibernate or simply go with stored procedures.
    Will you please tell me?

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