DEVFYI - Developer Resource - FYI

Many connections from an Oracle8i pooled connection returns statement closed. I am using import oracle.jdbc.pool.* with thin driver. If I test with many simultaneous connections, I get an SQLException that the statement is closed.

JDBC Interview Questions and Answers

(Continued from previous question...)

Many connections from an Oracle8i pooled connection returns statement closed.
I am using import oracle.jdbc.pool.* with thin driver. If I test with many simultaneous connections, I get an SQLException that the statement is closed.

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

package package6;

/**
 * package6.executableTester
 * 
 */
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

package package6;
import oracle.jdbc.pool.*;

/**
 * package6.myConnectionPoolDataSource.
 * 
 */
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

package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;

/**
 * package6.workerClass .
 * 
 */
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

package package6;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;

/**
 * package6.executableTester2
 * 
 */
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

package package6;
import javax.sql.ConnectionPoolDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.driver.*;
import java.sql.*;
import java.sql.SQLException;

/**
 * package6.myOracleConnectCache 
 * 
 */
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
 * 
 */
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..

(Continued on next question...)

Other Interview Questions