package dbpool;
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 ex)
{
ex.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 e)
{
e.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();
}
}
---------------------------------------------------------------------------------------------------------------------------------------------
package dbpool;
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("com.mysql.jdbc.Driver");
dbids.addElement("jdbc:mysql://127.0.0.1:3306/testmysql?useUnicode=true&characterEncoding=UTF8");
usernames.addElement("root");
passwds.addElement("");
maxconns.addElement("1000");
//添加sqlserver数据库的连接信息
poolnames.addElement("sqlserver");
drivernames.addElement("com.microsoft.jdbc.sqlserver.SQLServerDriver");
dbids.addElement("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb");
usernames.addElement("sa");
passwds.addElement("");
maxconns.addElement("10");
//添加oracle数据库的连接信息
poolnames.addElement("oracle");
drivernames.addElement("oracle.jdbc.driver.OracleDriver");
dbids.addElement("jdbc:oracle:thin:@localhost:1521:mydb");
usernames.addElement("root");
passwds.addElement("");
maxconns.addElement("10");
//添加db2数据库的连接信息
poolnames.addElement("db2");
drivernames.addElement("com.ibm.db2.jdbc.app.DB2Driver");
dbids.addElement("jdbc:db2://localhost:5000/mydb");
usernames.addElement("admin");
passwds.addElement("");
maxconns.addElement("10");
//添加access数据库的连接信息
poolnames.addElement("access");
drivernames.addElement("sun.jdbc.odbc.JdbcOdbcDriver");
dbids.addElement("jdbc:odbc:TestData");
usernames.addElement("");
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);
}
}
}
---------------------------------------------------------------------------------------------------------------------------------------------
package dbpool;
import java.util.*;
import java.sql.*;
import java.io.*;
public class ExecuteSQL
{
private static Connection con = null;
private ResultSet rs = null;
private Statement stmt = null;
private static DBConnManager connManager;
public ExecuteSQL()
{
connManager = new DBConnManager();
}
//return query Resultret
public ResultSet executeQuery(String sql)
{
try
{
con = getConnection();
stmt = con.createStatement();
rs=stmt.executeQuery(sql);
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
return rs;
}
public int executeUpdate(String sql)
{
int result = 0;
try
{
con = getConnection();
stmt = con.createStatement();
result = stmt.executeUpdate(sql);
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
connManager.releaseConnection("mysql",con);
return result;
}
public static Connection getConnection()
{
try
{
con = connManager.getConnection("mysql");
}
catch(Exception e)
{
System.err.println(e);
}
if (con == null)
{
System.out.print("不能连接数据库!");
}
return con;
}
public void closeDB()
{
try
{
if (rs!=null)
{
rs.close();
}
if(stmt!=null)
{
stmt.close();
}
connManager.releaseConnection("mysql",con);
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
}
public void destory()
{
connManager.closeConns();
}
public static String TransUTF8(String chi)
{
String result="";
try
{
result=new String(chi.getBytes("ISO8859_1"),"UTF-8");
}
catch (UnsupportedEncodingException e)
{
System.out.println(e.toString());
}
return result;
}
public static String TransISO(String chi)
{
String result="";
try
{
result = new String(chi.getBytes("UTF-8"),"ISO8859_1");
}
catch (UnsupportedEncodingException e)
{
System.out.println(e.toString());
}
return result;
}
}