Java Web基础的查漏补缺——编写一个自己的数据库连接池

梦里梦外; 2021-12-13 19:33 302阅读 0赞

其实，在[《Java Web基础入门第五十七讲 数据库连接池》][Java Web_]这篇文章中，我就已详细介绍过如何编写一个自己的数据库连接池了。本文只不过是再一次的重温。

# 一个非常简单的自定义数据库连接池 #

该小节只是简单模拟一个池子的概念，让大家有个对连接池有个认识。待会我们自己编写的数据库连接池因为要存放一些数据库连接，所以关于如何获取数据库连接的操作，我们就抽取到一个工具类中，例如JDBCUtil类。

package com.meimeixia.util;
    
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    public class JDBCUtil { 
    	
    	static String driverClass = null;
    	static String url = null;
    	static String name = null;
    	static String password= null;
    	
    	static{ 
    		try { 
    			//1. 创建一个属性配置对象
    			Properties properties = new Properties();
    // InputStream is = new FileInputStream("jdbc.properties");
    			
    			//使用类加载器，去读取src底下的资源文件。
    			InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
    			//导入输入流。
    			properties.load(is);
    			
    			//读取属性
    			driverClass = properties.getProperty("driverClass");
    			url = properties.getProperty("url");
    			name = properties.getProperty("name");
    			password = properties.getProperty("password");
    			
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	/** * 获取连接对象 * @return */
    	public static Connection getConn(){ 
    		Connection conn = null;
    		try { 
    			Class.forName(driverClass);
    			//静态代码块 ---> 类加载了，就执行。 java.sql.DriverManager.registerDriver(new Driver());
    			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    			//DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
    			//2. 建立连接 参数一： 协议 + 访问的数据库 ， 参数二： 用户名 ， 参数三： 密码。
    			conn = DriverManager.getConnection(url, name, password);
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}
    		return conn;
    	}
    	
    	/** * 释放资源 * @param conn * @param st * @param rs */
    	public static void release(Connection conn , Statement st , ResultSet rs){ 
    		closeRs(rs);
    		closeSt(st);
    		closeConn(conn);
    	}
    	public static void release(Connection conn , Statement st){ 
    		closeSt(st);
    		closeConn(conn);
    	}
    
    	
    	private static void closeRs(ResultSet rs){ 
    		try { 
    			if(rs != null){ 
    				rs.close();
    			}
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}finally{ 
    			rs = null;
    		}
    	}
    	
    	private static void closeSt(Statement st){ 
    		try { 
    			if(st != null){ 
    				st.close();
    			}
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}finally{ 
    			st = null;
    		}
    	}
    	
    	private static void closeConn(Connection conn){ 
    		try { 
    			if(conn != null){ 
    				conn.close();
    			}
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}finally{ 
    			conn = null;
    		}
    	}
    }

接着，正式来编写我们自己的数据库连接池。一开始先往池子里面放10个连接，即开始就创建10个连接，来的程序可以通过getConnection方法获取连接，用完之后，使用addBack方法归还连接，而且，记得还要扩容。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3llcmVueXVhbl9wa3U_size_16_color_FFFFFF_t_70]  
最后，对我们自己编写的数据库连接池进行测试。

package com.meimeixia.util;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import org.junit.Test;
    
    public class TestPool { 
    
    	@Test
    	public void testPool() { 
    		Connection conn = null;
    		PreparedStatement ps = null;
    		MyDataSource dataSource = new MyDataSource();
    		
    		try { 
    			conn = dataSource.getConnection();
    			
    			String sql = "insert into account values(NULL,'xilali',10)";
    			ps = conn.prepareStatement(sql);
    			ps.executeUpdate();
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} finally { 
    			try { 
    				ps.close();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}
    			//归还连接
    			//conn.close();
    			dataSource.addBack(conn);
    			//JDBCUtil.release(conn, ps);
    		}
    		
    	}
    	
    }

## 自定义数据库连接池出现的问题 ##

以上自定义数据库连接池出现了如下一些问题：

*  对象没有做成单例的。在哪里使用，都需要`new MyDataSource()`，这就会造成有多个对象的情况出现，那就不只一个池子了；
 *  需要额外记住addBack这个方法。sun公司定义的数据库连接池里面并没有这个addBack方法，所以谁需要用我们这个连接池，就需要记住这个方法是用来回收连接对象的；
 *  无法面向接口编程。由于我们的连接池直接定义成了一个类，并且里面还额外添加了一个addBack方法，这就造成了我们无法面向接口编程。即我们再也不能编写如下代码了：
    
        DataSource dataSource = new MyDataSource();//因为DataSource接口里面没有定义addBack方法

## 解决自定义数据库连接池出现的问题 ##

怎么解决以上那些问题呢？这里以addBack方法为切入点。由于多了一个addBack方法，所以使用这个连接池的地方，需要额外记住这个方法，并且还不能面向接口编程。我们不打算添加addBack方法，而是扩展原来的close方法。让close方法不关闭连接，而是把连接对象归还给池子。要想实现这个需求，有以下几种方式：

*  直接修改JDBC里面的那个Connection接口的具体实现，让它的close方法别关闭连接，而是变成回收连接对象。答案是无法办到，因为咱们没办法修改人家的源代码；
 *  既然不能直接修改，那么我们改成自己定义一个类，然后继承那个Connection接口的实现类，然后在它的close方法基础上，加入我们的回收逻辑。答案也是无法办到，因为我们根本不知道Connection接口的实现类是哪一个；
 *  使用装饰者模式来实现；
 *  使用动态代理技术，拦截你对真实业务对象的访问，采用拦截的方式进行增强，这也是终极解决方案。例如Spring AOP（向切面编程）。

下面，我们就以装饰者模式来改进上面编写的自定义数据库连接池。首先，编写一个ConnectionWrap包装类来继承Connection接口。

package com.meimeixia.util;
    
    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.List;
    import java.util.Map;
    import java.util.Properties;
    import java.util.concurrent.Executor;
    
    public class ConnectionWrap implements Connection { 
    	
    	Connection connection = null;
    	List<Connection> list;
    	public ConnectionWrap(Connection connection, List<Connection> list) { 
    		super();
    		this.connection = connection;
    		this.list = list;
    	}
    
    	@Override
    	public void close() throws SQLException { 
    		//connection.close();
    		System.out.println("有人来归还连接对象了，归还之前，池子里面是：" + list.size());
    		list.add(connection);
    		System.out.println("有人来归还连接对象了，归还之后...，池子里面是：" + list.size());
    	}
    	
    	@Override
    	public PreparedStatement prepareStatement(String sql) throws SQLException { 
    		return connection.prepareStatement(sql);
    	}
    
    	@Override
    	public <T> T unwrap(Class<T> iface) throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public boolean isWrapperFor(Class<?> iface) throws SQLException { 
    		// TODO Auto-generated method stub
    		return false;
    	}
    
    	@Override
    	public Statement createStatement() 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 String nativeSQL(String sql) throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public void setAutoCommit(boolean autoCommit) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public boolean getAutoCommit() throws SQLException { 
    		// TODO Auto-generated method stub
    		return false;
    	}
    
    	@Override
    	public void commit() throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public void rollback() throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	
    
    	@Override
    	public boolean isClosed() throws SQLException { 
    		// TODO Auto-generated method stub
    		return false;
    	}
    
    	@Override
    	public DatabaseMetaData getMetaData() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public void setReadOnly(boolean readOnly) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public boolean isReadOnly() throws SQLException { 
    		// TODO Auto-generated method stub
    		return false;
    	}
    
    	@Override
    	public void setCatalog(String catalog) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public String getCatalog() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public void setTransactionIsolation(int level) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public int getTransactionIsolation() throws SQLException { 
    		// TODO Auto-generated method stub
    		return 0;
    	}
    
    	@Override
    	public SQLWarning getWarnings() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public void clearWarnings() throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public Statement createStatement(int resultSetType, int resultSetConcurrency) 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 CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public Map<String, Class<?>> getTypeMap() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public void setTypeMap(Map<String, Class<?>> map) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public void setHoldability(int holdability) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public int getHoldability() throws SQLException { 
    		// TODO Auto-generated method stub
    		return 0;
    	}
    
    	@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 rollback(Savepoint savepoint) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public void releaseSavepoint(Savepoint savepoint) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
    			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 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, 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 Clob createClob() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public Blob createBlob() 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 boolean isValid(int timeout) throws SQLException { 
    		// TODO Auto-generated method stub
    		return false;
    	}
    
    	@Override
    	public void setClientInfo(String name, String value) throws SQLClientInfoException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public void setClientInfo(Properties properties) throws SQLClientInfoException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public String getClientInfo(String name) throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public Properties getClientInfo() throws SQLException { 
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public Array createArrayOf(String typeName, Object[] elements) 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 void setSchema(String schema) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public String getSchema() 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 setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { 
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public int getNetworkTimeout() throws SQLException { 
    		// TODO Auto-generated method stub
    		return 0;
    	}
    	
    }

紧接着，改进我们自定义的数据库连接池。这里，我并没有使用单例模式来设计该类，实际上，这里应使用单例模式。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3llcmVueXVhbl9wa3U_size_16_color_FFFFFF_t_70 1]  
最后，对完善好的自定义数据库连接池进行测试。

package com.meimeixia.util;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import org.junit.Test;
    
    public class TestPool { 
    
    	@Test
    	public void testPool() { 
    		Connection conn = null;
    		PreparedStatement ps = null;
    		MyDataSource dataSource = new MyDataSource();
    		
    		try { 
    			conn = dataSource.getConnection();
    			
    			String sql = "insert into account values(NULL,'xilali',10)";
    			ps = conn.prepareStatement(sql);
    			ps.executeUpdate();
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} finally { 
    			try { 
    				ps.close();
    			} catch (SQLException e) { 
    				e.printStackTrace();
    			}
    			//归还连接
    			//conn.close();
    			//dataSource.addBack(conn);
    			JDBCUtil.release(conn, ps);
    		}
    		
    	}
    	
    }

[Java Web_]: https://blog.csdn.net/yerenyuan_pku/article/details/52356418
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3llcmVueXVhbl9wa3U_size_16_color_FFFFFF_t_70]: /images/20211213/86ddd27b912e4a208f18d19bec338704.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3llcmVueXVhbl9wa3U_size_16_color_FFFFFF_t_70 1]: /images/20211213/02e4a6607d6741cea11bc438483b4553.png