tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Johnny Kewl" <j...@kewlstuff.co.za>
Subject Re: Connection Pool and Connections
Date Fri, 01 Jun 2007 21:22:09 GMT
Hi LightBulb...

No its not going to work like I think you want it to...

                  Statement stmt= con.createStatement();
                  ResultSet resultSet = stmt.executeQuery(sSql); //For 
Select

and  say

                  Statement stmt = con.createStatement();
                  recs = stmt.executeUpdate(sSql); //For UPDATE

Will use the same connection because you just opened it or got it from the 
pool

BUT... they work independently......

ie one can succeed and the other can fail... and thats what I think you 
asking

If you want them to behave as a single TRANSACTION... its got nothing to do 
with the connection.

Code has to look something like this


                  con.setAutoCommit(false);

                    PreparedStatement ps1 = con.prepareStatement(sSql1);
                    PreparedStatement ps2 = con.prepareStatement(sSql2);


                    ps1.executeUpdate();
                    ps2.executeUpdate();

                  con.commit(); //Commit all statements together or none at 
all
                  con.setAutoCommit(true);

Now they must both work.... or both fail.... read up on it.... you can also 
rollback
and set roll back points and do all sorts of fancy things.

In normal JDBC programming you

OPEN CONNECTION
Execute Queries
CLOSE CONNECTION

but opening a connection takes a long time.... will make app slow
So all the pool does is open a whole lot of connections.... and it will give 
you one instantly.
When you give it back... it doesnt close it, it holds it open for another 
thread to use...
That all it really does....

What maybe confusing you is that in EJB application servers they hide the dB 
behind an entity bean, and then let you set annotations that control the 
transactions... but they have to coz the sql is now hidden... ultimately 
(simplified) underneath... the above is all thats happening.
Tomcat is not an EJB server... but once you know how to do POJO, its kinda 
nice knowing this code will work in any application... I think its easier, 
doing it the hard way ;)

Have fun

----- Original Message ----- 
From: "lightbulb432" <veerukrishnan@hotmail.com>
To: <users@tomcat.apache.org>
Sent: Friday, June 01, 2007 9:01 PM
Subject: Connection Pool and Connections


>
> When using the tomcat-dbcp DataSource, when my web application code gets a
> connection:
>
> myConnection = myDataSource.getConnection();
>
> then executes multiple separate statements
>
> myStatement1 = myConnection.createStatement();
> myStatement1.execute();
>
> myStatement2 = myConnection.createStatement();
> myStatement2.execute();
>
> then close the connection
>
> myConnection.close();
>
> Is it possible that myStatement1 and myStatement2 would be run using
> different physical database connections, or are they absolutely guaranteed
> to be executed using the same connection?
>
> Or is connection pooling only for not actually closing the physical 
> database
> connection on myConnection.close(), instead returning it to the connection
> pool?
>
> A different way of asking this is does connection pooling pool connections
> within an application connection (myDataSource.getConnection() and
> myConnection.close()), or between application connections?
>
> If this question doesn't make sense, I can clarify. Thanks a lot.
> -- 
> View this message in context: 
> http://www.nabble.com/Connection-Pool-and-Connections-tf3853952.html#a10918685
> Sent from the Tomcat - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
> 


---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message