一个在jsp中使用连接池访问数据库的实例,出现了点问题,请高人指点

[xuantian868]

1.用javabean实现连接池类DBConnPool.
package test;

import java.sql.*;
import java.util.*;
/*连接池类,能够根据要求创建新连接,直到最大连接数为止*/
public class DBConnPool
{
//实际使用中的连接数
private int inUse=0;
//空闲连接
private Vector connections=new Vector();
//连接池名
private String poolname;
//数据库标识
private String dbid;
//驱动程序名
private String drivername;
//数据库账号
private String username;
//数据库密码
private String passwd;
//最大连接数
private int maxconn;

public DBConnPool(String poolname,String drivername,String dbid,
String username,String passwd,int maxconn)
{
this.poolname=poolname;
this.dbid=dbid;
this.drivername=drivername;
this.username=username;
this.passwd=passwd;
this.maxconn=maxconn;

}

/*将连接返回给连接池*/
public synchronized void releaseConnection(Connection con)
{
//将指定连接加入到向量末尾
connections.addElement(con);
//连接数减一
inUse--;
}
/*从连接池得到一个连接*/
public synchronized Connection getConnection()
{
Connection con=null;
if(connections.size()>0)
{
//获取连接列表中获得第一个连接
con=(Connection)connections.elementAt(0);
connections.removeElementAt(0);
//如果此连接关闭,则继续获取
try
{
if(con.isClosed())
con=getConnection();
}
catch(Exception e)
{
e.printStackTrace();
}
}//如果实际使用的连接小于最大连接数,就新创建一个连接
else if
(maxconn==0||inUse<maxconn)
{
con=newConnection();
}
if(con!=null)
{
//连接数增一
inUse++;
}
//返回一个连接
return con;
}
/*创建新的连接*/
private Connection newConnection()
{
Connection con=null;
try
{
//加载驱动程序
Class.forName(drivername);
//建立连接
con=DriverManager.getConnection(dbid,username,passwd);

}
catch(Exception ex)
{
ex.printStackTrace();
return null;
}
//返回连接数
return con;
}
/*关闭所有连接*/
public synchronized void closeConn()
{
Enumeration allConnections=connections.elements();
while(allConnections.hasMoreElements())
{
Connection con=(Connection)allConnections.nextElement();
try
{
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
connections.removeAllElements();
}
}
_________________________________________________________________________
2.用javabean实现连接池管理类DBConnManager
package test;

import java.sql.*;
import java.util.*;

/*连接池管理类,可以管理多个数据库连接池*/

public class DBConnManager
{
//连接池名列表
private Vector poolnames=new Vector();
//驱动程序名列表
private Vector drivernames=new Vector();
//数据库标识列表
private Vector dbids =new Vector();
//用户名列表
private Vector usernames=new Vector();
//密码列表
private Vector passwds =new Vector();
//最大连接数列表
private Vector maxconns=new Vector();
//连接池列表
private Hashtable connPools =new Hashtable();

public DBConnManager()
{
//添加mysql数据库的连接信息
poolnames.addElement("mysql");
drivernames.addElement("org.gjt.mm.mysql.Driver");
dbids.addElement("jdbc:mysql://localhost/testmysql");
usernames.addElement("root");
passwds.addElement("123456789");
maxconns.addElement("10");
//添加access 数据库的连接 信息
poolnames.addElement("sqlserver");
drivernames.addElement("sun.jdbc.odbc.JdbcOdbcDriver");
dbids.addElement("jdbc:odbc:test");
usernames.addElement("sa");
passwds.addElement("");
maxconns.addElement("5");

// 创建连接池

createPools();

}
//将连接返回给已指定的连接池
public void releaseConnection (String name,Connection con)
{
DBConnPool pool=(DBConnPool)connPools.get(name);
if(pool!=null)
{
pool.releaseConnection(con);
}

}
//得到一个指定连接池中的连接
public Connection getConnection(String name)
{
DBConnPool pool=(DBConnPool) connPools.get(name);
if(pool!=null)
return pool.getConnection();
return null;
}

/*关闭所有连接 */
public synchronized void closeConns()
{
Enumeration allPools=connPools.elements();
while(allPools.hasMoreElements())
{
DBConnPool pool=(DBConnPool) allPools.nextElement();
pool.closeConn();
}
}
/*创建连接池*/
private void createPools()
{
for(int i=0;i<poolnames.size();i++)
{
String poolname=poolnames.elementAt(i).toString();
String drivername=drivernames.elementAt(i).toString();
String dbid=dbids.elementAt(i).toString();
String username=usernames.elementAt(i).toString();
String passwd=passwds.elementAt(i).toString();
int maxconn=0;
try
{
maxconn=Integer.parseInt(maxconns.elementAt(i).toString());

}
catch(NumberFormatException e )
{
e.printStackTrace();

}
DBConnPool pool =new DBConnPool(poolname,drivername,dbid,username,passwd,maxconn);
connPools.put(poolname,pool);
}
}
}
_______________________________________________________________
3.在jsp中,使用连接池
<html>
<head>
<title>使用连接池连接数据库</title>
</head>
<%@ page contentType="text/html;charset=GB2312"%>
<%@ page import="java.sql.*"%>
<jsp:useBean id="connManager" scope="application" calss="test.DBConnManager" />
<body bgcolor="#CFF1E1">
<center><h2>
使用连接池连接数据库
</h2></center>
<%
Connection con1=connManager.getConnection("sqlserver");
Connection con2=connManager.getConnection("mysql");
if(con1==null||con2==null)
{
%>
对不起,现在数据库忙,请稍后再试
<%
}
Statement stmt1=con1.createStatement();
Statement stmt2=con2.createStatement();

%>
<h3>访问sqlserver型数据库test</h3>
<table border=1 align="center">
<tr>
<td>用户名</td>
<td>姓名</td>
<td>电子邮件</td>
<td>住址</td>
</tr>
<%
ResultSet rs=stmt1.executeQuery("select * from Users");
while(rs.next())
{
%>
<tr>
<td><%=rs.getString("UserID")%></td>
<td><%=rs.getString("Name")%></td>
<td><%=rs.getString("Email")%></td>
<td><%=rs.getString("Address")%></td>
</tr>
<%
}
rs.close();
stmt1.close();
connManager.releaseConnection("sqlserver",con1);


%>
</table>
<h3>访问Mysql型数据库testmysql</h3>
<table border=1 align="center">
<tr>
<td>用户名</td>
<td>姓名</td>
<td>电子邮件</td>
<td>住址</td>
</tr>
<%
rs=stmt2.executeQuery("select * from Users");
while(rs.next())
{
%>
<tr>
<td><%=rs.getString("UserID")%></td>
<td><%=rs.getString("Name")%></td>
<td><%=rs.getString("Email")%></td>
<td><%=rs.getString("Address")%></td>
</tr>
<%
}
rs.close();
stmt2.close();
connManager.releaseConnection("mysql",con2);

%>
</table>
<%
connManager.closeConns();
%>
</html>