File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Stored procedures in J2EE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored procedures in J2EE" Watch "Stored procedures in J2EE" New topic
Author

Stored procedures in J2EE

raj joe
Ranch Hand

Joined: Mar 17, 2005
Posts: 99
When should I use stored procedures in a J2EE applications, what are advantages and disadvantages of using stored procedures in J2EE applications.
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
One advantage is that access path is cached.


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by raj joe:
When should I use stored procedures in a J2EE applications, what are advantages and disadvantages of using stored procedures in J2EE applications.


Its usually performance vs. portability. All ORM solutions (such as Entity Beans) carry a performance overhead which is usually tolerable because of the benefits of portability. If the performance of the application is key then SPs will usually win over an ORM solution.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sravan Kumar
Ranch Hand

Joined: Sep 11, 2005
Posts: 121
Also, if the database and the server exist in two different machines (which they normally do), you wouldn't want to place multiple database calls to achieve one simple logic that involves multiple queries.

So, it's better to push the logic to database by writing stored procedures and just place one call using CallableStatement's, if it involves huge database operations.


keep smilin :: sravan<br /><a href="http://sravanpens.blogspot.com" target="_blank" rel="nofollow">I scribble here</a>
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Sravan Kumar:
Also, if the database and the server exist in two different machines (which they normally do), you wouldn't want to place multiple database calls to achieve one simple logic that involves multiple queries.

So, it's better to push the logic to database by writing stored procedures and just place one call using CallableStatement's, if it involves huge database operations.


This wouldn't bother me. Is a far lower cost to implement better app. server to database conectivity than to pay a developer to code Stored Procredures for every database type the application may have to support (and possibly recode them as database versions change).
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Some J2EE apps use big databases. One of the problems as I understand with SPs is one of scalability: they may work well with a small DB, but what happens when you need to filter on a DB that has 50 or more tables? In this case, I would be quite surprised if SPs with optional parameters could perform as well as dynamic queries.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored procedures in J2EE