Java Reference
In-Depth Information
used by applications (and for that matter, there are even some who believe that
SQL
itself should not be used). On the other end, you have the database purists
who believe that every single database interaction should be performed through a
stored procedure.
The simple fact of the matter is that the old adage that “Purists are always
wrong” applies here, because both extremes are wrong. Stored procedures are a
tool, and should be viewed as nothing more and nothing less than that. As an anal-
ogy, let's look at the carpenter: he uses a hammer, a tape measure, and a saw. While
he could use a hammer to measure a board, a tape measure does a much better job
of it (and the same can be said for using the saw to drive nails). Every job has a tool
that is the most appropriate, and every tool has a job that it was designed to do.
Any time you use the wrong tool for a job, it does not work very well.
Using the right tool for the job
Such is the case with
SQL
, stored procedures, and application code. Some opera-
tions are accomplished very well by simple
SQL
, others by stored procedures, and
still others by application code.
For example, consider a case in which you have to query a handful of tables for
a report. Grabbing all of the data from all of the tables and then filtering and join-
ing it all back together in application code makes very little sense. Creating a
stored procedure for a simple query also adds complexity with very little added
value. Putting that
SQL
into an i
BATIS
mapped statement and running it that way
is a quick, easy, and efficient solution to the problem in this case.
Now, for a more complex report that has to do multiple subqueries and left
joins to gather the data from tables with millions of rows in them, a stored proce-
dure makes much more sense. Using a stored procedure, you have many more
options available for optimization.
For an application where you want to use Dynamic
SQL
, mapped statements
are also very useful. In chapter 8, we have an example of using Dynamic
SQL
where the
SQL
statement is built using Java, a stored procedure, and a mapped
statement with dynamic elements in it. We won't spoil the surprise, but if you just
can't wait to find out, skip ahead to section 8.5 and take a look.
Another consideration with stored procedures is that they can be called to do
updates and return data. This can cause issues when transactions are not commit-
ted because the method used to call the procedure does not normally require a
commit. In those cases, the transaction manager needs to be configured to always
commit, even after read operations, or you have to manually manage the transac-
tions yourself, as in the following example:
