Listing 1: Connecting to the Database
Connection newCon = null;
try{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
newCon = DriverManager.getConnection( "jdbc:odbc:nformant200", "username", "password" );
}catch(Exception E){
System.out.println( "[jdbcServlet.service():" + E + "]" );
_res.setStatus( HttpServletResponse.SC_NO_CONTENT );
return;
}
Listing 2: Running the Query
Statement Statmt;
ResultSet Res;
PrintWriter Out = new PrintWriter( _res.getOutputStream() );
try{
Statmt = newCon.createStatement();
Res = Statmt.executeQuery( "SELECT * FROM USER_TABLE" );
ResultSetMetaData RM = Res.getMetaData();
while (Res.next()){
String columnString = "";
for ( int x=0; x < RM.getColumnCount(); x++ ){
columnString += Res.getString(x+1) + " ";
}
Out.println( columnString );
}
Res.close();
Statmt.close();
newCon.close();
}catch(SQLException E){
System.out.println( "[jdbcServlet.service():" + E + "]" );
_res.setStatus( HttpServletResponse.SC_NO_CONTENT );
return;
}
Listing 3: Setting Up the Connection Pool
public class dbBroker. {
private static dbBroker Broker = null;
public synchronized static void getInstance(){
if ( Broker == null )
Broker = new dbBroker();
}
}
Listing 4: Setting Up the Wrapper Class for a Connection
class dbConnection extends Object {
public Connection Con;
public boolean bActive;
public long timeTaken;
public long averageTime;
public long maxTime;
public int hitRate;
public dbConnection( Connection _Con ){
Con = _Con;
bActive = false;
timeTaken = 0;
averageTime = 0;
hitRate = -1;
maxTime = -1;
}
public void setInActive(){
bActive = false;
long t = System.currentTimeMillis() - timeTaken;
if ( t < 120000 )
averageTime += t;
timeTaken = 0;
if ( t > maxTime )
maxTime = t;
}
public void setActive(){
bActive = true;
timeTaken = System.currentTimeMillis();
hitRate++;
}
public long getAverage(){
if ( hitRate == 0 ) return 0;
return averageTime/(long)hitRate;
}
public String toString(){
return "[Hit: " + hitRate + "] [Avg.: " + getAverage() + "]
[Use: " + bActive + "] [Max: " + maxTime + "]";
}
}
Listing 5: Creating the Instance of dbBroker
private dbBroker(){
Properties INI = new Properties();
try{
INI.load( new FileInputStream("dbbroker.ini") );
dbDriver = INI.getProperty( "driver" );
dbName = INI.getProperty( "database" );
dbUser = INI.getProperty( "username" );
dbPassword= INI.getProperty( "password" );
noCon = Integer.parseInt(INI.getProperty("connections"));
} catch (Exception E){
System.out.println( "[dbBroker:" + E + "]" );
System.out.println( "[dbBroker: Please ensure you have the following fields: " );
System.out.println( "[dbBroker: driver=" );
System.out.println( "[dbBroker: database=" );
System.out.println( "[dbBroker: username=" );
System.out.println( "[dbBroker: password=" );
System.out.println( "[dbBroker: connections=" );
System.out.println( "[dbBroker: in a file named dbbroker.ini]" );
}
dbList = new Vector();
//-- Attempt to open the database connections
Connection Con;
for ( int x=0; x < noCon; x++ ){
Con = openConnection();
if ( Con != null )
dbList.addElement( new dbConnection(Con) );
}
}
Listing 6: Opening the Connection to the Database
private Connection openConnection(){
Connection newCon = null;
try{
Class.forName( dbDriver );
newCon = DriverManager.getConnection( dbName, dbUser, dbPassword );
}catch(Exception E){
System.out.println( "[dbBroker.openConnection():" + E + "]" );
newCon = null;
}
System.out.println( "[dbBroker.openConnection(): Success " );
return newCon;
}
Listing 7: Getting a Connection
public static Connection pop(){
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){}
}
}
if ( Broker.dbList.size() == 0 ){
System.out.println( "[dbBroker.pop: No free connections" );
return null;
}else{
dbCon.setActive();
return dbCon.Con;
}
}
}
Listing 8: giving the Connection Back
public static void push( Connection _Con ){
if ( Broker == null || _Con == null ) return;
synchronized (Broker){
//-- Need to check the validity of the connection
dbConnection dbCon = Broker.getConnection( _Con );
if ( dbCon == null ) return;
//-- Check the status of the connection
try{
dbCon.Con.commit();
dbCon.Con.clearWarnings();
}catch(Exception E){
Broker.closeConnection( dbCon.Con );
}
if ( Broker.isClosed(dbCon.Con) ){
dbCon.Con = Broker.openConnection();
if ( dbCon.Con == null ){
System.out.println( "[dbBroker.push: Failed to reopen a dead connection]" );
Broker.dbList.removeElement( dbCon );
return;
}
}else{
dbCon.setInActive();
}
Broker.notifyAll();
}
}
Listing 9: Checking the Connections
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.out.println( "[dbBroker.run(): " + dbCon.toString() );
}
}
try{
Thread.currentThread().sleep( 60000 );
}catch(Exception E1){}
}
}
Listing 10: Setting Up the Connection Pool
public void service( HttpServletRequest _req, HttpServletResponse _res)
throws ServletException, IOException{
dbBroker.getInstance();
Connection newCon = dbBroker.pop();
Statement Statmt;
ResultSet Res;
PrintWriter Out = new PrintWriter( _res.getOutputStream() );
try{
Statmt = newCon.createStatement();
Res = Statmt.executeQuery( "SELECT * FROM USER_TABLE" );
ResultSetMetaData RM = Res.getMetaData();
while (Res.next()){
String columnString = "";
for ( int x=0; x < RM.getColumnCount(); x++ ){
columnString += Res.getString(x+1) + " ";
}
Out.println( columnString );
}
Res.close();
Statmt.close();
dbBroker.push( newCon );
}catch(SQLException E){
System.out.println( "[jdbcServlet.service():" + E + "]" );
_res.setStatus( HttpServletResponse.SC_NO_CONTENT );
return;
}
Out.flush();
}
