数据库连接池Datasource

在三层架构中，DAO层直接与数据库交互，首先要建立与数据库的连接，如果采用下图（a）所示，则用户每次的请求都要创建连接，用完又关闭，而数据库连接的创建和关闭需要消耗较大的资源，因此实际开发中常采用图（b）所示，在应用程序启动时创建一个包含多个Connection对象的连接池，DAO层使用时直接从池子里取一个Connection对象，用完后放回池子，避免了重复创建关闭数据库连接造成的开销。

2、编程实现

Datasource适配器

package jdbc;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

/**
 * 类似于DataSource适配器
 */
public abstract class BaseDataSource implements DataSource{

	public Connection getConnection() throws SQLException {
		return null;
	}

	public Connection getConnection(String username, String password) throws SQLException {
		return null;
	}

	public <T> T unwrap(Class<T> iface) throws SQLException {
		return null;
	}

	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return false;
	}

	public PrintWriter getLogWriter() throws SQLException {
		return null;
	}

	public void setLogWriter(PrintWriter out) throws SQLException {

	}

	public void setLoginTimeout(int seconds) throws SQLException {

	}

	public int getLoginTimeout() throws SQLException {
		return 0;
	}

	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		return null;
	}
} 

  自定义数据源

package jdbc;

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

/**
 * 自定义数据源
 */
public class MyDataSource extends BaseDataSource {

	private ConnectionPool pool = null ;

	private static int MAX = 5 ;

	public MyDataSource(){
		pool = new ConnectionPool();
		initPool() ;
	}

	/**
	 * 初始化连接池
	 */
	private void initPool() {
		try {
			String driverClass = "com.mysql.jdbc.Driver" ;
			String url = "jdbc:mysql://localhost:3306/big6" ;
			String username= "root" ;
			String password = "root" ;
			Class.forName(driverClass);

			for(int i = 0 ; i < MAX ; i ++){
				//原生mysql连接
				Connection conn = DriverManager.getConnection(url,username,password);
				MyConnection myconn = new MyConnection(conn,pool) ;
				pool.addConnection(myconn);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获得连接
	 */
	public Connection getConnection() throws SQLException {
		return pool.getConnection() ;
	}

}

连接池

package jdbc;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

/**
 * 连接池
 */
public class ConnectionPool {

	//集合
	private static List<Connection> list = new ArrayList<Connection>();

	public synchronized Connection getConnection(){
		while(list.isEmpty()){
			try {
				wait();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
		return list.remove(0);
	}

	/**
	 * 添加连接
	 */
	public synchronized void addConnection(Connection conn){
		list.add(conn) ;
		notifyAll();
	}
}

连接适配器

package jdbc;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

/**
 * 连接适配器
 */
public abstract class ConnectionAdaptor implements Connection {
	public Statement createStatement() throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return null;
	}

	public CallableStatement prepareCall(String sql) throws SQLException {
		return null;
	}

	public String nativeSQL(String sql) throws SQLException {
		return null;
	}

	public void setAutoCommit(boolean autoCommit) throws SQLException {

	}

	public boolean getAutoCommit() throws SQLException {
		return false;
	}

	public void commit() throws SQLException {

	}

	public void rollback() throws SQLException {

	}

	public void close() throws SQLException {

	}

	public boolean isClosed() throws SQLException {
		return false;
	}

	public DatabaseMetaData getMetaData() throws SQLException {
		return null;
	}

	public void setReadOnly(boolean readOnly) throws SQLException {

	}

	public boolean isReadOnly() throws SQLException {
		return false;
	}

	public void setCatalog(String catalog) throws SQLException {

	}

	public String getCatalog() throws SQLException {
		return null;
	}

	public void setTransactionIsolation(int level) throws SQLException {

	}

	public int getTransactionIsolation() throws SQLException {
		return 0;
	}

	public SQLWarning getWarnings() throws SQLException {
		return null;
	}

	public void clearWarnings() throws SQLException {

	}

	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return null;
	}

	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return null;
	}

	public Map<String, Class<?>> getTypeMap() throws SQLException {
		return null;
	}

	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {

	}

	public void setHoldability(int holdability) throws SQLException {

	}

	public int getHoldability() throws SQLException {
		return 0;
	}

	public Savepoint setSavepoint() throws SQLException {
		return null;
	}

	public Savepoint setSavepoint(String name) throws SQLException {
		return null;
	}

	public void rollback(Savepoint savepoint) throws SQLException {

	}

	public void releaseSavepoint(Savepoint savepoint) throws SQLException {

	}

	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return null;
	}

	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		return null;
	}

	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		return null;
	}

	public Clob createClob() throws SQLException {
		return null;
	}

	public Blob createBlob() throws SQLException {
		return null;
	}

	public NClob createNClob() throws SQLException {
		return null;
	}

	public SQLXML createSQLXML() throws SQLException {
		return null;
	}

	public boolean isValid(int timeout) throws SQLException {
		return false;
	}

	public void setClientInfo(String name, String value) throws SQLClientInfoException {

	}

	public void setClientInfo(Properties properties) throws SQLClientInfoException {

	}

	public String getClientInfo(String name) throws SQLException {
		return null;
	}

	public Properties getClientInfo() throws SQLException {
		return null;
	}

	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		return null;
	}

	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		return null;
	}

	public void setSchema(String schema) throws SQLException {

	}

	public String getSchema() throws SQLException {
		return null;
	}

	public void abort(Executor executor) throws SQLException {

	}

	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {

	}

	public int getNetworkTimeout() throws SQLException {
		return 0;
	}

	public <T> T unwrap(Class<T> iface) throws SQLException {
		return null;
	}

	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return false;
	}
}

连接装饰类

package jdbc;

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

/**
 * 连接装饰类
 */
public class MyConnection extends ConnectionAdaptor{

	//原生的mysql连接
	private Connection conn ;

	private ConnectionPool pool ;

	public MyConnection(Connection conn , ConnectionPool pool){
		this.conn = conn ;
		this.pool = pool ;
	}

	public Statement createStatement() throws SQLException {
		return conn.createStatement();
	}

	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return conn.prepareStatement(sql);
	}

	public void setAutoCommit(boolean autoCommit) throws SQLException {
		conn.setAutoCommit(autoCommit);
	}

	public void commit() throws SQLException {
		conn.commit();
	}

	public void rollback() throws SQLException {
		conn.rollback();
	}

	public void close() throws SQLException {
		pool.addConnection(this);
	}
}

测试

package jdbc;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;

/**
 */
public class TestPool {
	public static void main(String[] args) throws Exception {
		//创建数据源对象
		DataSource ds = new MyDataSource();
		//得到连接(自定义)
		Connection conn = ds.getConnection();
		conn.setAutoCommit(true) ;
		//
		Statement st = conn.createStatement();

		st.execute("insert into customers(name,age) values('uu',8)") ;
		st.close();
		conn.close();
		System.out.println("xxx");
	}
}