nhvo73
asked on
DB connection pool using Hashtable class
Hi,
I am working on a project to build a DBConnection pool (using Hashtable(key,object)) for many database servers to use in many servlets. I am somehow stuck at the method getConnection(), where it will return a conn for the servlet. Basically, this method will return a connection if it is available in the pool; otherwise, it will create an instance right away and return it in the servlet. If there is a timeout or a connection error, it will throw an exception.
Below is my code. I created an array in every servlet that contains the dataURLs for all servers and pass it in the object here.
Please let me know how I can fix it. Thanks so much.
import java.util.*;
import java.sql.*;
import oracle.sql.*;
public class DBCache {
Hashtable dbConnectionTable = new Hashtable();
String databaseUserID = null;
String databasePw = null;
String databasePath = null;
Driver d = null;
public DBCache (String dbUID, String dbPW,
String dbURL []) {
for (int i=0;i<=10;i++) {
databaseUserID = dbUID;
databasePw = dbPW;
databasePath = dbURL[i];
try
{
final Properties prop = new Properties();
prop.setProperty("user", databaseUserID);
prop.setProperty("password ", databasePw);
d = (Driver)Class.forName("ora cle.jdbc.d river.Orac leDriver") .newInstan ce();
dbConnectionTable.put(data basePath,d .connect(" jdbc:oracl e:thin:@"+ databasePa th, prop));
}
catch(Exception se)
{
se.printStackTrace();
}
} //for loop
}//end of DBCache Constructor
public Connection getConnection(String dbURL) throws
SQLException {
Connection cReturn = null;
try {
if (databasePath.equalsIgnore Case(dbURL )) {
cReturn = (Connection)dbConnectionTa ble.get
(databasePath);
}
else {
?????????
}
}
catch (Exception ex) {
ex.printStackTrace();
}
return cReturn;
}
} //end of DBCache class
I am working on a project to build a DBConnection pool (using Hashtable(key,object)) for many database servers to use in many servlets. I am somehow stuck at the method getConnection(), where it will return a conn for the servlet. Basically, this method will return a connection if it is available in the pool; otherwise, it will create an instance right away and return it in the servlet. If there is a timeout or a connection error, it will throw an exception.
Below is my code. I created an array in every servlet that contains the dataURLs for all servers and pass it in the object here.
Please let me know how I can fix it. Thanks so much.
import java.util.*;
import java.sql.*;
import oracle.sql.*;
public class DBCache {
Hashtable dbConnectionTable = new Hashtable();
String databaseUserID = null;
String databasePw = null;
String databasePath = null;
Driver d = null;
public DBCache (String dbUID, String dbPW,
String dbURL []) {
for (int i=0;i<=10;i++) {
databaseUserID = dbUID;
databasePw = dbPW;
databasePath = dbURL[i];
try
{
final Properties prop = new Properties();
prop.setProperty("user", databaseUserID);
prop.setProperty("password
d = (Driver)Class.forName("ora
dbConnectionTable.put(data
}
catch(Exception se)
{
se.printStackTrace();
}
} //for loop
}//end of DBCache Constructor
public Connection getConnection(String dbURL) throws
SQLException {
Connection cReturn = null;
try {
if (databasePath.equalsIgnore
cReturn = (Connection)dbConnectionTa
(databasePath);
}
else {
?????????
}
}
catch (Exception ex) {
ex.printStackTrace();
}
return cReturn;
}
} //end of DBCache class
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A BSD licensed connection pooling package:
http://www.bitmechanic.com/projects/jdbcpool/
might be useful.
http://www.bitmechanic.com/projects/jdbcpool/
might be useful.
why you don't use "import oracle.jdbc.pool.*;" ??
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a83724/connpoc2.htm
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a83724/connpoc2.htm
ASKER
I somehow got the idea from your comment to finish up the code. Thanks a lot.
There are a lot of connection pooling package and pretty much every application server provides one, it's best to use them rather than reinventing the wheel. Also they have Datasources, rather than Connections which is what you are using.
Is there a particular reason for writing one?