Many connections from an Oracle8i pooled connection returns statement closed.
Created May 7, 2012
The Executable Member
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 ).
// 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..