JDBC Connection Pooling

[birdman5]

Hi,

I have a java procedure that executes SQL on a remote database. To improve performance, I have set up connection pooling, so connections are re-used.

This works ok for one user / session. It does not seem to work for multiple sessions though - new db connections are created each time. Any ideas what I'm doing wrong?

Here is a code excert:

import java.sql.*;
import java.io.IOException;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;

//dbURL = username/password@server:port:sid

public class doSql{
    public static void remoteCall(String dbURL)
        throws SQLException, IOException {

		Connection conn = getConnection(dbURL);

        //do SQL
        conn.close();
    }

  	private static OracleConnectionCacheImpl connectionCache;

	private static void createDataSource(String dbURL)
		throws SQLException {
		OracleConnectionPoolDataSource pds = new OracleConnectionPoolDataSource();

		pds.setURL("jdbc:oracle:thin:" + dbURL);

		connectionCache = new OracleConnectionCacheImpl(pds);
		connectionCache.setCacheScheme(OracleConnectionCacheImpl.DYNAMIC_SCHEME);
	}

	public static Connection getConnection(String dbURL)
		throws SQLException {
		if (connectionCache == null)
		{
			createDataSource(dbURL);
		}
		Connection conn = connectionCache.getConnection();
		return conn;
	}
}

Thanks,
Paul