I've been programming J2SE since 96 and J2EE for the past
3 years and I've had an idea for quite some time that no
one seems to have brought up:

Stored procedures are evil mostly due to maintainance and
portability issues, however in "real life" projects we
need them to minimize the database hit. Caching can solve
some things but not with all deployments. Furthermore some
things are just more elegant as a stored procedure i.e.:

We have a logging system that is completely based on
database triggers and doesn't require us to write code
inside every entity bean that interfaces with the database.


Anyway J2EE can solve these problems. Since most major
databases support stored procedures in Java (even if they
use a different API an adapter can be used to unify a
subset of their features). We can present an interface to
the user which they can implement as the stored procedure.
The schema on which it will be applied can be indicated in
the deployment descriptor and the ejb home can define
a method to invoke the stored procedure (if it is not a
trigger).

Problems I forsee are:

1. Deployment will get "tricky" - since deploying a stored
procedure isn't automatic JBoss will probably have to
generate the code for the database and instructions
for the DBA to deploy the procedure... Ugly at first but
I think that this will encorage database vendors to work
on a unified stored procedure deployment API.

2. Triggers invoking EJB's - this could cause a problem
since the database's internal VM may be different from
the VM of the bean. This caused a problem for us under
websphere which relies on an IBM VM for IIOP and Oracle
that has an internal VM.

I think that a lot of ejbhome methods will migrate to
stored procedures and significantly boost the performance
of J2EE.

I'd like feedback from JBoss developers (would be glad to
help with the implementation myself with the little time
I have) about feasability and issues. If this can be made
to work I have no doubt this will make it into J2EE as
well.