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

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 th开发者_运维问答at 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

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.

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.

So how and what to say/present/explain/argument to at least shift their attitude toward ORM?

Ask if they want to keep writing the same boilerplate SQL JDBC CRUD code for every new entity type you add to your application.

Without an ORM solution in the data layer, each new entity class requires N units of work to make that entity available through the data layer (DAOs, CRUD code, stored procedures, writing queries, etc.).

ORM transforms that N into a fraction of itself, assuming that adding new entities then requires you to just add another mapping for that entity in metadata.

The answer to your question is actually quite simple and completely independent of whether you're a die-hard DBA. You have to ask yourself:

Are you writing a domain-model-centric application with a lot of CRUD?

Or are you writing a relational-model-centric application with not that much CRUD?

In the first case, choose an ORM. In the second case, choose SQL. If you have both in your application, choose both. The two models aren't mutually exclusive, although ORMs do impose a lot of rules on your application design.

Note that ORMs are not "the next thing" in the Java world. They're a great invention to solve only some problems (namely complex or repetitive CRUD)

Less boilerplate and extra security. You get to save your fingers and junior programmers have less chances to introduce SQL injection vulnerabilities which I find very important.

I think this answer from your related question link nails the underlying issue. If the database's purpose is tightly coupled to your application, and it doesn't really have a separate identity, then ORM makes a ton of sense.

If, however, your database represents a larger picture and your application is one of the many applications that accesses data, using ORM can have potential negative impacts on the coupling between your application and the database and the need to change the database over time.

But to some degree you can answer your question yourself - what objections did you have? How were you convinced the first time?

ORMs half the opportunity for errors in an application while stored procedures on the other hand double the opportunity for errors.

If there is a bug in the application you have to think: is it the code or there's just something about that stored procedure.

Besides, how do you test SPs in isolation?

Remember, a bug in code and a bug in database can double up nicely to create a working solution. Just like how in math two negatives make a positive. It becomes chaos, when the SP is fixed without fixing the code, and vice-versa.