I second this suggestion and recommend looking at jforum for an example
of putting prepared statements in properties files.

I've used all of the options mentioned so far, and none of them is
vastly superior. Frankly though, if you find it necessary to change your
sql that often, then it is a good indication that you need to study more
sql!

One of the guidelines I try to follow now is that there shouldn't be any
complex sql 'in' an application, regardless of whether or not it has
been externalized. If you need complex sql externalize it into a view
and let the dba handle the tweaks. Unlike stored procs, views are
relatively easy to port between databases, and they have the advantage
that they are well within scope for the database experts. The complexity
is still there, it's just accessible to the people most skilled at
dealing with the database (IME, selects tend to be much more complex
than either insets, updates or deletes; YMMV).

Shane

Shane

Less scalable than business logic in the business layer.  I am curious,
though, how do you rate the optimization of the execution?  Have you compared
the stored procedure approach to application-server clusters with the business
logic in the middle tier under massive parallel loads?

I should think that the middleware tier is more scalable.

I use a semantic approach to architecture that argues that business logic be
placed in the business layer, and data logic in the data layer.  It turns out
that that viewpoint simplifies the decision and leads to things like optimal
scalability and stability.