commons-pool-1.6.jar
mysql-connector-java-5.1.20-bin.jar
二、配置文件:dbcp.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/cwb
username=root
password=8345660
#<!-- 初始化连接 -->
dataSource.initialSize=20
#<!-- 最大空闲连接 -->
dataSource.maxIdle=20
#<!-- 最小空闲连接 -->
dataSource.minIdle=5
#最大连接数量
dataSource.maxActive=100
#是否在自动回收超时连接的时候打印连接的超时错误
dataSource.logAbandoned=true
#是否自动回收超时连接
dataSource.removeAbandoned=true
#超时时间(以秒数为单位)
#设置超时时间有一个要注意的地方,超时时间=现在的时间-程序中创建Connection的时间,
#如果 maxActive比较大,比如超过100,那么removeAbandonedTimeout可以设置长一点比如180,也就是三分钟无响应的连接进行回收,当然应用的不同设置长度也不同。
dataSource.removeAbandonedTimeout=180
#<!-- 超时等待时间以毫秒为单位 -->
#maxWait代表当Connection用尽了,多久之后进行回收丢失连接
dataSource.maxWait=1000
package com.cwb.tool; import java.util.Properties; import java.io.*; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import org.apache.log4j.Logger; public class DBCPManager { private static final Logger logger = Logger.getLogger(DBCPManager.class); static private DBCPManager instance; // 唯一实例 static private DataSource ds; private DBCPManager() { ds=setupDataSource(); } private static DataSource setupDataSource() { try{ Properties prop = new Properties(); InputStream inputStream = DBCPManager.class.getClassLoader().getResourceAsStream("dbcp.properties"); prop.load(inputStream); ds= BasicDataSourceFactory.createDataSource(prop); } catch (IOException e) { logger.error("加载 dbcp.properties 错误"); logger.info(e.toString()); }catch(Exception e){ logger.info(e.toString()); } if(ds!=null) logger.info("获取数据源成功"); return ds; } static synchronized public DBCPManager getInstance() { if (instance == null) { instance = new DBCPManager(); } return instance; } public void free(ResultSet rs, Statement st, Connection conn) { try{ if (rs != null) rs.close(); }catch (SQLException e){ logger.info("关闭ResultSet出错"); logger.info(e.toString()); }finally{ try{ if (st != null) st.close(); }catch (SQLException e){ logger.info("关闭Statement出错"); logger.info(e.toString()); }finally{ try{ if (conn != null) conn.close(); } catch (SQLException e) { logger.info("关闭Connection出错"); logger.info(e.toString()); } } } } public Connection getConnection() { if(ds!=null){ try { Connection conn=ds.getConnection(); return conn; } catch (SQLException e) { logger.info("获取Connection出错"); logger.info(e.toString()); } } return null; } //测试代码 public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rset = null; DBCPManager db=DBCPManager.getInstance(); conn=db.getConnection(); if(conn==null) System.out.println("conn==null"); try { System.out.println("Creating connection."); conn = db.getConnection(); System.out.println("Creating statement."); stmt = conn.createStatement(); System.out.println("Executing statement."); String sql="select * from count_num"; rset = stmt.executeQuery(sql); System.out.println("Results:"); int numcols = rset.getMetaData().getColumnCount(); while(rset.next()) { for(int i=1;i<=numcols;i++) { System.out.print("\t" + rset.getString(i)); } System.out.println(""); } } catch(SQLException e) { e.printStackTrace(); } finally { db.free(rset,stmt,conn); } } }四、JSP页面中可以这样用:
<%@ page contentType="text/html; charset=GB2312" %>
<%@ page import="org.apache.log4j.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="com.cwb.tool.DBCPManager" %>
<%
Logger logger = Logger.getLogger("mysqltestjsp");
logger.debug("测试DBCP连接池的使用!!");
%>
<h1> Test DBCP连接池!!!</h1>
<%
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
//主要是这两句
DBCPManager db=DBCPManager.getInstance();
conn=db.getConnection();
try {
} catch(SQLException e) {
logger.info(e.toString());
} finally {
db.free(rset,stmt,conn);
}
%>