Java -- JDBC 数据库连接池
1. 原理代码示例
public class JdbcPool implements DataSource {
private static LinkedList<Connection> list = new LinkedList<Connection>();
static{
try{
InputStream in = JdbcPool.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String username = prop.getProperty("username");
String password = prop.getProperty("password");
Class.forName(driver);
for(int i=0;i<10;i++){
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("获取到了链接" + conn);
list.add(conn);
}
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/*
1.写一个子类，覆盖close方法
2、写一个connection的包装类，增强close方法
3、用动态代理，返回一个代理对象出去，拦截close方法的调用，对close进行增强
*/
public Connection getConnection() throws SQLException {
//proxyConnection.commit() proxyConnection.rollback
if(list.size()>0){
final Connection conn = list.removeFirst(); //myconnection.commit
System.out.println("池大小是" + list.size());
return (Connection) Proxy.newProxyInstance(JdbcPool.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler(){
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
if(!method.getName().equals("close")){
return method.invoke(conn, args);
}else{
list.add(conn);
System.out.println(conn + "被还给池了！！");
System.out.println("池大小为" + list.size());
return null;
}
}
});
}else{
throw new RuntimeException("对不起，数据库忙");
}
}
public Connection getConnection(String username, String password)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub
}
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub
}
}
/*
用包装设计模式对某个对象进行增强
1. 写一个类，实现与被增强对象 (mysql 的 connection) 相同的接口
2、定义一个变量，指向被增强对象
3、定义一个构造方法，接收被增强对象
4、覆盖想增强的方法
5、对于不想增强的方法，直接调用被增强对象的方法
*/
class MyConnection implements Connection{
private Connection conn;
private List pool;
public MyConnection(Connection conn,List pool){
this.conn = conn;
this.pool = pool;
}
public void close() throws SQLException {
pool.add(conn);
}
public void clearWarnings() throws SQLException {
this.conn.clearWarnings();
}
public void commit() throws SQLException {
this.conn.commit();
}
public Statement createStatement() throws SQLException {
return this.conn.createStatement();
}
public Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return false;
}
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return false;
}
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return false;
}
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql, String[] columnNames)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
public void rollback() throws SQLException {
// TODO Auto-generated method stub
}
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub
}
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub
}
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub
}
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub
}
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub
}
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub
}
}
2. 开源连接池 •DBCP 数据库连接池
加入 dbcp 链接池
1. 导入 jar 包
commons-dbcp-1.2.2.jar commons-pool.jar
2、在类目录下加入 dbcp 的配置文件：dbcpconfig.properties
3、在 jdbcUtils 的静态代码块中创建池
private static DataSource ds = null;
static{
try{InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties prop = new Properties();
prop.load(in);
BasicDataSourceFactory factory = new BasicDataSourceFactory();
ds = factory.createDataSource(prop);
System.out.println(ds);
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}</pre>
重写后的工具类
public class JdbcUtils_DBCP {
private static DataSource ds = null;
static{
try{
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties prop = new Properties();
prop.load(in);
BasicDataSourceFactory factory = new BasicDataSourceFactory();
ds = factory.createDataSource(prop);
System.out.println(ds);
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return ds.getConnection();
}
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try{
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
配置文档 dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000 毫秒 /1000 等于 60 秒 -->
maxWait=60000
#JDBC 驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名 =property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见 javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=REPEATABLE_READ
3.开源数据库连接池 •C3P0
数据库连接池
配置文档 c3p0-config.xml 需要放在 src 目录下，自动搜索
<?xml version="1.0" encoding="UTF-8"?>
<!--
c3p0-config.xml
private static ComboPooledDataSource ds;
static{
try {
ds = new ComboPooledDataSource("c3p0config");
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
-->
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/transaction</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">100</property>
</default-config>
<named-config name="c3p0config">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/transaction</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">100</property><!-- intergalactoApp adopts a different approach to configuring statement caching -->
</named-config>
</c3p0-config>
工具类：
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource ds = null;
static{
try{
ds = new ComboPooledDataSource("c3p0config");
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return ds.getConnection();
}
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try{
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}