编写自己的jdbc数据库连接池
浏览数：41 /
时间：2015年06月12日
1、为什么要使用数据库连接池
在使用jdbc的一般开发中，每次都要从数据库获取连接，典例的查询的做法如下：
Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); String sql = "SELECT * FROM persons"; st = conn.prepareStatement(sql); rs = st.executeQuery(); while(rs.next()){ //在这里将查询到的数据封装或者做一些其他的事情 //..... } }catch(Exception e){ throw new RuntimeException(e); }finally{//close resource if(rs != null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); }finally{ rs = null; } } if(st != null){ try { st.close(); } catch (Exception e) { e.printStackTrace(); }finally{ st = null; } } if(conn != null){ try { conn.close(); } catch (Exception e) { e.printStackTrace(); }finally{ conn = null; } } }
这样写一次两次次还好，如果存在几百个这样的方法，那真的是一件痛苦的事情。每次都要去创建连接，不仅难以管理，而且每次创建连接都需要较长的时间，降低了性能。有没有可能将这些链接统一的管理，每次获取链接时不再自己手动的去获取连接，而是直接的去缓存里面拿连接呢？
2、开始编写自己的数据库连接池
首先编写一些用于获取连接的工具类：
<pre name="code" class="java">package cn.zq.util;
import java.sql.Connection;
import java.sql.DriverManager;
public class JdbcUtil {
private static Connection conn;
static{
try {
/* jdbc4.0规范:
* 如果在jdbc驱动的jar包的
* META-INF/services/java.sql.Driver文件中写入相应的驱动的类名，
* 则Class.forName("com.mysql.jdbc.driver")就可以不用写了，
* 不过为了保险起见，建议这句话最好还是写上
*/
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
return conn;
}
}
每次获取连接时，直接用JdbcUtil.getConnection()去获取连接，相比前面的直接去获取连接要简单点，这个工具类只维护一个Connection，而且每次用完连接还不能关闭该连接，要不然下次再去获取连接再做一些其他的事情时就会抛出com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed异常。多个线程用这个连接，这几个线程的事务也会存在问题。仅仅就这个连接根本就满足不了要求，那么就需要用一个容器来缓存多个连接，改造后：
package cn.zq.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
public class JdbcUtil {
private static ArrayList<Connection> pool = new ArrayList<Connection>();
static{
try {
/* jdbc4.0规范:
* 如果在jdbc驱动的jar包的
* META-INF/services/java.sql.Driver文件中写入相应的驱动的类名，
* 则Class.forName("com.mysql.jdbc.driver")就可以不用写了，
* 不过为了保险起见，建议这句话最好还是写上
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String pwd = "123456";
int initialSize = 3;
for(int i = 0; i < initialSize; i++){
Connection conn = DriverManager.getConnection(url, user, pwd);
pool.add(conn);
}
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static synchronized Connection getConnection(){
return pool.remove(0);
}
}
这样初始化时就可以获取3个连接，这样做还有一个问题：只能从里面拿3个连接，如果再拿第4个连接时就会抛出异常，还需要改造。应该提供一个方法，让程序员手动的还连接package cn.zq.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
public class JdbcUtil2 {
private static ArrayList<Connection> pool = new ArrayList<Connection>();
static{
try {
/* jdbc4.0规范:
* 如果在jdbc驱动的jar包的
* META-INF/services/java.sql.Driver文件中写入相应的驱动的类名，
* 则Class.forName("com.mysql.jdbc.driver")就可以不用写了，
* 不过为了保险起见，建议这句话最好还是写上
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String pwd = "123456";
int initialSize = 3;
for(int i = 0; i < initialSize; i++){
Connection conn = DriverManager.getConnection(url, user, pwd);
pool.add(conn);
}
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static synchronized Connection getConnection(){
return pool.remove(0);
}
public static synchronized void close(Connection conn){
if(conn != null){
pool.add(conn);
}
}
}
虽然这样能解决问题，但是程序员不一定会安装要求去调用这个方法，一般喜欢调用Connection.close()方法，能不能让程序员调用这个方法时就还连接呢？那么就需要对Connection的close方法进行增强，一般有这几个方式：继承、包装、代理。下面就演示下用代理来增强：
package cn.zq.util;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
/**
* @author zq
*
*/
public class JdbcUtil2 {
//Connection pool
private static LinkedList<Connection> pool = new LinkedList<Connection>();
static{
try {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql:///test";
String user = "root";
String pwd = "123456";
int initialSize = 3;
Class.forName(driver);
for(int i = 0; i < initialSize; i++){
final Connection conn = DriverManager.getConnection(url, user, pwd);
//proxy
Object connProxy = Proxy.newProxyInstance(JdbcUtil.class.getClassLoader(),
new Class[]{Connection.class},
new InvocationHandler() {
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
//只对close方法进行增强
if(method.getName().equals("close")){
synchronized (pool) {
pool.addLast((Connection) proxy);
pool.notify();
return null;
}
}
return method.invoke(conn, args);
}
});
pool.addLast((Connection) connProxy);
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
public static Connection getConnection(){
synchronized (pool) {
if(pool.size() == 0){
try {
pool.wait();
} catch (InterruptedException e) {
throw new RuntimeException(e.getMessage(), e);
}
}
Connection conn = pool.removeFirst();;
return conn;
}
}
public static void release(ResultSet rs, Statement st, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
}
}
上面的代码还有可以优化的地方，比如驱动，用户名、密码等等都应该从一个配置文件里面读取，还有上面的连接在类加载的时候进行放到连接池中的，那么为什么不在获取连接的时候进行代理呢？那么就可以减少内存的消耗。改造后如下：
package cn.zq.util;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.Properties;
/**
* @author zq
*
*/
public class JdbcUtil {
//Connection pool
private static LinkedList<Connection> pool = new LinkedList<Connection>();
static{
try {
Properties prop = loadJdbcConfig("jdbc.properties");
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String pwd = prop.getProperty("pwd");
int poolSize = new Integer(prop.getProperty("initialSize"));
Class.forName(driver);
for(int i = 0; i < poolSize; i++){
Connection conn = DriverManager.getConnection(url, user, pwd);
pool.addLast((Connection) conn);
}
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
private static Properties loadJdbcConfig(String filename) throws IOException{
InputStream in = null;
try {
in = JdbcUtil.class
.getClassLoader()
.getResourceAsStream(filename);
Properties prop = new Properties();
prop.load(in);
return prop;
}finally{
if(in != null){
in.close();
}
}
}
public static Connection getConnection(){
synchronized (pool) {
if(pool.size() == 0){
try {
pool.wait();
} catch (InterruptedException e) {
throw new RuntimeException(e.getMessage(), e);
}
}
final Connection conn = pool.removeFirst();;
//proxy
Object connProxy = Proxy.newProxyInstance(JdbcUtil.class.getClassLoader(),
new Class[]{Connection.class},
new InvocationHandler() {
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
if(method.getName().equals("close")){
synchronized (pool) {
pool.addLast(conn);
pool.notify();
return null;
}
}
return method.invoke(conn, args);
}
});
return (Connection) connProxy;
}
}
public static void release(ResultSet rs, Statement st, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
}
}
jdbc.properties
driver = com.mysql.jdbc.Driver url = jdbc:mysql:///contacts?characterEncoding=UTF8 username = root pwd =123456 initialSize=2使用包装来完成上面的事情：
package cn.zq.util;
import java.io.IOException;
import java.io.InputStream;
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.DriverManager;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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;
/**
* @author zq
*
*/
public class JdbcUtil3 {
//Connection pool
private static LinkedList<Connection> pool = new LinkedList<Connection>();
static{
try {
Properties prop = loadJdbcConfig("jdbc.properties");
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String pwd = prop.getProperty("pwd");
int poolSize = new Integer(prop.getProperty("initialSize"));
Class.forName(driver);
for(int i = 0; i < poolSize; i++){
Connection conn = DriverManager.getConnection(url, user, pwd);
pool.addLast((Connection) conn);
}
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
private static Properties loadJdbcConfig(String filename) throws IOException{
InputStream in = null;
try {
in = JdbcUtil3.class
.getClassLoader()
.getResourceAsStream(filename);
Properties prop = new Properties();
prop.load(in);
return prop;
}finally{
if(in != null){
in.close();
}
}
}
public static Connection getConnection(){
synchronized (pool) {
if(pool.size() == 0){
try {
pool.wait();
} catch (InterruptedException e) {
throw new RuntimeException(e.getMessage(), e);
}
}
Connection conn = pool.removeFirst();;
return new ConnectionWrapper(conn);
}
}
public static void release(ResultSet rs, Statement st, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
}
static class ConnectionWrapper implements Connection {
private Connection conn;
public void close() throws SQLException {
synchronized (pool) {
pool.addLast(conn);
pool.notify();
}
}
public ConnectionWrapper(Connection conn) {
this.conn = conn;
}
public <T> T unwrap(Class<T> iface) throws SQLException {
return conn.unwrap(iface);
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return conn.isWrapperFor(iface);
}
public Statement createStatement() throws SQLException {
return conn.createStatement();
}
public PreparedStatement prepareStatement(String sql)
throws SQLException {
return conn.prepareStatement(sql);
}
public CallableStatement prepareCall(String sql) throws SQLException {
return conn.prepareCall(sql);
}
public String nativeSQL(String sql) throws SQLException {
return conn.nativeSQL(sql);
}
public void setAutoCommit(boolean autoCommit) throws SQLException {
conn.setAutoCommit(autoCommit);
}
public boolean getAutoCommit() throws SQLException {
return conn.getAutoCommit();
}
public void commit() throws SQLException {
conn.commit();
}
public void rollback() throws SQLException {
conn.rollback();
}
public boolean isClosed() throws SQLException {
return conn.isClosed();
}
public DatabaseMetaData getMetaData() throws SQLException {
return conn.getMetaData();
}
public void setReadOnly(boolean readOnly) throws SQLException {
conn.setReadOnly(readOnly);
}
public boolean isReadOnly() throws SQLException {
return conn.isReadOnly();
}
public void setCatalog(String catalog) throws SQLException {
conn.setCatalog(catalog);
}
public String getCatalog() throws SQLException {
return conn.getCatalog();
}
public void setTransactionIsolation(int level) throws SQLException {
conn.setTransactionIsolation(level);
}
public int getTransactionIsolation() throws SQLException {
return conn.getTransactionIsolation();
}
public SQLWarning getWarnings() throws SQLException {
return conn.getWarnings();
}
public void clearWarnings() throws SQLException {
conn.clearWarnings();
}
public Statement createStatement(int resultSetType,
int resultSetConcurrency) throws SQLException {
return conn.createStatement(resultSetType, resultSetConcurrency);
}
public PreparedStatement prepareStatement(String sql,
int resultSetType, int resultSetConcurrency)
throws SQLException {
return conn.prepareStatement(sql, resultSetConcurrency);
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
}
public Map<String, Class<?>> getTypeMap() throws SQLException {
return conn.getTypeMap();
}
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
conn.setTypeMap(map);
}
public void setHoldability(int holdability) throws SQLException {
conn.setHoldability(holdability);
}
public int getHoldability() throws SQLException {
return conn.getHoldability();
}
public Savepoint setSavepoint() throws SQLException {
return conn.setSavepoint();
}
public Savepoint setSavepoint(String name) throws SQLException {
return conn.setSavepoint(name);
}
public void rollback(Savepoint savepoint) throws SQLException {
conn.rollback(savepoint);
}
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
conn.releaseSavepoint(savepoint);
}
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return conn.createStatement(resultSetType, resultSetConcurrency,
resultSetHoldability);
}
public PreparedStatement prepareStatement(String sql,
int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
return conn.prepareStatement(sql, resultSetType,
resultSetConcurrency, resultSetHoldability);
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return conn.prepareCall(sql, resultSetType, resultSetConcurrency,
resultSetHoldability);
}
public PreparedStatement prepareStatement(String sql,
int autoGeneratedKeys) throws SQLException {
return conn.prepareStatement(sql, autoGeneratedKeys);
}
public PreparedStatement prepareStatement(String sql,
int[] columnIndexes) throws SQLException {
return conn.prepareStatement(sql, columnIndexes);
}
public PreparedStatement prepareStatement(String sql,
String[] columnNames) throws SQLException {
return conn.prepareStatement(sql, columnNames);
}
public Clob createClob() throws SQLException {
return conn.createClob();
}
public Blob createBlob() throws SQLException {
return conn.createBlob();
}
public NClob createNClob() throws SQLException {
return conn.createNClob();
}
public SQLXML createSQLXML() throws SQLException {
return conn.createSQLXML();
}
public boolean isValid(int timeout) throws SQLException {
return conn.isValid(timeout);
}
public void setClientInfo(String name, String value)
throws SQLClientInfoException {
conn.setClientInfo(name, value);
}
public void setClientInfo(Properties properties)
throws SQLClientInfoException {
conn.setClientInfo(properties);
}
public String getClientInfo(String name) throws SQLException {
return conn.getClientInfo(name);
}
public Properties getClientInfo() throws SQLException {
return conn.getClientInfo();
}
public Array createArrayOf(String typeName, Object[] elements)
throws SQLException {
return conn.createArrayOf(typeName, elements);
}
public Struct createStruct(String typeName, Object[] attributes)
throws SQLException {
return conn.createStruct(typeName, attributes);
}
}
}
java提供了标准的数据库连接池的接口javax.sql.DataSource，定义了数据库连接池的一些方法，读者可以自行完成标准的数据库连接池。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
