其实，在《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方法归还连接，而且，记得还要扩容。
最后，对我们自己编写的数据库连接池进行测试。
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;
}
}
紧接着，改进我们自定义的数据库连接池。这里，我并没有使用单例模式来设计该类，实际上，这里应使用单例模式。
最后，对完善好的自定义数据库连接池进行测试。
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);
}
}
}
扫一扫
7019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
