J2EE developers (and Java developers in general) tend to hate stored procedures. There is some justification for this:
Stored procedures aren't object-oriented. Readers familiar with Oracle will counter that Oracle 8.1.5 introduced Java stored procedures. However, they don't solve the O/R impedance mismatch as much as move it inside the database, and they don't foster truly object-oriented use of Java.
Stored procedures aren't portable. Support for stored procedures varies much more than SQL dialects between RDBMSs. Nevertheless, it would be rare to lose the entire investment in a set of stored procedures on migrating from one RDBMS to another.
If stored procedures grow complex, they may reduce an application's maintainability.
Some other common objections have less validity:
"Using stored procedures puts business logic in the wrong place"
If we distinguish between persistence logic and business logic, the idea of putting persistence logic in a relational database makes perfect sense.
"Using stored procedures means that J2EE security may be compromised"
Security is a matter of business logic, not persistence logic: if we keep our business logic in J2EE, there is no need to restrict access to data.
"The database will become a performance bottleneck"
Especially, if a single database instance is serving a cluster of J2EE servers, the processing of stored procedures may limit overall performance. However, there are trade-offs to consider:
In my experience, it's much commoner to see network performance between application server and database limit the overall performance of a J2EE application than the performance of a well-designed database.
There's no reason to perform an operation in a stored procedure rather than a J2EE component unless the operation can be done more naturally and efficiently inside the database server than in Java. Thus if we've implemented an operation efficiently inside the RDBMS and it still eats the server's CPU, it probably indicates that the RDBMS is badly tuned or needs to run on better hardware. Performing the same heavily-requested operation less efficiently in the application server will probably result in a more severe problem, and the need for more additional hardware.
The use of stored procedures from J2EE applications is an area where we should be pragmatic, and avoid rigid positions. I feel that many J2EE developers' blanket rejection of stored procedures is a mistake. There are clear benefits in using stored procedures to implement persistence logic in some situations:
Stored procedures can handle updates spanning multiple database tables. Such updates are problematic with O/R mapping.
A more general form of the first point) Stored procedures can be used to hide the details of the RDBMS schema from Java code. Often there's no reason that Java business objects should know the structure of the database.
Round trips between the J2EE server and the database are likely to be slow. Using stored procedures can consolidate them in the same way in which we strive to consolidate remote calls in distributed J2EE applications to avoid network and invocation protocol overhead.
Stored procedures allow use of efficient RDBMS constructs. In some cases, this will lead to significantly higher performance and reduce load on the RDBMS.
Many data management problems can be solved much more easily using a database language such as PL/SQL than by issuing database commands from Java. It's a case of choosing the right tool for the job. I wouldn't consider using Perl in preference to Java to build a large application; neither would I waste my time and my employer's money by writing a text manipulation utility in Java if I could write it in Perl with a fraction of the effort.
There may be an investment in existing stored procedures that can be leveraged.
Stored procedures are easy to call from Java code, so using them tends to reduce, rather than increase, the complexity of J2EE applications.
Very few enterprises with existing IT shops have ported all their applications to J2EE, or are soon likely to. Hence persistence logic may be more useful in the RDBMS than in the J2EE server, if it can be used by other non J2EE applications (for example, custom reporting applications or in-house VB clients).
The danger in using stored procedures is the temptation to use them to implement business logic. This has many negative consequences, for example:
There is no single architectural tier that implements the application's business logic. Updates to business rules may involve changing both Java and database code.
The application's portability will reduce as stored procedures grow in complexity.
Two separate teams (J2EE and DBA) will share responsibility for business logic, raising the possibility of communication problems.
If we distinguish between persistence logic and business logic, using stored procedures will not break our architecture. Using a stored procedure is a good choice if it meets the following criteria:
The task cannot be accomplished simply using SQL (without a stored procedure). There is a higher overhead in invoking a stored procedure using JDBC than in running ordinary SQL, as well as greater complexity in the database.
The stored procedure can be viewed as a database-specific implementation of a simple Java interface.
It is concerned with persistence logic and not business logic and does not contain business rules that change frequently.
It produces a performance benefits.
The code of the stored procedure is not unduly complex. If a stored procedure is appropriate, part of the payoff will be a simpler implementation than could have been achieved in a Java object running within the J2EE server. Especially in an organization with DBA resources, 10 lines of PL/SQL will prove easier to maintain than 100 lines of Java, as such a size discrepancy would prove that PL/SQL was the right tool for the job.
Do not use stored procedures to implement business logic. This should be done in Java business objects. However, stored procedures are a legitimate choice to implement some of the functionality of a DAO. There is no reason to reject use of stored procedures on design grounds.