RE: Database programming standards

  • From: Leslie Tierstein <leslie_tierstein@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 3 Jun 2004 08:34:45 -0700 (PDT)

See inline comments, below:

-----Original Message-----

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of deen dayal
Sent: Wednesday, June 02, 2004 5:26 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Database programming standards

> Here is the list of problems I heard from others, do not know how far
> they are true:

> 1) Some Java programs do not close open cursors. 

> 2) On the fly SQLs ( no bind variables ) and each SQL is new for
> database causing scalability problems. 

True; both examples of sloppy coding. #2 is why we have cursor_sharing = FORCE. 
These are easy enough to check for and legislate against.

> 3) Applications do not typically make use stored procedures/packages and
> all the SQL are dumped into one huge database class file

Partially true, but too simplistic. There seem to be two approaches to coding 
Java applications with Oracle DB access:

1) the javahead approach

2) the database-centric approach.

(I believe these approaches are discussed in the Dorsey/Koletzke book, 
JDeveloper Handbook; a new edition is coming out soon, probably in the fall, 
but if you can get a copy of the first edition, I think this discussion is 
still there. The book also includes a set of standards for use in Java code 
that you might find useful, although it is geared heavily toward JDeveloper 
development.)

In the database-centric approach, the application puts as much intelligence as 
possible into the database. For example, a java class could be based on an 
updateable view, with the INSTEAD OF triggers doing a lot of the work. A 
database-centric approach also has the (obvious to us) advantage that the 
referential integrity constraints are in the database, so bad data can't be 
snuck in, either via sloppy code in the java application or via another 
application. Java programmers are strongly urged not to directly interact with 
the database, but to (1) use the updateable views and (2) use the equivalent of 
Designer's TAPI's (Table interfaces), written by the DB group, which 
encapsulate all DML and return cursors (oops; result sets) to the application.

When I first read about the database-centric approach, I thought this was 
wishful thinking. But I have actually come across projects where this is being 
used (especially encapsulating the DML within packaged procedures).

In the javahead approach, the application puts most of the intelligence into 
the java that runs on the middle tier (via web services, EJBs, Oracle's ADF BC 
(business components) or whatever. Of course, some intelligence here is 
required for a good user interface; but putting a lot of things here 
essentially duplicates what the database can do much better. Except you will 
probably need to see some connection pooling and caching being done.

One of the key points you need to investigate is what frameworks the developers 
are using.  For example, what Object-Relational mapping tool is the application 
going to use? Oracle supports (owns) both TopLink and ADF BC. TopLink was 
developed externally and bought by Oracle, so I don't know how much corporate 
support it's getting. ADF BC is home grown and, in my experiments, looks pretty 
powerful.  Are they using an MVC pattern? If so, what controller are they 
using? And so on.

In addition to Dorsey/Koletzke an Oracle center of excellence in Europe has 
developed JHeadstart, which generates java code from Designer modules. I have 
read excellent papers on java by people in this group. Don't know if anything 
is posted on the web, however.

Leslie


                
---------------------------------
Do you Yahoo!?
Friends.  Fun. Try the all-new Yahoo! Messenger

Other related posts: