JDBC Interview Questions and Answers

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.
ere 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..

Page Numbers : 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Java Interview Questions for more Java Interview Questions with answers

Check Structs Interview Questions for more Structs Interview Questions with answers

Check Servlet Interview Questions for more Servlet Interview Questions with answers