//Initial number of connections private int size=0;
//username private String username=null;
//password private String password=null;
//Vector of JDBC connections private Vector pool=null;
/* 方法部分 */ public ConnectionPool(){
}
//set the value of JDBC driver public void setDriver(String value){ if (value!=null){ driver=value; } }
//get the value of JDBC driver public String getDriver(){ return driver; }
//set the url pointing to the database public void setURL(String value){ if (value!=null){ url=value; } }
//get the url pointing to the database public String getURL(String value){ return url; }
//set the initial number of connections public void setSize(int value){ if (value>1){ size=value; } }
//get the initial number of connections public int getSize(){ return size; }
//set the username public void setUsername(String value){ if (value!=null){ username=value; } }
//get the username public String getUsername(){ return username; }
//set the password public void setPassword(String value){ if (value!=null){ password=value; } }
//get the password public String getPassword(){ return password; }
/*create and return a connection 创建一个连接并且返回该连接 */ private Connection createConnection() throws Exception{ Connection con=null;
//create a connection con=DriverManager.getConnection(url,username,password); return con; }
//initialize the pool public synchronized void initializePool() throws Exception{ //check our initial values if (driver==null){ throw new Exception("no driver name specifide!"); }
if (url==null){ throw new Exception("no URL specified!"); }
if (size<1){ throw new Exception("Pool size is less than 1!"); }
//create the connections
try{ //load driver class file,转载驱动文件 Class.forName(driver);
//create the connections based on the size member for (int i=0;i<size;i++){ System.err.println("Open JDBC connection "+i); Connection con=createConnection(); if (con==null){ //create a pooledconnection to encapsulate the real JDBC connection PooledConnection pcon=new PooledConnection(con); addConnection(pcon); } } } catch(SQLException sqle){ System.err.print(sqle.getMessage()); } catch(ClassNotFoundException cnfe){ System.err.print(cnfe.getMessage()); } catch(Exception e){ System.err.print(e.getMessage()); } }
//adds the pooledConnection to the pool private void addConnection(PooledConnection value){ //if pool if null,create a new vector with initial size of "size" if (pool==null){ pool=new Vector(size); }
//add the pooledconnection to the vector pool.addElement(value);
}
//release Connection public synchronized void releaseConnection(Connection con){ //find the pooledconnection object for (int i=0;i<pool.size();i++){ PooledConnection pcon=(PooledConnection)pool.elementAt(i); if (pcon.getConnection()==con){ System.err.print("releasing connection "+i);
//set its inuse attribute to false,which release it for use pcon.setInUse(false); break; } }
}
//find a available connection public synchronized Connection getConnection() throws Exception{
PooledConnection pcon=null;
//find a connection not in use for (int i=0;i<pool.size();i++){ pcon=(PooledConnection)pool.elementAt(i);
//check to see the connection is in use if(pcon.inUse()==false){ //make it as in use pcon.setInUse(true);
//return the JDBC connection stored in //the pooledconnection object return pcon.getConnection();
} }
//cann't find a free connection //create and add a new one try{ //create a new JDBC connection Connection con=createConnection();
//create a new pooledconnection ,passing it the JDBC connection pcon=new PooledConnection(con);
// the connection is in use pcon.setInUse(true);
//add the new pooledconnection to the pool pool.addElement(pcon);
//return the new connection return pcon.getConnection();
}
//while shut down the pool,you need to first empty it public synchronized void emptyPool(){ //iterate over the entire pool closing the JDBC connections for (int i=0;i<pool.size();i++){ System.err.print("closing JDBC connection "+i);
//if the pooledconnection is not in use ,close it if (pcon.inUse()==false){ pcon.close(); } else{ //if it is still in use ,sleep for 30 seconds and force close try{ java.lang.Thread.sleep(30000); pcon.close(); } catch (InterruptedException ie){ System.err.print(ie.getMessage()); } } } }
//boolean flag used to determine if connection is in use private boolean inUse=false;
//constructor that takes the passed in JDBC connection //and stored it in the connection attribute public PooledConnection(Connection value){ if (value!=null){ connection=value; } }
//return the reference to the JDBC connection public Connection getConnection(){ return connection; }
//set the state of the pooledconnection public void setInUse(boolean value){ inUse=value; }
//return the current state of the pooledconnection public boolean inUse(){ return inUse; }
//close the connection public void close(){ try{ connection.close(); } catch (SQLException SQLe){ System.err.print(SQLe.getMessage()); } } }
public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{ response.setContentType("text/html"); PrintWriter out =response.getWriter(); out.println("This Servlet does not service requests!");
文件1:
ConnectionPool.java
/*
JDBC数据库连接池*/import java.sql.*;
import java.util.*;public class ConnectionPool {
/*
定义字段部分
*/
//JDBC driver
private String driver=null;
//URL of database
private String url=null;
//Initial number of connections
private int size=0;
//username
private String username=null;
//password
private String password=null;
//Vector of JDBC connections
private Vector pool=null;
/*
方法部分
*/
public ConnectionPool(){
}
//set the value of JDBC driver
public void setDriver(String value){
if (value!=null){
driver=value;
}
}
//get the value of JDBC driver
public String getDriver(){
return driver;
}
//set the url pointing to the database
public void setURL(String value){
if (value!=null){
url=value;
}
}
//get the url pointing to the database
public String getURL(String value){
return url;
}
//set the initial number of connections
public void setSize(int value){
if (value>1){
size=value;
}
}
//get the initial number of connections
public int getSize(){
return size;
}
//set the username
public void setUsername(String value){
if (value!=null){
username=value;
}
}
//get the username
public String getUsername(){
return username;
}
//set the password
public void setPassword(String value){
if (value!=null){
password=value;
}
}
//get the password
public String getPassword(){
return password;
}
/*create and return a connection
创建一个连接并且返回该连接
*/
private Connection createConnection() throws Exception{
Connection con=null;
//create a connection
con=DriverManager.getConnection(url,username,password);
return con;
}
//initialize the pool
public synchronized void initializePool() throws Exception{
//check our initial values
if (driver==null){
throw new Exception("no driver name specifide!");
}
if (url==null){
throw new Exception("no URL specified!");
}
if (size<1){
throw new Exception("Pool size is less than 1!");
}
//create the connections
try{
//load driver class file,转载驱动文件
Class.forName(driver);
//create the connections based on the size member
for (int i=0;i<size;i++){
System.err.println("Open JDBC connection "+i);
Connection con=createConnection();
if (con==null){
//create a pooledconnection to encapsulate the real JDBC connection
PooledConnection pcon=new PooledConnection(con);
addConnection(pcon);
}
}
}
catch(SQLException sqle){
System.err.print(sqle.getMessage());
}
catch(ClassNotFoundException cnfe){
System.err.print(cnfe.getMessage());
}
catch(Exception e){
System.err.print(e.getMessage());
} }
//adds the pooledConnection to the pool
private void addConnection(PooledConnection value){
//if pool if null,create a new vector with initial size of "size"
if (pool==null){
pool=new Vector(size);
}
//add the pooledconnection to the vector
pool.addElement(value);
}
//release Connection
public synchronized void releaseConnection(Connection con){
//find the pooledconnection object
for (int i=0;i<pool.size();i++){
PooledConnection pcon=(PooledConnection)pool.elementAt(i);
if (pcon.getConnection()==con){
System.err.print("releasing connection "+i);
//set its inuse attribute to false,which release it for use
pcon.setInUse(false);
break;
}
}
}
//find a available connection
public synchronized Connection getConnection()
throws Exception{
PooledConnection pcon=null;
//find a connection not in use
for (int i=0;i<pool.size();i++){
pcon=(PooledConnection)pool.elementAt(i);
//check to see the connection is in use
if(pcon.inUse()==false){
//make it as in use
pcon.setInUse(true);
//return the JDBC connection stored in
//the pooledconnection object
return pcon.getConnection();
}
}
//cann't find a free connection
//create and add a new one
try{
//create a new JDBC connection
Connection con=createConnection();
//create a new pooledconnection ,passing it the JDBC connection
pcon=new PooledConnection(con);
// the connection is in use
pcon.setInUse(true);
//add the new pooledconnection to the pool
pool.addElement(pcon);
}
catch(Exception e){
System.err.print(e.getMessage());
}
//return the new connection
return pcon.getConnection();
}
//while shut down the pool,you need to first empty it
public synchronized void emptyPool(){
//iterate over the entire pool closing the JDBC connections
for (int i=0;i<pool.size();i++){
System.err.print("closing JDBC connection "+i);
PooledConnection pcon=(PooledConnection)pool.elementAt(i);
//if the pooledconnection is not in use ,close it
if (pcon.inUse()==false){
pcon.close();
}
else{
//if it is still in use ,sleep for 30 seconds and force close
try{
java.lang.Thread.sleep(30000);
pcon.close();
}
catch (InterruptedException ie){
System.err.print(ie.getMessage());
}
}
}
}
}
PooledConnection.java
import java.sql.*;public class PooledConnection {
//real JDBC connection
private Connection connection=null;
//boolean flag used to determine if connection is in use
private boolean inUse=false;
//constructor that takes the passed in JDBC connection
//and stored it in the connection attribute
public PooledConnection(Connection value){
if (value!=null){
connection=value;
}
}
//return the reference to the JDBC connection
public Connection getConnection(){
return connection;
}
//set the state of the pooledconnection
public void setInUse(boolean value){
inUse=value;
}
//return the current state of the pooledconnection
public boolean inUse(){
return inUse;
}
//close the connection
public void close(){
try{
connection.close();
}
catch (SQLException SQLe){
System.err.print(SQLe.getMessage());
}
}
}
ConnectionPoolServlet.java /**
* 数数据库连接池公用Servlet
*
*
*/import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;public class ConnectionPoolServlet extends HttpServlet{
//初始化全局变量
public void init (ServletConfig config)
throws ServletException{
super.init(config);
ConnectionPool pool=new ConnectionPool();
try{
pool.setDriver("com.microsoft.jdbc.sqlserver.SQLServerDriver");
pool.setURL("jdbc:microsoft:sqlserver://192.168.0.208:1433;DataBaseName=pubs");
pool.setUsername("sa");
pool.setPassword("");
pool.initializePool();
//一旦连接池初始化后,把它加入全局变量 ServletContext中
//这使得其他Servlet都可以使用它
ServletContext context=getServletContext();
context.setAttribute("CONNECTION_POOL",pool);
}
catch (Exception e){
System.err.println(e.getMessage());
}
}
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
response.setContentType("text/html");
PrintWriter out =response.getWriter();
out.println("This Servlet does not service requests!");
out.close();
}
public void destroy(){
ServletContext context=getServletContext();
ConnectionPool pool=(ConnectionPool)context.getAttribute("CONNECTION_POOL");
if(pool!=null){
pool.emptyPool();
context.removeAttribute("CONNECTION_POOL");
}
else{
System.err.println("Can't get a reference to pool!");
}
}public String getServletInfo(){
return "ConnectionPoolServlet information!";
}
}
conf/server.xml中</host>前面加下面一段
<Context path="/tongji" docBase="tongji" debug="0" reloadable="true">
<Resource name="jdbc/tj" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/tj">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=数据库名字</value>
</parameter>
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value></value>
</parameter>
<parameter>
<name>maxActive</name>
<value>9000</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>50</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
</ResourceParams>
</Context>
其中jdbc/tj中tj改成你的数据源名称,path后面的tongji就是你能用此连接池的虚拟站点目录
然后在tongji/web-inf/web.xml加一段
<resource-ref>
<description>
jdbc/tj
</description>
<res-ref-name>
jdbc/tj
</res-ref-name>
<res-type>
javax.sql.DataSource
</res-type>
<res-auth>
Container
</res-auth>
</resource-ref>
再写一个javabean:
package sql;import java.sql.*;
import javax.naming.Context;
import javax.sql.DataSource;
import javax.naming.InitialContext;public class DBConn
{
public static Connection getConnection() throws Exception
{
try
{
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/tj");//查找数据源
return ds.getConnection();//返回连接
}
catch (Exception e)
{
throw e;
}
}
}
在你的程序中就可以Connection conn = DBConn.getConnection();获得与数据的连接了