题目

  1. 自定义数据库连接池
  2. 遵循数据库连接池规范去创建数据库连接池

代码

自定义数据库连接池

步骤:

  1. 初始化一个数据库池并向里面添加10个数据库连接
  2. 从连接池获取连接
  3. 当程序完成后,需要将该连接重新放回到连接池

数据库连接池

package databasePool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collections;
import java.util.LinkedList;

import util.DBUtil;

/*
 * 模拟编写一个数据库连接池
 * 步骤:
 * 1:初始化一个数据库池并向里面添加10个数据库连接
 * 2:从连接池获取连接
 * 3:当程序完成后,需要将该连接重新放回到连接池
 * 
 */
public class simpleDatabasePool {
	
		//创建一个数据库连接池,保证线程安全
		//因为会频繁的对数据连接池进行取出和放回的操作,所以用LinkedList作为数据库连接池
		public static LinkedList<Connection> pool=(LinkedList<Connection>)Collections.synchronizedList(new LinkedList<Connection>()); 

		//在数据加载后需在数据库池中添加10个数据库连接对象
		static {
				try {
					for(int i=0;i<10;i++) {
					Connection connection = DBUtil.getConnection();
					pool.add(connection);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		//从连接池取出一个数据库连接
		public static Connection getConnectionFromPool() {
			
			Connection conn=null;
			//判断数据库连接池中是否还有数据库连接
			if(pool.size()>0) {
				conn=pool.removeFirst();
			}		
			return conn;
		}
		//当程序完成后,需要将该连接重新放回到连接池
		public static void release(Connection conn) {
			pool.addLast(conn);
		}
}

DBUtils数据库连接类

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;


/*
 * 
 * 数据库连接,调用properties文件
 * 
 * */
public class DBUtil {
	private static String classDriver;
	private static String url;
	private static String username;
	private static String password;
	static {
		ResourceBundle rb = ResourceBundle.getBundle("db");
		classDriver = rb.getString("classDriver");
		url = rb.getString("url");
		username = rb.getString("username");
		password = rb.getString("password");

		try {
			Class.forName(classDriver);
		} catch (ClassNotFoundException e) {
			System.out.println("driver ok");
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);

	}
}

db.properties数据库连接文件

classDriver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/userlist
username=root
password=@abc1234

规范数据库连接池

MyConnection装饰类

使用装饰者模式来重新装修(扩展)Connection接口

  1. 编写一个类实现与被装饰类相同的接口
  2. 定义一个被包装类类型的变量
  3. 定义构造方法,把被包装类的对象诸如,给被包装类的变量赋值
  4. 对其不需要改写的方法调用原有的方法
  5. 对于需要改写的方法,写上自己改写的方法
package databasePool;

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;
/*
 * 使用装饰者模式来重新装修(扩展)Connection接口
 * 1:编写一个类实现与被装饰类相同的接口
 * 2:定义一个被包装类类型的变量
 * 3:定义构造方法,把被包装类的对象诸如,给被包装类的变量赋值
 * 4:对其不需要改写的方法调用原有的方法
 * 5:对于需要改写的方法,写上自己改写的方法
 * 
 */
public class MyConnection implements Connection {

		private Connection oldConnection;//实际上她是com.mysql.jdbc.Connection
		private LinkedList<Connection> pool;//连接池对象
		
	
	public MyConnection(Connection oldConnection, LinkedList<Connection> pool) {
		   this.oldConnection = oldConnection;
			this.pool = pool;
		}
	//将Connection重新放回到连接池中,不进行关闭
	@Override
	public void close() throws SQLException {


	}

	@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 arg0) 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 arg0, Object[] arg1) 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 arg0, int arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int arg0, int arg1, int arg2) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Struct createStruct(String arg0, Object[] arg1) 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 arg0) 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 arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public String nativeSQL(String arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String arg0, int arg1, int arg2) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String arg0, int arg1, int arg2, int arg3) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int[] arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, String[] arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1, int arg2) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String arg0, int arg1, int arg2, int arg3) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void releaseSavepoint(Savepoint arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void rollback() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void rollback(Savepoint arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setAutoCommit(boolean arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setCatalog(String arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setClientInfo(Properties arg0) throws SQLClientInfoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setClientInfo(String arg0, String arg1) throws SQLClientInfoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setHoldability(int arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setNetworkTimeout(Executor arg0, int arg1) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setReadOnly(boolean arg0) 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 arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setSchema(String arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setTransactionIsolation(int arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setTypeMap(Map<String, Class<?>> arg0) throws SQLException {
		// TODO Auto-generated method stub

	}

}

MyPool据库连接池规范

package databasePool;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.Collections;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import util.DBUtil;
/*
 * 
 * 遵循数据库连接池规范去创建数据库连接池
 * 
 */
public class MyPool implements DataSource {
	
	//创建一个数据库连接池,保证线程安全
	//因为会频繁的对数据连接池进行取出和放回的操作,所以用LinkedList作为数据库连接池
	public static LinkedList<Connection> pool=(LinkedList<Connection>)Collections.synchronizedList(new LinkedList<Connection>()); 

	//在数据加载后需在数据库池中添加10个数据库连接对象
	static {
			try {
				for(int i=0;i<10;i++) {
				Connection connection = DBUtil.getConnection();
				pool.add(connection);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	
	@Override
	public Connection getConnection() throws SQLException {

		Connection conn=null;
		//判断数据库连接池中是否还有数据库连接
		if(pool.size()>0) {
			conn=pool.removeFirst();
		}		
		return conn;
	}

	@Override
	public Connection getConnection(String arg0, String arg1) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}


	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		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;
	}


}

用完后把链接放回池中

package databasePool;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.Collections;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import util.DBUtil;

/*
 * 模拟编写一个数据库连接池
 * 步骤:
 * 1:初始化一个数据库池并向里面添加10个数据库连接
 * 2:从连接池获取连接
 * 3:当程序完成后,需要将该连接重新放回到连接池
 * 
 */
public class MyClosePool implements DataSource {
	
		//创建一个数据库连接池,保证线程安全
		//因为会频繁的对数据连接池进行取出和放回的操作,所以用LinkedList作为数据库连接池
		public static LinkedList<Connection> pool=(LinkedList<Connection>)Collections.synchronizedList(new LinkedList<Connection>()); 

		//在数据加载后需在数据库池中添加10个数据库连接对象
		static {
				try {
					for(int i=0;i<10;i++) {
					Connection connection = DBUtil.getConnection();
					pool.add(connection);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		//从连接池取出一个数据库连接
		public static Connection getConnectionFromPool() {
			
			Connection conn=null;
			MyConnection myConn=null;
			//判断数据库连接池中是否还有数据库连接
			if(pool.size()>0) {
				conn=pool.removeFirst();
				 myConn = new MyConnection(conn,pool);
			}else {
				new RuntimeException("服务器忙,稍后再试!");
			}
			return myConn;
		}
		//当程序完成后,需要将该连接重新放回到连接池
		public static void release(Connection conn) {
			pool.addLast(conn);
		}
		@Override
		public PrintWriter getLogWriter() throws SQLException {
			// TODO Auto-generated method stub
			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() throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
		@Override
		public Connection getConnection(String arg0, String arg1) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}
}

测试类

package databasePool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

public class MyCloseTest {

	public static void main(String[] args) {
		//规范便于理解
		MyClosePool ds=new MyClosePool();
		//自己定义的数据库连接池,可能一眼看不出来是数据库连接池
//		simpleDatabasePool sdp=new simpleDatabasePool();
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn= ds.getConnectionFromPool();
			conn.prepareStatement("insert into .......");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				
//				这里会调用我们自定义的MyConnection类中的close方法将连接放回到数据库连接池
			conn.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}