JDBC连接池
java JDBC连接中用到Connection 在每次对数据进行增删查改 都要 开启 、关闭 ，在实例开发项目中 ，浪费了很大的资源 ，以下是之前连接JDBC的案例
package com.jdbc.connection; 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.Properties; public class jdbcConnection { private static String driver;
private static String url;
private static String username;
private static String password; /**
* 静态代码块加载配置文件信息
*/
static {
try {
// 1.通过当前类获取类加载器
ClassLoader classLoader = jdbcConnection.class.getClassLoader();
// 2.通过类加载器的方法获得一个输入流
InputStream is = classLoader.getResourceAsStream("db.properties");
// 3.创建一个properties对象
Properties props = new Properties();
// 4.加载输入流
props.load(is);
// 5.获取相关参数的值
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
} } /**
* 获取连接方法
*
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
} /**
* 释放资源方法
*
* @param conn
* @param pstmt
* @param rs
*/
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
} }
}
考虑节省资源 ，可以创建一个connection连接池 ，每次使用connection连接时 ，直接从连接池中取出一个连接，不用时再放回连接池 ，代替之前的关闭连接 。 java提供了javax.sql.DataSource 接口 ，各大连接池厂商直接实现这个接口
常见的连接池有C3P0 DBCP连接池
自定义连接池
在上面代码的基础上添加一个自定义连接池，减少对资源的浪费
1.创建一个类实现javax.jdbc.dataSource 类
2.创建一个 存放多个连接的容器 ，因为需要经常删除 ，查找 。 这里用LinkedList 容器
3.在MyDataSource类中静态代码块中创建多个连接，添加到连接池容器中 (这里面创建连接可以直接使用 com.jdbc.connection包中的jdbcConnection 中的静态方法getConnection)
static{
for (int i = 0; i < 5; i++) {
Connection conn=jdbcConnection.getConnection();
}
}
4.重写Datasource 方法中的 getConnection() 方法 ，
注：在使用pool这个变量之前需要判断pool是否为空 ，若为空需要重新创建
4.在MyDataSource方法中添加 一个 把当前连接归还给连接池的方法
public void addBack(Connection conn){
pool.add(conn);
}
具体操作代码如下
package com.jdbc.dataSource; import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger; import javax.sql.DataSource; import com.jdbc.connection.jdbcConnection; public class MyDataSource implements DataSource {
private static LinkedList<Connection> pool=new LinkedList<Connection>();
static{
for (int i = 0; i < 5; i++) {
Connection conn=jdbcConnection.getConnection();
pool.add(conn);
}
}
@Override
public Connection getConnection() throws SQLException {
Connection conn=null;
if (pool.size()==0) {
for (int i = 0; i < 5; i++) {
conn=jdbcConnection.getConnection();
pool.add(conn);
}
}
conn=pool.remove(0);
return conn;
} public void addBack(Connection conn){
pool.add(conn);
}
@Override
public PrintWriter getLogWriter() throws SQLException { return null;
} @Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
} @Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
} @Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub } @Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Connection getConnection(String arg0, String arg1)
throws SQLException { return null;
} }
连接池测试类
package com.jdbc.Util; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException; import org.junit.Test; import com.jdbc.dataSource.MyDataSource; public class JdbcTest { @Test
public void test1(){
Connection conn=null;
PreparedStatement pstm=null;
MyDataSource myDataSource=new MyDataSource();
try {
conn=myDataSource.getConnection(); } catch (SQLException e) {
e.printStackTrace();
} }
}
自定义连接池：方法增强
装饰者模式
- 创建类B，并实现接口A
- 提供类B的构造方法，参数类型为A，用于接收A接口的其他实现类（c）
- 给类B添加类型为A的成员变量，用于存放A接口的其他实现类
- 增加需要的方法
- 实现不需要增强的方法，方法体 重点调用成员变量存放的其他实现类对应的方法
翻译过来就是创建类 MyConnection 实现Connection接口
提供MyConnection的构造方法，参数类型为Connection的、 MyDataSource 类中的连接池对象list
。。。等
package com.jdbc.dataSource; import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor; public class MyConnection implements Connection {
private Connection conn;
private LinkedList<Connection> pool;
public MyConnection(Connection conn ,LinkedList<Connection> pool) {
this.pool=pool;
this.conn = conn;
}
@Override
public void close() throws SQLException {
pool.add(conn);
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public void abort(Executor executor) throws SQLException {
// TODO Auto-generated method stub } @Override
public void clearWarnings() throws SQLException {
// TODO Auto-generated method stub } @Override
public void commit() throws SQLException {
// TODO Auto-generated method stub } @Override
public Array createArrayOf(String typeName, Object[] elements)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Blob createBlob() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Clob createClob() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public NClob createNClob() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public SQLXML createSQLXML() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Struct createStruct(String typeName, Object[] attributes)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Properties getClientInfo() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public String getClientInfo(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return 0;
} @Override
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public int getNetworkTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
} @Override
public String getSchema() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return 0;
} @Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public boolean isValid(int timeout) throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql, String[] columnNames)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub } @Override
public void rollback() throws SQLException {
// TODO Auto-generated method stub } @Override
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setClientInfo(Properties properties)
throws SQLClientInfoException {
// TODO Auto-generated method stub } @Override
public void setClientInfo(String name, String value)
throws SQLClientInfoException {
// TODO Auto-generated method stub } @Override
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setNetworkTimeout(Executor executor, int milliseconds)
throws SQLException {
// TODO Auto-generated method stub } @Override
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub } @Override
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public void setSchema(String schema) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub } }
更改后的myDataSource类为
package com.jdbc.dataSource; import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger; import javax.sql.DataSource; import com.jdbc.connection.jdbcConnection; public class MyDataSource1 implements DataSource{
private static LinkedList<Connection> pool=new LinkedList<Connection>();
static{
for (int i = 0; i < 5; i++) {
Connection conn=jdbcConnection.getConnection();
pool.add(conn);
}
}
@Override
public Connection getConnection() throws SQLException {
if (pool.size()==0) {
for (int i = 0; i < 5; i++) {
Connection conn=jdbcConnection.getConnection();
//MyConnection 类实现了Connection接口
//将创建的连接用装饰者类包装
MyConnection myConnection=new MyConnection(conn, pool);
//pool.add(conn);
pool.add(myConnection);//添加被装饰后的connection对象
}
}
Connection conn=pool.remove(0);
return conn;
} @Override
public PrintWriter getLogWriter() throws SQLException { return null;
} @Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
} @Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
} @Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub } @Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub } @Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
} @Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
} @Override
public Connection getConnection(String username, String password)
throws SQLException {
// TODO Auto-generated method stub
return null;
} }
测试
package com.study.jdbc.test; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException; import org.junit.Test; import com.study.jdbc.Utils.JDBCUtils_V3;
import com.study.jdbc.dataSource.MyDataSource;
import com.study.jdbc.dataSource.MyDataSource1; public class TestMyDataSource { /*
* 添加用户
* 使用改造过的connection
*
*/
@Test
public void dataTestAddUser1(){
Connection conn=null;
PreparedStatement pstm=null;
MyDataSource1 dataSource=new MyDataSource1();
try {
conn=dataSource.getConnection();
System.out.println(conn);
String sql="insert into user values(10,?,?)";
pstm=conn.prepareStatement(sql);
pstm.setString(1,"吕布1");
pstm.setString(2,"点错1");
int rows =pstm.executeUpdate();
if (rows>0) {
System.out.println("ok");
}else {
System.out.println("no");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
//release中conn.close()方法已经被装饰
JDBCUtils_V3.release(conn, pstm, null);
} }
}
需要注意的是：MyConection 实现Connection接口时重新了Connection所有的方法，在使用 被MyConnection类修饰的Connecting连接的某些方法要注意需要重写MyConnection类中的这些方法
比如：当使用prepareStatement(sql); 时 要注意MyConnection中的prepareStatement(String sql)方法
MyConnection 在未处理之前的prepareStatement(String sql)方法如下图所示
不重写直接使用 返回的结果永远是null ，重写后代码：
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql);
}