JPA vs. JDBC, stored procedures and Co. or How to convince old school programmer to try ORM?

Question

This is something I deal periodically with, and first time it was me who needed conviction. Luckily, I just tried, made extra effort to learn and thanks to this book, Spring support, and Hibernate I won't start a project without considering JPA. But not everyone is willing to go extra mile that often (just in anything we deal with I guess). So how and what to say/present/explain/argument to at least shift their attitude toward ORM?

Related: Convincing a die hard DBA to use an ORM for the majority of CRUD vs Stored Procedures, View, and Functions

Answer 1

I'll assume that you actually have an object model that's richer than mere DTOs and not simply a 1:1 correspondence with your relational schema. If not, you don't win the argument.

You win if the SQL generated by Hibernate is at least as efficient as the hand-coded stuff that's in the stored procs.

You lose if the stored procs can be optimized to perform better than Hiberate-generated SQL.

You lose if the database is shared by other apps that depend on the stored procs. You can't move the logic to Spring and the middle tier as easily.

You have a better case if your app owns the database.

Since you're already using Spring, that suggests that you have a DAO layer that takes advantage of Spring Validation. PreparedStatements are already used underneath, so SQL injection is as unlikely for Spring as it is for stored procs.

Answer 2

OK, I'm going to play the devil's advocate here.

Stored procedures are a layer of abstraction. JPA requires you to know the underlying table structure. Stored procedures/functions mask that; you only need to know procedure or function to call and what its return type is.

JDBC makes calling stored procedures very easy, using the Connection object's prepareCall methods.

Stored procedures also add a layer of security: The application itself usually cannot manually modify the tables, only call the procedures that do the modification. The SP/SF should also be validating the arguments passed to it.

The major drawback with stored procedures is getting data back out... you need to create your own facility to map arrays and structs coming back to Java objects, usually with a type Map and special programmatically created objects implementing the SQLData interface.