Solved

DB connection pool using Hashtable class

Posted on 2001-08-08
6
597 Views
Last Modified: 2008-03-17
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("oracle.jdbc.driver.OracleDriver").newInstance();
      dbConnectionTable.put(databasePath,d.connect("jdbc:oracle:thin:@"+databasePath, 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.equalsIgnoreCase(dbURL)) {
        cReturn = (Connection)dbConnectionTable.get
                  (databasePath);
      }
      else {
       ?????????
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return cReturn;
  }
 } //end of DBCache class
0
Comment
Question by:nhvo73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:black
ID: 6366644
I'd suggest you look at using Datasource's and third party connection pooling to do this. They are pretty standard now and provide all this functionality. Which servlet engine are you using, maybe it already provides this for you. It's quite hard to write a good connection pooling package, and I can assure you that the above class will grow extremely complex once you try to make it threadsafe, ensure connections aren't been held, make sure that they closed properly etc...
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?
0
 
LVL 6

Accepted Solution

by:
kotan earned 300 total points
ID: 6366645
I found that there is an error in the getConnection().
How come you compare the dbURL with databasePath. You should compare it with dbConnectionTable if that url exist.
if you want to ignore case, you can change all the databasePath to lower case first
     databasePath.toLowerCase()
in DBCache() when in into hashtable. And then, compare the dbURL in lowercase in getConnection().

If it is not DbConnection pool, you can make another connection like
     cReturn = DriverManager.getConnection("jdbc:oracle:thin:@" + dbURL");
but I think you should provide the user name and password.

For the timeout and connection error, it will return a null value. Then you can throw a SQLException() when cReturn is null.


public Connection getConnection(String dbURL) throws SQLException {
     Connection cReturn = null;
     String dbURLlower = dbURL.toLowerCase();
     if (dbConnectionTable.containsKey(dbURLLower)) {
          cReturn = (Connection) dbConnectionTable.get(dbURLLower);
     } else {
          cReturn = DriverManager.getConnection("jdbc:oracle:thin:@" + dbURL");
     }

     if (cReturn == null) {
          throw new SQLException();
     }

     return cReturn;
}
0
 
LVL 3

Expert Comment

by:black
ID: 6366648
A BSD licensed connection pooling package:
http://www.bitmechanic.com/projects/jdbcpool/
might be useful.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Expert Comment

by:graf27
ID: 6367327
0
 
LVL 3

Expert Comment

by:graf27
ID: 6367553
0
 

Author Comment

by:nhvo73
ID: 6389608
I somehow got the idea from your comment to finish up the code.  Thanks a lot.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
numbers ascending pyramid 101 264
Running JavaFX on JDeveloper 12C 1 116
java imports not found 4 71
Is there a Google Voice API? 9 70
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question