database connection pooling problem

I am working on this project for schools where each school is supposed
to have her own database, that contains students and their results ..etc..

i need to connect to these databases through a jsp.. so i created
several classes in an attempt to apply the connection pooling
technique.. its the first time for me to use connection people and I
am having a hard time !!..

here is what i did.. first i created a DBConn class that implements
the Connection interface and I believe I've wrote the necessary code
properly.. then i created a DBConnectionPool class.. to represent a
connection pool to a specific database.. since i will be needing more
than one pool to different schools.. lastly i created a Database class
that has an array list of DBConnectionpools and another array list of
school IDs where for example the first element in the schoolID array
list refers to the school to which the first pool in the
DBCOnnectionpools array list connects to.. and i wrote the code
required to get a connection and release a connection from the pools
..I then compiled the 3 classes and they worked properly.. so far so
good...

the problem occured when i attempted to use the DatabaseConnections in
the jsp file using useBeans..

<jsp:useBean id="myconpools" class="myschool.DatabaseConnections"
scope="application" />

when i later in the page tried to call the (public)method in the
DatabaseConnection class which is called getConnection(int id)

myconpools.getConnection(0);

i get the following error

exception here :

Code:

org.apache.jasper.JasperException
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:370)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

java.lang.NullPointerException
myschool.DatabaseConnections.getConnection(DatabaseConnections.java:86)

org.apache.jsp.Register_005f1_jsp._jspService(org.apache.jsp.Register_005f1_jsp:\
78)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)


I am not sure if i should attach the code of my 3 classes.. but i
really do need help.. I've been trying to figure out the problem for 3
days but i have no clue... I will try to paste the imp parts of my
classes ... I would really appreciate the help, thank you in advance


--------------------------------------------------------------

Code:

public class DBConn implements Connection
{
private Connection conn;
private boolean isfree;
private long timeStamp;

public DBConn(Connection conn)
{
this.conn = conn;
isfree = true;
timeStamp =0;
}

public boolean valid()
{
try{
conn.getMetaData();
return true;
}
catch(SQLException se) {
return false;
}
}

public boolean isFree()
{
return isfree;
}

public boolean lease()
{
if(isfree) {
isfree=false;
timeStamp=System.currentTimeMillis();
return true;
}
else{
return false;
}
}

public void release()
{
isfree = true;
}

public long getTimestamp() {
return timeStamp;
}

..../// the rest of the neccessary code to implement the interface methods
}


----------------------------------------------------

Code:

public class DBConnectionPool
{

private String username;
private String password;
private String dbURL;

protected ArrayList pool;
private int poolsize;
final private long timeout = 60000;

private Connection createConnection() throws SQLException
{
return
DriverManager.getConnection(this.dbURL,this.username,this.password);
}


public DBConnectionPool(String dbURL,String username,String
password) throws SQLException {
this.dbURL = dbURL;
this.username = username;
this.password = password;

pool = new ArrayList(poolsize);

for(int i=0; i <pool.size() ; i++)
{
pool.add(new DBConn(createConnection()));
}
}


public DBConn getConnection() throws SQLException {
DBConn con;
for(int i=0; i<pool.size();i++)
{
con = (DBConn)pool.get(i);
if(con.lease())
{
return con;
}
}
// if all connections are in use create a new one
con = new DBConn(createConnection());
con.lease();
pool.add(con);
return con;
}

public void returnConnection(DBConn con)
{
con.release();
}

.....
}

---------------------------------------------------


public class DatabaseConnections extends Thread
{
private static String username;
private static String password;
private static String dbURL;
private static String dbDriver;

private static int maxConNo;

private static ArrayList dbconnectionpools;
private static ArrayList schID;
private static long delay;

public void run() {
while(true) {
try {
sleep(delay);
} catch( InterruptedException e) { }
/// need to write some more code here later
}
}

private static void readProperties()throws Exception
{
// data stored on a file
FileInputStream fis = new FileInputStream("properties.txt");
InputStreamReader isr = new InputStreamReader(fis);
BufferedReader br = new BufferedReader(isr);

dbDriver = br.readLine();
dbURL = br.readLine();
username = br.readLine();
password = br.readLine();
maxConNo = Integer.parseInt(br.readLine());
delay = Integer.parseInt(br.readLine());

br.close();
isr.close();
fis.close();
}

private void createConnectionPools() throws ClassNotFoundException,
InstantiationException,
IllegalAccessException,
FileNotFoundException,
IOException, SQLException
{

Class.forName(dbDriver).newInstance();

FileInputStream fis = new FileInputStream("dbnames.txt");
InputStreamReader isr = new InputStreamReader(fis);
BufferedReader br = new BufferedReader(isr);

dbconnectionpools = new ArrayList();
schID = new ArrayList();

schID.add(0);
dbconnectionpools.add(new DBConnectionPool(dbURL +
"schools",username,password));

String s;

while((s=br.readLine())!= null)
{
String[] ars = s.split(",");
schID.add(Integer.parseInt(ars[0]));
String sdburl = dbURL + ars[1];
dbconnectionpools.add(new
DBConnectionPool(sdburl,username,password));
}

br.close();
isr.close();
fis.close();
}

public DBConn getConnection(int schoolID) throws SQLException {
for(int i=0; i<dbconnectionpools.size() ; i++) {
if((Integer)schID.get(i)==schoolID) {
return
((DBConnectionPool)dbconnectionpools.get(i)).getConnection();
}
}

return null;
}


public DatabaseConnections()
{
try
{
readProperties();
createConnectionPools();
// run();
}

catch(Exception e)
{

}
}

}

Can I ask why you're not using drivers that already exist? If the answer
is that these databases could use different engines (Oracle, MySQL,
etc.), then I think I may have a solution. Use the Data Access Object
(DAO) design pattern in conjunction with the Factory pattern. Here's how
it works:
1) Create a Factory. The Factory contains constants of the form
public final static int XXX = n;
2) The Factory creates the correct DAO object depending upon the value
of the passed constant.
3) Each DAO interfaces to a single JDBC driver.

An example call:

DAOFactory daof = new DAOFactory ();
DAO dao = daof.getDAO (DAOFactory.ORACLE, "MachineName", "MY_DATABASE");

I'm assuming that you will have to pass the name of the database to the
DAO Factory object.

I would not do this in a JSP page; it'll be too messy. Instead, create
the proper classes and run them on the server.