J
June Moore
Hi everyone,
I am using a DbBroker class to manage database connection pooling
within my application. The DbBroker class (that I sourced from the
internet) is below.
Basically whenever I need to access the database, I do a pop() method
and when I have finished, I do a push(...) method. However, when an
Oracle database error, e.g. "java.sql.SQLException: End of TNS data
channel", is encountered, it tends to not return the connection to the
pool and as time goes by the maximum number of connections will be
used up.
Anyone has any ideas on how to resolve this issue/pinpoint where the
hole is?
All suggestions are welcome.....
Regards,
June
--
<DbBroker.java>
package app.util.db;
//Title: DbBroker
//Version: 1.0
//Copyright: Copyright (c) 1998
//Author: AR Williamson
//Company: N-ARY Limited
//Description: Connection broker pooling class
import java.sql.*;
import java.util.*;
import java.io.*;
import app.util.*;
public class DbBroker extends Thread implements Serializable{
private static DbBroker Broker = null;
private String dbDriver,dbName,dbUser,dbPassword;
private String transactionType=null,schema=null,uidReserved=null;
private int noCon;
private Vector dbList;
private static long _count;
//----------------------------
private DbBroker() throws Exception{
try{
ApplConfig.getInstance();
dbDriver = ApplConfig.getDbDriver();
dbName = ApplConfig.getDbName();
dbUser = ApplConfig.getDbUser();
dbPassword= ApplConfig.getDbPassword();
noCon = ApplConfig.getDbNoOfCon();
transactionType =
ApplConfig.getStringProperty("TransactionType");
schema = ApplConfig.getStringProperty("schema");
uidReserved = ApplConfig.getStringProperty("uidReserved");
if (transactionType == null)
transactionType="RDB";
if (transactionType.equalsIgnoreCase(""))
transactionType="RDB";
if (uidReserved==null) {
uidReserved="false";
}
if (uidReserved.equalsIgnoreCase("")) {
uidReserved="false";
}
System.err.println( "[DbBroker: Connection with the following
parameters: " );
System.err.println( "[DbBroker: driver=" + dbDriver + "]");
System.err.println( "[DbBroker: database=" + dbName + "]");
System.err.println( "[DbBroker: username=" + dbUser + "]");
System.err.println( "[DbBroker: Transaction Type=" +
transactionType + "]");
} catch (Exception E) {
System.err.println( "[DbBroker:" + E + "]" );
System.err.println( "[DbBroker: Please ensure you have the
following fields: " );
System.err.println( "[DbBroker: driver=" );
System.err.println( "[DbBroker: database=" );
System.err.println( "[DbBroker: username=" );
System.err.println( "[DbBroker: password=" );
System.err.println( "[DbBroker: connections=" );
throw E;
}
dbList = new Vector();
//-- Attempt to open the database connections
Connection Con = null;
for ( int x=0; x < noCon; x++ ){
//Con = openConnection();
//if ( Con != null )
dbList.addElement( new dbConnection(Con) );
}
if (dbList.size() < 1) { // No connection
throw new Exception("No connection is made");
}
new Thread(this).start();
System.err.println("[DbBroker: " + dbList.size() + " connections
opened]" );
}
//----------------------------
public static String getUidReserved() {
if ( Broker == null )
return(null);
return(Broker.getUidReserved1());
}
public String getUidReserved1() {
if (uidReserved == null)
return("false");
if (uidReserved.equals(""))
return("false");
return(uidReserved);
}
//----------------------------
public static String getTransactionType() {
if ( Broker == null )
return(null);
return(Broker.getTransactionType1());
}
//----------------------------
public String getTransactionType1() {
if (transactionType == null)
return("RDB");
if (transactionType.equals(""))
return("RDB");
return(transactionType);
}
//----------------------------
public void run(){
int debugCount=0;
for (;
{
debugCount++;
if ( debugCount%30 == 0 ){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
System.err.println( "[DbBroker.run(): " + dbCon.toString()
);
}
}
try{
Thread.currentThread().sleep( 60000 );
}catch(Exception E1){}
}
}
public synchronized static void getInstance() throws Exception{
try {
if ( Broker == null )
Broker = new DbBroker();
}
catch (Exception e) {
throw e;
}
}
//----------------------------
public static Connection pop() throws Exception{
try {
synchronized( Broker ){
dbConnection dbCon;
for (;{
dbCon = Broker.getFreeConnection();
if ( dbCon != null )
break;
if ( dbCon == null && Broker.dbList.size() != 0 ){
try{
Broker.wait();
}
catch(Exception E){
throw E;
}
try
{
Thread.sleep(200);
}
catch (InterruptedException e)
{
// don't care
}
}
}
if ( Broker.dbList.size() == 0 ){
System.err.println( "[DbBroker.pop: No free connections" );
return null;
}else{
PreparedStatement s0 = null;
dbCon.openConnection();
//dbCon.checkConnection();
dbCon.setActive();
try {
String tType=null;
try {
tType = getTransactionType();
} catch (Exception e) {
System.err.println("Error:1");
throw e;
}
if (tType == null) {
System.err.println("Error:2");
}
if (tType.equalsIgnoreCase("RDB")) {
try {dbCon.Con.rollback();} catch (SQLException e) {} //
Just rollback
s0 = dbCon.Con.prepareStatement("set transaction read
only");
s0.executeQuery();
if (s0 != null)
//s0.close();
s0 = null;
}
}
catch (SQLException e) {
if (s0 != null)
//s0.close();
s0 = null;
}
finally {
if (s0 != null)
//s0.close();
s0 = null;
}
return dbCon.Con;
}
}
}
catch (Exception e) {
throw e;
}
}
//----------------------------
public static void shutdown(){
if ( Broker == null ) return;
Enumeration E = Broker.dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if (dbCon.Con != null)
Broker.closeConnection( dbCon.Con );
}
}
//----------------------------
public static void push( Connection _Con ) throws Exception{
if ( Broker == null || _Con == null ) return;
try {
synchronized (Broker){
//-- Need to check the validity of the connection
dbConnection dbCon = Broker.getConnection( _Con );
if ( dbCon == null )
{
System.out.println("Con is not known!");
return;
}
//-- Check the status of the connection
/*
try{
dbCon.Con.rollback();
dbCon.Con.clearWarnings();
}
catch(Exception E){
Broker.closeConnection( dbCon.Con );
}
*/
dbCon.closeConnection();
dbCon.setInActive();
/*
if ( Broker.isClosed(dbCon.Con) ){
System.err.println("[DbBroker.push: Reopen closed Connection]");
dbCon.Con = Broker.openConnection();
if ( dbCon.Con == null ){
System.err.println( "[DbBroker.push: Failed to reopen a
dead connection]" );
Broker.dbList.removeElement( dbCon );
return;
}
}
else{
dbCon.setInActive();
}
*/
Broker.notifyAll();
}
}
catch (Exception e) {
throw e;
}
}
//----------------------------
private Connection openConnection(){
Connection newCon = null;
try{
Class.forName( dbDriver );
newCon = DriverManager.getConnection( dbName, dbUser,
dbPassword );
if (schema != null) {
if (!schema.equals("")) {
Statement s = newCon.createStatement();
s.execute("set schema '" + schema.trim() + "'");
//s.close();
}
}
//newCon.setAutoCommit(false);
//newCon.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
System.err.println( "[DbBroker.openConnection(): Success " );
}catch(Exception E){
System.err.println( "[DbBroker.openConnection():" + E + "]" );
System.err.println( "[DbBroker.openConnection(): dbName =" +
dbName + "]" );
System.err.println( "[DbBroker.openConnection(): dbUser =" +
dbUser + "]" );
System.err.println( "[DbBroker.openConnection(): dbPass =" +
dbPassword + "]" );
newCon = null;
}
return newCon;
}
//----------------------------
private void closeConnection( Connection _Con ){
try{
_Con.rollback();
_Con.close();
_Con = null;
}catch(Exception E){ E.printStackTrace(); }
}
//----------------------------
private boolean isClosed( Connection _Con ){
try{
return _Con.isClosed();
}catch(Exception E){
return true;
}
}
//----------------------------
private synchronized dbConnection getConnection( Connection _Con ){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if ( dbCon.Con == _Con )
return dbCon;
}
return null;
}
//----------------------------
private synchronized dbConnection getFreeConnection(){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if ( dbCon.bActive == false )
return dbCon;
}
return null;
}
//----------------------------
//----------------------------
class dbConnection extends Object {
public Connection Con;
public boolean bActive;
public long timeTaken;
public long averageTime;
public long maxTime;
public int hitRate;
public long lastUsedMillis;
public long _useCount;
public long _id;
public dbConnection( Connection _Con ){
Con = _Con;
bActive = false;
timeTaken = 0;
averageTime = 0;
hitRate = -1;
maxTime = -1;
_id = _count++;
}
public void setInActive(){
bActive = false;
long t = System.currentTimeMillis() - timeTaken;
if ( t < 120000 )
averageTime += t;
timeTaken = 0;
if ( t > maxTime )
maxTime = t;
lastUsedMillis = System.currentTimeMillis();
}
public void setActive(){
bActive = true;
timeTaken = System.currentTimeMillis();
hitRate++;
}
public void openConnection()
{
System.out.println("dbCon openConnection() : " + _id);
Con = Broker.openConnection();
}
public void closeConnection()
{
System.out.println("dbCon closeConnection() : " + _id);
Broker.closeConnection(Con);
Con = null;
}
public void checkConnection()
{
_useCount++;
if (_useCount % 5 == 0)
//if (true)
{
System.err.println("[DbBroker.setActive(): Cycle Connection");
Broker.closeConnection(Con);
Con = null;
//System.gc();
for(;
{
Con = Broker.openConnection();
if (Con != null)
{
break;
}
try
{
Thread.sleep(500);
}
catch (InterruptedException e)
{
// Don't care!
}
}
}
}
public long getAverage(){
if ( hitRate == 0 ) return 0;
return averageTime/(long)hitRate;
}
public String toString(){
return "[Hit: " + hitRate + "] [Avg.: " + getAverage() + "]
[Use: " + bActive + "] [Max: " + maxTime + "]";
}
}
}
</DbBroker.java>
I am using a DbBroker class to manage database connection pooling
within my application. The DbBroker class (that I sourced from the
internet) is below.
Basically whenever I need to access the database, I do a pop() method
and when I have finished, I do a push(...) method. However, when an
Oracle database error, e.g. "java.sql.SQLException: End of TNS data
channel", is encountered, it tends to not return the connection to the
pool and as time goes by the maximum number of connections will be
used up.
Anyone has any ideas on how to resolve this issue/pinpoint where the
hole is?
All suggestions are welcome.....
Regards,
June
--
<DbBroker.java>
package app.util.db;
//Title: DbBroker
//Version: 1.0
//Copyright: Copyright (c) 1998
//Author: AR Williamson
//Company: N-ARY Limited
//Description: Connection broker pooling class
import java.sql.*;
import java.util.*;
import java.io.*;
import app.util.*;
public class DbBroker extends Thread implements Serializable{
private static DbBroker Broker = null;
private String dbDriver,dbName,dbUser,dbPassword;
private String transactionType=null,schema=null,uidReserved=null;
private int noCon;
private Vector dbList;
private static long _count;
//----------------------------
private DbBroker() throws Exception{
try{
ApplConfig.getInstance();
dbDriver = ApplConfig.getDbDriver();
dbName = ApplConfig.getDbName();
dbUser = ApplConfig.getDbUser();
dbPassword= ApplConfig.getDbPassword();
noCon = ApplConfig.getDbNoOfCon();
transactionType =
ApplConfig.getStringProperty("TransactionType");
schema = ApplConfig.getStringProperty("schema");
uidReserved = ApplConfig.getStringProperty("uidReserved");
if (transactionType == null)
transactionType="RDB";
if (transactionType.equalsIgnoreCase(""))
transactionType="RDB";
if (uidReserved==null) {
uidReserved="false";
}
if (uidReserved.equalsIgnoreCase("")) {
uidReserved="false";
}
System.err.println( "[DbBroker: Connection with the following
parameters: " );
System.err.println( "[DbBroker: driver=" + dbDriver + "]");
System.err.println( "[DbBroker: database=" + dbName + "]");
System.err.println( "[DbBroker: username=" + dbUser + "]");
System.err.println( "[DbBroker: Transaction Type=" +
transactionType + "]");
} catch (Exception E) {
System.err.println( "[DbBroker:" + E + "]" );
System.err.println( "[DbBroker: Please ensure you have the
following fields: " );
System.err.println( "[DbBroker: driver=" );
System.err.println( "[DbBroker: database=" );
System.err.println( "[DbBroker: username=" );
System.err.println( "[DbBroker: password=" );
System.err.println( "[DbBroker: connections=" );
throw E;
}
dbList = new Vector();
//-- Attempt to open the database connections
Connection Con = null;
for ( int x=0; x < noCon; x++ ){
//Con = openConnection();
//if ( Con != null )
dbList.addElement( new dbConnection(Con) );
}
if (dbList.size() < 1) { // No connection
throw new Exception("No connection is made");
}
new Thread(this).start();
System.err.println("[DbBroker: " + dbList.size() + " connections
opened]" );
}
//----------------------------
public static String getUidReserved() {
if ( Broker == null )
return(null);
return(Broker.getUidReserved1());
}
public String getUidReserved1() {
if (uidReserved == null)
return("false");
if (uidReserved.equals(""))
return("false");
return(uidReserved);
}
//----------------------------
public static String getTransactionType() {
if ( Broker == null )
return(null);
return(Broker.getTransactionType1());
}
//----------------------------
public String getTransactionType1() {
if (transactionType == null)
return("RDB");
if (transactionType.equals(""))
return("RDB");
return(transactionType);
}
//----------------------------
public void run(){
int debugCount=0;
for (;
{debugCount++;
if ( debugCount%30 == 0 ){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
System.err.println( "[DbBroker.run(): " + dbCon.toString()
);
}
}
try{
Thread.currentThread().sleep( 60000 );
}catch(Exception E1){}
}
}
public synchronized static void getInstance() throws Exception{
try {
if ( Broker == null )
Broker = new DbBroker();
}
catch (Exception e) {
throw e;
}
}
//----------------------------
public static Connection pop() throws Exception{
try {
synchronized( Broker ){
dbConnection dbCon;
for (;
{dbCon = Broker.getFreeConnection();
if ( dbCon != null )
break;
if ( dbCon == null && Broker.dbList.size() != 0 ){
try{
Broker.wait();
}
catch(Exception E){
throw E;
}
try
{
Thread.sleep(200);
}
catch (InterruptedException e)
{
// don't care
}
}
}
if ( Broker.dbList.size() == 0 ){
System.err.println( "[DbBroker.pop: No free connections" );
return null;
}else{
PreparedStatement s0 = null;
dbCon.openConnection();
//dbCon.checkConnection();
dbCon.setActive();
try {
String tType=null;
try {
tType = getTransactionType();
} catch (Exception e) {
System.err.println("Error:1");
throw e;
}
if (tType == null) {
System.err.println("Error:2");
}
if (tType.equalsIgnoreCase("RDB")) {
try {dbCon.Con.rollback();} catch (SQLException e) {} //
Just rollback
s0 = dbCon.Con.prepareStatement("set transaction read
only");
s0.executeQuery();
if (s0 != null)
//s0.close();
s0 = null;
}
}
catch (SQLException e) {
if (s0 != null)
//s0.close();
s0 = null;
}
finally {
if (s0 != null)
//s0.close();
s0 = null;
}
return dbCon.Con;
}
}
}
catch (Exception e) {
throw e;
}
}
//----------------------------
public static void shutdown(){
if ( Broker == null ) return;
Enumeration E = Broker.dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if (dbCon.Con != null)
Broker.closeConnection( dbCon.Con );
}
}
//----------------------------
public static void push( Connection _Con ) throws Exception{
if ( Broker == null || _Con == null ) return;
try {
synchronized (Broker){
//-- Need to check the validity of the connection
dbConnection dbCon = Broker.getConnection( _Con );
if ( dbCon == null )
{
System.out.println("Con is not known!");
return;
}
//-- Check the status of the connection
/*
try{
dbCon.Con.rollback();
dbCon.Con.clearWarnings();
}
catch(Exception E){
Broker.closeConnection( dbCon.Con );
}
*/
dbCon.closeConnection();
dbCon.setInActive();
/*
if ( Broker.isClosed(dbCon.Con) ){
System.err.println("[DbBroker.push: Reopen closed Connection]");
dbCon.Con = Broker.openConnection();
if ( dbCon.Con == null ){
System.err.println( "[DbBroker.push: Failed to reopen a
dead connection]" );
Broker.dbList.removeElement( dbCon );
return;
}
}
else{
dbCon.setInActive();
}
*/
Broker.notifyAll();
}
}
catch (Exception e) {
throw e;
}
}
//----------------------------
private Connection openConnection(){
Connection newCon = null;
try{
Class.forName( dbDriver );
newCon = DriverManager.getConnection( dbName, dbUser,
dbPassword );
if (schema != null) {
if (!schema.equals("")) {
Statement s = newCon.createStatement();
s.execute("set schema '" + schema.trim() + "'");
//s.close();
}
}
//newCon.setAutoCommit(false);
//newCon.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
System.err.println( "[DbBroker.openConnection(): Success " );
}catch(Exception E){
System.err.println( "[DbBroker.openConnection():" + E + "]" );
System.err.println( "[DbBroker.openConnection(): dbName =" +
dbName + "]" );
System.err.println( "[DbBroker.openConnection(): dbUser =" +
dbUser + "]" );
System.err.println( "[DbBroker.openConnection(): dbPass =" +
dbPassword + "]" );
newCon = null;
}
return newCon;
}
//----------------------------
private void closeConnection( Connection _Con ){
try{
_Con.rollback();
_Con.close();
_Con = null;
}catch(Exception E){ E.printStackTrace(); }
}
//----------------------------
private boolean isClosed( Connection _Con ){
try{
return _Con.isClosed();
}catch(Exception E){
return true;
}
}
//----------------------------
private synchronized dbConnection getConnection( Connection _Con ){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if ( dbCon.Con == _Con )
return dbCon;
}
return null;
}
//----------------------------
private synchronized dbConnection getFreeConnection(){
Enumeration E = dbList.elements();
dbConnection dbCon;
while (E.hasMoreElements()){
dbCon = (dbConnection)E.nextElement();
if ( dbCon.bActive == false )
return dbCon;
}
return null;
}
//----------------------------
//----------------------------
class dbConnection extends Object {
public Connection Con;
public boolean bActive;
public long timeTaken;
public long averageTime;
public long maxTime;
public int hitRate;
public long lastUsedMillis;
public long _useCount;
public long _id;
public dbConnection( Connection _Con ){
Con = _Con;
bActive = false;
timeTaken = 0;
averageTime = 0;
hitRate = -1;
maxTime = -1;
_id = _count++;
}
public void setInActive(){
bActive = false;
long t = System.currentTimeMillis() - timeTaken;
if ( t < 120000 )
averageTime += t;
timeTaken = 0;
if ( t > maxTime )
maxTime = t;
lastUsedMillis = System.currentTimeMillis();
}
public void setActive(){
bActive = true;
timeTaken = System.currentTimeMillis();
hitRate++;
}
public void openConnection()
{
System.out.println("dbCon openConnection() : " + _id);
Con = Broker.openConnection();
}
public void closeConnection()
{
System.out.println("dbCon closeConnection() : " + _id);
Broker.closeConnection(Con);
Con = null;
}
public void checkConnection()
{
_useCount++;
if (_useCount % 5 == 0)
//if (true)
{
System.err.println("[DbBroker.setActive(): Cycle Connection");
Broker.closeConnection(Con);
Con = null;
//System.gc();
for(;
{
Con = Broker.openConnection();
if (Con != null)
{
break;
}
try
{
Thread.sleep(500);
}
catch (InterruptedException e)
{
// Don't care!
}
}
}
}
public long getAverage(){
if ( hitRate == 0 ) return 0;
return averageTime/(long)hitRate;
}
public String toString(){
return "[Hit: " + hitRate + "] [Avg.: " + getAverage() + "]
[Use: " + bActive + "] [Max: " + maxTime + "]";
}
}
}
</DbBroker.java>