A design issue: JDBC queries VS. Stored procedures
1 posts in topic
Flat View  Flat View

Posted By:   Shane_Ye
Posted On:   Wednesday, August 14, 2002 09:36 AM

TO All Professional researchers and gurus here
What I am wondering is which one is the better practice: I am about to put JDBC queries as system's middle inside session bean methods while others suggest it's a better way to code all SQL queries into stored procedures and put them along with tables in the database and system's middle is used to call those stored procedures instead to construct queries within itself. I personally prefer to treat queries as part of system's business logic instead of data model, therefore it's better to be put into java source as part of middle-tier. Otherwise It might be against MVC pattern. What you think of this? any advice or opinion are greatly welcome. Thanks -Shane

Re: A design issue: JDBC queries VS. Stored procedures

Posted By:   Bozidar_Dangubic  
Posted On:   Wednesday, August 14, 2002 10:01 AM

I think the answer to this question is somewhat a matter of preference. as you said, some people swear by stored procedures while others never used them at all. this issue can be debated on several levels but I will give you opinion from what I think works.

stored procedures for me work well with inserts because of the fact that it is difficult to extract the primary key that gets created on the insert into a database table (e.g. autonumber, serial, ...) You can use a stored procedure that returns this value back to you after the insert occurs and it is somewhat easy to do on most database that I have used so far.

stored procedures do not work well for me for updates to the system. basically, if you take an approach to update only things that have changed (and not update entire objects at the time in which case you can use stored procedures in somewhat straight-forward manner), it would be very difficult and at times impossible implementation.

stored procedures work well as far as security is concerned in some cases. because you can give and revoke priviledges to stored procedures to users or group of users, it may be easier to do than giving individual prividges on the database or tables. what I mean by that is that is if you have all your SQLs run through stored procedures, you can give CRUD prividges on the database only to stored procedures and disallow any other activity on any table in the database. for managers that are mostly concerned with data security, this could be a preferred way to go.

lastly, transactional work could be helped with stored procedures. you can group SQLs that need to be executed to run in a single transactional stored procedure therefore eliminating the need to invoke several different SQLs that run in a single transaction. this was never an big issue in my opinion but I have seen the system where stored procedures handled themselves all transactional work on the database side by grouping logically SQLs that need to executed in a single transaction.

overall, I have always limited usage of stored procedures to inserts only. in the persistence component, reads, updates, and deletes I always handle myself. this is just a preference and what works for me the best and gives me the most flexibility.
About | Sitemap | Contact