Good practice: Middle-tier DAL or Database API – Database access guideline

The reason I don't use "Best Practice" is that will prevent rest of people create some better new ideas, and let you out the 'Keep an open mind' zone.

So I turned to Good practice:

Middle-tier DAL or Database API – Database access guideline
(Hibernate vs Store procedure)

• Maximise SQL and minimise PL/SQL
• Minimise client code and maximise database server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

Reference:

Business Logic - PL/SQL Vs Java
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:883929178230

I prefer to put all logic that deals with data in PLSQL. There is no more natural language to interact with SQL data then PLSQL. None.

For example -- native compilation was added -- turned plsql into object code that runs natively on the OS.

Database API, build the data API in the database, you call the data API
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25405782527721

When to use Hibernate DAL
• One step SQL (SELECT OR UPDATE) to finish your goal
• Heavy and forced interaction in mid tier, like CCBB encryption, that means high coulping too, and hard to encapsulate the process logic.

Jave developer can help us on this list.

When to use Database API and Store Procedure Package
• Complex SQL
For example, book delete check, reference Single Hash Table and list of IDs.
• Many (more than 2) steps data machinations logic in a sigle module
• Reture rows more than one page, let's say 20 rows from database.

We like Database API approach, because it:
1.. make software components modular, I'm totally into modular programming
2.. software modules must carry out a very specific task (and be very efficient at carrying it out)
3.. each software module should be loosly coupled (to limit dependencies)
4.. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
5.. It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
6.. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
7.. The same APIs are available to all applications that access the database. No duplication of effort.

Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

Our concept is "build the data API in the database, you call the data API" .
Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.

On top of database schema we have an API that does the data stuff.

In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is – well, right where it belongs – next to the data, waiting for the 'next great programming paradigm to come along')

The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, a mobile phone, .

–
Storing application code in the database has had it's champions and detractors.

When you start putting application code in the database, you are in the thoroughly non-portable arena. That code, were you to port your application to another database, would have to be rewritten. But think about your application mid-tier layer, will you port Java code to C#/.Net,-> Pascal -> C -> VB, then to Python, Ruby on Rail ...etc., will you do that?

Its very specificity to that database means it can also take advantage of, and wire close to that engine. There are situations where stored code in the database can be notably faster. Supposed you have to update some chunk of a million rows after doing some machinations on the data.

In a stored procedure the data is read, manipulated, and updated in one step. Meanwhile if you did the same in your middle tier application code, you would have to send that data set over the network, do your manipulations, and send it back. Not only would it make this one task slower, but other transactions vying for that same data could potentially have to wait while that data is in transit, and being manipulated. Also, stored code can serve to encapsulate specific requests which can be invaluable at simplifying your overall application. All three databases support stored procedures and functions. Oracle also supports packages, or collections of stored procedures as well as various object oriented features, which almost no one ever uses. An additional note, a database engine actually context switches between stored code, and the SQL code embedded therein. As since of 9i, Oracle introduced bulk binding, so you can do work on large sets of rows, and update them all in one go, instead of each loop iteration. This feature can even further improve performance quite dramatically.

Document the SQL access database

We'll build a small application for this target, on Oracle APEX (HTML DB).
then QA (and Developer) need to record all the SQL access db in the new developing applications.
* SQL execution path
* SQL performance statistics