数据库连接池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");}}