Re: Embedded SQL in Java classes

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Sat, 05 Mar 2005 11:20:42 +0000
Message-ID: <4229960A.1040308@orindasoft.com>

Disclaimer: I work for a company that markets a product that writes Java source code to run PL/SQL procedures....

jimmy gogo wrote:
> Hi,
>
> I am currently working with BMP EJBs and Oracle 8.1.7.
> I am finding that the queries are growing larger and more complex with
> lots of bind variables. I am uncomfortable hiding this logic away in
> the Java because:
> 1) The prepared statements get messy with so many input parameters
> 2) Hides dependencies on database objects
> 3) Need to rebuild/redeploy EAR If SQL logic changes.
>
> So I would prefer to move this logic out of the Java and back into the
> database. Now I could write a PL/SQL package to encapsulate this and
> return a ref cursor to the EJB (as multiple row result-set). I have
> also considered placing most logic in views and joining across same
> simplifying the Java end queries. However I suspect there may be a
> performance hit if I do not limit the results to what I actually want
> (as opposed to a big view).
>
> I am wondering what other options exist? What is the usual best
> practice approach here?
>

Creating a PL/SQL package is usually the best thing to do in this situation. I would avoid trying to solve this with views - it just makes life more complicated and can lead to weird performance issues. As a general rule you should also avoid bringing anything back from the database you don't actually need, so filtering results *after* they are returned is a big no-no.

PL/SQL allows you to be vague about what's in a cursor that's returned by a procedure. If your app has lots of lookup stuff you could write one procedure that returns different 'stuff' depending on what you ask it for - e.g. a list of Counties, Medical procedure names or countries depending on what you asked.

The real advantage of PL/SQL packages is that they allow you stop worrying about the exact nature and shape of your DB and instead focus on creating a library of well behaved Procedures that mesh with the needs of your application.

David Rolfe
Orinda Software
Dublin, Ireland
www.orindasoft.com Received on Sat Mar 05 2005 - 05:20:42 CST