在三层架构中,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"); }}