i have a question about the right architecture. i have an application which uses connection pool. there are different methods at my "model" tier which has to work with db: f.e. a method which makes update and method which returns some values taken from resultset. i know that in many cases i'll have to use these "update" and "select" methods one right after another, and in the future may be even a number of such methods together . the question is: should each of these methods take a connection from the connection pool inside the method body (which will take 2 or more connections from the pool but keep the mvc architecture clean), or should they recieve a connection as a value passed from outside and just use different statements (which will use only one connection from the pool, but will move the call to the pool to the view tier) ? thanks
There are a couple of situations you need to consider- 1. what is the autocommit settings on the connection? if this is set to false This will influence if u need to send the connection object as a parameter. i.e of the 'select' needs to read what the 'update' as done, then they should either have been done using the same connection or the 'select' should be reading committed data. 2. from a puristic viewpoint it is practise to set commit to true. therefore each transaction with the database must be granular and complete in itself. from this viewpoint, it is best to obtain a new connection for each 'sql' activity. hope this helps.
Hi Cyc, I would also go with the second option. It is the container's responsibility to manage the pool. I would leave it up to the container. I believe it would be just as efficient to use the container as it would be to pass the connection object to different SQL statements depending on how you had your container configured. Just my $.02 Regards, Ryan
SCJP 1.4, SCWCD
Java: The power, elegance, and simplicity of a hand grenade