编写自己的jdbc数据库连接池

2023-11-05

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，定义了数据库连接池的一些方法，读者可以自行完成标准的数据库连接池。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)