I am relatievely new to JPA space, how ever I would like to debate about the pros and cons of adopting JPA for Data Manipulation (vs) Stored Procs over JDBC. Can you gurus provide your valuable insights.
They are really different things only closely related.
Simplified, people use of Stored Procs for a few reasons:
1. To push the SQL to the database server and make all data base calls look like methods to the applications. It is easier to "fix" the SQL because you don't have to re-compile/re-deploy the source.
2. The Information Architectects (DBA's) like it because it locates the SQL in the database server, something they understand rather than application code, something they don't. Counter argument is that often lots of business logic creeps into the stored procs and processing can be shifted from application servers to database servers.
3. For performance reasons, it can be more efficient to use a stored proc with multiple SQL statements rather than execute multiple SQL calls from the application. Stored procs can simplify complex database operations when a language like PL/SQL is used.
Stored Proc's do nothing to really help the application developer, they are procedure based and cannot be cached (except for what caching a database server may provide).
JPA is really geared towards application development. Like Stored Proc's they abstract away the SQL from the developer. The abstraction however is more tuned to the application development methodology and this is where object-relational mapping (O/RM)comes into play.
The JPA implementation (e.g. Hibernate or Top Link) is responsible for query optimization and can provide some caching services as an aide to performance. XML configuration files or Java Annotations provide the metadata used by the JPA implementation to generate and optimize the SQL on the fly. Depending on which of the above you use, you still have to do some form of compile and re-deploy if things change.
Stored Procs are a good way to remove SQL out of the reach of the developers. My experience has been that developers end up writing both stored procs or the embedded SQL so I could never really understand why one had to have a mandatory separation.
In a crunch, having SQL in a stored proc is typically easier to maintain than SQL embedded in Java code. However, most of the type a parameter needs to be added which means you have to change both sides of the equation, so little time is saved in new development efforts.