stmt.getConnection() != Connection used to create the statement

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

stmt.getConnection() != Connection used to create the statement

Alexander Rupsch
Hi,

I'm not an expert in implementing connection pools or jdbc itself. But
shouldn't the following code work?

Connection con = pool.getConnection()
PreparedStatement ps = con.prepareStatement()

con.equals(ps.getConnection) // returns false!

Ok, I don't need it to be equal, but the following also does not work:

ps.getConnection().close()
con.isClosed() // is false!!!

That means, if I have a Statment and want to close its connection, I
have to remember the conncetion by myself. Is that the requested
behavior? Because of this my pool was running over.

The java.sql API says that Statment.getConnection() has to be the
connection which created the statement.

regards.

--
Alexander Rupsch
http://www.dreinhalb.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

Alexander Sack-2
Alexander Rupsch wrote:

> Hi,
>
> I'm not an expert in implementing connection pools or jdbc itself. But
> shouldn't the following code work?
>
> Connection con = pool.getConnection()
> PreparedStatement ps = con.prepareStatement()
>
> con.equals(ps.getConnection) // returns false!
>
> Ok, I don't need it to be equal, but the following also does not work:
>
> ps.getConnection().close()
> con.isClosed() // is false!!!
>
> That means, if I have a Statment and want to close its connection, I
> have to remember the conncetion by myself. Is that the requested
> behavior? Because of this my pool was running over.
>
> The java.sql API says that Statment.getConnection() has to be the
> connection which created the statement.
>


I cant tell what pool technology you are using and in what specific
environment. But in general, pools do the handling for you. They
implement connection reuse: that is, the connnection is only closed
virtually (e.g. freed for reuse) and not closed physically to improve
performance. In consequence pools need to keep track if a connection can
be reused or not (e.g. pool grows on subsequent getConnection requests).
This is done by keeping track whether all statements and resultset have
been closed so far. Otherwise, the connection will not be reused and
your pool will grow and grow ... So check if you forgot to close a
statement or a resultset somewhere (put the close() calls into a finally
block to get sure that they are invoked properly).

Hope it helps.


--
Alexander Sack      +49 (40) 692 13 - 179     e-mail: [hidden email]
Contelligent ... CMS for j2ee & devels - http://www.contelligent.com
C:1 Financial Services GmbH  -  Dorotheenstr. 64   -   22301 Hamburg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

Alexander Rupsch
Hello Alexander,

> I cant tell what pool technology you are using and in what specific
> environment. But in general, pools do the handling for you. They
> implement connection reuse: that is, the connnection is only closed
> virtually (e.g. freed for reuse) and not closed physically to improve
> performance.

Well, of course, but I have to tell the pool that I don't want to use
the connection anymore. So if I ask the pool for a connection I have to
call close() to show that I'm ready.

In my case I ask for a connection and then I use it to drive several
queries. to create, use and close the statement I use only one method
with different sqls. the return of the statement looks like this:

      Connection conn = null;
      try
      {
         conn = stmt.getConnection();
      } catch (SQLException e)
      {
         throw new DAOException(e);
      }finally
      {
         DBUtil.cleanUp(conn, stmt);
      }

cleanUp() first close the stmt an then the connection. this works well
if I remember the connection which created the statement, but not if I
ask the statement for its creator to close it.

I can go around this problem very easy by using the "remember the con by
myself" workaround, but I was wondering about the current
implementation. You can see this behavior in dbcp and in dbpool (can't
find the link right now) and this makes me wonder....

I also could take a look in the statement pooling feature and don't even
use a connection., but this wouldn't clarify the problem.

Well, if I find the time, I'll write a little unit test to show what I
mean. That way I can also preclude the side effect of the integration of
dbcp in our application.

regards.
--
Alexander Rupsch
http://www.dreinhalb.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

elifarley
I have the same problem while using dbcp with OC4J. Are you using it ?


On 8/17/05, Alexander Rupsch <[hidden email]> wrote:

> Hello Alexander,
>
> > I cant tell what pool technology you are using and in what specific
> > environment. But in general, pools do the handling for you. They
> > implement connection reuse: that is, the connnection is only closed
> > virtually (e.g. freed for reuse) and not closed physically to improve
> > performance.
>
> Well, of course, but I have to tell the pool that I don't want to use
> the connection anymore. So if I ask the pool for a connection I have to
> call close() to show that I'm ready.
>
> In my case I ask for a connection and then I use it to drive several
> queries. to create, use and close the statement I use only one method
> with different sqls. the return of the statement looks like this:
>
>       Connection conn = null;
>       try
>       {
>          conn = stmt.getConnection();
>       } catch (SQLException e)
>       {
>          throw new DAOException(e);
>       }finally
>       {
>          DBUtil.cleanUp(conn, stmt);
>       }
>
> cleanUp() first close the stmt an then the connection. this works well
> if I remember the connection which created the statement, but not if I
> ask the statement for its creator to close it.
>
> I can go around this problem very easy by using the "remember the con by
> myself" workaround, but I was wondering about the current
> implementation. You can see this behavior in dbcp and in dbpool (can't
> find the link right now) and this makes me wonder....
>
> I also could take a look in the statement pooling feature and don't even
> use a connection., but this wouldn't clarify the problem.
>
> Well, if I find the time, I'll write a little unit test to show what I
> mean. That way I can also preclude the side effect of the integration of
> dbcp in our application.
>
> regards.
> --
> Alexander Rupsch
> http://www.dreinhalb.de
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

Alexander Rupsch


> I have the same problem while using dbcp with OC4J. Are you using it ?
No, dbcp is directly integrated in my application. It's a problem of dbcp...


--
Alexander Rupsch
http://www.dreinhalb.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

Alexander Sack-2
In reply to this post by Alexander Rupsch
Alexander Rupsch wrote:
> I can go around this problem very easy by using the "remember the con by
> myself" workaround, but I was wondering about the current
> implementation. You can see this behavior in dbcp and in dbpool (can't
> find the link right now) and this makes me wonder....

Yes, you have to do it. I can't tell about a specific standardization
document, but I think it's a best practice of pool implementors to rely
on users to close connections explicitly. The reason is that you can use
connections from pools for more than one statement, thus, you have to
close it in order to allow the pool to determine if that connection can
be reused.

What is your problem with remembering the reference to the connection?

>
> I also could take a look in the statement pooling feature and don't even
> use a connection., but this wouldn't clarify the problem.

Sorry, I don't get what you mean here .... ?


--
Alexander Sack      +49 (40) 692 13 - 179     e-mail: [hidden email]
Contelligent ... CMS for j2ee & devels - http://www.contelligent.com
C:1 Financial Services GmbH  -  Dorotheenstr. 64   -   22301 Hamburg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: stmt.getConnection() != Connection used to create the statement

Alexander Rupsch


Hello Alexander,

> Yes, you have to do it. I can't tell about a specific standardization
> document, but I think it's a best practice of pool implementors to rely
> on users to close connections explicitly. The reason is that you can use
> connections from pools for more than one statement, thus, you have to
> close it in order to allow the pool to determine if that connection can
> be reused.

Well, I know. But if I ask a statement for its creating connection
(stmt.getConnection()) and then then call close on that connection, the
connection won't be closed (at least not the one I used to call
createStatment()). So, what does the close call do in that
constellation?  what is closed/returned to the pool?

> What is your problem with remembering the reference to the connection?

Nothing in common. I have a BaseDAO class which is used as base for my
DAOs. The DAOs are generated. To simplify my generator template there is
a method createPreparedStatement() which is getting a connection an
creates a prepared statement. The method always ask the pool for a
connection. The specific DAO uses a standard cleanup which closes the
statement and the its creating connection. After changing to dbcp as
pool implementation, i run into the problem described above. Apart from
the fact, that the DAO only should to worry about the statement if it
only gets a statement, I wanted to know why pools don't implement
stmt.getConnection() in the way the JDBC-API claims it.


>>I also could take a look in the statement pooling feature and don't even
>>use a connection., but this wouldn't clarify the problem.
> Sorry, I don't get what you mean here .... ?

Statement pooling is a feature of dbcp, but I never used it until know
and I don't know how it works. If statement pooling would do what its
name says, it could be a solution for me. But this wouldn't answer my
question.

regards...

--
Alexander Rupsch
http://www.dreinhalb.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]