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
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.
An IDE is no substitute for an Intelligent Developer.
Sumeet Singh Aryan
Joined: Jul 30, 2008
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.