Business Logic in PL/SQL or Java?

[Sten Vesterli]

Hi all, 

I'm designing an application to support the employment process in a large organization. As part of the process, the application must validate a number of rules on the prospective employment contract, e.g. minimum/maximum number of hours in various periods, no more than X temporary employments, etc.  

The application is regular ADF 11g (JSF + BC) on top of an Oracle 11g database.

We discussed whether this logic should be implemented in the application (Java AM method) or the database (PL/SQL stored proc called from AM). What are the arguments for one or the other?

Best regards

Sten Vesterli

[John Flack]

I hate to say this, because it is more work, but I think the answer is usually “Both”.  In other words, I have constraints in the database to keep bad data out, no matter whether the data comes from my ADF application, or some goofball developer like me with SQL Developer.  But I also have validations in the ADF application.  The ADF validations are for three purposes:

1.       They are closer to the user, give a better error message than the database constraint, and often provide a more immediate response with no database round trip.

2.       They can be warning or informational messages – i.e. “You might want to check this entry – it doesn’t look right, but I’ll let it in anyway if you say so.”  This is impossible to do in a database constraint – either it passes the constraint or it doesn’t.

3.       I can validate non-permanent entries – stuff that doesn’t get stored in the database, like query parameters.

--
You received this message because you are subscribed to the ADF Enterprise Methodology Group (http://groups.google.com/group/adf-methodology). To unsubscribe send email to adf-methodolo...@googlegroups.com
 
All content to the ADF EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the ADF EMG with a link to the Google Group (http://groups.google.com/group/adf-methodology).

[MARTIN Olivier]

Hi all

 

I think this depends on the profile of your developers (java developers vs forms developers to be short)

 

The way we have solves this in my company is closed to typical java application

All business logic in java except for  Business rules that requires to do business logic on a large set of data (>1000 rows) and returns only a limited rows of data as a result (<200). Otherwise all is done in java with sometimes complex SQL query.

 

Olivier

 

De : adf-met...@googlegroups.com [mailto:adf-met...@googlegroups.com] De la part de John Flack
Envoyé : vendredi 18 novembre 2011 15:23
À : adf-met...@googlegroups.com
Objet : RE: [ADF EMG] Business Logic in PL/SQL or Java?

CMA CGM SYSTEMS
Siege Social : 4, Quai d'Arenc, 13002 Marseille
Forme juridique : S.A
RCS Marseille 493 732 713
Capital Social : €10.000.800
SIREN/SIRET : 493 732 713 00018

[Ammar™ <ammar.sajdi@gmail.com>]

hello

I always favor PLSQL Stored procedures for three reasons

1)  You can use from many other different presentation layers and other applications

2)   You can modify without having to open the application and then go through the deployment steps.

3) PLSQL is easier to deal with and tightly integrated with the Database.

rgds

Ammar Sajdi

Amman - Jordan

Sten Vesterli

[Mark Robinson]

On Tue, Nov 22, 2011 at 5:01 AM, Ammar™ <ammar...@gmail.com> <ammar...@gmail.com> wrote:

hello

I always favor PLSQL Stored procedures for three reasons

1)  You can use from many other different presentation layers and other applications

That's really true of any place to store the business logic.  If it's well designed, you can move it anywhere you want.
 

2)   You can modify without having to open the application and then go through the deployment steps.

Going to have to say that's a terrible idea.  The purpose of a deployment process is to ensure that you can replicate what is running in production.  If you don't do that properly then who knows what you're running.  You might fix some bugs now, but if your fixes evaporate the next time someone else does a deployment then you haven't really fixed the bug.  Besides, how can you test your fixes in your development DB and make sure you've pushed out all your changes?

Also, the steps to do a deployment should be "mvn deploy", which really cuts down on the excuse that doing a real deployment is too much work.
 

3) PLSQL is easier to deal with and tightly integrated with the Database.

PL/SQL might be easier to deal with if you're a PL/SQL guy. 

Being tightly integrated with the database isn't really a good thing.  It's fine to use database features but excessive coupling will only make you more sensitive to any change in the database.  It's similar to the IE6 issue where it makes the cost of upgrading prohibitive because of how tightly IE6 is coupled.  You never want to the be in the position of being 'That App' that is forcing everyone to stay on old versions of the software indefinitely.

Mark
 

[Jean-Marc Desvaux]

In our case, we use a balance of both but always have a set of pl/sql package backing the application and used in Forms & ADF and more if needed.

If you can put constraints like unique keys, foreign keys and checks, why not PL/SQL packages which are a clean way to organise and "extend" the database.The only reason I see to avoid PL/SQL is if you want to achieve database independency.

Of course as Olivier pointed out, it's afterall a choice driven by  a Java v/s PL/SQL coding preference.

I know two negative points using PL/SQL, but both manageable:-
1/. Package dependency has to be managed to avoid auto recompilation causing an error when called first time after the changes are made.
2/. If your packages include DML you must be careful of inconsistencies in data between VOs and DB that can cause all sorts of errors like "fail to validate row" etc... for example you must make sure any related data is refreshed on your apps side.

Taking for granted that we stick to an Oracle db and have no need for db independency, I have added some comments in favour of PL/SQL below following Mark's comments on Ammar points.

-Jean-Marc

Le mercredi 23 novembre 2011 05:07:30 UTC+4, Mark Robinson a écrit :

On Tue, Nov 22, 2011 at 5:01 AM, Ammar™ <ammar...@gmail.com> <ammar...@gmail.com> wrote:

hello

I always favor PLSQL Stored procedures for three reasons

1)  You can use from many other different presentation layers and other applications

That's really true of any place to store the business logic.  If it's well designed, you can move it anywhere you want.

If true for any place, why not the database? It's there waiting for you to enrich its dictionary with your pl/sql code.
Of course you have to love it to enrich it :)

 

2)   You can modify without having to open the application and then go through the deployment steps.

Going to have to say that's a terrible idea.  The purpose of a deployment process is to ensure that you can replicate what is running in production.  If you don't do that properly then who knows what you're running.  You might fix some bugs now, but if your fixes evaporate the next time someone else does a deployment then you haven't really fixed the bug.  Besides, how can you test your fixes in your development DB and make sure you've pushed out all your changes?Also, the steps to do a deployment should be "mvn deploy", which really cuts down on the excuse that doing a real deployment is too much work.

 I don't agree it's a terrible idea. I can't see why you can't replicate what is running in prod with pl/sql. You do it similarly to what you do for the other db objects.

 Same applies to a change in the DB tables for example and you have to manage db side changes anyway.

 Usually the pl/sql code role is to make checks, return data other than values directly in a database (format an employee name for ex, pipelining is a good example too) or run a process(close an accounting period for ex), I mean it is part of the Database design and build independently of your application.
Your ADF application will consume the functions and will follow/implement the changes, not the opposite.

 

3) PLSQL is easier to deal with and tightly integrated with the Database.

PL/SQL might be easier to deal with if you're a PL/SQL guy. 

Being tightly integrated with the database isn't really a good thing.  It's fine to use database features but excessive coupling will only make you more sensitive to any change in the database.  It's similar to the IE6 issue where it makes the cost of upgrading prohibitive because of how tightly IE6 is coupled.  You never want to the be in the position of being 'That App' that is forcing everyone to stay on old versions of the software indefinitely.

If you don't want to be dependant on an Oracle DB I agree otherwise I don't.
PL/SQL written 20 years ago is running well on 11gR2, in fact when you have new releases it is much better to have PL/SQL guy fine tune the large processes using the new releases techniques and features as it is more part of the SQL & PL/SQL expertise.
PL/SQL brings more performance capabilites/features than SQL on its own.
That's why to me tighly integration is a good thing for performance and tuning.
Of course we can say what will happen if Oracle stops PL/SQL development, but I think we can easily bet on the fact that it won't be the case very soon.
 

[Mark Robinson]

Hi Jean-Marc,

Thanks for your comments.

Le mercredi 23 novembre 2011 05:07:30 UTC+4, Mark Robinson a écrit :

On Tue, Nov 22, 2011 at 5:01 AM, Ammar™ <ammar...@gmail.com> <ammar...@gmail.com> wrote:

hello

I always favor PLSQL Stored procedures for three reasons

1)  You can use from many other different presentation layers and other applications

That's really true of any place to store the business logic.  If it's well designed, you can move it anywhere you want.

If true for any place, why not the database? It's there waiting for you to enrich its dictionary with your pl/sql code.
Of course you have to love it to enrich it :)

That's true but only to the extant that it actually enriches the dictionary.  Is it actually extending the dictionary or are you building a proper app on top of the database?
 

 

2)   You can modify without having to open the application and then go through the deployment steps.

Going to have to say that's a terrible idea.  The purpose of a deployment process is to ensure that you can replicate what is running in production.  If you don't do that properly then who knows what you're running.  You might fix some bugs now, but if your fixes evaporate the next time someone else does a deployment then you haven't really fixed the bug.  Besides, how can you test your fixes in your development DB and make sure you've pushed out all your changes?Also, the steps to do a deployment should be "mvn deploy", which really cuts down on the excuse that doing a real deployment is too much work.

 I don't agree it's a terrible idea. I can't see why you can't replicate what is running in prod with pl/sql. You do it similarly to what you do for the other db objects.

 Same applies to a change in the DB tables for example and you have to manage db side changes anyway.

 Usually the pl/sql code role is to make checks, return data other than values directly in a database (format an employee name for ex, pipelining is a good example too) or run a process(close an accounting period for ex), I mean it is part of the Database design and build independently of your application.
Your ADF application will consume the functions and will follow/implement the changes, not the opposite.

It's a bad idea because of the implications.  Changing one method instead of another because it skips the deployment process is a terrible development pattern.  In reality, the pl/sql(or whatever) need to be deployed simultaneously with any web applications.  It will, by definition, lead to unrepeatable builds and there is nothing more fun than trying to figure out why X works on production but not in test even though "nothing has changed".  It reminds me of the practice of 'Partial Deployment' where people would hot patch the JAR files.  I managed to stop that non-sense by making it harder to do it badly than to do it right.
 

 

3) PLSQL is easier to deal with and tightly integrated with the Database.

PL/SQL might be easier to deal with if you're a PL/SQL guy. 

Being tightly integrated with the database isn't really a good thing.  It's fine to use database features but excessive coupling will only make you more sensitive to any change in the database.  It's similar to the IE6 issue where it makes the cost of upgrading prohibitive because of how tightly IE6 is coupled.  You never want to the be in the position of being 'That App' that is forcing everyone to stay on old versions of the software indefinitely.

If you don't want to be dependant on an Oracle DB I agree otherwise I don't.
PL/SQL written 20 years ago is running well on 11gR2, in fact when you have new releases it is much better to have PL/SQL guy fine tune the large processes using the new releases techniques and features as it is more part of the SQL & PL/SQL expertise.
PL/SQL brings more performance capabilites/features than SQL on its own.
That's why to me tighly integration is a good thing for performance and tuning.
Of course we can say what will happen if Oracle stops PL/SQL development, but I think we can easily bet on the fact that it won't be the case very soon.

I'm not saying PL/SQL is bad, it's just that the over-reliance on it will cause problems.  PL/SQL for data intensive tasks is an excellent idea since the major bottleneck is throughput.  On the other hand, how many people use PL/SQLUnit to make sure their code still works in the morning?  More to the point, I would disagree that performance is the best metric.  I would prefer things like maintainability or extensibility over performance.  Programmer time increases at 5% per year, hardware costs drop by 33% per year.

Oracle will never drop PL/SQL, they might however drop functions or change how they work.  It's all fun and games until you need to correct some 'fixed' behavior in a new release -_-

Mark