Mailing-List: contact commons-dev-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Jakarta Commons Developers List" <commons-dev@jakarta.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Date: Tue, 24 May 2005 11:31:31 -0400
From: Andres Galeano <agaleano@perfectorder.com>
Subject: Re: [dbcp] Stmt Pooling FAQ Update and Example
In-reply-to: <1116945158.3932.53.camel@dhcp-5010-110.poss.com>
To: Jakarta Commons Developers List <commons-dev@jakarta.apache.org>
Message-id: <1116948691.3932.64.camel@dhcp-5010-110.poss.com>
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_m6Ds4N8AkIjLaRc8IAQOmA)"
References: <1116945158.3932.53.camel@dhcp-5010-110.poss.com>

--Boundary_(ID_m6Ds4N8AkIjLaRc8IAQOmA)
Content-type: text/plain
Content-transfer-encoding: 7BIT

Hi all,

Sorry for the second email, but I realized I made a mistake with the
first answer...

"GenericKeyedObjectPoolFactory" actually pools prepared statements even
if you provide "null" as the "KeyedPoolableObjectFactory".  I discovered
this through testing(attached), it would be nice if someone could add
that to the javadoc.

************************************************************
Q: <nowiki>Does the current 1.1 release support the poolable/caching of
PreparedStatements. (etc...)

A: The implementation of
org.apache.commons.pool.KeyedPoolableObjectFactory we provide is
org.apache.commons.dbcp.PoolingConnection.   You don't see it in the
commons-pool api because it's exists in the commons-dbcp api.

Although you don't need to provide a KeyedPoolableObjectFactory at all.
"GenericKeyedObjectPoolFactory" will correctly pool prepared statements
even if you provide "null" as the "KeyedPoolableObjectFactory".
************************************************************

Thanks again for reading this,
Andy G.

On Tue, 2005-05-24 at 10:32, Andres Galeano wrote:
> Hi all,
> 
> I'd like to offer an answer to one of your unanswered FAQs:
> 
> On: http://wiki.apache.org/jakarta-commons/DBCP
> Under: FAQ
> There is the following question:
> 
> ************************************************************
> Q: <nowiki>Does the current 1.1 release support the poolable/caching of
> PreparedStatements. (etc...)
> 
> A: ??
> ************************************************************
> 
> Currently this question is unanswered.  I had the same issue, and
> through reading the API of commons-DBCP and commons-POOL I was able to
> figure out how to use your Prepared Statement pooling.
> 
> In face I created JUnit test case(attached) that shows exactly how to do
> this.
> 
> I think it would be great if you could answer this questions with
> something like:
> 
> ************************************************************
> A: The implementation of
> org.apache.commons.pool.KeyedPoolableObjectFactory we provide is
> org.apache.commons.dbcp.PoolingConnection.   You don't see it in the
> commons-pool api because it's exists in the commons-dbcp api.
> 
> You can created one with code like this:
> ------------
> ConnectionFactory connectionFactory = new
> DriverManagerConnectionFactory(url, user, password);
> new PoolingConnection(connectionFactory.createConnection());
> ------------
> 
> Remember to close your PoolingConnection when your done, so that the
> underlying java.sql.Connection also get's closed.
> 
> Also see this example that Andres Galeano
> <andres.galeano@perfectorder.com> from Perfect Order has provided...
> ************************************************************
> 
> Any way, Thanks for reading this,
> Andy G.
-- 
Andres Galeano <andres.galeano@perfectorder.com>
Perfect Order Software Solutions
Work: 717-506-1112 x350
Cell: 717-919-5524
AIM/ICQ id: andygaleano

--Boundary_(ID_m6Ds4N8AkIjLaRc8IAQOmA)
Content-type: text/x-java; name=KeyedObjectPoolTest.java; charset=us-ascii
Content-transfer-encoding: 7BIT
Content-disposition: attachment; filename=KeyedObjectPoolTest.java

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DelegatingPreparedStatement;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.KeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * This test case tests DBCP's ability to pool {@link PreparedStatement}. 
 */
public class KeyedObjectPoolTest extends TestCase {
    
    /** I believe GenericObjectPool is thread safe. */
    private GenericObjectPool connectionPool = null;
    
    /** I believe PoolingDataSource is thread safe. */
    private PoolingDataSource dataSource = null;

    
  	public KeyedObjectPoolTest(String name) {
    		super(name);
  	}
  	
  	public static void main (String[] args) {
    		junit.textui.TestRunner.run (suite());
  	}
  	
  	protected void setUp() {
        // the following will likely need to be configurable....
        int maxActive = -1;
        int minIdle = 1;
        String url="", user="", password="";
        String driver="";
        // end [configurable]
        
        try {
            DriverManager.registerDriver((Driver)Class.forName(driver).newInstance());
            
            // I believe ConnectionFactory is thread safe and could be a member. 
            final ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, user, password);
            
            String sql = "select count(*) from messages";
            
            byte whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_BLOCK; 
            long maxWait = -1; // "When less than 0, the borrowObject() method may block indefinitely."
            int maxIdle = -1; // "Unlimited number of idle instances", why not they will expire.
            boolean testOnBorrow = true;
            boolean testOnReturn = false;
            // "The number of milliseconds to sleep between runs of the idle object evictor thread."
            long timeBetweenEvictionRunsMillis = 2 * 60 * 1000; // two minutes.
            // "when the value is -n, roughly one nth of the idle objects will be tested per run."
            int numTestsPerEvictionRun = -1;
            // "the minimum amount of time an object may sit idle in the pool before it is eligable
            //     for eviction by the idle object evictor"
            long minEvictableIdleTimeMillis = 2 * 60 * 1000; // two minutes.
            boolean testWhileIdle = false;
            
            ////////////
            // This is the basic connection pool...
            connectionPool = new GenericObjectPool(null, maxActive, whenExhaustedAction,
                     maxWait, maxIdle, minIdle, testOnBorrow, testOnReturn, timeBetweenEvictionRunsMillis,
                     numTestsPerEvictionRun, minEvictableIdleTimeMillis, testWhileIdle);
            
            ////////////
            // This is the perpared statment pool...
            KeyedObjectPoolFactory keyPoolFactory;
            {
                int maxTotal = -1;
                // The connection created here will be closed when the "PoolingConnection" is closed.
                // poolingConnForStmts = new PoolingConnection(connectionFactory.createConnection());
                // Oddly this does not define the "minIdle" argument or set method.  Is this a dbcp bug?
                keyPoolFactory =
                    new GenericKeyedObjectPoolFactory(null, maxActive, whenExhaustedAction,
                    maxWait, maxIdle, maxTotal, testOnBorrow, testOnReturn, timeBetweenEvictionRunsMillis,
                    numTestsPerEvictionRun, minEvictableIdleTimeMillis, testWhileIdle);
            }
            
            ////////////
            // This is the connection factory that will leverage both pools above.
            // I believe PoolableConnectionFactory is thread safe and could be a member. 
            final PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
                    connectionFactory,connectionPool,keyPoolFactory,sql,false,false,
                    Connection.TRANSACTION_READ_COMMITTED);
            
            connectionPool.setFactory(poolableConnectionFactory);
            
            poolableConnectionFactory.setPool(connectionPool);
             
            dataSource = new PoolingDataSource(connectionPool);
            
            // Usually it's bad to allow code to break the abastraction and get
            // the actual underlying connection.
            dataSource.setAccessToUnderlyingConnectionAllowed(true);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
  	}
  	protected void tearDown() {
        dataSource = null;
        
        try {
            connectionPool.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        connectionPool = null;
    }
  	
  	public static Test suite() {
    		return new TestSuite(KeyedObjectPoolTest.class);
  	}
  	
  	public void testBorrowObject() {
        
        try {
            Connection conn = dataSource.getConnection();
            try {
                String sqlA = "select count(*) from messages";
                String sqlB = "select count(*) from properties";
                
                String stmtA1 = execute(conn, sqlA);
                String stmtB1 = execute(conn, sqlB);
                
                String stmtA2 = execute(conn, sqlA);
                String stmtB2 = execute(conn, sqlB);
                
                String stmtA3 = execute(conn, sqlA);
                String stmtB3 = execute(conn, sqlB);
                
                assertTrue(stmtA1.equals(stmtA2) && stmtA2.equals(stmtA3));
                
                assertTrue(stmtB1.equals(stmtB2) && stmtB2.equals(stmtB3));
                
            } finally {
                conn.close(); // returns it to pool..
            }
            assertTrue(true);
            
        } catch(Exception ex) {
            fail(ex.getMessage());
            ex.printStackTrace();
        }
    }

    /**
     */
    private String execute(Connection conn, String sql) throws SQLException {
        
        String stmtInstance; 
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        try {
            {
                assertTrue(stmt instanceof DelegatingPreparedStatement);
                
                DelegatingPreparedStatement delegateStmt = (DelegatingPreparedStatement)stmt;
                Statement innerStmt = delegateStmt.getInnermostDelegate();
                assertTrue(innerStmt instanceof PreparedStatement);
                
                stmtInstance = innerStmt.toString();
                System.out.println("Using '" + stmtInstance + "'"); // should contain the memory address.
            }
            
            stmt.execute();
        } finally {
            stmt.close();
        }
        
        return stmtInstance;
    }
}


--Boundary_(ID_m6Ds4N8AkIjLaRc8IAQOmA)
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
--Boundary_(ID_m6Ds4N8AkIjLaRc8IAQOmA)--