Re: A design issue: JDBC queries VS. Stored procedures
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.