Repost: Help with PreparedStatement pooling

[Edward Q. Bridges]

Sorry for the repost, but after working with this further, I'm really 
stuck as to how I may confirm whether or not statement pooling is really 
working.

As far as I can tell, I'm getting new instances (i.e. == is returning 
false) of an innermost delegate statement for the same SQL query on the 
same connection.  This does not sound correct.  Maybe I'm missing something?

Thanks in advance for any comments.
Edward Q. Bridges wrote:
Dirk, the code you provided isn't working for me (see below).
I'm thinking that my configuration is wrong.  Can someone please 
double check what I'm doing to configure it?  The complete 
configuration is below, but here's the key part:

      KeyedObjectPoolFactory stmtPoolFactory = new 
GenericKeyedObjectPoolFactory(null);

      PoolableConnectionFactory x = new PoolableConnectionFactory(
              connFactory,
              connPool,
              stmtPoolFactory,
              cfg.getValidationQuery(),
              cfg.isDefaultReadOnly(),
              cfg.isDefaultAutoCommit(),
              cfg.getDefaultTransactionIsolation()
      );
Do I need to provide something else to the GenericKeyedObjectPoolFactory
besides a "null" value before giving it over to the 
PoolableConnectionFactory?


I copied and pasted what you provide as such:
     conn = ds.getConnection();
     stmt1 = conn.prepareStatement("select getDate()");
     Statement ustmt1 = ((DelegatingStatement) stmt1)
                                .getInnermostDelegate();
     stmt1.close();
     stmt2 = conn.prepareStatement("select getDate()");
     Statement ustmt2 = ((DelegatingStatement) stmt2)
                                .getInnermostDelegate();
     stmt2.close();
     assertSame(ustmt1, ustmt2);
And I get this:
junit.framework.AssertionFailedError:
 expected same:<[EMAIL PROTECTED]>
 was not:      <[EMAIL PROTECTED]>
When I change to use assertEquals() (assertSame() uses "=="):
junit.framework.AssertionFailedError:
 expected:<[EMAIL PROTECTED]>
 but was: <[EMAIL PROTECTED]>
Thanks in advance.
Ed
-------------------------------------------------------------------------------------------------- 


Dirk Verbeeck wrote:
Hi Edward,
Did you close the first statement before preparing the second one?
If you didn't close it the second prepare will indeed return another 
stmt.

You want something like this:
  Connection conn = ds.getConnection();
  Statement stmt1 = conn.prepareStatement("select 1 from dual");
  Statement ustmt1 = ((DelegatingStatement) 
stmt1).getInnermostDelegate();
  stmt1.close();
  Statement stmt2 = conn.prepareStatement("select 1 from dual");
  Statement ustmt2 = ((DelegatingStatement) 
stmt2).getInnermostDelegate();
  stmt2.close();
  assertSame(ustmt1, ustmt2);

Regards
Dirk
Edward Q. Bridges wrote:
Hi, I'm quite confused about how to get prepared statement pooling 
to work.

This is the code I'm workign with:
       ConnectionFactory connFactory = new 
DriverManagerConnectionFactory(
               cfg.getUrl(),
               cfg.getUsername(),
               cfg.getPassword()
       );

       ObjectPool connPool = new GenericObjectPool(null, 
cfg.toConfig());
       KeyedObjectPoolFactory stmtPoolFactory = new 
GenericKeyedObjectPoolFactory(null);

       PoolableConnectionFactory x = new PoolableConnectionFactory(
               connFactory,
               connPool,
               stmtPoolFactory,
               cfg.getValidationQuery(),
               cfg.isDefaultReadOnly(),
               cfg.isDefaultAutoCommit(),
               cfg.getDefaultTransactionIsolation()
       );
       DataSource ds = new PoolingDataSource(connPool);
And when I run this, I'm getting an assertion failed when i retrieve 
two prepared statements for the same connection.

   assertEquals(stmt, stmt2);
   junit.framework.AssertionFailedError:
     
expected:<[EMAIL PROTECTED]> 
but was: <[EMAIL PROTECTED]>

   
assertEquals(((DelegatingPreparedStatement)stmt).getInnermostDelegate(), 


                    
((DelegatingPreparedStatement)stmt2).getInnermostDelegate());
   junit.framework.AssertionFailedError:
     expected:<[EMAIL PROTECTED]>
     but was: <[EMAIL PROTECTED]>

What should I be expecting on this?  How would I tell that the 
pooling is working correctly with PreparedStatements?
Can someone point out what I'm doing wrong above?

Thanks in advance
Ed Bridges

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]