I want to know whether the usage of stored procedure in a web based application is advisable or not.I understand that most of the DB interaction can be handled by using an efficient App Server.Should the stored procedure be called by using session beans as interface or the logic should be written in session bean itself.
Pls consider this for a web application which might have search based features and minimal txns.
Most likely, your project can be successfully completed using ejbs or stored procs or some combination of the two.
If you use ejbs, rather than stored procedures, your app is more likely to be "database vendor neutral". This is useful for portability of your code and portability of your knowledge.
I typically avoid stored procs, moving my business logic into an ejb instead (or a plain java class). Then, I may add a stored proc here and there if needed (for performance, say). In this case, you can call the stored proc from a session ejb.
Smart Soft - The Java Training Company
Thanq so much for a prompt reply.
One more query -If the sql statements r written in the session beans then again the beans become databse dependent.Or shud we go for entity beans for retreival of data.
No, the only thing "database dependent" about having SQL statements in your session beans is the fact that you are dependent on a database which supports SQL. As a side note, you would be dependent on the EJB server you were running your session bean within from the fact that you must get an initial context to the database your are using by means of that particular EJB vendors specifications, using their JDBC driver, etc.
Using entity beans versus session beans for retrieving data from the database depends on many things, such as: is the data read-only?; will multiple clients be retrieving the same data; etc.
every decent rdbms uses stored procedures. my advice - code for them. they are fast, lean, efficent, encapsulate a tremendous amount of functionality that to program in java would be horrendous, and above all - they allow easy change without recompilation. they also act as a good mechanism of indirection in that - should table names change - architectures alter - etc. - your bean can still work. also - don't forget the fact that you could alter a stored proc without even bringing a live application server down to do this.
the level of indirection is superb for writing against different vendors systems. should even the SQL standards change - recompilation won't be an issue.
your bean can now magically take on new identities - and gain a performance benefit an order of magnitude - not to mention very advanced transactional management.
don't let the java hype fool you into thinking that there is a better way. these debates have raged for years and had just been sorted - then for lack of anything better to do - something threw some mud into the pie.
true - in some applications an entity bean might seem cool. i wouldn't like to be the one who had to rip it apart in a few years time with egg on my face because the database had become 100 times larger. basically - the lessons of the last 20 years are more applicable now than ever. scalability won't go away - it will always get bigger. software becomes more complex - and complexity is built from basic building blocks. stateless objects encapsulate the idea of atomicity very well. atomicity is inherently suitable for transactional manipulation.
use the tool for the job - its never failed anybody yet. j2ee is a set of interfaces describing things that enterprise developers do - both the good things and the bad things.
Yeah, let's just stick _everything_ into the database - business logic, triggers, stored procedures, etc.
Wait a minute, won't that mean we'll have a two-tier, client-server application? Wasn't that tried and tested and proved to be inferior? Isn't that why people invented N-tier applications which led to application servers and the EJB architecture? But then again, if you say that stored procedures are so advantageous, then why add the overhead of an application server (that just calls stored database procedures anyway)? Let's cut out the middle man and increase our profits! Why should I let a real estate agent sell my house when I can sell it myself? Why should I let a mechanic fix my car when I can fix it myself? Maybe because the mechaninc is better at fixing it than I am (because that's his area of expertise). Maybe I'm better at driving the car (which is my area of expertise). Why buy bread from the baker when I can bake my own? Maybe because when I suddenly need to dramatically increase my required amount of bread, I'll have big trouble because I don't have the resources - but a baker does since he has to be able to increase his capacity and that should be transparent to me.
But hey, if I make my own bread, I'll save myself a trip to the baker's and that means I'll get my bread quicker(?)
Food for thought - my two cents' worth.
It's not black and white. For business logic, there is room for both : session beans and stored procedures. I prefer
doing my business logic in session beans. However, if it
involves too many network round trips to the database,
I optimize by implementing some of the business logic in
The whole thread looks pretty interested, but I will put more weight implementing the business logic in the Business layer (BL) rather then in Data access layer (even in database it self). Despite of the fact that you cant achieve that much performance using object pooling and caches then you can get from SPs. All transactions based functions are atomic and very closely bounded with the business terms: - for example, <br/>
Withdrawing Funds and Deposits makes one transaction, does it sounds like a business function or a technical function; surely it is a business function and should be handled in BL, with transactions. We have other areas those SP will not support up to my knowledge
- Heterogeneous database support in Transactions<br/>
Moreover if we implement over Business Logic in SPs then we are scattering our BL into pieces and it will be hell to maintain the large system, which suppose to be extensible. <br/>
Conclusions: - <br/>
Before going for SP or EJB etc, consider the followings
1. how large would your system be,
2. how much it is required to be extensible
3. how many types of database would participate in a transaction
4. how much clean do you want to have you Technical Design Document, if you are distributing your business logic in not BL related layers you are surely making mess and it would be difficult for the maintenance team to maintain the project
I would like to know how concurrent access is managed in stored procedures.
e.g. If we use EJB's we can have pool of beans.
Do we have something similar to that in StoredProcedures
One of the core features of RDBMS systems (aka database) is to manage concurrent access while maintaining data integrity. SP is part of the RDBMS API, same as SQL.
You can regard an SP as a database service/data API interface.
My rule-of-thumb for SP is to use only if we get performance boost of 500% or more.
Other cases may include; when your team has bunch of PL/SQL or T-SQL developers and you can't fire them.
I suggested a rule of thumb for choosing between
(i) EJB (or middle-tier Java EE component,
(ii) and Java stored procedures,
(iii) and PL/SQL or other proprietary stored procedure language, in the freely-available sample chapter of my book http://db360.blogspot.com/2006/03/stored-procedure-as-database.html#links