Java Reference
In-Depth Information
Modern stored procedures
In some circles stored procedures are still considered best practice for three-tier
and N-tier applications, such as web applications. Stored procedures are now
treated more like remote procedure calls from the middle tier, and many of the
performance constraints are solved by pooling connections and managing data-
base resources. Stored procedures are still a valid design choice for implementing
the entire data access layer in a modern object-oriented application. Stored proce-
dures have the benefit of performance on their side, as they can often manipulate
data in the database faster than any other solution. However, there are other con-
cerns beyond simply performance.
Putting business logic in stored procedures is widely accepted as being a bad
practice. The primary reason is that stored procedures are more difficult to
develop in line with modern application architectures. They are harder to write,
test, and deploy. To make things worse, databases are often owned by other teams
and are protected by tight change controls. They may not be able to change as fast
as they need to to keep up with modern software development methodologies.
Furthermore, stored procedures are more limited in their capability to imple-
ment the business logic completely. If the business logic involves other systems,
resources, or user interfaces, the stored procedure will not likely be able to han-
dle all of the logic. Modern applications are very complex and require a more
generic language than a stored procedure that is optimized to manipulate data.
To deal with this, some vendors are embedding more powerful languages like Java
in their database engines to allow for more robust stored procedures. This really
doesn't improve the situation at all. It only serves to further confuse the bound-
aries of the application and the database and puts a new burden on the database
administrators: now they have to worry about Java and C# in their database. It's
simply the wrong tool for the job.
A common theme in software development is
overcorrection
. When one problem
is found, the first solution attempted is often the exact opposite approach. Instead
of solving the problem, the result is an equal number of completely different
problems. This brings us to the discussion of inline
SQL
.
Inline SQL
An approach to dealing with the limitations of stored procedures was to embed
SQL
into more generic languages. Instead of moving the logic into the database,
the
SQL
was moved from the database to the application code. This allowed
SQL
statements to interact with the language directly. In a sense,
SQL
became a feature
