> I'm right now choosing the design patterns for an web portal for an
academic
> institution. I just started a philosofical discussion about how we will do
> database access. I was thinking about use stored procedures extensively,
but
> some people says me I should avoid them whenever possible, and put the
> business logic in Java. What you all think about it?
There's never a right or wrong way. In general, I've stayed away from
stored procedures because they aren't portable and a few databases don't
even support them.
Clearly, there are advantages in having a lot of database work taken care of
within the database, saving the interaction overhead between the client and
server. For example, we have quite a few objects that require mutiple calls
to fully delete them, and a stored procedure would work well. This is also
a better strategy is there's multiple ways the database can be transformed,
and not just from a database app that you've written (i.e. another app,
possibly written in a different language, via the SQL command line, or using
one of the various SQL-GUI tools).
An advantage to keeping in the Java code is it's more portable and of course
the "stored procedure" is written in Java, so learning db-specifics isn't
required. Also, there's a clarity advantage when database interactions are
all in one place, making it obvious how the data is created, queried,
modified and deleted.
Good luck...
David