Java Reference
In-Depth Information
This behavior is almost a bug—not quite, but almost. If you do not care
about the order that the statements get executed in, one person did man-
age to hack the
SqlExecutor
class to make it reuse the prepared state-
ments out of order. However, this is not yet supported in i
BATIS
.
NOTE
Some people use batched statements to insert a group of records that differ only by
a value that can be easily determined. One example is a system that inserted up
to 200 records that represented tickets in the database. The tickets differed only by
the database-generated primary key, and a ticket number had a starting value and
was incremented for each ticket inserted. A loop in the
DAO
was used to accomplish
the job, but a stored procedure would have been a faster and cleaner solution.
While running statements in batch mode may improve performance slightly,
using a stored procedure (section 5.5) will generally provide more of an improve-
ment if the statements can be grouped into a stored procedure easily. For exam-
ple, we could implement a
deleteOrder
method using the same approach as with
the update example earlier. However, to delete an order and its related order
items, all we need to know is the identity of the order, which in this case is just an
integer. Deleting the order and its children would be faster and easier using a
stored procedure than the equivalent i
BATIS
code would be.
5.5 Working with stored procedures
Stored procedures are blocks of code that execute in the database server process.
While most stored procedures are written in a database-specific language that is
generally based on
SQL
, some vendors are now allowing other languages (e.g.,
Oracle allows stored procedures in Java, Microsoft has planned support for stored
procedures in C#, and Postgre
SQL
allows nearly any language).
5.5.1
Considering the pros and cons
Stored procedures are often seen as an enemy by Java developers, because they
are platform specific (database platform specific, not necessarily operating system
specific), which offends the sensibilities of some Java developers.
As developers who are more interested in solving problems than using a partic-
ular solution, we find stored procedures to be a compelling option for optimiza-
tion and also for encapsulating the solutions to complex data-centric problems.
Don't be an extremist!
There are two polar extremes in discussing when to use stored procedures. On
one end, you have Java purists who believe that stored procedures are never to be
