This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JSF and the fly likes Where to put the business logic? in java beans or in Database procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Java » JSF
Bookmark "Where to put the business logic? in java beans or in Database procedures" Watch "Where to put the business logic? in java beans or in Database procedures" New topic

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

Sumeet Singh Aryan
Ranch Hand

Joined: Jul 30, 2008
Posts: 43
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

The woods are lovely, dark and deep, But I have promises to keep; And miles to go before I sleep, And miles to go before I sleep. - Robert Frost
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17018

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.

Customer surveys are for companies who didn't pay proper attention to begin with.
Sumeet Singh Aryan
Ranch Hand

Joined: Jul 30, 2008
Posts: 43
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)


    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.
    I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
    subject: Where to put the business logic? in java beans or in Database procedures
    It's not a secret anymore!