• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Where to put the business logic? in java beans or in Database procedures

 
Sumeet Singh Aryan
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just want to know that where to put the business logic either in java beans or in database stored procedures. As i discussed with some of my friends from different companies; they suggested me to put in stored database procedures due to precompiled procedures irrespective of single queries, less databse hits etc... Please guide me regarding the same
 
Tim Holloway
Saloon Keeper
Posts: 18300
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Get a new set of friends.

I inherited an application where most of the business logic was in stored procedures. It's horrible. Here's why:

First and foremost, stored procedures are notoriously vendor-specific. Even Oracle and PostgreSQL stored procedures require some conversion to run on their opposite numbers and those are 2 databases that have more in common than almost any other. You may be sure that you'll never run on anything but SQL server, but one day your boss may come and say "we got a good deal on hosting this app on Linux. How hard will it be to make it run under MySQL?".

Stored procedures execute on the database server. Often a single database server may back a dozen or more application servers. Database server boxes are generally pretty powerful, but do you really want work that could be spread out over a dozen machines all be forced to execute on the database box? Will your DBA come and kill you if you do? Will the CIO?

When you code an app with application logic in more than one place, it frequently degenerates into a treasure hunt. There's no "one stop shopping" for code. You have to determine which of 2 codesets in which of 2 languages you're dealing with and you may have complex interactions between the two.

Stored procedure code is a maintenance issue. It's likely that your IDE doesn't allow stored procedures to be edited as easily as native code processes can be.

Source archiving and redistribution can be a MAJOR issue. DBMS's don't generally provide good support for keeping the stored procedure source code in a traditional version control system. And I've run into serious problems when people send me a zip file of "source code" and half the source code isn't there (because it's in someone's database locked up in their datacenter).

Stored procedures have some very important uses. But power lack that needs to be used judiciously.
 
Sumeet Singh Aryan
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks TIM for your explanation of your point of view

I agree with your point of view, even the source code of stored procedure is also open to the client if we give our DB script but this will not be the case for java .war/.ear/etc files. The client can modify the DB script of their own if he gots the source code in DB script.

but what you think about the following case:

suppose there is a button in your JSP.... and when you click on that button, i want to perform the following tasks in database:

  • Task 1: I want to fetch some data from the table1 on basis of some conditions.


  • Task 2: now i want to insert some of the extracted data in table2 (based on some condition)


  • Task 3: and some of the data in table3(based on some conditions)


  • Task 4: now if table2 and table3 are successfully inserted, then i want to update some status in a particular row in table4

  • So if i do the same in java then i have to do at least 4 Database Hits; one for each task (Since different DB tables play role in this, so i can't do the same in single query and also need some conditional logic)

    or

    I can do the same with only one database hit by calling the stored procedure (and the stored procedure will perform all my tasks that i defined earlier)

    please consider the above scenario in WEB application where AS is on different server and the DB is on different server.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic