java实现 数据库连接池_java数据库连接池实现 | 学步园

1.DBManager.java (数据库连接池管理类)

public class DBManager {

private static ConnectionPool connPool=new ConnectionPool();;

public static PooledConnection getConnection(){

PooledConnection pc=connPool.getConnection();

return pc;

}

public static void closeConnection(PooledConnection pc){

connPool.closeConnection(pc);

}

public static void closePool(){

connPool.closePoolConnect();

}

}

2.PooledConnection.java(数据库基本操作类)

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Map;

import javax.servlet.jsp.jstl.sql.Result;

import javax.servlet.jsp.jstl.sql.ResultSupport;

import com.sun.crypto.provider.RSACipher;

public class PooledConnection {

private Connection conn=null;

private boolean busy=false;

public Connection getConn() {

return conn;

}

public void setConn(Connection conn) {

this.conn = conn;

}

public boolean isBusy() {

return busy;

}

public void setBusy(boolean busy) {

this.busy = busy;

}

public void close(){

this.busy=false;

}

public PooledConnection(Connection conn) {

super();

this.conn = conn;

}

public Map[] excuteQuery(String sql){

ResultSet rs=null;

try {

rs=conn.createStatement().executeQuery(sql);

Result result=ResultSupport.toResult(rs);

Map[] maps=result.getRows();

return maps;

} catch (SQLException e) {

// TODO: handle exception

System.out.println("excuteQuery failed");

return null;

}finally{

try {

rs.close();

} catch (SQLException e2) {

// TODO: handle exception

}

}

}

public Map[] excuteQuery(String sql,Object[] params){

PreparedStatement ps=null;

ResultSet rs=null;

try {

ps=conn.prepareStatement(sql);

for(int i=0;i

ps.setObject(i+1, params[i]);

}

rs=ps.executeQuery();

Result result=ResultSupport.toResult(rs);

Map[] maps=result.getRows();

return maps;

} catch (SQLException e) {

// TODO: handle exception

System.out.println("excuteQuery failed");

return null;

}finally{

try {

rs.close();

ps.close();

} catch (SQLException e2) {

// TODO: handle exception

}

}

}

public int excuteUpdate(String sql){

try {

int result=conn.createStatement().executeUpdate(sql);

return result;

} catch (SQLException e) {

// TODO: handle exception

System.out.println("excuteUpdate failed");

return -1;

}

}

public int excuteUpdate(String sql,Object[] params){

PreparedStatement ps=null;

try {

int result;

ps=conn.prepareStatement(sql);

for(int i=0;i

ps.setObject(i+1, params[i]);

}

result=ps.executeUpdate();

return result;

} catch (SQLException e) {

// TODO: handle exception

System.out.println("excuteUpdate failed");

return -1;

}finally{

try {

ps.close();

} catch (SQLException e2) {

// TODO: handle exception

}

}

}

}

3. ConnectionPool.java(连接池类)

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Enumeration;

import java.util.Vector;

public class ConnectionPool {

private final String DRIVER = "oracle.jdbc.driver.OracleDriver";

private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";

private final String username = "system";

private final String password = "root";

private int initialNum = 10;

private int stepNum = 5;

private int maxNum = 100;

private Vector connections = null;

public int getInitialNum() {

return initialNum;

}

public void setInitialNum(int initialNum) {

this.initialNum = initialNum;

}

public int getStepNum() {

return stepNum;

}

public void setStepNum(int stepNum) {

this.stepNum = stepNum;

}

public int getMaxNum() {

return maxNum;

}

public void setMaxNum(int maxNum) {

this.maxNum = maxNum;

}

public ConnectionPool() {

createPool();

}

public ConnectionPool(int initialNum, int stepNum, int maxNum) {

this.initialNum = initialNum;

this.stepNum = stepNum;

this.maxNum = maxNum;

createPool();

}

//1.加载驱动

//2.建立initialNum连接

private synchronized void createPool(){

try {

Class.forName(DRIVER);

this.connections=new Vector();

createConnections(this.initialNum);

System.out.println("createPool success");

} catch (Exception e) {

// TODO: handle exception

System.out.println("createPool failed");

}

}

//1.添加事务判断

private void createConnections(int num){

while(connections.size()<=this.maxNum && num>0){

connections.addElement(new PooledConnection(createNewconn()));

num--;

}

}

//1.判断数据库连接数量是否超过最大连接数

private Connection createNewconn(){

try {

Connection conn=DriverManager.getConnection(URL, username, password);

if(connections.size()==0){

DatabaseMetaData dMetaData=conn.getMetaData();

int max=dMetaData.getMaxConnections();

//max=0说明数据库连接无限制

if(max>0 && this.maxNum>max){

this.maxNum=max;

}

}

return conn;

} catch (SQLException e) {

// TODO: handle exception

System.out.println("createNewConn failed");

return null;

}

}

//1.查找是否存在空闲连接,没有等待一段时间后继续连接,个人觉得把connection封装起来比较好啦，返回一个对象

public synchronized PooledConnection getConnection(){

if(this.connections==null) return null; //连接池还未创建时，返回null

PooledConnection pConn=getFreeConnection();

while (pConn==null) {

connWait();

pConn=getFreeConnection();

}

return pConn;

}

//1.查找是否存在空闲连接，存在则返回，设置busy=true，否则创建新的连接

private PooledConnection getFreeConnection(){

PooledConnection pc=findFreeConnection();

if(pc==null){

createConnections(this.stepNum);

pc=findFreeConnection();

}

return pc;

}

private PooledConnection findFreeConnection() {

Enumeration enumeration=connections.elements();

while (enumeration.hasMoreElements()) {

PooledConnection pc = (PooledConnection) enumeration.nextElement();

if(!pc.isBusy()){ //如果此服务不忙

//验证有效性

pc.setBusy(true);

return pc;

}

}

return null;

}

//关闭连接

public synchronized void closeConnection(PooledConnection pConn){

pConn.close();

}

//关闭所有连接，真正的断开连接

public synchronized void closePoolConnect(){

Enumeration enumeration=connections.elements();

while (enumeration.hasMoreElements()) {

PooledConnection pc = (PooledConnection) enumeration.nextElement();

pc.setBusy(false);

try {

pc.getConn().close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

connections.clear();

}

private void connWait(){

try {

Thread.sleep(300);

} catch (InterruptedException e) {

// TODO: handle exception

}

}

}