Andrej Koelewijn

I ran into this problem again today as the naming of these objects is quite confusing.

It’s a quite common mistake to use an OracleConnectionPoolDataSource when you need a pool of database connections. However, OracleConnectionPoolDataSource represents one poolable physical database connection. Usually you’ll want to use an OracleConnectionCacheImpl. This object will provide a pool of reusable connection. You can specify the minimum and maximum number of physical database connection you want, and what you want to do when all physical database connections are in use.

The following example shows how you can use the OracleConnectionPoolDataSource.

/*
 * Created on Oct 21, 2003
 */
package org.ako.testoraclepool;

import oracle.jdbc.pool.*;
import java.sql.*;

/**
 * @author akoelewi
 */
public class Main {

	/*
	 *  Thread to run query
	 */
	class QueryThread extends Thread {
	    Connection connection;
	    int id;
	    public QueryThread
	              (String name
	              , Connection _connection
	              , int _id) {
	        super(name);
	        connection = _connection;
	        id = _id;
	    }
	    public void run() {
	        try {
	            queryUser(this.connection, this.id);
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }
	    private void queryUser(Connection _connection
	                    , int _id)
	        throws SQLException {

	        System.out.println("Starting query: " + _id);
	        PreparedStatement stmt =
	        _connection.prepareStatement(
	            "select * from v$session"
	                            + " where machine like 'akoelewi%' "
	                            + " and program like 'JDBC%' "
	                            + " order by program ");
	        ResultSet rset = stmt.executeQuery();
	        while (rset.next()) {
	            System.out.println(
	                "Query #"
	                + _id
	                + ", osuser: "
	                + rset.getString("OSUSER")
	                + ", machine: "
	                + rset.getString("MACHINE")
	                + ", program: "
	                + rset.getString("PROGRAM")
	                + ", sid: "
	                + rset.getString("SID")
	                + ", serial: "
	                + rset.getString("SERIAL#"));
	        }

	        _connection.close();
	        System.out.println("Done query: " + _id);
	    }
	}

	public Main() throws Exception {

	    OracleConnectionCacheImpl ods = 
	                new OracleConnectionCacheImpl();
	    ods.setURL("jdbc:oracle:thin:@machine:port:sid");
	    ods.setUser("username");
	    ods.setPassword("password");
	    ods.setMaxLimit(4);
	    ods.setMinLimit(2);
	    ods.setCacheScheme
	             (OracleConnectionCacheImpl.FIXED_WAIT_SCHEME);
	    
		for (int i = 0; i < 10; i++) {
			QueryThread queryThread =
				new QueryThread("query" + i
	                              , ods.getConnection(), i);
			queryThread.start();
		}

	}

	public static void main(String[] args) throws Exception {
		Main main = new Main();
	}
}