I am also working on the same problem. Does it make sense to only pool the
prepared statements and if the connection is needed, get it from the prepared
statement that is checked out from the pool? The problems with maintaining a
pool of open prepared statements may depend on the database and the driver. I
would be interested to know what you come up with.
- Steven Peterson
Johan Compagner wrote:
> Hi,
>
> I was just wondering if i gane much when i also pool all my
> PreparedStatements.
>
> Now i get a connections of the pool in a method.
> In that method i created a PreparedStatement.
> set some values in it
> and execute it.
> Sometimes that preparedstatement is reused because it is in a lus.
>
> But now everytime i come in that method a new PreparedStatement is created.
> So why not pool them in the GenericConnection?
> The prepareStatement method:
>
> public PreparedStatement prepareStatement(String sql) throws SQLException {
> if (closed) throw new SQLException(SQLEXCEPTION_CLOSED);
> return (conn.prepareStatement(sql));
> }
> change to this:
>
> public PreparedStatement prepareStatement(String sql) throws SQLException {
> if (closed) throw new SQLException(SQLEXCEPTION_CLOSED);
> PreparedStatement statement = null;
> synchronize(_hmStatements)
> {
> statement = _hmStatemen.remove(sql);
> }
> if(statement == null)
> {
> statement = new
> PooledPreparedStatement(this,conn.prepareStatement(sql));
> }
> return statement;
> }
>
> and the close of the PooledPreparedStatement class add's that statement to
> the pool.
>
> The problem is that a PreparedStatement is attached to one Connection.
> So when i have 4 connections for pooling then i get 4 the same prepared
> statements
> for every connection 1.
>
> And if you use a lot of kind's of PreparedStatements over time. A lot of
> statements
> are open. Does the database mind?
>
> If this is not the right approace,
> How else should PreparedStatements be pooled?
>
> Johan Compagner
--
Steven Peterson, President
Frontier Productions, Inc.
310 Wesley Drive
Chapel Hill, NC 27516
http://www.frontierproductions.net
Tel: 919-942-1386
Fax: 919-933-2677
