Many connections from an Oracle8i pooled connection returns statement closed.

Bernie Acs

Here is an example of concurrent operation of pooled connections from the OracleConnectionPoolDataSource. There is an executable for kicking off threads, a DataSource, and the workerThread.

The Executable Member

// Copyright (c) 2000
package package6;

/**
 * package6.executableTester
 * 
 * @author Me
 */
public class executableTester {
  protected static myConnectionPoolDataSource dataSource = null;
  static int i = 0;

  /**
   * Constructor
   */
  public executableTester() throws java.sql.SQLException
  {
  }

  /**
   * main
   * @param args
   */
  public static void main(String[] args) {

    try{
      dataSource = new myConnectionPoolDataSource();
    }
    catch ( Exception ex ){
      ex.printStackTrace();
    }

    while ( i++ < 10 ) {
     try{
        workerClass worker = new workerClass();
        worker.setThreadNumber( i );
        worker.setConnectionPoolDataSource( dataSource.getConnectionPoolDataSource() );
        worker.start();
        System.out.println( "Started Thread#"+i );
      }
      catch ( Exception ex ){
        ex.printStackTrace();
      }
    }
  }

}
The DataSource Member
// Copyright (c) 2000
package package6;
import oracle.jdbc.pool.*;

/**
 * package6.myConnectionPoolDataSource.
 * 
 * @author Me
 */
public class myConnectionPoolDataSource extends Object {
protected OracleConnectionPoolDataSource ocpds = null;

  /**
   * Constructor
   */
  public myConnectionPoolDataSource() throws java.sql.SQLException {
    // Create a OracleConnectionPoolDataSource instance
    ocpds = new OracleConnectionPoolDataSource();

    // Set connection parameters
    ocpds.setURL("jdbc:oracle:oci8:@mydb");
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    }

    public OracleConnectionPoolDataSource getConnectionPoolDataSource() {
    return ocpds;
    }

}
The Worker Thread Member
// Copyright (c) 2000
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;

/**
 * package6.workerClass .
 * 
 * @author Me
 */
public class workerClass extends Thread {
  protected  OracleConnectionPoolDataSource ocpds = null;
  protected  PooledConnection pc = null;

  protected Connection conn = null;

  protected  int threadNumber = 0;
  /**
   * Constructor
   */

  public workerClass() {
  }

  public void doWork( ) throws SQLException {

    // Create a pooled connection
    pc  = ocpds.getPooledConnection();

    // Get a Logical connection
    conn = pc.getConnection();

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ename from emp");

    // Iterate through the result and print the employee names
    while (rset.next ())
      //  System.out.println (rset.getString (1));
      ;

    // Close the RseultSet
    rset.close();
    rset = null;

    // Close the Statement
    stmt.close();
    stmt = null;

    // Close the logical connection
    conn.close();
    conn = null;

    // Close the pooled connection
    pc.close();
    pc = null;

    System.out.println( "workerClass.thread# "+threadNumber+" completed..");

  }

  public void setThreadNumber( int assignment ){
    threadNumber = assignment;
  }

  public void setConnectionPoolDataSource(OracleConnectionPoolDataSource x){
    ocpds = x;
  }

  public void run() {
    try{
      doWork();
    }
    catch ( Exception ex ){
      ex.printStackTrace();
    }
  }

}
The OutPut Produced
Started Thread#1
Started Thread#2
Started Thread#3
Started Thread#4
Started Thread#5
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
Started Thread#10
workerClass.thread# 1 completed..
workerClass.thread# 10 completed..
workerClass.thread# 3 completed..
workerClass.thread# 8 completed..
workerClass.thread# 2 completed..
workerClass.thread# 9 completed..
workerClass.thread# 5 completed..
workerClass.thread# 7 completed..
workerClass.thread# 6 completed..
workerClass.thread# 4 completed..
The oracle.jdbc.pool.OracleConnectionCacheImpl class is another subclass of the oracle.jdbc.pool.OracleDataSource which should also be looked over, that is what you really what to use. Here is a similar example that uses the oracle.jdbc.pool.OracleConnectionCacheImpl. The general construct is the same as the first example but note the differences in workerClass1 where some statements have been commented ( basically a clone of workerClass from previous example ).

The Executable Member

// Copyright (c) 2000
package package6;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;

/**
 * package6.executableTester2
 * 
 * @author Me
 */
public class executableTester2 {
  static int i = 0;
  protected static myOracleConnectCache connectionCache = null;

  /**
   * Constructor
   */
  public executableTester2() throws SQLException
  {
  }

  /**
   * main
   * @param args
   */
  public static void main(String[] args) {
    OracleConnectionPoolDataSource dataSource = null;

    try{

      dataSource = new OracleConnectionPoolDataSource() ;
      connectionCache = new myOracleConnectCache(  dataSource );

    }
    catch ( Exception ex ){
      ex.printStackTrace();
    }

    while ( i++ < 10 ) {
     try{
        workerClass1 worker = new workerClass1();
        worker.setThreadNumber( i );
        worker.setConnection( connectionCache.getConnection() );
        worker.start();
        System.out.println( "Started Thread#"+i );
      }
      catch ( Exception ex ){
        ex.printStackTrace();
      }
    }
  }
  protected void finalize(){
    try{
    connectionCache.close();
    } catch ( SQLException x) {
      x.printStackTrace();
    }
    this.finalize();
  }

}
The ConnectCacheImpl Member
// Copyright (c) 2000
package package6;
import javax.sql.ConnectionPoolDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.driver.*;
import java.sql.*;
import java.sql.SQLException;

/**
 * package6.myOracleConnectCache 
 * 
 * @author Me
 */
public class myOracleConnectCache extends OracleConnectionCacheImpl {

  /**
   * Constructor
   */
  public myOracleConnectCache( ConnectionPoolDataSource x) throws SQLException {
    initialize();
  }

  public void initialize() throws SQLException {
    setURL("jdbc:oracle:oci8:@myDB");
    setUser("scott");
    setPassword("tiger");
    //
    // prefab 2 connection and only grow to 4 , setting these
    // to various values will demo the behavior clearly, if it is not
    // obvious already
    //
    setMinLimit(2);
    setMaxLimit(4);

  }

}
The Worker Thread Member
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;

/**
 * package6.workerClass1
 * 
 * @author Me
 */
public class workerClass1 extends Thread {
  // protected  OracleConnectionPoolDataSource ocpds = null;
  // protected  PooledConnection pc = null;

  protected Connection conn = null;

  protected  int threadNumber = 0;
  /**
   * Constructor
   */

  public workerClass1() {
  }

  public void doWork( ) throws SQLException {

    // Create a pooled connection
    // pc  = ocpds.getPooledConnection();

    // Get a Logical connection
    // conn = pc.getConnection();

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ename from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
      //  System.out.println (rset.getString (1));
      ;

    // Close the RseultSet
    rset.close();
    rset = null;

    // Close the Statement
    stmt.close();
    stmt = null;

    // Close the logical connection
    conn.close();
    conn = null;

    // Close the pooled connection
    // pc.close();
    // pc = null;

    System.out.println( "workerClass1.thread# "+threadNumber+" completed..");

  }

  public void setThreadNumber( int assignment ){
    threadNumber = assignment;
  }

  // public void setConnectionPoolDataSource(OracleConnectionPoolDataSource x){
  //   ocpds = x;
  // }

  public void setConnection( Connection assignment ){
    conn = assignment;
  }

  public void run() {
    try{
      doWork();
    }
    catch ( Exception ex ){
      ex.printStackTrace();
    }
  }

}
The OutPut Produced
Started Thread#1
Started Thread#2
workerClass1.thread# 1 completed..
workerClass1.thread# 2 completed..
Started Thread#3
Started Thread#4
Started Thread#5
workerClass1.thread# 5 completed..
workerClass1.thread# 4 completed..
workerClass1.thread# 3 completed..
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
workerClass1.thread# 8 completed..
workerClass1.thread# 9 completed..
workerClass1.thread# 6 completed..
workerClass1.thread# 7 completed..
Started Thread#10
workerClass1.thread# 10 completed..
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

About | Sitemap | Contact