利用tomcat配置数据库连接池

973114

1。写一个属性文件dbconfig.properties
     dbType=oracle9i
     driver_class=oracle.jdbc.driver.OracleDriver
     host_name=127.0.0.1
     port=1521
     url=jdbc:oracle:thin:@
     username=scott
     password=tiger
     dbsid=sk
2.建立连接池 DBConnectionPool.java
     package com.sk.javaWeb.utils.dataBase;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Properties;
import java.util.Vector;

public class DBConnectionPool {
private static DBConnectionPool instance = null;
// 连接池的名字，因为可能会有多个连接池，依靠名字来对他们进行区分,因为现在只有一个Oracle连接池，所以没什么在用在这里
private String name;
// 连接字符串
private String _url = "";
// 驱动
private String driver = "";
// 用户名
private String username = "";
// 密码
private String password = "";
// 最大连接数
private int maxConn = 20;
// 当前连接数
private int curConn = 0;
// 空闲的连接
private Vector<Connection> freeConns = new Vector<Connection>();

private DBConnectionPool() {
init();
}

public void init() {
InputStream in = this.getClass().getResourceAsStream(
"/com/sk/javaWeb/utils/dataBase/dbconfig.properties");//以流的形式读入属性文件
Properties props = new Properties();
try {
props.load(in);
driver = props.getProperty("driver_class");
username = props.getProperty("username");
password = props.getProperty("password");
_url = props.getProperty("url") + props.getProperty("host_name")
+ ":" + props.getProperty("port") + ":"
+ props.getProperty("dbsid");
} catch (IOException e) {
e.printStackTrace();
}

}

public static DBConnectionPool getInstance() {
if (instance == null) {
instance = new DBConnectionPool();
}
return instance;
}

// 获得一个连接，如果没有空闲连接并且当前连接数小于最大连接数，就创建一个连接
public synchronized Connection getConnection() {
Connection conn = null;
if (freeConns.size() > 0) {
// 得到空闲连接里面第一个连接
conn = freeConns.firstElement();
freeConns.removeElementAt(0);
try {
// 如果得到的这个连接失效了，就释放掉他，然后重新递归的调用本方法
if (conn.isClosed()) {
conn = getConnection();
}
} catch (SQLException e) {
e.printStackTrace();
conn = getConnection();
}
// 如果现在没有空闲连接，并且当前连接数小于最大连接数，就新创建一个连接
} else if (curConn < maxConn) {
conn = newConnection();
}
if (conn != null) {
curConn++;
}

return conn;
}

/**
* 从连接池获取可用连接.可以指定客户程序能够等待的最长时间 参见前一个getConnection()方法.
*
* @param timeout以毫秒计的等待时间限制
*/
public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con = null;
while ((con = getConnection()) == null) {
try {
wait(timeout);
} catch (InterruptedException e) {
e.printStackTrace();
}
if ((new Date().getTime() - startTime) >= timeout) {// wait()返回的原因是超时
return null;
}
}
return con;
}

private Connection newConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(_url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}

// 释放一个连接
public synchronized void freeConn(Connection conn) {
freeConns.addElement(conn);
curConn--;
notifyAll();
}

// 关闭所有连接
public void freeAllConns() {
Enumeration<Connection> conns = freeConns.elements();
while (conns.hasMoreElements()) {
Connection conn = conns.nextElement();
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 除去空闲连接当中的所有连接
freeConns.removeAllElements();

}

public int getRowCount(String sql) {
Connection conn = getConnection();
int rowCount = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
rowCount = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if(conn!=null){
this.instance.freeConn(conn);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
return rowCount;
}
}

3.获得连接 ConnectionPool.java

package com.sk.javaWeb.utils.dataBase;

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
* tomcat配置数据库连接池
* @author 沈奎
* 日期：2009-9-19下午03:21:43
* 版本: 0.1
*/
public class ConnectionPool {
private static Connection conn = null;

private static DataSource dataSource = null;

public static Connection getConnection(){
try {
Context context = new InitialContext();
dataSource = (DataSource) context.lookup("java:comp/env/oracle");
if(dataSource!=null){
conn = dataSource.getConnection();
}
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}